Let’s suppose I have on my marts layer the following tables:
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
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!
@Joe Bloom originally posted this reply in Slack. It might not have transferred perfectly.