# AI-assisted data engineering: a worked example on synthetic data

I work with AI across the whole data engineering workflow, and I'm helping others on my team adopt it.

This folder is a worked example of a workflow, on an invented company. It is **not a transcript of real work**, and it uses no real system or data. The company ("Fictional Spirits Co.") reuses the synthetic data behind the talk-to-data demo, with defects added on purpose.

## What it shows

An AI coding assistant in the editor, plus a small script that gives it a read-only connection with schema and query access. The assistant proposes models, writes SQL, and helps validate the data and the joins. I orchestrate: I set the scope, review each proposal, and decide what goes forward. The page shows seven steps, each with what was proposed, what was checked, and what I accepted, changed or rejected. The decisions are illustrative.

## Pages

- `index.html` - the workflow, the entity diagrams, the computed checks, guardrails, and a small evaluation
- `eval.html` - proposed model against a hand-written reference, table by table, with limits

## Numbers (all computed; see `data/results.json`)

- 7 source tables, 34 columns, 5,483 rows
- 16 automated checks in `sql/checks.sql` (7 on the source, 9 on the model)
- 6 of 6 seeded defect types caught by the source checks, with counts matching the seed exactly
- Draft v1: 5 of 9 model checks fail. Reviewed v2: 1 fails, 1 warn, 7 pass
- v2 sales fact matches the hand-written reference on 1,800 of 1,800 keys (v1: 1,767)

## Files

| Path | What |
|---|---|
| `sql/source_schema.sql` | The seven source tables |
| `sql/model_v1.sql` | The assistant’s initial draft |
| `sql/model_v2.sql` | The model after review |
| `sql/checks.sql` | All 16 checks, one per block, as run |
| `sql/reference_model.sql` | Hand-written reference build of the sales fact |
| `data/source/*.csv` | The synthetic source tables |
| `data/results.json` | Every result shown on the pages |
| `data/reference_model.json` | The hand-written reference: tables, grain, keys, columns |
| `connector_rules.txt` | The access rules, in plain text |

## Reproducing the checks

The SQL is standard enough to run in any SQL engine that supports window functions: load the CSVs into the tables from `source_schema.sql`, run `model_v1.sql`, `model_v2.sql` and `reference_model.sql`, then the blocks in `checks.sql` (replace `{v}` with `v1` or `v2`, `{q_rows}` with the rows in `q_invoice_duplicates_v2`, and `{q_net}` with their net sales). The results on the pages were pre-computed by a build script that is not published, so there is no build step here.

## Limits

One task, one run, one author for the reference, the seeded defects and the checks. The numbers show the method. They are not a benchmark, and the scenario and decisions are illustrative. Read the limits on `eval.html` before quoting anything.