How do the Bank of Canada's policy rate, inflation, exchange rate, and bond yields move together, for example through the 2022–24 tightening cycle? Each of these lives in a different place, at a different frequency. This pipeline lands all four on a single monthly timeline so they can be read on one chart.

The domain is deliberate: clean, credible, financial-services data that stays interesting to talk through, and a realistic testbed for a production-shaped dbt project.

A Python script lands raw data in BigQuery; dbt then transforms it in clean layers, each building on the last, into a small star schema that Looker Studio reads directly.

The layered pattern (staging → intermediate → mart) keeps a clean lineage: every number on the dashboard can be traced back through the models to the original API observation.

The same dbt project runs on BigQuery and Snowflake, chosen by target, with no forked models, no forked ingestion. The BigQuery-isms are resolved with dbt cross-database macros (date_trunc, datediff, generate_surrogate_key) and a target-aware source, so all 8 tests, including the custom one, pass identically on both. Snowflake auth is key-pair via env vars, mirroring BigQuery's; the ingestion script loads either warehouse with a --warehouse flag.

A local Apache Airflow stack (Docker Compose, LocalExecutor + Postgres) runs the pipeline on a schedule: one DAG, ingest_raw → dbt_build, @daily with retries and catchup=False. dbt runs from an isolated venv baked into the image so its dependencies never collide with Airflow's, and it targets an isolated CI dataset so scheduled runs never touch the dashboard's data.