Generate a dbt Model from a Spec with Schema in Context

This playbook shows the context-rich prompt pattern and the verification loop that separates a mergeable model from a plausible-looking guess.

When to reach for this

Reach for this whenever you are starting a new dbt model (or plain SQL transformation) from a written spec, a ticket, or a stakeholder request, and the target tables already exist with known schemas. It also fits rebuilding a model you inherited but do not want to reverse-engineer by hand.

Triggers:

• •
  A new mart or staging model with a clear grain and known sources.
• •
  A source-to-target mapping document that needs to become SQL today.
• •
  A stakeholder request specific enough that the only remaining work is writing the query.

Skip it for one-line changes to an existing model — editing directly is faster than reviewing a regenerated file.

The workflow

1. 1.

   Collect real context before prompting. Pull the DDL (or information_schema output) for every source table, plus 5–10 sample rows per table with sensitive values masked. Assistants hallucinate column names constantly when you make them guess; they almost never do when the schema is in the prompt.

2. 2.

   State the grain, the sources, and the rules — then prompt.

   Copy

   Write a dbt model named fct_orders_daily.
   
   Sources (dbt refs): stg_orders, stg_payments, stg_customers.
   Schemas and 5 sample rows for each are pasted below.
   
   Requirements:
   - Grain: one row per order_id. Assert this explicitly.
   - Columns: order_id, customer_id, order_date, total_amount
     (sum of successful payments only, payment_status = 'success'),
     payment_method_count, first_order_flag (boolean: customer's
     first-ever order by order_date, ties broken by order_id).
   - Use CTEs with one purpose each; no subqueries in SELECT.
   - Handle orders with zero successful payments: total_amount = 0, not NULL.
   - ANSI SQL only; no vendor functions.
   - If any requirement is ambiguous, ask before writing.
   
   [paste DDL + sample rows here]
   

3. 3.

   Read the output as a reviewer, not a consumer. Check the join keys against the schemas you pasted, confirm every business rule from the spec appears in the SQL, and look for silent additions — assistants love inventing helpful extra columns you did not ask for.

4. 4.

   Verify the grain immediately. Before anything else, run the one query that catches most AI-generated model bugs:

   Copy

   SELECT order_id, COUNT(*) AS n
   FROM fct_orders_daily
   GROUP BY order_id
   HAVING COUNT(*) > 1;
   

   Zero rows or the model is wrong — usually a join that fans out on payments.

5. 5.

   Test the edge cases you specified. Query for an order with zero successful payments and confirm total_amount is 0. Query a customer's first order and confirm the flag. Every rule you wrote in the prompt gets one confirming query.

6. 6.

   Iterate in the same conversation, scoped. Paste failing query results back with "fix only the payment aggregation, change nothing else." Regenerating the whole model on every fix erases your earlier review.

7. 7.

   Generate the schema YAML last. Ask the assistant for unique/not_null tests on the grain key and accepted_values where the spec named enums, then run dbt build --select fct_orders_daily.

Verify before you trust it

• •
  Grain check (step 4) returns zero duplicates.
• •
  Row count is explainable: SELECT COUNT(*) on the model vs the driving source table, with a reason for any difference.
• •
  Aggregate spot-check: total of total_amount matches a hand-written sum over the payments source for one sample day.
• •
  Every business rule in the spec has a confirming query with expected output written down before you run it.
• •
  dbt build passes including the generated tests; no test was weakened to make it pass.

Pitfalls

• Fan-out joins that inflate aggregates. A one-to-many join before a SUM silently doubles revenue. The grain check catches it; skipping the grain check ships it.
• Hallucinated columns from stale context. If you paste an old DDL, the assistant will confidently use columns that no longer exist. Pull schemas fresh, from the warehouse.
• NULL-handling improvisation. Unspecified NULL behavior gets an arbitrary choice (COALESCE here, filter there). Specify it, or the model's behavior is whatever the assistant felt like.
• Unasked-for "improvements." Extra columns, window functions, or ordering clauses you never requested add review surface and sometimes cost. The "add nothing I did not ask for" line in the prompt exists for a reason.
• Timezone drift on date columns.order_date derived from a timestamp is off-by-one near midnight unless you state the timezone rule explicitly.

Tools

• dbt Copilotgenerates SQL, tests, and docs directly from project metadata, so schema context comes free; requires a paid dbt platform plan.
• Claude Code / Cursorrepo-aware agents that read your existing staging models and match project conventions; the daily driver on dbt Core projects.
• Databricks Genie Codethe same workflow inside Databricks notebooks and the SQL editor, with workspace metadata as context; free on Databricks Free Edition.

Client-safe variant

On client engagements where the MSA prohibits pasting client schemas into public AI tools, run the identical workflow through an assistant in approved tenancy — Amazon Bedrock or Azure OpenAI inside the client's own cloud account — or a local model via Ollama for fully offline drafting. Before prompting, replace client table and column names with synthetic stand-ins (customer → party, real product lines → generic categories) and mask all sample-row values; keep a local mapping file to translate the generated SQL back. The verification queries in this playbook run against the real warehouse either way, so the safety of the model never depends on which assistant drafted it.