How close did the proposed model get to a reference I wrote by hand?
A small comparison, computed from the data. It is meant to show the method and to be honest about how little it proves.
Method
Seven tables (four dimensions, a promotion bridge, two facts) with grain and key for each, written separately from the assistant’s SQL: reference_model.json. The reference sales fact is built by its own SQL, reference_model.sql, which groups on the cleaned key and keeps the lowest invoice line.
For each reference table: is it present, does the declared grain match, is the declared key actually unique in the data, and how many reference columns appear. For the sales fact: how many keys exist in the reference, and how many rows are identical to the reference on every money column.
v1 is the assistant’s initial draft. v2 is the version after the checks and my decisions. Both are built in a scratch copy of the source by model_v1.sql and model_v2.sql.
The build script counts them from the tables it builds, and writes them to results.json under eval. Nothing on this page is typed by hand.
Headline
The v2 fact holds 764,620,724 of net sales, the reference 764,620,724: equal. The source holds 769,672,150; the difference, 5,051,426, is the 12 duplicate lines held in the review table (5,051,426).
Table by table: draft v1
| Reference table | In model | Grain matches | Key unique | Columns | Extra columns | Missing columns |
|---|---|---|---|---|---|---|
| dim_product | present | yes | yes | 3 of 4 | brand_id | brand_name |
| dim_region | present | yes | yes | 2 of 2 | — | — |
| dim_month | present | yes | yes | 3 of 3 | — | — |
| dim_promo | missing | — | — | 0 of 5 | — | promo_id, promo_type, discount_pct, start_month, end_month |
| bridge_promo_coverage | missing | — | — | 0 of 4 | — | promo_id, month, region_id, sku |
| fact_sales_monthly | present | yes | no (1,803 rows, 1,785 keys) | 9 of 10 | inventory_cases, promo_type | gross_profit |
| fact_inventory_monthly | missing | — | — | 0 of 4 | — | month, region_id, sku, inventory_cases |
Table by table: reviewed v2
| Reference table | In model | Grain matches | Key unique | Columns | Extra columns | Missing columns |
|---|---|---|---|---|---|---|
| dim_product | present | yes | yes | 4 of 4 | — | — |
| dim_region | present | yes | yes | 2 of 2 | — | — |
| dim_month | present | yes | yes | 3 of 3 | — | — |
| dim_promo | missing | — | — | 0 of 5 | — | promo_id, promo_type, discount_pct, start_month, end_month |
| bridge_promo_coverage | present | yes | yes | 4 of 4 | discount_pct, promo_type | — |
| fact_sales_monthly | present | yes | yes | 9 of 10 | active_promos, amount_check_failed | gross_profit |
| fact_inventory_monthly | present | yes | yes | 4 of 4 | — | — |
“Grain matches” compares the grain the assistant declared for a table with the reference’s. “Key unique” tests that declared key against the built table, which is where v1’s sales fact fails (1,803 rows for 1,785 distinct keys).
Limits
- Not independent. I wrote the reference, planted the defects and wrote the checks. A different author would likely have built a different reference.
- Tiny sample. One task on one synthetic company, run once. The numbers describe this run only. They are not a rate, and they are not a comparison between assistants.
- Coarse measures. Table and column counts match names, not meaning. Row equality is checked on money columns only.
- The decisions are part of the result. v2 reflects my review as well as the assistant’s SQL, so it says how the workflow ended, not how the assistant did alone. The accept, change and reject decisions are illustrative.
- Synthetic faults are simple. The seeded defects are ones I already knew how to look for. Real data has faults nobody planted, and the checks were not tested on those.