Chain a New Data Source Onboarding Workflow

What This Builds

Onboarding a new source system today takes several hours and gets done a little differently every time, depending on who is doing it and how much time they have. This n8n chain takes a sample extract from the new source, infers a schema, drafts a dbt model skeleton, generates starter tests, and writes a first pass at documentation, each step feeding the next automatically. The four steps happen the same way every time, whether it is your third onboarding this quarter or your first.

Prerequisites

• Comfortable with dbt project conventions (staging, intermediate, marts folders) and dbt's own AI features for generating tests and docs from a model
• A place to run n8n: self-hosted (free, but you manage the server) or n8n's cloud plans (check n8n's current pricing page for tiers, since self-hosted vs cloud changes the cost picture entirely)
• A Claude or equivalent API key for the schema-inference and drafting steps, billed per token with a monthly cap you set yourself
• Total ongoing cost: n8n hosting (free if self-hosted, or a cloud plan if not) plus metered API usage for however many sources you onboard per month, which for most teams stays small since onboarding is not a daily event

The Concept

Think of this as an assembly line with four stations, each one handing its output to the next. Station one looks at a sample of the new data and figures out what columns it has and what type each one is. Station two takes that schema and writes a starter dbt model. Station three looks at the model and writes basic tests (not null, unique, accepted values where it can guess). Station four writes a first-pass description of what the source is and what each field seems to mean. You review and correct the output at the end instead of building all four pieces from nothing.

Build It Step by Step

Part 1: Trigger and schema inference

1. In n8n, start with a Manual Trigger node (or a Webhook node if you want to kick this off from a form or Slack command) that takes a file path or table reference to a sample extract of the new source. Use a masked or limited sample, not a full production pull. If the source has customer, financial, or health data, strip or hash identifying columns before this sample ever reaches the workflow. Nothing that looks like a real customer record should reach the AI step.
2. Add a node to read the sample (a Read Binary File node for a CSV, or a database query node if the sample already landed in a staging table).
3. Add an AI Agent or HTTP Request node calling Claude with this prompt:

You are inferring a schema from a sample data extract for a new source system. You will receive column headers and 20-50 sample rows. For each column, infer: a likely data type (string, integer, float, boolean, timestamp, date), whether it looks like it could be a natural key or part of one, and a one-line guess at what the column represents based on its name and values. If a column's purpose is genuinely unclear from the sample, say "unclear from sample" rather than guessing. Output as a structured list, one entry per column.

Part 2: Draft the model, tests, and docs

1. Chain a second AI node that takes the schema inference output and drafts a dbt staging model SQL skeleton: a SELECT with renamed and cast columns, following your project's staging naming convention (source system prefix, snake_case column names).
2. Chain a third AI node that takes the drafted model and writes a starter schema.yml with not_null and unique tests on any column flagged as a likely key, plus a placeholder test block for anything that looked like an enum or status field.
3. Chain a fourth AI node that writes a first-pass model description and column descriptions for the same schema.yml, explicitly marking anything based on a name-only guess so a reviewer knows what to double check.

Part 3: Land the output

1. Add a node that writes the drafted model SQL, schema YAML, and a short markdown summary to a new branch or folder in your dbt repo (via a Git node, or simply drop the files somewhere a human picks them up for a PR).
2. Post a summary to Slack or email with a link to the draft, so whoever requested the new source knows it is ready for review, not ready to ship.

Real Example: Onboarding a new payments vendor extract

Setup: A finance team requests a new payments processor as a data source. A masked sample of 40 rows (customer IDs hashed) is dropped into the workflow's watched folder and the workflow runs manually the first time.

Input: Column headers like txn_id, cust_hash, amt_cents, status, created_ts, merchant_code.

Output: A schema inference marking txn_id as a likely primary key, amt_cents as integer (amount in cents, flagged "likely" not "confirmed"), status as a probable enum. A staging model skeleton with cast and renamed columns, a schema.yml with unique and not_null tests on txn_id, and a description noting merchant_code is "unclear from sample, confirm with source team."

Time saved: Turns a 3 to 4 hour manual onboarding into roughly 45 minutes of review and correction against the drafted output.

What to Do When It Breaks

• Workflow runs but the drafted model has wrong data types → Schema inference from a small sample is a guess, not a guarantee. Always pull a larger sample (200+ rows) if the first pass looks shaky on a specific column, and never ship the drafted model without a human confirming types against the source system's actual documentation if one exists.
• You wire this up once and forget to check it for a month, and it turns out the AI node's API key expired weeks ago → This is the failure that hides easily on a workflow you only trigger occasionally. Turn on n8n's built-in error workflow feature (a separate workflow that fires when any other workflow errors) and route it to a Slack channel, so an expired key or a broken node surfaces immediately instead of silently failing the next time someone tries to onboard a source.
• AI drafts tests that do not actually match the real constraints of the source → Treat every generated test as a draft claim, not a fact. Run the draft dbt tests against the real (not sample) data once during review, before merging, to catch a unique test that fails against the full dataset.
• Column descriptions are confidently wrong instead of flagged as uncertain → Tighten the schema-inference prompt to be more insistent about flagging uncertainty, and spot check a few "confirmed"-sounding descriptions against whatever source documentation exists.

Variations

• Simpler version: Skip the automated schema inference and start the chain from a human-provided column list, keeping just the model, test, and doc drafting steps automated.
• Extended version: Add a step that opens a draft pull request directly instead of just dropping files, so the review happens in your normal PR flow.

What to Do Next

• This week: Run the chain against a source you have already onboarded manually, and compare the draft output to what you actually built, to calibrate how much to trust it.
• This month: Add your team's specific naming conventions and common source patterns into the prompts so the drafts need less correction over time.
• Advanced: Feed the finished source's lineage into the "Auto Draft Schema Change Impact Analysis From Lineage" workflow so future changes to it get the same downstream-impact check.

Advanced guide for Data Engineer professionals. These techniques use more sophisticated AI features that may require paid subscriptions.