- Sources: 30+ raw tables from Fivetran (Stripe, Salesforce, HubSpot, Postgres RDS).
- Staging models (stg_): 50+ models that clean and rename raw fields.
- Intermediate models (int_): 20+ models that join related entities (customers, orders, products).
- Mart models (mart_): 30+ fact and dimension tables for BI consumption.
- Tests: 500+ data quality tests across all models.
- Documentation: YAML descriptions on every model and column.
- Snapshots: Slowly-changing dimensions for customer and product attributes.
- Macros: Reusable macros for cents-to-dollars, date formatting, and pivot logic.
Running dbt on a project of this size takes 1–2 hours and produces 50+ transformed tables ready for BI consumption.
Pitfalls
Watch out for these traps when adopting dbt:
- No tests on critical models: Every mart model should have at minimum not_null and unique tests on primary keys.
- Long dependency chains: If model A depends on B which depends on C... rebuilding A is slow. Keep chains shallow.
- Hardcoding source names: Use sources in dbt, not hardcoded table names. Sources enable env-specific config.
- No documentation: dbt's auto-generated docs site is only useful if you write YAML descriptions. Document every model and column.
- Skipping staging layer: Always have a staging layer (stg_) that cleans raw data, and a marts layer that joins/aggregates. Skipping the staging layer creates brittle marts.
Quick Reference — Cheatsheet
- dbt = SELECT statements + Jinja templating + materialisation.
- Use incremental models for large fact tables — full refresh only for small dimensions.
- Tests are data quality contracts — fail the build on broken tests.
- dbt Cloud Developer plan is free for 1 developer.
- Use macros for repeated SQL patterns — DRY principle for transformations.
Frequently Asked Questions
What is dbt?
An open-source SQL-first transformation framework. Write SELECT statements with Jinja templating; dbt handles materialisation, testing, documentation, and lineage.
What is the difference between dbt Core and dbt Cloud?
dbt Core: CLI, free. dbt Cloud: managed SaaS with UI, scheduler, CI/CD, observability.
What databases does dbt support?
Snowflake, BigQuery, Redshift, Databricks, Postgres, MySQL, Spark, Trino, and more.
What are dbt models, tests, snapshots, and macros?
Models: SELECT → table/view. Tests: data quality checks. Snapshots: SCD Type 2. Macros: reusable Jinja snippets.
Is dbt a replacement for Airflow?
No — complementary. Airflow orchestrates the workflow; dbt handles SQL transformations. Airflow often triggers dbt runs.
What dbt certifications are available?
dbt Analytics Engineer Certification. Plus free + paid training at learn.getdbt.com.