#71: The Missing Piece In Many Data Pipelines

Every data team has to deal with source data, and source data is almost never ready to report on. The piece that gets skipped is the very first transformation step, which I call staging. A staging layer is one view per raw source table, one to one, where you handle the cleanup that belongs to that table alone: renaming columns, rounding numbers, simple conditionals, filtering out junk rows. Nothing is joined there. Everything downstream selects from the staging view instead of from the raw table, which gives you three things: modularity, consistency and clarity. Without it, that same light cleanup gets rewritten in ten different places and drifts.

What a staging layer is

Most teams handle source data in one of two ways. They reference the raw tables directly in query after query, or they spin up ad hoc custom tables to patch over small formatting problems.

Neither one has a strategy behind it, and neither one has consistent naming. That is the gap staging fills.

Where it sits

I use a three layer approach: staging, warehouse, marts. Staging is layer one, the step that happens before any real modeling starts.

In front of it sit the raw source tables. That is wherever your extraction tool drops data, usually a cloud database, and I like to keep each source system in its own schema.

That raw data is unformatted and untouched on purpose. Nobody edits it, nobody queries it directly.

What you build on top

For every source table you actually use, you create one staging view. One to one, no joins, stored somewhere separate. I put mine in a schema called staging.

I would not do this for every table in the source. Do it for the ones you need, and add a new staging view at the moment you reach for a new table.

The content is light cleanup that belongs to that table alone:

• Rounding numbers to the precision you want
• Renaming columns to a consistent case, snake case or whatever you choose
• Simple conditional logic specific to that one table
• Filtering out rows you know are test data or garbage

select
    order_id,
    customer_id,
    lower(status)                as order_status,
    round(order_amount, 2)       as order_amount,
    order_ts::date               as order_date
from raw_gsheets.orders
where is_test_record = false

From then on, every query that needs that source selects from the staging view rather than from the raw table.

Why a view and not a table

I keep saying view because I mean the database object. A table stores data, a view only runs its select statement when you query it.

Staging is a cleanup pass, not new information. Materializing it as a table would double your storage for no reason.

Reason 1: Modularity

Modularity means breaking things down to their most basic components so you do not repeat yourself.

The ten query problem

Say one raw source table gets used in ten different queries, maybe twenty. In each one, a column is cast to a decimal with one decimal place, because that is what you agreed on.

Then somebody asks for two decimal places. Now you are editing that logic in ten or twenty places and hoping you found them all.

With a staging model you do it once. The cast lives in the staging view, every downstream query selects from that view, and the change flows through from a single edit.

Why it adds up

That is one column in one source. Multiply it across many columns and many sources and the difference in how fast your team moves is real.

Everyone agrees with this when you say it out loud. It still gets missed constantly, usually because nobody set the layer up before the first twenty models were written.

Reason 2: Consistency

Consistency is the second benefit and it is close kin to the first. Modularity is about not repeating the work, consistency is about not repeating it differently.

If every developer writes their own version of a simple transformation wherever they happen to need it, you have to trust that all of them did it the same way. With a staging layer you know where the logic lives and what it says.

A naming convention to borrow

The staging layer is also a good place to establish conventions, starting with how you name the object. The pattern I use came out of the dbt community and I have kept it:

stg_<source>__<table>

stg_gsheets__orders
stg_salesforce__accounts
stg_stripe__charges

A stg_ prefix, one underscore, the source system, then two underscores and the table name.

Plenty of people find that verbose for the name of a view. The verbosity is the point: it removes the per developer naming preferences that make a project hard to read.

One home for source specific logic

The other half of consistency is where source centric transformations live. By that I mean anything that applies to a single table and needs no joins.

Here is the signal to watch for. If you are deep in the warehouse or marts layer writing a transformation that touches only one table, that logic belongs back in staging where everyone else can use it.

Moving it there gives you one obvious answer to a question that otherwise gets answered differently every time.

Reason 3: Clarity

Clarity in a data architecture is arguably the most valuable thing you can have, above almost anything else.

What the name tells you

When a staging object shows up in a query, three things are immediately readable:

• What kind of object it is. It is in the staging layer, so it is a view.
• Which source it came from. The source system is in the name.
• What was done to it. A cleaned up, one to one version of that source table, nothing more.

Compare that to a project with inconsistent naming, where answering the same three questions means reading the query, digging through the project, or asking whoever wrote it.

A clear starting point for new sources

It also gives you a repeatable answer when something new arrives. A new table from an existing source, or a whole new system the company just bought, follows the same path.

Create the staging view, rename the columns, handle the light transformations, and use it from there. No debate required.

A gate for bad data

Because a staging model is a query, it is also a place to control what gets through. If you know some rows are test records or are simply wrong, a where clause in the staging view keeps them out of everything downstream.

That filter is explicit, version controlled and visible to the whole team. Everybody can see what is being excluded and why.

The tempting shortcut

It is genuinely tempting to take source data and start transforming it straight into reporting. That is where the visible value is.

The upfront staging layer is what keeps that work from collapsing later, and it costs very little to put in.

Related reading

• How to Create a 3 Layer Data Model Pipeline
• dbt Project Naming Conventions
• The Key to Building a Reliable Data Model
• Data Modeling in the Modern Stack

Final takeaway

When I come into a messy pipeline, the fix is rarely a better model. It is usually that nothing was cleaned up before the modeling started, and adding a staging layer is the cheapest piece of structure a small team can put in.