# Building a Kimball dimensional model with dbt

**URL:** <https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940>\
**Category:** In-Depth Discussions\
**Tags:** devblog\
**Created:** [April 21, 2023, 1:57am UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940 "2023-04-21T01:57:38Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [April 21, 2023, 1:57am UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/1 "2023-04-21T01:57:38Z")

</div>

This is a companion discussion topic for the original entry at [Building a Kimball dimensional model with dbt | dbt Developer Blog](https://docs.getdbt.com/blog/kimball-dimensional-model)

---

<div class="post-metadata">

**Author:** ![emakarova](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@emakarova](https://discourse.getdbt.com/u/emakarova)\
**Post date:** [May 23, 2023, 10:19pm UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/2 "2023-05-23T22:19:29Z")

</div>

Hi folks!  
A great article, thank you for compiling it all together!  
Was confused by one moment : seems you suggest to create a visual ER diagram as only part of documentation, Part 6.  
Typically I start with visual ER diagram , and even use it to generate tables DDLs and even dbt code(If I work with dbt).  
Any reason you put it only at the end? Will not data engineers benefit from having it in from of them even before they started writing any dbt code, dbt tests etc ? Thoughts ?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [May 23, 2023, 10:19pm UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/3 "2023-05-23T22:19:29Z")

</div>



---

<div class="post-metadata">

**Author:** ![jonathanneo](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jonathanneo/32/2626_2.png) [@jonathanneo](https://discourse.getdbt.com/u/jonathanneo)\
**Post date:** [June 28, 2023, 9:45am UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/4 "2023-06-28T09:45:35Z")

</div>

Hey there,

Yes, it’s better practice to start with an ER Diagram first as a visual communication tool to stakeholders on what you’re going to create and quickly get their confirmation.

Usually, once the development process starts, the schema will change anyway.

At the end of the development process, it’s good to revisit the initial diagram and make any tweaks/updates to it to reflect the final ERD.

Cheers

---

<div class="post-metadata">

**Author:** ![Koen](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/koen/32/2815_2.png) [@Koen](https://discourse.getdbt.com/u/Koen)\
**Post date:** [August 2, 2023, 1:02pm UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/5 "2023-08-02T13:02:55Z")

</div>

Thank you, @jonathanneo, for your invaluable blog! It has been incredibly helpful during our transition from a traditional handcrafted DWH to dbt.

I have a question concerning the handling of missing records in dimensional tables. How would you suggest dealing with this situation?

Here are some related resources I found on the topic:

[Design Tip #43: Dealing With Nulls In The Dimensional Model](https://www.kimballgroup.com/2003/02/design-tip-43-dealing-with-nulls-in-the-dimensional-model/)  
[How do you deal with missing dimensions for foreign keys? - In-Depth Discussions - dbt Community Forum (getdbt.com)](https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274/5))

---

<div class="post-metadata">

**Author:** ![jonathanneo](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jonathanneo/32/2626_2.png) [@jonathanneo](https://discourse.getdbt.com/u/jonathanneo)\
**Post date:** [August 8, 2023, 2:11pm UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/6 "2023-08-08T14:11:58Z")

</div>

Hey @Koen, thanks for your question. The way I would handle records in the fact table that don’t match to a record in the dimension table is through the method that @josh suggested in his post [here](https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274/2).

So, let’s say our dimension table is `dim_user`, and we have the following columns:

- `customer_id`
- `customer_key` (the surrogate key, created by using `hash(customer_id)`
- `customer_name`

In the dbt model used to generate `dim_user`, I would add a row (using `union all`) for the following record:

```sql
select 
  -1 as customer_id,  
  hash(-1) as customer_key, 
  'Unknown Customer' as customer_name 

```

Then from the fact table (e.g. `fact_sales`), I will try to generate a surrogate key that matches `dim_user`.

```sql
select 
  sale_id, 
  price, 
  quantity, 
  hash(coalesce(customer_id, -1)) as customer_key -- if customer_id is null, -1 will be used instead. 
from 
  {{ ref('staging_sales') }} 

```

This way, the fact table will reference a customer with the customer name `Unknown Customer` instead of `null` which might cause confusion.

---

<div class="post-metadata">

**Author:** ![lassebenni](https://avatars.discourse-cdn.com/v4/letter/l/85f322/32.png) [@lassebenni](https://discourse.getdbt.com/u/lassebenni)\
**Post date:** [September 6, 2023, 2:00pm UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/7 "2023-09-06T14:00:50Z")

</div>

Hi @jonathanneo , thanks for the article. Finally someone talking about dimensional modelling in a dbt blog!

My question is the following though: you create a surrogate key for all models, even when they have a single natural key e.g.

```auto
{{ dbt_utils.generate_surrogate_key(['stg_product.productid']) }} as product_key, 

```

Isn’t this unnecessary since you already have the `productid` here? Or is it for consistency purposes? I have had this discussion with other AE’s where they feel that creating a surrogate when there is already a natural key is unnecessary.

Curious to hear your reasoning.

Kind regards,  
Lasse

---

<div class="post-metadata">

**Author:** ![ngmforest](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ngmforest/32/3013_2.png) [@ngmforest](https://discourse.getdbt.com/u/ngmforest)\
**Post date:** [October 4, 2023, 1:52pm UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/8 "2023-10-04T13:52:15Z")

</div>

Hello @lassebenni  
It’s recommended to use surrogate keys in your models to have control over your unique primary key. It prevents the DWH from being affected by operational changes we don’t control, such as a `productid` being reused in the future. That’s among other advantages, like being able to support dimension change tracking.  
But in this case, since the macro `generate_surrogate_key` generates a hashed `productid`, I don’t see how it’s different from directly using the natural key. Kimball recommends using an automatically incremented 4-byte integer but I don’t think that’s possible in dbt.  
I think the best practice would be to hash your natural key and a timestamp field, and use that as a surrogate key.

---

<div class="post-metadata">

**Author:** ![rorydph](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/rorydph/32/3079_2.png) [@rorydph](https://discourse.getdbt.com/u/rorydph)\
**Post date:** [October 10, 2023, 11:04pm UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/9 "2023-10-10T23:04:19Z")

</div>

Thanks for this; it’s answered a number of questions I had about the use of surrogate keys.

I’m currently working with a legacy three-tier EDW (Teradata) where we master our surrogate keys from the atomic tables in our raw tier into reference tables where they can then be reused across our platform. Is the above approach of generating the primary and foreign surrogate keys in the presentation tier sustainable across multiple marts e.g. if you had a generic dimensional model plus multiple domain-specific data models?

I’m new to dbt and am working out options of how to apply it In our current space, so I’m keen to understand what is good practice.

---

<div class="post-metadata">

**Author:** ![ngmforest](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ngmforest/32/3013_2.png) [@ngmforest](https://discourse.getdbt.com/u/ngmforest)\
**Post date:** [October 16, 2023, 11:06am UTC](https://discourse.getdbt.com/t/building-a-kimball-dimensional-model-with-dbt/7940/10 "2023-10-16T11:06:44Z")

</div>

Good question, I’m not sure. If we’re going by Kimball, he recommends assigning the surrogate keys at the end of the transformation process, just before loading the dimension tables into the DWH. The key map table that matches natural keys with the surrogate keys is in the staging part of the ETL system. This key map table is also used during fact table processing later on to make sure referential integrity is maintained.

Here’s two diagrams showing how surrogate keys are managed, taken from The Data Warehouse Toolkit. But the book is 10 years old by now, so I don’t know how relevant that’s still is.

 ![Screenshot from 2023-10-16 13-01-04](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/2/28c799eb2a4d8702fc4fc8a2ceb328cbdad1922e.png)

 ![Screenshot from 2023-10-16 13-01-24](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/d/de435e7af01ce00039dfe98a667063e97bdb3b3e.png)