Modern Warehouse Modeling

You know three warehouse methods: Kimball, Inmon and Data Vault. This chapter asks a newer question. In a cloud warehouse with dbt, a tool that builds tables from SQL files, how do you lay out your tables?

Many cloud warehouses bill for the data a query reads, or for the computer time it uses. So the shape of a table affects the cost of every query that touches it.

Recap: a layer is a group of tables at one stage of cleaning, such as raw, cleaned and serving. A star puts one fact table in the middle with dimension tables around it. The grain says what one row of a table means.

What you will learn

1. The medallion layers (bronze, silver, gold), what each holds, and the tests for each.
2. How dbt builds the layers: model names, ref() and incremental models.
3. When to use a dbt snapshot to keep history.
4. One big table or a star: build both for the shop and compare the costs.
5. The semantic layer: define paid revenue once.
6. Names and contracts, and what to say in an interview.

The medallion architecture sorts tables into three layers: bronze, silver and gold. Each layer is cleaner than the one before it.

Bronze: keep what arrived

Bronze holds the data as it came, plus a load time. You never fix a row here. On 2026-01-23 the source sent order 103 again, so bronze has it twice:

Order 102 says Paid with a capital letter. Order 103 sits in two loads. Bronze keeps both, because it is your proof of what arrived.

Silver: clean, one row each

Silver fixes what is wrong but changes no meaning. It does three things:

• It keeps the newest copy of each order, so order 103 appears once.
• It writes status in lower case, so Paid becomes paid.
• It changes text into real types: amount into a number, order_date into a date.

The incremental model in the dbt section shows the code. This is the result:

There are six rows, one for each order. Order 103 kept its newer copy, loaded on 2026-01-23.

Silver is not always one table for each source. Many teams put a Data Vault here, as the chapter Inmon vs Kimball vs Data Vault showed. A dbt snapshot keeps old versions of a source table, so it sits beside bronze.

Gold: the tables people use

Gold holds the star, and the wide tables that a later section builds. Here is fct_orders, the fact table. Its customer_key values are the dimension keys from earlier chapters:

Orders 101 and 102 use keys 1001 and 1006. These are Asha’s IN row and Asha’s DE row.

Each layer makes a different promise, so each layer gets different tests:

Here are the tests as dbt YAML, in two files. The first file is for bronze, and the second is for silver and gold:

# models/sources.yml  (bronze)
sources:
  - name: shop_raw
    loaded_at_field: _loaded_at
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: orders

# models/schema.yml  (silver and gold)
models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: status
        data_tests:
          - accepted_values:
              values: ['paid', 'pending', 'refunded']
  - name: fct_orders
    columns:
      - name: customer_key
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key

A singular test is one SQL file. It fails when it returns rows. This one checks that gold and silver agree on paid revenue:

select g.total as gold_total, s.total as silver_total
from (select sum(amount) as total from {{ ref('fct_orders') }} where status = 'paid') as g
cross join (select sum(amount) as total from {{ ref('stg_orders') }} where status = 'paid') as s
where g.total <> s.total

dbt builds your layers from SQL files. Each file is a model: one select statement that dbt turns into a table or view. The file name tells you the layer:

Bronze is not a model. dbt reads it as a source. The folders are usually staging, intermediate and marts.

ref() links the models

In a model you write ref() with another model’s name, not a table name. Then dbt learns which model must build first. It also fills in the right schema for development and for production.

This model counts the items in each order. It reads stg_order_items with ref():

select
  order_id,
  count(*)      as item_count,
  sum(quantity) as units
from {{ ref('stg_order_items') }}
group by order_id

Incremental models

A normal model rebuilds every row on each run. An incremental model builds only the new rows after its first run. On a large table this saves computer time.

In the code, source() reads a bronze table, as ref() reads a model. The config line sets materialized, which tells dbt how to build the model. It can build a view, a table, or an incremental table.

The silver orders model fits well. On later runs it reads only bronze rows loaded since the last run:

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

select order_id, customer_id,
       lower(trim(status))        as status,
       cast(amount as numeric(10,2)) as amount,
       cast(order_date as date)   as order_date,
       _loaded_at
from (
  select *,
         row_number() over (partition by order_id order by _loaded_at desc) as rn
  from {{ source('shop_raw', 'orders') }}
  {% if is_incremental() %}
  where _loaded_at > (select max(_loaded_at) from {{ this }})
  {% endif %}
) as ranked
where rn = 1

The is_incremental() check is false on the first run. It is also false with --full-refresh, which rebuilds the whole table. Then the model reads all of bronze.

{{ this }} means the table this model already built.

The chapter Incremental Models Done Right covers late data in depth.

The source customers table is overwritten when a customer moves. Today Asha’s row says DE, and the IN is gone:

Recap: SCD Type 2 keeps one row for each version of a customer, with valid_from and valid_to dates. A dbt snapshot builds those versions for you. Each run, it compares the source with its saved copy:

{% snapshot snap_customers %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at'
) }}
select * from {{ source('shop_raw', 'customers') }}
{% endsnapshot %}

The current rows have dbt_valid_to NULL, not 9999-12-31. The dimension model fills in the far-future date, so date joins work:

select
  {{ dbt_utils.generate_surrogate_key(['customer_id', 'dbt_valid_from']) }} as customer_key,
  customer_id, name, country,
  cast(dbt_valid_from as date)                                     as valid_from,
  coalesce(cast(dbt_valid_to as date), cast('9999-12-31' as date)) as valid_to
from {{ ref('snap_customers') }}

Since dbt 1.9, the dbt_valid_to_current config can store the far-future date for you. Also, dbt 1.9 and later prefer snapshots in YAML, with schema instead of target_schema.

An as-of join picks the dimension row that was valid on the order date. This is how fct_orders gets its customer_key:

select o.order_id, d.customer_key, o.order_date, o.status, o.amount
from {{ ref('stg_orders') }} as o
join {{ ref('dim_customer') }} as d
  on d.customer_id = o.customer_id
 and o.order_date >= d.valid_from
 and o.order_date <  d.valid_to

Two strategies

dbt must decide that a row changed. It has two strategies:

• timestamp: dbt compares an updated_at column. It is cheap, and dbt_valid_from is the updated_at value. Use it when the source keeps updated_at correct.
• check: dbt compares the columns you list in check_cols, such as country. Use it when there is no reliable updated_at.

Both strategies see only the state at each run. If Asha moved twice between two runs, the snapshot keeps only the last state.

When to snapshot

• Snapshot when the source overwrites rows and nothing else keeps the history.
• Skip it when the source already keeps history, such as a change log. A change data capture (CDC) feed also does this: it sends every insert, update and delete. Build the SCD Type 2 table from that feed.
• Snapshot raw source tables with a simple select, not finished models.
• A row deleted in the source stays open in the snapshot. To close it, set hard_deletes to invalidate (older versions: invalidate_hard_deletes).
• Protect the snapshot table. dbt cannot rebuild lost versions, because the source no longer has them.

The chapter Snapshots and Slowly Changing Dimensions in dbt covers the details.

A one big table (OBT) is a wide table. It holds the facts and the dimension columns already joined, so a dashboard reads it with no joins.

Build it from the star, not from raw data. This model joins fct_orders, dim_customer and the item rollup. The grain is one row for each order:

select
  o.order_id, o.order_date, o.status, o.amount,
  c.name    as customer,
  c.country as country,
  coalesce(i.item_count, 0) as item_count,
  coalesce(i.units, 0)      as units
from {{ ref('fct_orders') }}  as o
join {{ ref('dim_customer') }} as c
  on c.customer_key = o.customer_key
left join {{ ref('int_order_items_rollup') }} as i
  on i.order_id = o.order_id

Now a dashboard asks for paid revenue by country. It reads one table:

SELECT country, SUM(amount) AS revenue
FROM obt_orders
WHERE status = 'paid'
GROUP BY country
ORDER BY revenue DESC;

The star and the one big table answer the same question. They differ in what happens when things change. Look at three costs.

Cost 1: bytes scanned

Recap: a columnar warehouse reads only the columns a query names. The revenue query reads status, amount and country from obt_orders. The other columns cost nothing for that query.

So a wide table is not slow because it is wide. Its saving is the join, not the scan. A star reads similar columns from two tables and joins them, and a small dimension is cheap to join.

A partition is a part of a table that holds one range of values, such as one day. A date filter helps both designs when the table is partitioned by order_date. Measure your own query in your warehouse, and do not guess.

Cost 2: the country is frozen

obt_orders picked one country for each order when it was built. It used the as-of country. A star can answer a second question from the same tables: revenue by country today.

The first join finds the order’s dimension row. The second join finds that customer’s current row:

SELECT cur.country, SUM(f.amount) AS revenue
FROM fct_orders   AS f
JOIN dim_customer AS d   ON d.customer_key = f.customer_key
JOIN dim_customer AS cur ON cur.customer_id = d.customer_id
                        AND cur.valid_to = DATE '9999-12-31'
WHERE f.status = 'paid'
GROUP BY cur.country
ORDER BY revenue DESC;

Here are both answers side by side:

The one big table holds only the first answer. To also answer the second, add a country_today column. Then every move by a customer means rewriting that customer’s rows.

Cost 3: repeated values

The name Asha is stored on both of Asha’s rows. Carla and US are stored on both of Carla’s rows. In a columnar format, repeated values compress well, so the extra storage is often small.

The bigger cost is change. A wrong customer name means rewriting every row of that customer in the wide table. A star fixes only the dimension rows: one row for Carla, not Carla’s two orders.

Side by side

A common answer is to use both. Keep the star as the truth. Build the wide table from it for a few busy dashboards, and rebuild it when the star changes.

A metric is a number the business tracks, such as paid revenue. If each dashboard writes its own SQL, the same name gets different numbers.

Two dashboards both say revenue. One adds every amount, and one adds only paid orders:

A semantic layer is one place where you define each metric once. Tools ask it for the metric by name. The definition is in code, so it is reviewed like code.

This is paid revenue in dbt’s MetricFlow format. A measure says how to add up one column, and the metric uses the measure:

semantic_models:
  - name: orders
    model: ref('fct_orders')
    defaults:
      agg_time_dimension: order_date
    entities:
      - name: order
        type: primary
        expr: order_id
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: status
        type: categorical
    measures:
      - name: paid_amount
        agg: sum
        expr: case when status = 'paid' then amount end

metrics:
  - name: paid_revenue
    label: Paid revenue
    type: simple
    type_params:
      measure: paid_amount

An entity is the key of the model (order_id). Here a dimension is a column you group or filter by, not a dimension table.

The semantic layer does not fix a wrong grain. Point it at a table with one row for each order item, and paid_revenue becomes 280.50. Define the metric on a table whose grain matches the measure.

The chapter The Semantic Layer and MetricFlow covers the tool.

Recap: a name should tell you the layer, and a data contract should tell you the columns. Both help the next engineer.

Use the prefixes from the dbt section. Put the grain in the model description too, because a short name does not always show it. Keep one name for one thing, for example order_id in every layer.

In dbt, a model contract makes the build fail when the table does not match its promise. Gold tables that other teams read are the best place for one:

models:
  - name: fct_orders
    description: One row for each order.
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: int
        constraints:
          - type: not_null
      - name: customer_key
        data_type: varchar
      - name: order_date
        data_type: date
      - name: status
        data_type: varchar
      - name: amount
        data_type: numeric(10,2)

The chapter Data Contracts, Schema Evolution & Governance covers versions and how to change a contract safely.

Five ideas to keep:

1. Bronze keeps what arrived. Silver cleans it to one row each. Gold holds the models people use, with the business rules.
2. Test the promise of each layer: it arrived, it is clean, it is right.
3. dbt builds layers from models. ref() sets the build order, and unique_key keeps an incremental model from adding duplicates.
4. Snapshot a source that overwrites history. A snapshot only sees the state at each run.
5. Keep a star as the truth, and define each metric once in a semantic layer.

Check yourself

• Why does bronze keep order 103 twice, and silver keep it once?
• What does obt_orders give for Asha’s country on order 101, and what does a current-country build give?
• Why is paid SUM(amount) 280.50 on a table with one row for each order item?

Each exercise opens a canvas for an entity-relationship diagram (ERD). You draw the tables, and the review checks them against sample rows.

Start at the top. The last two are harder, so they are optional.