Is it ok to join marts tables ?

In-Depth Discussions
1

Let’s suppose I have on my marts layer the following tables:

  • dim_customers
  • dim_accounts
  • fct_transactions

I want to create a report that joins all these tables. Therefore my query will contain the 3 tables.

BUT I do not want to put the query in my visualisation tool. Instead I would like to create a table/view that joins these tables from the marts folder and my visualisation tool would simply do a select * from “joined_table”.

Should I create that “joined_table” in the marts layer by joining the dim and fct tables ?

How would you approach this scenario and what is your current approach ?

Thanks in advance

2

We do lots of this on my team. We have a separate directory called /datasets/ which is downstream of /marts/ where we put our joined and pre-aggregated datasets for our BI users to benefit from. This works well with our BI tool, Sigma, which has a presentation-layer concept of “Datasets” where we do the friendly naming, set column formats, add descriptions, etc. We materialize our datasets as tables in dbt. 95% of the time our BI tool is querying a flat table for a given viz/chart. We find it to be extremely fast, and our non-data expert end users get a lot of value. Happy joining!

Note: @Joe Bloom originally posted this reply in Slack. It might not have transferred perfectly.

1 Like