Skip to content

dbt

dbt is our transformation tool for modeling data in BigQuery. It allows us to write SQL transformations, test data quality, and document our data warehouse.

Airflow authentication

Airflow dbt jobs use Workload Identity Federation and service-account impersonation. The airflow_federated target name does not mean BigQuery runs directly as the workload principal.

The authentication flow is:

  1. The dbt pod runs as Kubernetes service account datateam:dt-dbt-dags.
  2. Its projected token authenticates to Google Cloud through Workload Identity Federation.
  3. dbt impersonates transformer@<warehouse-project>.iam.gserviceaccount.com and executes BigQuery jobs as that service account.

The Airflow dbt operator sets DBT_GCP_IMPERSONATE_SERVICE_ACCOUNT; the dbt profile passes it to impersonate_service_account.

Why impersonation is retained

PR #1115 introduced impersonation, and PR #1130 removed the key-based profiles and made federation the default. The likely migration rationale was to remove stored service-account keys while preserving the existing transformer identity and permissions; the PR descriptions do not explicitly record that rationale.

This setup retains the transformer's dataset grants and the service-account identity also used by Google Drive integrations.

Dataset permissions and direct federation

With the current configuration, retain module.data_warehouse_service_accounts.transformer_account as a dataset writer for dbt outputs. A writer_principals grant to dt-dbt-dags does not replace the impersonated account's permissions: the workload principal authenticates the pod, while transformer is the effective BigQuery identity.

Direct-principal dbt access is an alternative, but requires disabling impersonation and verifying source-read access, destination-write access, and job execution permissions in the execution project. Validate Google Drive/Sheets-backed dependencies separately before changing shared authentication, and confirm the effective identity in BigQuery job metadata during a stage test.

Best Practices

SQL Styling

We follow the SQL Styling conventions on dbt

The styling is enforced by the sqlfluff linter.

Exceptions

  • Leading commas - Use leading commas for field lists
  • Line length - 140 characters maximum (excluding comments)

Documentation

  • Each model must have documentation. Specify partition and cluster column (to guide AI when writing SQL)
    • if the model is Deprecated, mention it in the documentation with the date of deprecation, and the reason.
  • Models with 'lookml' tag are used to create a Looker view
    • These models must have documentation for every column
  • Models with 'airflow' tag are run by a DAG outside of Frequent or Daily runs
    • These models must have documentation for every column

Naming Conventions

Columns

Country Columns

  • 3-Letter Country Code - suffix: _id
    • Example: country_id with value USA
  • 2-Letter Country Code - suffix: _code
    • Example: country_code with value US
  • Full Country Name - suffix: _name
    • Example: country_name with value United States

Date and Timestamp Columns

  • Date Columns - suffix: _date

    • Example: order_date, signup_date
  • UTC Timestamps - suffix: _at
    • Example: created_at, updated_at
    • Exception: Source/S3 tables may use different conventions. These should be fixed in the _source model.
  • Timezone-Specific Timestamps - suffix: _at_<timezone>
    • Example: passed_kyc_at_mt (Mountain Time)

Models

Seeds & Source Models
  • If the data is coming from a source, use the _source model to ingest the data. Include partition.
  • Do not use seeds directly in the models. Use the _source model to ingest the data. It allows for testing and documentation.
  • Add seeds documentation in the properties.yaml file. See Seeds.
Fact and Dimension Models

Fact Models (Fact Tables)

Facts contain measurable, quantitative data:

  • Contain metrics and measurements
  • Usually numeric and additive (can be aggregated)
  • Represent business events or transactions (orders, sales, etc.)
  • Often large tables that grow continuously
  • Answer "how many/much" questions

Examples: fact_remittance, fact_transaction, fact_user_session

Dimension Models (Dimension Tables)

Dimensions contain descriptive attributes that provide context:

  • Contain text-based, categorical data
  • Represent business entities (customers, products, stores, etc.)
  • Change less frequently than fact tables
  • Have primary keys that fact tables reference
  • Answer "who/what/when/where/why" questions

Incremental Strategy

  • Add incremental strategy when possible for better performance. See more below.

Additional Requirements

Unique Keys

  • Every model must have a unique key
  • Use meaningful keys from the data source when possible (e.g., user_id if the level of detail is per user)

Seeds

Seeds load version-controlled CSVs into the seed_data BigQuery dataset.

When to use

  • Static reference data that changes infrequently (mappings, fee tables, status codes)
  • Data with no upstream source system
  • Keep seeds small: ~1k rows / a few hundred KB. Larger or frequently changing data belongs in an ingestion pipeline

Adding a seed

  1. Add the CSV to seeds/ (snake_case name, header row)
  2. Declare column types in seeds/properties.yaml
  3. Add the seed as a table under the majority_seeds source in models/src_seeds.yml
  4. Create a <seed>_source model that selects from source('majority_seeds', '<seed>')
  5. Document and test the _source model (primary key unique + not_null)

Using a seed

  • Downstream models ref('<seed>_source')
  • Never reference a seed directly with ref('<seed>') or source('majority_seeds', '<seed>') outside its _source model

Loading

  • Dev: uv run dbt seed -t dev_seeds -s <seed>
  • Prod: uv run dbt seed -t prod_seeds -s <seed>

Materializations

Merge

When to Use:

  • Small tables (less than 1GB)
  • Data that can change within longer and undefined time frames (e.g., SCD2 or remittance)

Requirements:

  • This is dbt's default materialization
  • Unique key must be present
  • If the table is partitioned, use the script variable _dbt_max_partition:
{%- if is_incremental() %}
    and date(ingested_at) >= date(_dbt_max_partition)
{%- endif %}

Insert Overwrite

When to Use:

  • Preferred method for larger tables (considered mandatory over 10GB)
  • Reading from source
  • Requires partitioning

Configuration:

To optimize cost, use current_date_offset:

meta = {current_date_offset: 1},
partition_by = {
    'field': 'dw_ingestion_at', --the same column needs to be used in the incremental condition
    'data_type': 'timestamp',
    'granularity': 'day'
}

Note: The partition granularity doesn't need to be always day. Granularities month, or year are supported, too. Adjust the condition so the partition overwrites correctly. The minimum data per partition/cluster should be 100MB. Size Partition and cluster results

Incremental Condition:

{% if is_incremental() %}
where date(dw_ingestion_at) >= date(current_date() - interval {{get_current_date_offset()}} day)
{% endif %}

Rerunning Models

Some models (mostly stage models with partitions) have the variable current_date_offset. This variable changes the size of the partition being rerun and helps avoid rerunning the whole table.

Usage:

Add the variable at the end of your dbt command:

dbt run -m model_name --vars '{current_date_offset: 3}'

This will rerun the last 3 days of data instead of the entire table.