Creating a Data Model w/ dbt: Facts (Part 2/3)

A fact table records what happened: the numeric measurements of a business action, plus the keys that point at the context around it. In dbt you build it as a model in the same warehouse folder as your dimensions, prefixed fct_ so it reads at a glance next to dim_. The measures come from your staging models, never from the dimensions, because you want the values as they are today. The dimensions are joined in for one reason only, to pick up their surrogate keys, which bakes the joins into the model so nobody downstream has to rediscover them. Dates are the exception worth knowing: because a date never changes, you can regenerate the calendar surrogate key inline instead of joining the calendar dimension at all.

Where this sits in the series

This is Part 2 of three. Part 1 built and configured the dimension tables, and Part 3 adds baseline data quality tests.

The dimensions already exist, so now the star schema gets its center. Under the facts folder, create one model named for the business action it records, in this case fct_games_played.

The prefixes are deliberate. dim_ and fct_ are both three letters, so names line up in the file tree and in the warehouse, and the type of the table is obvious before you open it.

Design the table around measures and keys

Dimensions are about description. A fact is about measurement: what actually happened, and what number came out of it.

That gives the model four blocks, and I comment each one the same way I do in a dimension.

• Surrogate key. One hashed value per fact row.
• Dimension keys. The surrogate keys that connect to context.
• Numeric values. The measures, including any you derive.
• Dates. Kept here for convenience, with a caveat below.

Is a surrogate key on a fact necessary?

Technically no. If the grain is right, a fact row is already unique and there should never be duplicates.

I add one anyway, because consistency across the warehouse is worth more to me than saving a column. Every table then has the same kind of key to test against.

Measures come from staging

Metrics are selected from the staging models, not from the dimensions. You want the values as the source reports them today, not as a dimension happens to hold them.

Some measures are straight from the source, and some you invent. In this build I added an average home points per quarter column and an away point differential, to show that the fact table is where derived numbers belong.

I also added a home win count, a one or zero per game. Pre-computing a flag like that turns a future question into a sum. How many games did home teams win becomes one aggregation instead of a case expression in every report.

Connecting to the dimensions

This is the part that takes real understanding of your data. You have to know what links to what, and establish a one to one relationship between the fact row and the matching context row.

Sometimes it is obvious, such as a game id in staging matching the game id on dim_games. Sometimes the link is less clean and you have to reason it out.

The important thing is that you only have to do it once. Once the join is defined in the fact model, every mart and every analyst downstream inherits it.

Filtering to active records

If your dimensions track history, a single business entity may have ten rows, nine of them inactive. Join on the natural id alone and you multiply your fact rows by ten.

The fix is an extra join condition on the active flag.

left join dim_games
    on games.game_id = dim_games.game_id
    and dim_games.dw_is_active

We are not tracking history in this project, so the flag is not doing real work yet. The habit is worth forming before it bites you.

Joining the same dimension twice

A game has a home team and an away team, and both are teams. So dim_teams gets joined twice under two aliases, producing two separate surrogate keys on the fact row.

You do not need two dimension tables for that, just two joins. Resolving it here means the marts layer can ask about home team performance without rebuilding the relationship.

Dealing with dates

Dates on a fact table are a gray area, and it comes down to preference. Strictly speaking the date belongs on the calendar dimension, but if it is a date I filter on constantly I keep the raw value on the fact too, so simple queries skip the join.

The surrogate key for the date is the interesting part. For teams and games, recreating the key would mean reaching back into all the underlying columns, so joining to the dimension is the sane path.

A date is different, because a date never changes. Cast the column to a date, run it through the same macro the calendar dimension used, and you get a key that is guaranteed to match.

{{ dbt_utils.generate_surrogate_key([
    'cast(games.game_date as date)'
]) }} as game_date_sk

No join needed. And if a fact has three or four dates you want to slice by, you copy that line and change the column.

A quick review before you run it

Step back and the model reads in one pass. Import CTEs at the top, then a final select whose blocks go key, dimension keys, measures, dates.

Every column has an obvious home, and a reviewer can tell in seconds whether something was put in the wrong place. That is most of what a convention is for.

The full fact model

with games as (

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

),

dim_games as (

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

),

dim_teams as (

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

),

final as (

    select
        -- surrogate key
        {{ dbt_utils.generate_surrogate_key(['games.game_id']) }} as game_played_sk,

        -- dimension keys
        dim_games.game_sk,
        home.team_sk as home_team_sk,
        away.team_sk as away_team_sk,
        {{ dbt_utils.generate_surrogate_key([
            'cast(games.game_date as date)'
        ]) }} as game_date_sk,

        -- numeric values
        games.home_team_points,
        games.away_team_points,
        games.home_team_points / 4 as avg_home_points_per_quarter,
        games.away_team_points - games.home_team_points as away_point_differential,
        case when games.home_team_points > games.away_team_points
             then 1 else 0 end as home_win_count,

        -- dates
        cast(games.game_date as date) as game_date

    from games
    left join dim_games
        on games.game_id = dim_games.game_id
    left join dim_teams as home
        on games.home_team_id = home.team_id
    left join dim_teams as away
        on games.away_team_id = away.team_id

)

select * from final

Deploy it and check the result

dbt run -s fct_games_played

Refresh the database and the table is there. Each row carries its own fact surrogate key, a game key, a home team key and an away team key, then the numbers, then the date at the end.

You will see repeated alternate keys in the date column, which is expected when several games happen on the same day.

Dimensions run first

This is the practical reason Part 1 came first. The fact model refers to the dimensions, so they have to exist before you can build or validate it.

The same order holds in production. A scheduled run builds dimensions, then facts, then anything downstream.

Commit, then add the guardrails

Commit the fact table on its own so the history reads clearly. At this point the development branch has staging views, three dimensions and one fact table.

What it does not have is any check that the joins behaved. We just wrote custom logic that could silently introduce duplicates, and with a small dataset you can eyeball it, with a real one you cannot.

That is what Part 3 is for.

Related reading

• Creating a Data Model w/ dbt: Dimensions (Part 1/3)
• Creating a Data Model w/ dbt: Tests (Part 3/3)
• The 3 Types of Facts
• Do You Need a Date Dimension?

Final takeaway

A fact table is where you make the joins once so nobody has to make them again. On every client project the measure of a good fact model is the same: can an analyst answer a question without asking the data team how two tables relate.