dbt Analytics Engineering
The analytics engineering layer on top of a serverless lake: transformations are versioned code, twelve tests gate every build, and one command reconciles the warehouse to them. Including a test that fails the build if gold revenue stops matching silver to the cent.
/01Problem
Ad-hoc SQL against a lake produces numbers nobody can reproduce and nobody can defend. The transformation lives in someone's query history, its correctness is assumed, and a change that silently drops or double-counts rows surfaces as a dashboard that looks plausible and is wrong.
The alternative worth demonstrating is that both the transformations and their correctness checks are versioned code, and that a single command brings the warehouse to that state or fails loudly.
/02Approach
- A dbt project on the dbt-athena adapter over bronze, silver, and gold layers, with Terraform provisioning the S3 lake, the Glue schema dbt materializes into, and the Athena workgroup.
- Bronze is a raw orders seed loaded into the catalog as-is. Silver is a view that types and cleans it: order dates cast to real dates, rows with non-positive quantity or price dropped, and a computed line total the marts can rely on.
- Gold is two Parquet marts a dashboard reads directly, revenue and counts by category and country, and one row per day with average order value.
- Twelve tests run on every build: uniqueness and not-null on the keys, accepted values on category and country, not-null on the gold revenue and count columns, and a singular reconciliation test.
/03Architecture
Staging is a cheap view over raw while the marts are materialized as Snappy Parquet, so downstream queries scan compressed columnar data instead of re-reading the seed on every dashboard load. That split is the whole reason to have a silver layer that is not itself materialized.
The test that earns its place is the singular one: it asserts that total revenue in the gold category mart reconciles to total line total in silver to the cent. Schema tests catch a column going null; only a reconciliation test catches a join that silently duplicates rows, which is the failure mode that actually reaches dashboards. CI compiles the model DAG on every push, so a broken reference fails before it reaches a warehouse.
/04Outcome
A warehouse where the transformations are reviewable in a pull request and the correctness checks run as a gate rather than as a follow-up. A transform that drops or double-counts rows fails the build instead of shipping bad numbers.
Deployed and demoed against real AWS on a deterministic seed, validated straight against Athena without dbt in the loop as an independent check, then destroyed. No warehouse endpoint, no cluster, no NAT, so nothing accrues while idle.