Intermediate12 min read

dbt and the Transformation Layer

How dbt structures a warehouse, incremental models, tests, snapshots, and the layered Raw → Stage → Core architecture — plus the questions asked about maintaining it at enterprise scale.

dbt does one thing: it turns SQL SELECT statements into a managed, tested, documented, dependency-aware set of tables and views. It is the T in ELT, and it brings software engineering practice to analytics.

The layered architecture

Each layer has one job. Because everything is rebuildable from RAW, backfills and bug fixes are safe.

Layer	Job	Rules
Raw / source	Land data exactly as it arrived	Append-only, no business logic, never edited
Stage	Clean, cast, rename, deduplicate	1:1 with a source table; no joins across domains
Core	Conformed facts and dimensions	Business logic lives here; SCDs applied; shared definitions
Marts	Domain-shaped, ready to consume	Wide, denormalised, optimised for BI

Incremental models — the workhorse

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {'field': 'event_date', 'data_type': 'date'},
    cluster_by = ['cell_site_id'],
    on_schema_change = 'append_new_columns'
) }}

select
    event_date,
    cell_site_id,
    count(*)                                  as call_attempts,
    countif(call_status = 'DROPPED')          as dropped_calls,
    sum(volume_bytes) / 1e9                   as total_gb
from {{ ref('stg_network_events') }}

{% if is_incremental() %}
  -- only reprocess a small trailing window, so late data is picked up
  where event_date >= date_sub(current_date(), interval 3 day)
{% endif %}

group by 1, 2

Tip — Why insert_overwrite beats merge here

insert_overwrite replaces whole partitions, so a rerun is naturally idempotent and cheap — BigQuery rewrites 3 days, not the whole table. merge is better for row-level upserts like SCD Type 2. Knowing which to pick, and why, is the real question behind 'do you know dbt'.

Testing and documentation

models:
  - name: fct_network_kpi_daily
    description: "Daily network KPIs per cell site. Grain: one row per site per day."
    columns:
      - name: cell_site_id
        description: "FK to dim_cell_site."
        tests:
          - not_null
          - relationships:
              to: ref('dim_cell_site')
              field: cell_site_id
      - name: drop_rate
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 1
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [event_date, cell_site_id]

sources:
  - name: raw
    tables:
      - name: network_events
        freshness:
          warn_after:  {count: 1, period: hour}
          error_after: {count: 6, period: hour}
        loaded_at_field: ingestion_ts

Generic tests — unique, not_null, accepted_values, relationships cover most needs
Singular tests — a SQL file that returns failing rows; use for real business rules
Source freshness — catches upstream systems that stopped delivering, the most common silent failure
Snapshots — dbt's built-in SCD Type 2 implementation over a source table

Practice questions

QHow would you structure a dbt project across many domains and a large team?show

Enforce a strict layer convention, one owner per domain, and a shared core — otherwise you get duplicated logic and conflicting metrics.

Folders mirror layers: staging/ → intermediate/ → core/ → marts/<domain>/
Naming convention: stg_, int_, dim_, fct_, mart_ — instantly readable lineage
Staging models are 1:1 with sources and may only be referenced by their own domain's intermediate models
Shared conformed dimensions live in core/ and are owned centrally so definitions do not fork
Enforce with dbt_project.yml materialisation configs, CI running dbt build --select state:modified+, and required code review
Publish docs/lineage so consumers can self-serve instead of asking the team

Likely follow-up: How do you stop two teams defining 'active subscriber' differently?

QHow do you handle a backfill of an incremental model?show

Backfills must be partition-scoped and idempotent, or they become incidents.

Use insert_overwrite with a partition filter, then run with --vars specifying a date range
Backfill in chunks (a month at a time) rather than five years at once — bounded cost and easy to resume
dbt run --full-refresh rebuilds everything: correct but potentially very expensive, so treat it as a deliberate decision
Verify row counts and key metrics against the previous version before publishing to consumers

QWhat are the limits of dbt — when is it the wrong tool?show

dbt is SQL-based, batch, and warehouse-resident. Outside that box, use something else.

Not for ingestion — dbt transforms data already in the warehouse; use Dataflow/Spark/Fivetran/Kafka Connect to land it
Not for streaming — dbt runs on a schedule; sub-minute latency needs a streaming engine
Not for complex procedural logic or ML — reach for Python/Spark
Not a replacement for orchestration at platform scale — Composer or Dagster coordinates dbt alongside everything else

QHow do you manage data quality across many domains at enterprise scale?show

Make quality a property of the pipeline, not a person's vigilance — tests in CI, contracts at boundaries, and alerting on data metrics.

Tests as gates — dbt build fails the run so bad data never reaches marts
Data contracts at source boundaries: agreed schema, freshness and semantics, enforced automatically
Quarantine, do not drop — route failing records to a side table with a reason code
Freshness and volume anomaly monitoring on every critical table
Ownership — every dataset has a named owner and a documented SLA; lineage makes blast radius visible before a change ships

← PreviousDimensional Modeling Interview Questions Next →How to Answer Data Pipeline System Design