Lab 01 · AI-accelerated dbt build-out

The lab ships a frozen "AI draft" of the project that contains three seeded defects you must not fix until your verification chain finds them — a NULL-handling trap, a join fan-out, and a unit error. Each one is the kind of mistake assistants make constantly on data code because the SQL compiles and runs; only tests and reconciliation queries expose that the numbers are wrong. The skill this lab trains is not writing dbt models — it is never promoting generated models that have not survived tests plus reconciliation against known-good totals.

Time & tier

~150 minutes hands-on · Core tier (maps to module M1 · AI-Accelerated Pipeline Development).

Setup

Everything runs locally on DuckDB — no warehouse account, no credentials, $0. You need Python 3.11–3.13. Create a working folder and install:

mkdir shoplite-lab && cd shoplite-lab
python3 -m venv .venv && source .venv/bin/activate
pip install dbt-duckdb duckdb

dbt-duckdb gives you the full dbt workflow (seeds, models, tests, docs) against a local file database. Everything you build here transfers verbatim to dbt on Snowflake, BigQuery, or Databricks — only profiles.yml changes.

Create the project skeleton and the data generator. First, shoplite/dbt_project.yml:

name: shoplite
version: "1.0.0"
profile: shoplite
model-paths: ["models"]
seed-paths: ["seeds"]
models:
  shoplite:
    staging: {+materialized: view}
    marts: {+materialized: table}

Then shoplite/profiles.yml (kept in the project dir for the lab; use ~/.dbt/ at work):

shoplite:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: shoplite.duckdb
      threads: 4

Then make_raw_data.py next to the shoplite/ folder — it writes deterministic raw CSVs into shoplite/seeds/ and prints the known-good totals you will reconcile against later:

"""Generate the ShopLite raw e-commerce data. Deterministic: same files every run."""
import csv, random, os
random.seed(42)
OUT = os.path.join(os.path.dirname(__file__), "shoplite", "seeds")
os.makedirs(OUT, exist_ok=True)

N_CUST, N_ORD = 200, 1000
statuses = ["completed"] * 70 + ["shipped"] * 15 + ["cancelled"] * 10 + [""] * 5  # "" -> NULL status
with open(f"{OUT}/raw_customers.csv", "w", newline="") as f:
    w = csv.writer(f); w.writerow(["customer_id", "email", "signup_date", "country"])
    for c in range(1, N_CUST + 1):
        w.writerow([c, f"user{c}@example.com",
                    f"2024-{random.randint(1,12):02d}-{random.randint(1,28):02d}",
                    random.choice(["IN", "US", "DE", "SG"])])

orders, payments, items = [], [], []
pay_id = item_id = 0
for o in range(1, N_ORD + 1):
    cust = random.randint(1, N_CUST)
    status = random.choice(statuses) or None
    day = f"2025-{random.randint(1,6):02d}-{random.randint(1,28):02d}"
    orders.append([o, cust, day, status or ""])
    n_items = random.randint(1, 4)
    order_total_cents = 0
    for _ in range(n_items):
        item_id += 1
        qty, unit = random.randint(1, 3), random.choice([499, 999, 1999, 4999])
        items.append([item_id, o, f"SKU-{random.randint(100,150)}", qty, unit])
        order_total_cents += qty * unit
    if status != "cancelled":
        if random.random() < 0.08:  # split payment: two rows for one order
            pay_id += 1; half = order_total_cents // 2
            payments.append([pay_id, o, day, half, "card"])
            pay_id += 1
            payments.append([pay_id, o, day, order_total_cents - half, "wallet"])
        else:
            pay_id += 1
            payments.append([pay_id, o, day, order_total_cents, "card"])

for name, header, rows in [
    ("raw_orders", ["order_id", "customer_id", "order_date", "status"], orders),
    ("raw_order_items", ["order_item_id", "order_id", "sku", "quantity", "unit_price_cents"], items),
    ("raw_payments", ["payment_id", "order_id", "paid_date", "amount_cents", "method"], payments),
]:
    with open(f"{OUT}/{name}.csv", "w", newline="") as f:
        w = csv.writer(f); w.writerow(header); w.writerows(rows)

total = sum(p[3] for p in payments)
print(f"orders={len(orders)} payments={len(payments)} items={len(items)}")
print(f"KNOWN-GOOD total paid: {total} cents = ${total/100:,.2f}")

Run python make_raw_data.py. Expected output: orders=1000 payments=955 items=2494 and KNOWN-GOOD total paid: 9768021 cents = $97,680.21. Write that dollar figure down — it is your ground truth for the whole lab.

Now install the "AI draft". In a real sprint you would generate these with your assistant (Claude Code, Cursor, or GitHub Copilot) by pasting the schema plus 10 sample rows per table and asking for staging and mart models. So that every learner debugs the same defects, the lab freezes one representative draft. Create these five files exactly as shown — do not fix anything you notice.

shoplite/models/staging/stg_customers.sql:

select
    customer_id,
    lower(email) as email,
    cast(signup_date as date) as signup_date,
    country
from {{ ref('raw_customers') }}

shoplite/models/staging/stg_orders.sql:

select
    order_id,
    customer_id,
    cast(order_date as date) as order_date,
    status
from {{ ref('raw_orders') }}
where status <> 'cancelled'

shoplite/models/staging/stg_payments.sql:

select
    payment_id,
    order_id,
    cast(paid_date as date) as paid_date,
    amount_cents,
    method
from {{ ref('raw_payments') }}

shoplite/models/marts/fct_orders.sql:

select
    o.order_id,
    o.customer_id,
    o.order_date,
    o.status,
    p.amount_cents / 100.0 as order_revenue
from {{ ref('stg_orders') }} o
left join {{ ref('stg_payments') }} p on o.order_id = p.order_id

shoplite/models/marts/dim_customers.sql:

select
    c.customer_id,
    c.email,
    c.country,
    c.signup_date,
    count(distinct o.order_id) as lifetime_orders,
    coalesce(sum(p.amount_cents), 0) as lifetime_value_usd
from {{ ref('stg_customers') }} c
left join {{ ref('stg_orders') }} o on c.customer_id = o.customer_id
left join {{ ref('stg_payments') }} p on o.order_id = p.order_id
group by 1, 2, 3, 4

Finally the test definitions — shoplite/models/staging/schema.yml:

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]
  - name: stg_payments
    columns:
      - name: payment_id
        data_tests: [unique, not_null]
      - name: order_id
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_orders')
                field: order_id

and shoplite/models/marts/schema.yml:

version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]
  - name: dim_customers
    columns:
      - name: customer_id
        data_tests: [unique, not_null]

Guided steps

1. 1.

   Start a defect log. Create defect-log.md with columns: defect · which check caught it · why the SQL still ran cleanly · fix. You will fill three rows.

2. 2.

   Optional but recommended — generate your own draft first. Before running the frozen draft, paste the four seed headers plus 5 rows each into your assistant and prompt: "Build dbt staging models and an fct_orders + dim_customers mart for this schema. Orders can have split payments. Amounts are in cents. Exclude cancelled orders from facts." Diff its output against the lab draft — you will usually find at least one of the same three bugs in your own generation, which is the point.

3. 3.

   Run 1 — build and let the tests speak. From inside shoplite/, run dbt build --profiles-dir .. The seeds load, staging builds, and then: FAIL 53 relationships_stg_payments_order_id__order_id__ref_stg_orders_ — 53 payment rows reference orders that do not exist in stg_orders, and both mart models get skipped. Investigate before touching code: SELECT status, count(*) FROM raw_orders GROUP BY 1 in the DuckDB CLI (duckdb shoplite.duckdb) shows ~52 orders with a NULL status. In SQL, status <> 'cancelled' evaluates to NULL for NULL statuses, so the WHERE clause silently drops them — and those orders have payments. This is Defect 1: the classic three-valued-logic trap that AI drafts hit constantly. Fix stg_orders.sql to where status is null or status <> 'cancelled' (or coalesce(status, '') <> 'cancelled' with a comment). Log it.

4. 4.

   Run 2 — the fan-out. Rebuild. Staging is green; now FAIL 58 unique_fct_orders_order_id. Fifty-eight order_ids appear twice in the fact table. Confirm the mechanism: SELECT order_id, count(*) FROM fct_orders GROUP BY 1 HAVING count(*) > 1 LIMIT 5, then look those orders up in raw_payments — each has two payment rows (split payments). Joining a one-per-order table directly to a many-per-order table multiplies the grain. This is Defect 2, and it is the single most common AI join error because the join condition is correct — only the grain is wrong. Fix by pre-aggregating in a CTE:

   Copy

   with order_payments as (
       select order_id, sum(amount_cents) as paid_cents
       from {{ ref('stg_payments') }}
       group by 1
   )
   select
       o.order_id, o.customer_id, o.order_date, o.status,
       coalesce(p.paid_cents, 0) / 100.0 as order_revenue
   from {{ ref('stg_orders') }} o
   left join order_payments p on o.order_id = p.order_id
   

   Rebuild until you see PASS=18 ERROR=0. Log it.

5. 5.

   Green does not mean correct. All 18 checks pass, but schema tests only verify structure. Now run the reconciliation audits — the checks that need business knowledge. In the DuckDB CLI:

   Copy

   -- Audit 1: row-count parity (source vs staging, matching the intended filter)
   select
     (select count(*) from raw_orders where status is null or status <> 'cancelled') as src,
     (select count(*) from stg_orders) as stg;
   -- Audit 2: revenue reconciliation against the generator's known-good total
   select round(sum(order_revenue), 2) as fct_total from fct_orders;
   select round(sum(lifetime_value_usd), 2) as dim_total from dim_customers;
   

   Audit 1 returns 897 | 897 — parity. Audit 2: fct_total is 97680.21, matching your known-good figure to the cent. But dim_total returns 9768021.0 — one hundred times too big. This is Defect 3: dim_customers sums amount_cents into a column named lifetime_value_usd without dividing by 100. No schema test can catch a unit error; only reconciliation against an independent total can. Fix (/ 100.0), rebuild, re-run the audits until both totals read 97680.21. Log it.

6. 6.

   Make the audits permanent. Convert Audit 2 into a dbt test so the next AI draft cannot re-introduce the bug: create tests/assert_ltv_matches_payments.sql containing select 1 from (select round(sum(lifetime_value_usd),2) a from {{ ref('dim_customers') }}) d, (select round(sum(amount_cents)/100.0,2) b from {{ ref('stg_payments') }}) p where d.a <> p.b. A singular test passes when it returns zero rows. Rebuild and confirm 19 green checks.

7. 7.

   Docs pass. Ask your assistant to draft description: fields for every model and column in the two schema.yml files from the SQL alone, then correct any description that states business meaning the assistant guessed wrong (it will invent something for lifetime_value_usd — check it says paid value, post-fix, excluding cancelled orders). Run dbt docs generate and confirm the lineage graph shows staging feeding both marts.

Verification

• •
  dbt build --profiles-dir . completes with 19 checks passing, 0 errors.
• •
  Row-count parity audit returns identical source and staging counts (897 = 897).
• •
  sum(order_revenue) in fct_orders and sum(lifetime_value_usd) in dim_customers both equal the generator's printed total ($97,680.21).
• •
  defect-log.md has three rows, each naming the check that caught the defect and why the SQL executed cleanly anyway.
• •
  Re-introduce the fan-out (revert step 4) and confirm the build fails — your safety net is real.

Completion artifact

The shoplite/ repo with fixed models, the singular reconciliation test, generated docs, and defect-log.md. Self-grading rubric (pass = 8/10):

• Defect capture (0–3): all three seeded defects logged with the correct catching mechanism = 3.
• Fix quality (0–2): NULL handling explicit and commented; fan-out fixed by pre-aggregation, not select distinct = 2.
• Reconciliation thinking (0–2): audits reconcile against an independent total, not against the model's own output = 2.
• Permanence (0–2): at least one audit converted into a committed dbt test that fails on regression = 2.
• Docs honesty (0–1): AI-drafted descriptions corrected where the assistant guessed business meaning = 1.

Stuck?

• •
  dbt build cannot find the profile: you are not inside shoplite/, or you dropped --profiles-dir ..
• •
  Seeds fail to load: re-run python make_raw_data.py from the folder above shoplite/ and check shoplite/seeds/ contains four CSVs.
• •
  Your failure counts differ slightly: confirm random.seed(42) is the first executable line of the generator and you have not re-ordered the random calls.
• •
  DuckDB file is locked: close any open duckdb shoplite.duckdb CLI session before running dbt.

Client-safe variant

On client engagements where schemas and data cannot enter public AI tools, run the identical workflow with the AI step pointed at an approved endpoint: the client's own tenancy (Amazon Bedrock or Azure OpenAI) or a local model via Ollama for the codegen and docs steps. Everything that makes this lab safe is deterministic and local — dbt, DuckDB, tests, and reconciliation queries see no LLM at all. If even the schema is restricted, use the synthetic-stand-in pattern: rename tables and columns to neutral equivalents before prompting, generate the models, then rename back with a scripted mapping. The verification chain is what makes AI assistance defensible under a client MSA: every generated model passes tests and reconciles against totals you can show the client.

Answer key (don't read before attempting)

1. 1Defect 1 (caught by the relationships test, 53 failures):where status <> 'cancelled' in stg_orders drops the ~52 NULL-status orders because NULL <> 'cancelled' is NULL, not TRUE. Their 53 payment rows became orphans. Fix: where status is null or status <> 'cancelled'.
2. 2Defect 2 (caught by the unique test on fct_orders, 58 failures): joining stg_payments (payment grain) directly onto orders duplicates every split-payment order. Fix: aggregate payments to order grain in a CTE before joining.
3. 3Defect 3 (caught only by revenue reconciliation):dim_customers sums amount_cents into lifetime_value_usd without / 100.0, inflating LTV 100x while every schema test stays green. Fix: divide, then reconcile both marts to $97,680.21.