# Dimensional modeling, adding foreign keys unavoidable duplication of joins?

**URL:** <https://discourse.getdbt.com/t/dimensional-modeling-adding-foreign-keys-unavoidable-duplication-of-joins/12691>\
**Category:** In-Depth Discussions\
**Created:** [April 1, 2024, 3:43pm UTC](https://discourse.getdbt.com/t/dimensional-modeling-adding-foreign-keys-unavoidable-duplication-of-joins/12691 "2024-04-01T15:43:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![maurice](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/maurice/32/3831_2.png) [@maurice](https://discourse.getdbt.com/u/maurice)\
**Post date:** [April 1, 2024, 3:43pm UTC](https://discourse.getdbt.com/t/dimensional-modeling-adding-foreign-keys-unavoidable-duplication-of-joins/12691/1 "2024-04-01T15:43:02Z")

</div>

**Context**  
I am building a star model for a transaction fact in dbt. (names simplified)

We start with all kind of stagings related to order. e.g.  
**stg\_orders**  
**stg\_order\_purchase**  
**stg\_order\_refund**  
**stg\_order\_product**

from there I would make all kind of event tables that just join in the bare minimum tables to get order\_id + some financial fields:

**int\_order\_event\_x**  
order\_id  
event\_date  
event  
financial column

And I make the below table joining in all kinds of tables to get a skeleton of foreign keys.  
**int\_order**  
order\_id  
product\_id  
refund\_id

**fct\_orders** union all of the int\_order\_event\_x tables and then ‘from int\_order left join events’  
order\_id PK  
product\_id FK  
refund\_id FK  
event\_date  
event  
financial columns

**dim\_orders**  
order\_id PK  
“case when on some fields existing in stg” end as reporting\_channel.  
status  
etc

**Problem**  
We have a budget file with target # orders per reporting\_channel.  
I must aggregate the fct\_orders to the reporting\_channel level to create a fct\_budget.  
But my logic to determine reporting\_channel is in dim\_orders. So then I would need to make fct\_budget by joining fct\_order + dim\_order. This is not preferred because if someone adds a column to dim\_order dbt starts to rebuild this downstream fct\_budget as well.

So probably you would want to define reporting\_channel in int\_order and then use it in fct\_order.  
_Note: you could say reporting channel could actually be a dim with reporting\_channel\_id in the int\_order but its really only 1 field so think its just better to keep it the fact directly._

**Question**  
Lets say reporting channel is based on several fields from different tables, but other values in those tables are actually also added to dim\_orders.

Am I not duplicating a lot by joining all these tables to generate int\_order, and then to make dim\_order I would need to do  
int\_order  
left join the same tables I’ve already joined to make int\_order.

This is also relevant for the foreign keys in general. I have to join a lot of tables to create int\_order, and then in creating the dimension, I use the same staging table to store e.g. the entity name.

Or is this the correct way you should do things?

---

<div class="post-metadata">

**Author:** ![PeakOil101](https://avatars.discourse-cdn.com/v4/letter/p/a6a055/32.png) [@PeakOil101](https://discourse.getdbt.com/u/PeakOil101)\
**Post date:** [April 20, 2024, 3:30am UTC](https://discourse.getdbt.com/t/dimensional-modeling-adding-foreign-keys-unavoidable-duplication-of-joins/12691/2 "2024-04-20T03:30:48Z")

</div>

It sounds like your list of reporting channels is not managed but is based on some business logic.

Ideally try to create logic that creates the unique set of Reporting Channels with an id field as dim\_reporting\_channel.

This allows reporting \_channel\_id to be used/contained in fct\_budget and also fct\_order without a dependency (except for there will be a dependency on the transformation that creates dim\_reporting\_channel.)