Why dbt for transformations

dbt brings software engineering discipline to SQL. Models live in version control. ref() builds a DAG. Tests run on every build. Docs generate automatically. None of those are exotic; they are table stakes for production analytics.

Alternatives we considered. Scheduled queries: easy, but no version control. Dataform: GCP-native, decent, less ecosystem. Custom Python: maximum flexibility, maximum maintenance burden. dbt sits in the sweet spot of community size, ecosystem, and discipline.

cloud_run_dbt/dbt/dbt_project.yml

yaml

name: 'pipeline_ecommerce'
version: '1.0.0'
profile: 'pipeline_ecommerce'

vars:
  gcp_project_id: "{{ env_var('GCP_PROJECT_ID', 'your-gcp-project-id') }}"
  bq_raw_dataset: "{{ env_var('BQ_RAW_DATASET', 'ecommerce_raw') }}"

models:
  pipeline_ecommerce:
    stg:
      materialized: table
      +dataset: stg
      +tags: ["stg"]
    marts:
      +dataset: marts
      +tags: ["marts"]

The dbt project file pins the project name, sets variables for GCP project ID and raw dataset, and configures stg vs marts as separate datasets.

Dataform works. Its ecosystem is smaller. The dbt community has packages, plugins, vendors offering managed runners, and a hiring pool that is already trained on it. For most teams the network effect makes dbt the safer choice.

Matching exercise: Match the transformation tool to the workload

Loading practice…