How to Create a 3 Layer Data Model Pipeline

A three layer data model pipeline moves raw source data through staging, warehouse and marts, and each layer has exactly one job. Staging is a set of views, one per raw source table, that rename columns, fix data types and apply simple formatting a single time. The warehouse layer builds the actual data model, usually a Kimball style star schema of facts and dimensions, and it reads only from those staging views. Marts join the warehouse tables into wider, flattened tables that dashboards, spreadsheets and data feeds connect to, so end users never touch the warehouse directly. It's the structure I put in place whether a team is starting from scratch or cleaning up a warehouse that has grown out of control, and the same idea scales well past the small example below.

The three layers at a high level

Picture a simplified data stack. Sources sit on the left, an ingestion tool loads them into a central landing zone, and from there the data moves across a series of layers before it lands in reporting.

A data warehouse is the central hub for most data teams, and in my experience it's also where the headaches concentrate. Either what's been built has gotten unorganized, or the team is starting fresh and doesn't know which conventions to adopt.

The approach I follow splits the warehouse into three layers:

• Staging. A cleaned up, one to one copy of each raw source table.
• Warehouse. Where the data modeling happens.
• Marts. The tables users and reporting tools actually query.

One rule holds it together

Each layer reads only from the one before it. Staging reads raw. Warehouse reads staging. Marts read warehouse.

Nothing skips a step, and that single rule is what makes the whole thing work.

If you've seen my videos on star schema modeling, this is how the two fit together:

• The staging layer is the same staging.
• The warehouse layer is where the star schema lives.
• Marts are the wider, flattened tables built on top of it.

Layer 1: Staging

A staging model is a one to one representation of a raw source table, with a naming convention that tells you where it came from.

The important design decision is that I deploy these as views, not tables. A view is an abstraction on top of the source table, so you aren't doubling your storage for what is essentially the same data with better column names.

What happens in staging

The work here is deliberately boring:

• Renames. Bring every column to a consistent snake_case.
• Type casts. Turn a date stored as a string into an actual date.
• Simple case statements. Convert a status code into a readable label or a boolean.
• Formatting. Round or standardize numbers.

These are all things you can do right up front, one time, so that nobody downstream has to do them again.

Layer 2: Warehouse

The warehouse layer is where most of the data modeling takes place. For me that usually means a Kimball style star schema: fact tables holding the events and measurements, dimension tables holding the context around them.

The key constraint is that every model in this layer pulls from the cleaned up staging views, never directly from the source.

That restriction feels small, but it's what keeps the layer focused. Since renames and conversions were already handled, the warehouse can be dedicated to the custom logic that's actually specific to your business.

Layer 3: Marts

Marts pull only from warehouse tables. Not staging, not source.

These are the tables connected to reporting, whether that's a dashboard, a spreadsheet or a data feed to another system, and everything in the layer is built with that audience in mind.

Split marts by audience

I often break marts into separate buckets, by department or by customer, because different user groups need different slices of the data. Isolating them gives you more control over security and visibility.

The users on the other end don't need to understand how the warehouse is structured. You're serving it up to them.

Fewer, wider tables

One preference worth stating: I don't like creating a mart table one to one for each report. Some people do, but it gets messy and creates a lot of fluff that goes out of date.

I'd rather have a few wider tables that get reused across several purposes.

Example: the staging layer

The example project is small on purpose, but I've applied this same structure to much larger architectures and it holds up.

Two raw schemas, one staging schema

On the database side there are two raw schemas, one for data loaded from Google Sheets and one for NBA game data. In a more modern cloud database the raw data often sits in a completely separate database, but here it's all in one place.

Next to the raw schemas is a staging schema containing no tables, only views, each one named so you can tell at a glance which source it came from.

The dbt project mirrors the same shape: a models directory with staging, warehouse and marts folders, and inside staging a subfolder per source, gsheets and nba.

That alignment between the project and the database is intentional. Keep it clean, keep it organized, and only add complexity later when you need it.

What a staging model looks like

Here's the shape of a staging model for a staff sheet loaded from Google Sheets. The source has:

• All capitalized column names
• An AS_OF_DATE stored as a string
• Nothing derived

with source as (

    select * from {{ source('gsheets', 'STAFF') }}

),

renamed as (

    select
        STAFF_ID                                   as staff_id,
        FULL_NAME                                  as staff_name,
        TEAM_NAME                                  as team_name,
        case when STATUS = 'A' then true else false end as is_active,
        cast(HIRE_DATE as date)                    as hire_date,
        cast(extract(year from cast(HIRE_DATE as date)) as integer) as year_hired,
        cast(AS_OF_DATE as date)                   as as_of_date
    from source

)

select * from renamed

Select from the raw table and you see capitalized names and a string date. Select from the view and you get:

• Clean snake_case names
• as_of_date as a real date
• A new year_hired integer that didn't exist in the source

The games model from the NBA source does the same kind of work, in that case flattening a JSON object into columns and adding a few simple booleans. In both cases the model reads from one source, one table, and only cleans it up.

Why one time matters

This looks simple, and it is. The point is that it's handled once, here, instead of what I see most teams do, which is redo the same conversions in several places.

That repetition leaves you open to human error and to two reports that disagree.

Repeat the pattern for every raw table, store the views in a staging schema in production, and you can group them by name.

Example: the warehouse layer

In the dbt project I split the warehouse folder into facts and dimensions, but when deployed they all land in the same warehouse schema as tables.

Every one of them references a staging model with ref('stg_...'), so you can see the lineage from the first line.

Business logic, not formatting

What stands out in the dimension models is how little is happening.

There will be cases where you need more logic, especially when you combine several staging models or need an intermediate common table expression in between, and that work belongs here.

But you aren't repeating the simple renaming and type conversion, because staging already did it. So the layer reads as business logic rather than formatting.

Example: the marts layer

The mart in this project is nba_games_detail. It joins the fact and dimension tables back together into one wide table with everything about each game on a given day, which is what an analyst or a dashboard actually wants to work with.

with games as (

    select * from {{ ref('fct_games') }}

),

teams as (

    select * from {{ ref('dim_teams') }}

),

final as (

    select
        games.game_id,
        games.game_date,
        home.team_name           as home_team_name,
        away.team_name           as away_team_name,
        games.home_score,
        games.away_score,
        games.home_score > games.away_score as home_team_won
    from games
    left join teams as home on games.home_team_key = home.team_key
    left join teams as away on games.away_team_key = away.team_key

)

select * from final

Keep mart logic light

There may be a little renaming here that's specific to a report, but the goal is to limit it. Staying modular means a change gets made once and flows through.

You can add custom logic in a mart if needed. Most of the time it's better to handle it in the fact table at a low enough grain and simply pull it through.

Why data modeling still matters

It's tempting to get cute, build everything as one big table and skip the modeling. I think that leaves you exposed to overly complicated logic that's painful to maintain.

With three layers there's a clear process from raw to staging to warehouse to marts, with reporting on the end. Your team knows:

• Where everything lives
• How to update it
• Which conventions to follow

Most of this is about mental clarity, not performance. If you spend your time figuring out where something should go, or worrying about what you might break, that's time not spent on changes that actually move the business.

A structure everyone understands gives that time back.

Related reading

• Data Architecture with dbt: Putting It All Together
• What Are Intermediate Models in dbt?
• A Simple 4-Step Process for Creating dbt Models
• The True Value of a Data Presentation Layer

Final takeaway

This is the default structure I set up when I come into a small data team, whether they're building a warehouse for the first time or untangling one that grew without a plan. It's simple enough to explain in a meeting and strict enough that it holds up as the project grows.