dbt (Data Build Tool)

dbt is the tool that transforms your data warehouse from a data swamp into a data platform. Without dbt, every analyst writes their own sessionisation query, their own conversion logic, their own bounce rate formula — and they all differ slightly. With dbt, these definitions exist once, version-controlled in Git, tested on every build, and documented for every team member. This is the single highest-leverage investment you can make in your analytics infrastructure.

The GA4-to-BigQuery pipeline is dbt's killer use case. Raw GA4 exports are nearly unusable without transformation: nested event parameters, missing session IDs, device-level deduplication issues, timezone inconsistencies. A well-designed dbt project transforms this into: stg_ga4_events (flattened, typed), int_sessions (sessionised with custom logic), int_users (first-touch attributed, lifecycle-staged), and mart_* models for each analytical domain. This layered architecture means your raw data schema can change without breaking downstream analyses.

Incremental materialisation is critical for analytics tables. GA4 exports append daily. Your dbt models should use incremental strategy with merge on event_id, partitioned by event_date. This means your daily build processes only new events, not the full history. For a property with 50M events/month, the difference between full-refresh and incremental is the difference between a $50 build and a $0.50 build — every single day.

Testing is where dbt pays for itself. unique tests on session_id and user_id catch deduplication failures. not_null tests on critical dimensions catch schema changes in upstream sources. accepted_values tests on event_name catch tracking implementation regressions. relationships tests between sessions and users catch join key mismatches. Custom data tests (SQL queries that return rows when assertions fail) catch business logic violations. Running these tests on every build catches production data quality issues regularly — issues that would have corrupted reports for days before being noticed.

The semantic layer — defining metrics as code — is dbt's most underappreciated capability. When you define 'conversion_rate' as conversions/sessions in one place, every dashboard, notebook, and report that references that metric uses the same denominator, the same filters, and the same edge-case handling. Metric definitions become a contract between the data team and the business. When a stakeholder asks 'how do we define engagement?', the answer is a URL to your dbt docs, not a slide in someone's deck.

dbt packages accelerate development significantly. dbt_utils provides generic tests, surrogate key generation, and date spine utilities. dbt_expectations provides Great Expectations-style data quality tests. codegen generates model YAML from database introspection. calogica/dbt_date handles date dimension generation. Evaluate packages for quality (stars, maintenance activity, test coverage) before adopting — but the top-tier packages save weeks of development.

dbt Cloud vs Core is a staffing decision, not a technical one. If you have a data engineer who maintains CI/CD, scheduling, and environment management, Core is free and fully capable. If your analytics team is SQL-focused without DevOps support, Cloud's web IDE, scheduling, and CI/CD dramatically reduce operational friction. The cost is justified by the infrastructure work it replaces.