Dimensional modeling, adding foreign keys unavoidable duplication of joins?

In-Depth Discussions
1

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?

2

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.)