AI-assisted data engineering · worked example · synthetic data
An AI assistant proposes and builds the model. I decide at every step, and the checks run on real numbers.
This page walks through one modeling task on a small, invented company, in seven steps. At each step it shows what the assistant proposed, what was checked, and what I accepted, changed or rejected. The checks, tables and numbers on this page are computed from the data, not typed in.
I work with AI across the whole data engineering workflow, and I'm helping others on my team adopt it.
Worked example · not a transcript of real workSynthetic dataDefects were seeded on purposeEvery number computed
Before you read
This is an illustration of the workflow, built for this page. The company (“Fictional Spirits Co.”) and its data are invented, and reuse the synthetic data behind the talk-to-data demo. The steps and the accept / change / reject decisions are illustrative: they were written to show how the work goes, they are not a log of a real session, and no real system or data is involved. What is real: the SQL, the checks, and every number, computed from the data by a build script that is not published (the results are pre-computed into results.json).
The workflow. An AI coding assistant in the editor, plus a small script that hands it a read-only connection to the data, with schema and query access. The assistant proposes models and writes SQL, then helps validate the data and the joins. I orchestrate: I set the scope, review each proposal, and decide what goes forward.
What the assistant does not do. It does not choose what to trust. A check that fails stays failed until the cause is fixed or the owner of the data signs off.
The example in numbers
7
source tables
5,483
source rows
16
automated checks
6 of 6
seeded defect types caught
5 → 1
failing model checks, v1 → v2
Source: data/results.json, computed by the build script from the synthetic tables in data/source/ (one CSV per table). The defects were seeded by me, and the checks were written knowing what kinds of fault to look for, so “6 of 6” shows the checks work as designed. It is not a claim that they would find faults nobody thought of.
The workflow, with the human decision at each step
Seven steps. The right-hand column is the decision point: what I accepted, changed or rejected. Draft v1 is the assistant’s initial proposal, and it is kept in the example so the checks can be run against it.
STEP 1 OF 7
Connect (read-only)
AI proposed
Proposed using the connector's two commands, describe and query, and asked whether it could create a scratch table in the source database for intermediate results.
What was checked
The connector took one SELECT per call, capped results at 200 rows, and logged every statement. A write attempted directly against the source account was refused by the account itself (see Guardrails).
My decision
Accepted Read-only account and the 200-row cap.
Rejected Scratch tables in the source database. Intermediate work goes to a scratch copy.
STEP 2 OF 7
Inspect schemas
AI proposed
Listed 7 tables and 34 columns with row counts and declared keys, noted that invoices, movement and inventory all sit at month × region × SKU, and proposed profiling the grain of the invoice lines.
What was checked
1,812 invoice lines against 1,800 distinct month-region-SKU keys once the SKU is trimmed and upper-cased. 6 movement rows with a null key. 9 invoice lines whose SKU has no exact product match. All three were queued for proper tests.
My decision
Accepted Invoice grain is month × region × SKU.
Changed Profile the trimmed, upper-cased SKU, not the raw value. The assistant had profiled the raw value.
STEP 3 OF 7
Propose model
AI proposed
Draft v1: dim_brand, dim_product, dim_region, dim_month, and one fact_sales_monthly at month × region × SKU holding money, cases, inventory and promotion type.
What was checked
Grain and key written down for every table so each can be tested. Compared against the source tables in the diagram below.
My decision
Accepted Star shape, and the grain of the sales fact.
Changed Fold brand into the product dimension (12 products, 5 brands).
Rejected Inventory and promotion type on the sales fact. Inventory is a month-end level, and promotions have their own grain. Both are split out in v2.
STEP 4 OF 7
Write SQL
AI proposed
Wrote model_v1.sql: four dimensions and the fact, with inner joins to product, region and movement and a left join to promotions.
What was checked
The SQL ran in the scratch copy without an error, and produced 1,803 fact rows from 1,812 source lines. A clean run said little on its own.
My decision
Accepted Dimension SQL as written.
Rejected Inner joins on the fact: rows can disappear with no error. Held for the join checks rather than rewritten by hand.
STEP 5 OF 7
Validate data
AI proposed
Proposed source checks S1–S7 (duplicate keys, SKU mismatch, null keys, overlapping promotions, cases outliers, region keys, amount arithmetic) and ran them through the connector.
What was checked
6 of 7 flagged something, and each count matched the seeded defect exactly (6 of 6 seeded defect types caught). S6 (region keys) was clean.
My decision
Accepted Trim and upper-case the SKU in staging (S2).
Accepted Keep the lowest-numbered line per key and hold the rest in a review table (S1).
Rejected Filling a missing region on movement rows from other columns (S3). The rows wait in a review table.
Changed Amount mismatches (S7): the assistant proposed recomputing net sales. Kept the source value and flagged the row.
Accepted Overlapping promotions (S4) and the ten-fold depletions (S5) go to the data owner unchanged.
STEP 6 OF 7
Validate joins
AI proposed
Proposed the model checks M1–M9: row-count and total reconciliation, fan-out at the declared grain, source keys dropped by a join, null keys, orphaned keys.
What was checked
On v1, row counts differed by only 9, but 1,812 − 15 dropped + 6 duplicated by promotions = 1,803: two faults partly cancelled. Net sales were short by 1,794,853. Fan-out: 18 extra rows.
My decision
Accepted Fan-out and dropped keys are blocking errors.
Changed A row count alone is not enough. Reconcile on totals and on keys too (M2, M6).
STEP 7 OF 7
Iterate
AI proposed
Rewrote the model as v2: staging with de-duplication, review tables, a promotion bridge, a separate inventory fact, left joins.
What was checked
All checks re-run on v2: 7 of 9 model checks pass, 1 warns and 1 fails. The fact table matches the hand-written reference on all 1,800 of 1,800 keys.
My decision
Accepted v2 goes to the data owner for review, with the review tables.
Rejected Marking M7 as passed. It stays red until the four source rows are corrected upstream.
Entity diagram and proposed model
Both diagrams are generated from the schema and the model SQL, not drawn by hand. Arrows point from the table holding a key to the table it refers to.
Source: 7 tables, 34 columns
Foreign keys are declared but not enforced when the data is loaded, so nothing stops a mismatched SKU. The checks below exist because of that.
Proposed dimensional model, v2 (the version after review)
Two facts at the same grain: sales are additive; inventory is a month-end level and is kept apart, so it is never summed across months. Promotions sit in a bridge at their own grain, so joining them cannot multiply sales rows. SQL: model_v1.sql, model_v2.sql, source_schema.sql.
Computed checks, including the seeded defects
All 16 checks are in one SQL file, checks.sql, and the build ran that exact text. 14 were in the initial set; two (S5, M9) were added while iterating.
On the source data (null, duplicate-key and validity tests)
ID
Check
Set
Result
Status
Seeded
S1
Duplicate grain keys in invoice lines (extra rows per month, region, SKU)
1
12
fail
12
S2
Invoice lines whose SKU does not match a product exactly
1
9
fail
9
S3
Movement rows with a missing key (month, region or SKU)
1
6
fail
6
S4
Overlapping promotions for the same SKU and region
1
5
fail
5
S5
Depletions above 4x the SKU-region average (possible unit error)
2
3
warn
3
S6
Invoice lines whose region is not in the region table
1
0
pass
—
S7
Invoice lines where net sales differs from gross sales minus discounts
1
4
fail
4
Result is the number of offending rows; zero passes. S5 is a warning (a threshold, not a proven error), so it shows as warn.
On the model: row-count reconciliation, fan-out and key tests, v1 and v2
ID
Check
Set
v1 result
v1
v2 result
v2
M1
Row count: source invoice lines vs model rows plus quarantined rows
1
1,812 vs 1,803
fail
1,812 vs 1,812
pass
M2
Net sales total: source vs model plus quarantined
1
769,672,150 vs 767,877,297
fail
769,672,150 vs 769,672,150
pass
M3
Join fan-out: fact rows beyond one per month, region, SKU
1
18
fail
0
pass
M4
Null keys in the fact table
1
0
pass
0
pass
M5
Fact rows with no matching product or region
1
0
pass
0
pass
M6
Source month-region-SKU keys missing from the fact table (dropped by a join)
1
15
fail
0
pass
M7
Fact rows where net sales differs from gross sales minus discounts
1
4
fail
4
fail
M8
Duplicate product keys in the product dimension
1
0
pass
0
pass
M9
Fact rows with no movement match (shipments and depletions are null)
2
0
pass
6
warn
M1 and M2 show two numbers: source, then model plus quarantined rows. v2 has 12 lines held in a review table (5,051,426 of net sales), and the checks account for them.
What the checks caught in v1. Promotion overlaps multiplied 6 sales rows at join time, the inner joins silently dropped 15 keys, and the model ended with 1,803 rows for 1,812 source lines. The row-count difference was only 9 because those faults partly cancelled, and only the key and total checks exposed them.
What v2 still fails, on purpose. M7 stays red: 4 source rows where net sales does not equal gross minus discounts. The model keeps the source values and flags them. M9 warns on 6 sales rows with no movement match, the rows whose movement keys are missing in the source.
Seeded defects vs what the source checks found
ID
Defect seeded
Rows seeded
Check
Found
Match
D1
Duplicate invoice lines (double load)
12
S1
12
match
D2
SKU written with different case or trailing space
9
S2
9
match
D3
Movement rows with a missing region key
6
S3
6
match
D4
Overlapping promotions for one SKU and region
5
S4
5
match
D5
Net sales that does not equal gross minus discounts
4
S7
4
match
D6
Depletions inflated ten-fold (unit error)
3
S5
3
match
No check flagged anything I had not seeded (no false positives on this data), and the one clean check, S6, was clean. That says little about real data, which has faults nobody planted.
Guardrails
Read-only access
The connection uses an account with read permission only. The assistant’s tools are two commands, describe and query. The script accepts a single SELECT (or WITH … SELECT) per call and refuses anything else. Even if the script were bypassed, the account itself cannot write.
Row limits
Every result is capped at 200 rows, so the assistant works from aggregates and samples rather than pulling tables into the conversation. Bulk copies are not part of the workflow. Models are built in a scratch copy, never in the source.
Credentials stay out of prompts
The credential is held by the script on my machine, in the environment. The assistant calls the script and never sees the credential, so it is not in the prompt, the files, or the log. The log keeps the purpose and the SQL text of each call. Rules in plain text: connector_rules.txt.
What the build actually did to test these rules (40 statements logged, 3 refused, 1 truncated by the cap):
Attempt
Outcome
UPDATE
refused by connector
DROP
refused by connector
two statements
refused by connector
direct write, bypassing the connector
refused by the read-only account (attempt to write a readonly database)
row cap
limit 200 rows; a query for all 1,812 rows returned 200
Limits: in this example the “source database” is a local file and the script is a demonstration of the rules, so it shows the behaviour, not a security review. Row limits reduce, but do not remove, the chance that sensitive values reach the assistant, so the account’s own permissions matter most. A human still reviews everything the assistant proposes.
A small evaluation against a hand-written reference
I wrote a reference model for the same source (7 tables, 32 columns) and compared the assistant’s draft and the reviewed version with it. The numbers are computed; the limits are below and on the evaluation page.
Measure
Draft v1
Reviewed v2
Reference tables present
4 of 7
6 of 7
Declared grain matches the reference (of tables present)
4 of 4
6 of 6
Declared key is actually unique in the data (of tables present)
3 of 4
6 of 6
Reference columns found (of 32)
17
26
Reference measures placed in the right table (of 8)
6
7
Sales-fact keys matching the reference (of 1,800)
1,785
1,800
Sales-fact rows identical to the reference (money columns)
1,767
1,800
Where v2 differs from the reference: no promotion dimension (promotion type and discount sit on the bridge instead), and no gross_profit column (it can be derived). Those are design differences, not errors, and I count them as misses here.
Limits
One task, one synthetic company, one run. Nothing here measures how often an assistant’s proposal is right in general.
I wrote the reference, seeded the defects, and wrote the checks. That is not independent, and the reference is my design, not a standard.
The counts of tables and columns are coarse. Matching a name does not mean the meaning is right.
The reviewed v2 is the result of my decisions plus the assistant’s SQL. The comparison says how the workflow ended up, not how the assistant did alone.
The scenario and decisions are illustrative. They show how I work, not a record of a real engagement.
Worked example on synthetic data. The invented company is not affiliated with any real company. All numbers on this page are computed; the scenario and decisions are illustrative.