Which database produced the Gold-QA numbers? (engines, errors, how to replicate)
Date: 2026-05-30 · Table: Roofing_Master_Gold_V1_20260512 (Apache Hudi COW) on s3://8020rei-gold-data-lake/ · Counties: FL-7 CallZeke (12053 12101 12103 12031 12095 12115 12086)
Plain version: the QA dashboard shows "paste into Athena" SQL, but those queries did not compute the dashboard numbers, and the automation user cannot run them. The numbers were computed in DuckDB, and independently confirmed in Athena Spark. Here is exactly what ran where, what failed, and how to reproduce each.
The three engines
| # | Engine | Used for | Runs for ignacio-claudecode? | Reads |
|---|---|---|---|---|
| 1 | DuckDB over raw S3 parquet | Computed the shipped dashboard numbers | ✅ yes | raw *.parquet glob |
| 2 | Athena Spark + native Hudi reader | Ground-truth cross-check (this audit) | ✅ yes (service exec role writes results) | .hoodie commit timeline (manifest), commit-pinned |
| 3 | Athena SQL (start-query-execution) — the dashboard's displayed query | nothing — blocked | ❌ no | (would use Glue catalog) |
Why Athena SQL is blocked for the automation user (captured reproducibly by scripts/roofing/qa_gold_audit_athena_sql.py, 0 / 19 queries ran):
InvalidRequestException: An error occurred (InvalidRequestException) when calling the
StartQueryExecution operation: Unable to verify/create output bucket
611201211946-us-east-1-athena-results-bucket-1tco3o1dlj
AthenaErrorCode: INSUFFICIENT_PERMISSIONSTwo independent blocks: (a) the user can't write the SQL results bucket (fails before the query even plans), and (b) glue:GetTables/GetDatabases/GetTable are denied. Athena Spark works where SQL doesn't because its *service exec role* (AWSAthenaSparkExecutionRole-…) writes the results, not the user, and it reads the S3 path directly without the Glue catalog. The displayed Athena SQL is valid and runs from a human AWS console (admin Glue + S3) — just not from this automation user.
Same logic, three dialects — worked example (C1 "empty FA_PROPERTYID", Pinellas 12103)
| Engine | Query (verbatim) | Result |
|---|---|---|
| Athena SQL (displayed) | SELECT count(*) FROM "8020rei-gold-data-lake".roofing_master_gold_v1 WHERE fips = 12103 AND cardinality(fa_propertyid) = 0 | ❌ INSUFFICIENT_PERMISSIONS (see above) |
DuckDB (qa_gold_audit.py) | COUNT(*) FILTER (WHERE FA_PROPERTYID IS NULL OR len(FA_PROPERTYID)=0) over read_parquet('s3://…/12103/*.parquet') | 717,056 (13.27%) |
Athena Spark (qa_gold_audit_athena.py) | SUM(CASE WHEN FA_PROPERTYID IS NULL OR size(FA_PROPERTYID)=0 THEN 1 ELSE 0 END) over format("hudi").option("as.of.instant",…) | 717,056 (13.27%) |
DuckDB == Athena Spark, to the row. The dialects differ (cardinality/len/size, CROSS JOIN UNNEST vs LATERAL VIEW explode) but the logic is identical. The Athena-SQL number is unavailable to automation, so it cannot be shown here — run it in the console to confirm.
How to replicate (copy-paste)
# 1) DuckDB — the engine that produced the dashboard numbers (3 counties, live S3, no cache)
# (exports AWS creds from the 8020rei-prod profile into AKID/SAK — see qa_gold_audit.py header)
uv run python scripts/roofing/qa_gold_audit.py --live
# -> data/sandbox/qa_gold_audit_results.json
# 2) Athena Spark — ground-truth cross-check, all FL-7, commit-pinned (~$0.11, auto-terminates)
uv run python scripts/roofing/qa_gold_audit_athena.py
# -> data/sandbox/qa_gold_audit_athena_fl7.json (verdict roll-up printed)
# 3) Athena SQL — attempt the dashboard's literal queries; records the per-query error
uv run python scripts/roofing/qa_gold_audit_athena_sql.py
# -> data/sandbox/qa_gold_audit_athena_sql_attempt.json (expect 0/19 for the automation user)
# 3b) ...or run any displayed query yourself in the AWS console (admin identity):
# Athena > Query editor > workgroup "primary" > paste the SQL shown on the dashboard.Bottom line on provenance: dashboard numbers = DuckDB; confirmed equal by Athena Spark (0 diffs, finding 91); the dashboard's Athena SQL is for humans in the console, not the automation user. Full audit verdict (108/167 checks fail, commit-pinned): finding 91.