Data Warehousing w/ dbt - A 3 Layered Approach

This is a friendly reminder that the "data grass" isn't always greener on the other side.

Everyone is doing their best and every business has their unique challenges.

But one thing I've recently noticed is that many teams struggle in the same area - the data warehouse.

While there's no one-size-fits all approach, I found myself repeating my recommendation over the past few weeks so figured I'd share here.

For context, what I'm going to share is focused around dbt projects.

The typical scenario is that a business starts a project on their own but quickly finds themselves with an unorganized and/or unscalable project.

Which is how they end up talking to me.

At a high level, here's the simple 3 layered approach I follow:

> Layer 1: Staging

> Layer 2: Warehouse

> Layer 3: Marts

Staging:

Create 1:1 with each source table (deploy as views to avoid duplicate storage)
Light transformations for modularity (ex. renaming columns, simple case-whens, conversions)
Break down into sub-folders by source system
Deploy to a Staging schema

models/staging/[source-system]

Warehouse:

models/warehouse/facts

models/warehouse/dimensions

Marts:

Pull from Warehouse (facts & dims allow for simple joins)
Create wide tables w/ multiple use cases (vs 1:1 for each report)
Either deploy to a single Mart schema or break up by business unit/user grouping

models/marts

(or) models/marts/[business-unit]

This doesn't cover other important topics like Environments, CI/CD & Documentation.

But if you're also working on your own project or considering approaches, hopefully this will help!

Other dbt users - how do you structure your project?

9 comments