Analytics Engineering

What ref() and source() actually do

ref('model_name') compiles to the fully-qualified table name for that model in the current target environment — so the same code resolves to dev_rmaldonado.stg_orders when you run it locally and analytics.stg_orders in production. source('salesforce', 'opportunities') does the same thing for raw tables declared in a sources.yml file.

# sources.yml
sources:
  - name: salesforce
    database: raw
    schema: salesforce
    tables:
      - name: opportunities

select * from {{ source('salesforce', 'opportunities') }}

Why hardcoding breaks things

• No lineage graph. dbt docs generate and dbt ls --select +my_model rely on ref()/source() calls to build the DAG. A hardcoded table reference is invisible to dbt, so lineage diagrams and impact analysis (“what breaks if I change this table?”) are wrong.
• No environment portability. A hardcoded raw.salesforce.opportunities in a model still points at the same table when you run in a dev schema, defeating the point of having separate environments — and it can’t be swapped out for a mocked/sampled table in tests.
• No freshness checks. Source freshness (dbt source freshness) is only possible on tables declared via source().
• No build ordering guarantees. dbt uses the DAG to decide what to build before what. A hardcoded reference to another dbt model bypasses that entirely and can cause the model to run against stale or not-yet-built data.

The fix is mechanical but the payoff is real: always route raw tables through source() and other dbt models through ref(), even when it feels like unnecessary ceremony for a “quick” model.

The four options

• View (dbt’s default) — just a CREATE VIEW. No storage cost, always reflects live data, but every query re-runs the underlying SQL, including any joins to other views. A chain of five stacked views means a dashboard query re-executes all of that logic every single time.
• Table — CREATE TABLE AS SELECT. Costs compute at build time (every dbt run), but reads are fast because the data is materialized once. Good default for anything queried frequently by BI tools.
• Incremental — like a table, but only new/changed rows are processed on each run instead of a full rebuild. Essential once a table gets into the tens or hundreds of millions of rows.
• Ephemeral — not built as a database object at all; dbt inlines the model’s SQL as a CTE wherever it’s referenced. Useful for small, reusable snippets of logic that don’t need their own table, but overusing it on large or heavily-reused models can bloat compiled SQL and hurt query planning.

Fixing the slow dashboard

The stacked-views problem in the question is a classic sign that a model should be promoted from a view to a table:

{{ config(materialized='table') }}

select
    o.order_id,
    c.customer_name,
    p.product_name
from {{ ref('stg_orders') }} o
join {{ ref('stg_customers') }} c on o.customer_id = c.customer_id
join {{ ref('stg_products') }} p on o.product_id = p.product_id

Now the join work happens once per dbt run instead of once per dashboard load. The tradeoff is that the table is only as fresh as the last run, and storage/compute cost shifts from query time to build time — usually a good trade for anything hit repeatedly by BI tools.

Generic tests (declared in YAML)

dbt ships four built-in generic tests you attach to columns in a .yml file:

models:
  - name: dim_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: region_id
        tests:
          - relationships:
              to: ref('dim_regions')
              field: region_id
      - name: customer_tier
        tests:
          - accepted_values:
              values: ['bronze', 'silver', 'gold']

Each test compiles to a SELECT query; if it returns any rows, the test fails. unique and not_null guard the primary key, relationships catches orphaned foreign keys, and accepted_values catches unexpected categorical values (like a new enum value from an upstream system nobody told you about).

Singular tests (custom SQL)

For business logic that doesn’t fit the generic templates, write a plain .sql file under /tests that returns failing rows:

-- tests/assert_no_negative_revenue.sql
select order_id, total_revenue
from {{ ref('fct_orders') }}
where total_revenue < 0

If this query returns any rows, dbt test fails and shows exactly which orders violated the rule — which is far more actionable than a stakeholder discovering it in a dashboard.

Running dbt build (or dbt test) as part of CI on every pull request, and again after every production run, is what actually makes “trustworthy” mean something rather than being an aspiration.

The three layers

• Staging (stg_) — one model per source table. Renames columns to a consistent convention, casts types, and does trivial cleanup. No joins, no business logic. If the underlying source table changes shape, this is the only place you fix it.
• Intermediate (int_) — reusable building blocks. Joins two or three staging models together, applies business logic that multiple marts need (e.g., “what counts as a completed order”). Usually not queried directly by analysts.
• Marts (fct_, dim_) — wide, denormalized, business-facing tables organized by department or domain (marts/finance, marts/marketing). This is what shows up in the BI tool.

Why it matters

Without this separation, a single upstream schema change forces you to hunt through every dashboard’s SQL to find every place that referenced the raw column. With layering, the blast radius of any change is contained to the staging model, and everything downstream inherits the fix automatically through ref().

models/
  staging/
    stg_stripe__charges.sql
    stg_salesforce__opportunities.sql
  intermediate/
    int_orders_joined_to_refunds.sql
  marts/
    finance/
      fct_orders.sql
      dim_customers.sql

A useful rule of thumb: staging models should be nearly 1:1 with source tables (mostly SELECT + CAST + RENAME), and any real join or CASE WHEN business logic belongs one layer up. This also makes testing easier — you can put not_null/unique tests on staging models to catch source data issues before they propagate into marts.

Sources: data that already exists in the warehouse

A source is a raw table dbt didn’t create but wants to reference, test, and document. You declare it in YAML and dbt never manages its contents — it’s owned by whatever loaded it (Fivetran, an app’s ETL job, etc.).

sources:
  - name: salesforce
    tables:
      - name: opportunities
        loaded_at_field: _fivetran_synced
        freshness:
          warn_after: {count: 12, period: hour}

Seeds: small, static, version-controlled data

A seed is a CSV file that lives in the /seeds directory of the dbt project itself. Running dbt seed loads it into the warehouse as a table. Seeds are appropriate for:

• Small reference/lookup tables (country codes, region mappings, a list of holidays)
• Data that doesn’t originate from any upstream system — you’re creating it
• Data that changes rarely and can be reviewed via pull request

# seeds/country_region_map.csv
country_code,region
US,NA
CA,NA
GB,EMEA
DE,EMEA

Seeds are not appropriate for large or frequently-changing datasets — there’s no incremental loading, no freshness checks, and every dbt seed reloads the whole file. If the country-region mapping instead came from an internal “reference data” microservice’s database table, it should be a source, not a seed, even though the row count is similarly small — the deciding factor is where the data originates, not its size.

The tradeoff

A star schema has one fact table surrounded by flat, denormalized dimension tables — dim_customers might repeat the region name and country name on every row instead of splitting them into separate dim_region and dim_country tables. A snowflake schema normalizes those dimensions into a hierarchy of smaller related tables, closer to a classic relational (3NF) design.

	Star	Snowflake
Joins per query	Fewer	More
Storage redundancy	Higher	Lower
Query simplicity for BI tools	High	Lower
Compression efficiency	High (columnar warehouses compress repeated values well)	Marginal benefit

Why star usually wins in a modern warehouse

• Storage is cheap, compute is what you pay for. Columnar warehouses like Snowflake, BigQuery, and Redshift compress repeated values in a column extremely well, so the “wasted space” argument for normalization mostly disappears.
• Fewer joins means faster, simpler queries — both for the query optimizer and for the analyst or BI tool writing SQL against it. Looker, Tableau, and similar tools generate much cleaner queries against a star schema.
• Snowflaking pays off mainly when a dimension is enormous and slowly changing — e.g., a deeply hierarchical product catalog with millions of SKUs — where the redundancy from flattening would actually cost meaningful storage and update complexity. Even then, teams often just accept the redundancy for the query-simplicity win.

The practical answer to the stakeholder: normalization was the right call in the 1990s when storage was expensive and warehouses were row-oriented; today’s columnar, elastic-compute warehouses flip that tradeoff, so star schemas are the default and snowflaking is the exception you reach for only when a dimension is unusually large or has hierarchies that themselves need independent versioning.

Declaring freshness on a source

sources:
  - name: shop
    tables:
      - name: orders
        loaded_at_field: _fivetran_synced
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}

dbt source freshness compares the most recent value of loaded_at_field against the current time. If it’s older than warn_after, dbt reports a warning; older than error_after, it reports a failure — both are visible in CI/orchestration logs and can be wired into alerting (Slack, PagerDuty, email).

Making it actually catch the Monday problem

The check is only useful if it runs before anyone looks at the dashboard and fails loudly when something’s wrong:

• Run dbt source freshness as an explicit step early in the scheduled job, before the transformation dbt run step, so a stale source blocks (or at least flags) the whole pipeline rather than silently building marts on stale data.
• Route freshness failures to an alert channel your team actually monitors over the weekend, not just a log nobody reads until Monday morning.
• For a weekend-specific gap like this one, make sure the schedule itself covers weekends — a job that only runs on weekdays will never catch a Saturday sync failure until Monday regardless of freshness config.

Beyond source freshness

Freshness checks catch “the data stopped arriving,” but they don’t catch “the data arrived but is wrong” (e.g., partial loads, duplicate rows). Pair freshness checks with row-count anomaly tests or dbt_utils.recency style tests further downstream for a fuller data-quality safety net.

Declare the grain first, always

Kimball’s first rule of dimensional modeling is to declare the grain of a fact table before choosing dimensions or measures. Grain is the answer to “what does one row in this table represent?” — e.g., “one row per order,” “one row per order line item,” “one row per daily account snapshot.”

Why line-item grain is correct here

If you asked for margin per SKU but the fact table were built at order grain (one row per order), you’d have no way to break the order’s total margin down by individual SKU — that information is gone once you’ve aggregated up. Going with the finer line-item grain solves both requirements:

select
    order_id,
    order_line_item_id,
    order_date,
    sku,
    quantity,
    unit_price,
    unit_cost,
    (unit_price - unit_cost) * quantity as line_margin
from {{ ref('stg_order_line_items') }}

• “Orders per day” → count(distinct order_id) grouped by date_trunc('day', order_date).
• “Margin per SKU” → sum(line_margin) grouped by sku.

Both roll up cleanly from line-item grain because you’re aggregating up, never trying to split a coarser row back into detail that was never captured.

What goes wrong if you mix grains

A common failure mode is a fact table that accidentally mixes grains — for example, joining an order-level table to a line-item table without being careful, which causes order-level measures (like shipping_cost) to be duplicated once per line item and wildly overstated when summed. Every measure in a fact table must be true at the declared grain; if it isn’t, it either needs to move to a dimension, or the grain needs to change.

Why Type 1 (overwrite) fails here

If you just UPDATE the rep’s territory in place every time it changes (SCD Type 1), every historical sale re-joins to the rep’s current territory the moment you run the report — silently rewriting history. Finance’s requirement is explicitly that a January sale should always show the January territory, even if the rep moved to a new territory in June.

SCD Type 2 with dbt snapshots

dbt’s snapshot feature automates Type 2 tracking by comparing each run’s source data against the previous snapshot and inserting new rows when tracked columns change:

-- snapshots/sales_reps_snapshot.sql
{% snapshot sales_reps_snapshot %}

{{
  config(
    target_schema='snapshots',
    unique_key='rep_id',
    strategy='check',
    check_cols=['territory_id', 'manager_id']
  )
}}

select * from {{ source('crm', 'sales_reps') }}

{% endsnapshot %}

Each dbt snapshot run adds dbt_valid_from and dbt_valid_to columns automatically. A rep who changes territory ends up with two rows: one with dbt_valid_to set to the change date, and a new one with dbt_valid_from starting there and dbt_valid_to null (current).

Joining facts to the right version

select
    f.sale_id,
    f.sale_date,
    r.territory_id
from {{ ref('fct_sales') }} f
join {{ ref('sales_reps_snapshot') }} r
    on f.rep_id = r.rep_id
    and f.sale_date >= r.dbt_valid_from
    and (f.sale_date < r.dbt_valid_to or r.dbt_valid_to is null)

This between-dates join is the core pattern of SCD Type 2 — it’s what lets a single dimension table serve both “what does the rep look like today” and “what did the rep look like at the time of this fact.”

Why natural keys aren’t enough

If System A’s order_id = 1001 and System B’s order_id = 1001 refer to completely different orders, using order_id alone as your fact table’s primary key silently merges unrelated rows. Natural keys are also often reused, recycled, or reset by upstream systems in ways you don’t control — a legacy system might restart its ID sequence, or a merger could bring in a second CRM with overlapping IDs.

Building a surrogate key

A surrogate key is a value with no business meaning, generated purely to guarantee uniqueness in your warehouse. dbt_utils.generate_surrogate_key() hashes a set of columns into a single deterministic string:

select
    {{ dbt_utils.generate_surrogate_key([
        'source_system',
        'order_id',
        'line_item_id'
    ]) }} as order_line_item_sk,
    source_system,
    order_id,
    line_item_id,
    sku,
    quantity,
    unit_price
from {{ ref('int_orders_unioned') }}

This produces a consistent hash (e.g., an MD5) that’s the same every time the same inputs are passed in — which is what makes it deterministic and safe to use with unique_key in an incremental model, and testable with a plain unique test.

Why this matters in practice

• Deterministic: rerunning the model produces the same key for the same input row, which is essential for idempotent incremental loads.
• Composable: it naturally extends to multi-system unions without needing to coordinate ID ranges across source systems.
• Testable: you can immediately add a unique and not_null test on the surrogate key column to catch grain violations early.

How it works

is_incremental() returns true only when: the model is materialized as incremental, the target table already exists, and you’re not running with --full-refresh. That lets you write one model that behaves differently on the first build (full historical load) versus every build after (only new/changed rows).

{{
  config(
    materialized='incremental',
    unique_key='order_id'
  )
}}

select
    order_id,
    customer_id,
    order_status,
    updated_at,
    order_total
from {{ source('shop', 'orders') }}

{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Key details

• unique_key tells dbt to merge/upsert incoming rows on that key instead of blindly appending, which matters when an existing order gets updated (e.g., status changes from “pending” to “shipped”) rather than only new orders arriving.
• {{ this }} refers to the model’s own already-built table, which is why you can query it for the current max timestamp.
• --full-refresh drops and rebuilds the whole table — you’ll need this if you change the model’s schema, fix a bug in historical logic, or the incremental logic gets out of sync.

The result: instead of scanning 200M rows nightly, the model scans roughly 50K new/changed rows, cutting both runtime and warehouse cost dramatically, while still producing the same final table as a full rebuild would.

Why idempotency matters

An idempotent model produces the exact same output every time it’s run with the exact same inputs, no matter how many times you run it or in what order. This property is what makes backfills, reruns after a failure, and manual reprocessing safe. Without it, “just rerun the job” becomes dangerous instead of routine.

Common ways idempotency breaks

1. Append-only incremental logic without a unique_key:

-- BAD: reruns for the same day insert duplicate rows
{{ config(materialized='incremental') }}
select * from {{ source('shop', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Fix: add unique_key='order_id' so dbt merges/upserts instead of blindly inserting, meaning reprocessing the same rows overwrites them rather than duplicating them.

2. Using wall-clock functions inside business logic:

-- BAD: result depends on when you happen to run it
select *,
  case when order_date = current_date then 'today' else 'past' end as flag
from {{ ref('stg_orders') }}

If you backfill this for a date three weeks ago, current_date still evaluates to today, not the date you’re backfilling — silently producing wrong output. Fix: pass the target date explicitly as a dbt variable or use the partition date being processed, never CURRENT_DATE()/NOW() for anything that determines row content.

Practical checklist for idempotent incremental models

• Always specify unique_key on incremental models where rows can be updated.
• Avoid CURRENT_TIMESTAMP/CURRENT_DATE inside transformation logic that isn’t purely about “when was this row processed.”
• Test idempotency directly: run the model, capture a row-count/checksum, run it again unchanged, and confirm the result is identical.
• Prefer merge strategy over append unless you have a specific reason rows should never be updated (e.g., true immutable event logs).

Why the failure was silent

Most SQL engines don’t error when you SELECT user_id from a table that no longer has that column named user_id if the underlying ELT tool auto-creates a new column (account_id) and simply leaves the old one absent or null-backfilled — the query might not even fail, it just silently returns NULL for every row, and nothing downstream necessarily crashes either. Dashboards keep rendering; they just quietly show zero or NULL-driven wrong numbers.

Catching it faster: tests at the boundary

models:
  - name: stg_events
    columns:
      - name: user_id
        tests:
          - not_null:
              config:
                severity: error

A not_null test on user_id in the staging model turns “silently wrong” into “pipeline fails loudly,” which is a strict improvement — a broken pipeline gets fixed same-day; a silently wrong dashboard can go unnoticed for weeks.

Making the model itself more resilient

dbt model contracts let you declare expected column names and types explicitly; if the incoming data doesn’t match, the run fails immediately at the model boundary rather than downstream:

models:
  - name: stg_events
    config:
      contract:
        enforced: true
    columns:
      - name: user_id
        data_type: string
      - name: event_type
        data_type: string

Additionally:

• Keep all upstream-facing column mapping (raw name → clean name) isolated in the staging layer, so if account_id really did replace user_id, there’s exactly one line to change (account_id as user_id) rather than a search-and-replace across dozens of downstream models.
• Where possible, set up a lightweight process with upstream teams — a Slack channel, a shared API changelog, or a contract test in their CI — so breaking changes are flagged before they ship, not discovered after the fact.
• Consider a “canary” row-count or distinct-value-count test on key dimensions (e.g., count(distinct user_id) shouldn’t suddenly crater) as an early warning even for changes that don’t produce outright NULLs.

Full-refresh backfill

The simplest and safest option when the table isn’t prohibitively large:

dbt run --full-refresh --select fct_orders

This drops the existing table and rebuilds it entirely using the current (fixed) model logic, so every historical row — not just the last 30 days — reflects the correction. Always validate in dev/staging first:

• Compare row counts before and after for a few known date ranges.
• Spot-check a handful of records that you know were affected by the bug.
• Confirm downstream models that depend on this one (via dbt run --select fct_orders+) also get rebuilt so the fix propagates.

Partition-level backfill (when full refresh is too expensive)

For a genuinely huge table where a full rebuild would take hours or cost a lot in compute, backfill just the affected window using a variable-driven filter:

{% if is_incremental() %}
where order_date >= '{{ var("backfill_start", "2099-01-01") }}'
  and order_date <  '{{ var("backfill_end",   "2099-01-01") }}'
{% endif %}

dbt run --select fct_orders --vars '{"backfill_start": "2026-06-29", "backfill_end": "2026-07-29"}'

Because the model has a unique_key, rows within that date range get merged/overwritten with corrected values rather than duplicated.

Communication matters as much as the mechanics

• Backfilling during a low-traffic window reduces the chance of contention with concurrently running jobs or live dashboard queries.
• Anyone consuming this table downstream (other dbt models, BI dashboards, exports to other systems) should be told the historical numbers are about to change, especially if the bug affected a metric that’s already been reported externally.
• Document the root cause and the date range affected so it can be referenced later if someone asks “why did last month’s number change?”

Why the naive filter fails

-- BAD: assumes data arrives in event_date order
{% if is_incremental() %}
where event_date > (select max(event_date) from {{ this }})
{% endif %}

If today the table’s max event_date is July 29, and a batch of mobile events with event_date = July 26 arrives late (because the phone was offline), this filter never looks at July 26 again — it already moved past that date. Those events are permanently dropped from the table.

The lookback window fix

Filter on the ingestion timestamp with a buffer wide enough to cover realistic delay, and reprocess a rolling window of recent days rather than only “new” days:

{{
  config(
    materialized='incremental',
    unique_key='event_id'
  )
}}

select
    event_id,
    event_date,
    ingested_at,
    user_id,
    event_type
from {{ source('app', 'events') }}

{% if is_incremental() %}
where event_date >= (
    select dateadd('day', -4, max(event_date)) from {{ this }}
)
{% endif %}

This reprocesses the last four days of event_date on every run — enough to catch the three-day maximum delay with a buffer — and relies on unique_key='event_id' to merge/upsert rather than insert duplicates for events that were already loaded on time.

Tradeoffs

• A wider lookback window catches more late data but costs more compute per run, since you’re reprocessing several days’ worth of rows every time instead of just the newest ones.
• If delay can occasionally exceed your lookback window (a phone offline for two weeks), no lookback window fully solves it — you’ll also want a periodic full-refresh or a longer, less-frequent “reconciliation” job that scans further back.
• The same pattern applies to late-arriving dimension rows (e.g., a customer record synced after their first order already loaded) — the fix there is typically a placeholder/unknown-member row plus a later correction pass rather than a lookback window.