Market Data Silver Layer
dbt & BigQuery
Project Summary
Builds directly on the bronze layer — raw market event records already land in BigQuery from the upstream ingest pipeline. This project adds a dbt transformation layer on top, promoting those raw records into a clean, tested silver table that is safe to query for analytics and ready to feed gold-layer aggregations.
The infrastructure is intentionally minimal: dbt reads from the bronze BigQuery tables and writes clean silver views and tables back into BigQuery. The model retains the same core fields from bronze, removes duplicate records, and enforces null checks on critical columns. Over 90 dbt tests validate value ranges, column types, and join conditions across every model before any run is considered successful.
Key Requirements
- • Read directly from bronze BigQuery tables — no re-ingestion.
- • Deduplicate records — bronze rows may arrive more than once.
- • Enforce null checks on all critical fields before writing silver.
- • Expose clean tables and views in BigQuery — schema matches bronze minus duplicates.
- • 90+ dbt tests covering values, types, and join conditions on every model.
Architecture
Simple two-component stack — dbt reads from bronze BigQuery tables and writes clean silver tables and views back into BigQuery.
Bronze — BigQuery
Raw market event records produced by the upstream ingest pipeline. Full-fidelity, append-only, partitioned by ingestion date.
dbt Model
Removes duplicates, enforces null checks on critical columns, and retains the full bronze schema. 90+ tests validate values, types, and join conditions on every run.
Silver — BigQuery
Clean tables and views in BigQuery. Same schema as bronze, duplicates removed. Ready for gold aggregations and BI tooling.
Engineering Decisions
Key design choices in the transformation layer.
Deduplication
Bronze records may arrive more than once due to the upstream ingest pattern. The dbt model uses a ROW_NUMBER() window partitioned over the natural key to keep only the first occurrence of each event, ensuring the silver table is free of duplicates.
Null Checks
All critical columns — identifiers, timestamps, and price fields — carry not_null dbt tests. Any row that fails is surfaced as a test failure before the silver table is promoted, preventing silent bad data from reaching downstream consumers.
90+ dbt Tests
Over 90 tests cover the full model surface: not_null and unique on keys, accepted_values on categoricals, type assertions on numeric columns, and relationship tests on join conditions between models. All tests run on every dbt invocation.
Tech Stack
Tools and services used across the transformation layer.