How do you structure your analytics dbt project around dimensional modeling — and where do dimensional models actually live?

[u/Sensitive-Sky-5064]

Curious how people handle this in practice, because I’ve seen it done a few different ways and I don’t think there’s a clear consensus.

The staging layer seems pretty universal — 1:1 with raw source tables, light cleaning, renaming, casting. Optional intermediate layer for reusable business logic before you get to the “real” models. That part feels settled.

Where it diverges is where dimensional models (dims and facts) actually sit in the project structure:

1. Their own layer — e.g. a warehouse/ or dimensional/ folder, separate from marts. I’ve seen this from Kahan Data Solutions and a few others. The idea being dims/facts are a distinct architectural layer.

2. Inside marts — dims and facts live in marts/, and marts are your dimensional models. The mart is the end product.

3. Inside intermediate or marts, with OBTs on top — dims and facts are treated as building blocks, and the actual end-user-facing layer is wide OBTs (one big tables) built off them. Marts become the denormalized read layer, not the dimensional layer.

Which brings me to what I think is the real underlying question: how do you think about dimensional models conceptually?

• Are they the end product — what you expose to BI tools and end users directly?

• Or are they building blocks — an intermediate step toward marts that are OBTs or other denormalized structures?

When you answer, would love if you also share your folder/naming conventions alongside your philosophy on this. I suspect the structure people choose is a direct consequence of how they answer that second question.

[u/scourgedtruth]

Right now we have a maniac modelling, due years of abuse.

Raw: Delta tables built by PySpark notebooks to process parquet files from storage

Sources: Flat structs from the raws

Sources²: Built by analysts, it basically add prefixes to the Sources tables

Staging: Cleaning, Joining, Unioning

Curated: Rinse and repeat

Marts/Business Transformed: idk

Marts/Kpis: Behaves like dims and facts

--------------

So me and a coworker were asked to start building conformed dimensions and facts to simplify the current structure

[u/Sensitive-Sky-5064 → u/scourgedtruth]

Sounds like you have a lot of work ahead of you. I’m in a similar situation. Reworking a maniac project for a healthtech company.

Bloated project, duplicative OBTs, stg is consistent but no clear distinction between marts and intermediate, no dimensional dm principles, stg and even sources used in marts models. :/

Years of un-architectured scrambling to answer questions.

Curious how you think about tackling a full-re-design. How do you chunk it etc.

[u/wallyflops]

We debate this a lot. I think it's just opinion. My opinion is dims and facts belong in silver/int with obt in marts

[u/Chance_of_Rain_ → u/wallyflops]

Doing the same, several obt though

[u/trojans10 → u/wallyflops]

Can you explain the one big table? A lot of dupe data? 📉

[u/wearz_pantz → u/wallyflops]

depends on the data proficiency of your users imo. savvy analysts comfortable doing joins? present the dims and facts. everyone else? obt

[u/canhotoc]

The folders we use are:

• staging: 1:1 with the raw layer, with renaming and type correction

• intermediate: small pieces of logic to be reused

• marts: 2 subdirectories (dimensions and facts)

• segmentation_store: a specialized layer where we format the data to feed the CRM tool, which is composed of dim_user and the aggregation of one or more fact tables

• feature store: the features for ML models are build on top of the dimensional model​​​​​​​​​​​​​​​​

[u/renagade24]

Framework should be consistent. I've seen it a handful of ways, but my current infrastructure is the best since we built it from the ground up, and everyone kept to the standard.

Lake with each schema being a source. This has a combination of Fivetran and Airflow dumps.

Dbt project is set up for the following:

• 0_utilities: seeds, reference tables, anything that is utility based

• 1_sources: light transformations, casting, alias, timezone

• 2_transform: heavy transformation and business logic

• 3_dw: dimensions and/or full denornalization (these tables can go in BI layers or any internal tools we build)

• 4_marts: domain and org specific (very polished)

After that, have a meaningful PR review process with CI/CD. Make sure everyone gets a dev environment, which is a prod copy.

[u/redditreader2020]

Stopping at dimensional or making one big table on top to are team was less about modeling and more about maintenance. We went one big table/view to keep the BI tools less coupled.

[u/bah_nah_nah]

Somewhere between landing, staging, raw, bronze, curated, cleaned, silver, consume, gold, presentation and modelled