Creating a Data Model w/ dbt: Dimensions (Part 1/3)

A dimension table holds the descriptive context around a business event: which team, which game, which date. In dbt you build one as a model inside a warehouse folder, selecting from your staging models through ref and materializing the result as a table instead of a view. The layout I use for every dimension is the same three blocks: a surrogate key at the top, the descriptive columns in the middle, and data warehouse metadata columns at the bottom. The surrogate key itself comes from the generate_surrogate_key macro in dbt_utils, which you add with a packages.yml file and a dbt deps run. Dimensions come first in the build order because the fact table joins to them, and it is much easier to write those joins when the keys already exist.

Plan the warehouse layer before you write SQL

This is the first of three parts. Part 1 builds the dimensions, Part 2 builds the fact table, and Part 3 adds the baseline tests.

Before any of that, I keep a plain text file in the project called plan.txt. It is not a deliverable, it is a map, and I would encourage you to keep something similar.

Mine lists four things, working backwards from the output:

• The deliverable. The marts that the business actually asked for.
• The warehouse layer. The main fact table and the dimensions around it.
• The metrics. Every measure you expect the fact table to carry.
• The keys. What each dimension will join on, written as sk for surrogate key.

That reverse engineering step is the whole strategy. You name the end state first, then build toward it, instead of modeling whatever happens to be in the raw data.

Set up the branch and the folders

Start clean. Pull main, confirm you are up to date, then cut a branch for the work.

git pull
git branch add-warehouse
git checkout add-warehouse

Inside models/ you should already have a staging folder. Add a warehouse folder next to it, and inside that, subfolders for dimensions and facts.

dbt is happy to let you nest as deep as you need. Mirroring the layers of your model in the directory tree keeps the project readable as it grows.

Each subfolder gets the same two kinds of file that staging has: a schema YAML file and the model files themselves. The YAML is where tests land later.

How I structure a dimension model

The first model is dim_teams, because it pulls from more than one staging model and shows the pattern well.

Import CTEs at the top

Every model opens with one CTE per source, each a plain select * from a ref. Think of them as the import statements at the top of a Python file.

Two things come out of that. The long reference appears exactly once, and anyone reading the model can see where the data comes from by name alone.

The ref function is also what makes dbt work at all. Hard coding a table name pins you to one database, while ref compiles to whatever schema the active target points at.

The three column blocks

After the imports, the final select is split into three commented blocks, always in the same order.

• Surrogate key. The single value that should be unique for a record in this table.
• Descriptive values. Names, categories, booleans. The context the dimension exists to provide.
• Data warehouse values. Columns such as deleted and active flags, used when you track history.

We are not loading incrementally here, so the data warehouse columns are nulls. I still leave the block in place with a header, because that is where those columns go the day you need them.

with franchises as (

    select * from {{ ref('stg_franchises') }}

),

teams as (

    select * from {{ ref('stg_teams') }}

),

final as (

    select
        -- surrogate key
        {{ dbt_utils.generate_surrogate_key([
            'teams.team_id',
            'teams.coach_id',
            'teams.gm_id'
        ]) }} as team_sk,

        -- descriptive values
        teams.team_id,
        teams.team_name,
        franchises.franchise_name,
        teams.coach_name,
        teams.general_manager_name,

        -- dw values
        null as dw_is_active,
        null as dw_deleted_at

    from teams
    left join franchises
        on teams.franchise_id = franchises.franchise_id

)

select * from final

Why the surrogate key is a combination of columns

A surrogate key is an internal key you create during development. It does not exist in the real world, it exists so you control what uniqueness means inside your warehouse.

Here it hashes the team, the coach and the general manager together. If the coach changes, the row gets a different team_sk, which is exactly what you want when you start tracking history.

It also makes duplicates cheap to catch. Two rows with the same key is a test failure, not a mystery you debug by eye.

Install dbt_utils for the macros

Compile the model as written and it fails: dbt_utils is undefined, calling a macro that does not exist.

That is expected. dbt_utils is a public package maintained by dbt Labs, and packages are opt in per project.

packages.yml and dbt deps

Browse the available packages at hub.getdbt.com, then declare the one you want at the root of the project in a file called packages.yml.

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1

Declaring it is not enough. One more command pulls it down and installs it into dbt_packages/.

dbt deps

Two macros from that package carry this whole build. generate_surrogate_key produces a cross database hash from a list of columns, and date_spine generates one row per day across a range you give it.

Check the compiled output

Run dbt compile again and it succeeds. Open target/compiled/ and find dim_teams to see what the macro actually wrote.

You get a concatenation of the columns you passed, each cast to a string and handled for nulls, wrapped in a hash function. That is work you do not have to write or maintain.

Materialize the warehouse layer as tables

Staging models are views. Warehouse models should be tables, and that is a folder level setting, not a per model one.

Open dbt_project.yml, copy the staging block, and point the copy at warehouse.

models:
  my_project:
    staging:
      +materialized: view
      +schema: staging
    warehouse:
      +materialized: table
      +schema: warehouse

The +schema value works with the custom schema macro from the earlier lesson. In development everything still lands in your personal schema, and once the target is prod it lands in a schema called warehouse.

Build the three dimensions

dim_teams

Run the single model with a selector rather than the whole project.

dbt run -s dim_teams

It creates a table, not a view, with 32 rows. Query it and every record has its own hash value, with the source columns joined together alongside it.

The select itself is almost boring, because the renaming and casting already happened in staging. That is the payoff of the layered approach.

dim_games

Same structure, one import CTE instead of two, and an extra block for dates since this table has them.

Everything in it is descriptive context. Nothing here gets aggregated, which is the clearest signal that a column belongs on a dimension rather than a fact.

dim_calendar_dates

This is where date_spine earns its place. The macro fills a CTE with one row per date, and you decorate those rows with whatever date parts you want to reuse.

with spine as (

    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2000-01-01' as date)",
        end_date="cast('2035-01-01' as date)"
    ) }}

),

final as (

    select
        {{ dbt_utils.generate_surrogate_key(['date_day']) }} as calendar_date_sk,
        cast(date_day as date) as calendar_date,
        extract(dayofweek from date_day) as day_of_week,
        extract(quarter from date_day) as quarter_of_year,
        extract(month from date_day) as month_of_year,
        format_date('%b', date_day) as month_short_name,
        format_date('%B %e, %Y', date_day) as full_date_name

    from spine

)

select * from final

Set it once and never reinvent it. Day of week, quarter, short month name, long date: all defined in one place, available to every downstream query.

If your team has a custom way of referring to dates, this is where it lives. I have seen teams want a text field for the Sunday to Saturday week, and a calendar dimension gives every row one.

Commit in small pieces

Three separate commits came out of this lesson rather than one: add packages, add warehouse config, add initial dimensions.

Breaking them up gives you a readable log. Months later the commit history tells you when the package arrived and when the config changed, instead of one lump labeled "warehouse".

Related reading

• Creating a Data Model w/ dbt: Facts (Part 2/3)
• Creating a Data Model w/ dbt: Tests (Part 3/3)
• The 4 Types of Dimensions
• The "Key" To Building A Reliable Data Model

Final takeaway

The dimensions are the part of a star schema most teams rush, and then spend a year paying for. When I set this up with a small data team, the win is not the SQL, it is that every dimension model looks identical and the keys are generated the same way every time.