Staging Layer

When a store is first built, there is no visible reason to divide it into layers. Pile up the source data and compute from it when you need something. With three tables and one person reading them, that is genuinely faster than designing a structure.

How far that goes is on record at Pagedeck. The single query counting monthly revenue reached 200 lines. Inside it, every run normalized date formats, excluded internal test accounts, and collapsed rows where a payment had been recorded twice.

The problem was that this query was not the only one using that cleaning. Revenue by plan, the weekly report, and the churned-account list made six queries in total, each carrying its own copy of the same exclusions.

The day three test accounts were added, six places needed editing, and Theo missed two of them. Two different monthly revenue figures went into that week's meeting. Both came from the same data; the sets of excluded accounts differed.

Doing the cleaning once, leaving the result as a table, and making everyone start from that table is the position this layer occupies.

What sits here is the source data with cleaning applied and nothing else changed. One table in the source corresponds to one table in this layer, and the row count and column layout stay broadly the same as the original. The only difference is that the values are clean.

Five kinds of work happen here.

1. Formats get unified. One source sends dates as 2026-01-05 and another as 01/05/2026, and a query reading both has to decide which is which every single time. Currency behaves the same way: payments arriving in dollars get converted and land in one column with the rest. One inconsistent format is enough to make a date filter drop rows without saying so.
2. Names get unified. The same value arrives as user_id from one source, account_no from another, and uid from a third. Settling on one name here means nobody has to work out which column matches which when tables get joined later.
3. Rows get excluded. Internal test accounts, rows left behind after a cancellation, records with an empty amount column. What was excluded lives as a rule in one place, and only that rule gets edited when the list changes.
4. Duplicates get collapsed. A collection job running twice and loading the same record in two copies is an ordinary occurrence, so a key is chosen and rows are merged until one event is one row. The key is usually the source's own event id, where it has one.
5. Types get fixed. A column that should hold numbers arriving as text gets converted here, so that whoever adds those values later is not converting them on every read.

What does not happen here is equally clear. Joining several tables into one row, and aggregating, both sit outside this layer. Those belong to the mart layer, shaped for analysts to query directly. Join early and it stops being obvious which tables have to be rebuilt when one source changes.

One property of this layer makes operating it easier: every table in it can be rebuilt from the source at any time, because the raw layer holding the untouched original still sits underneath. So discovering six months later that a cleaning rule was wrong means dropping this layer and rebuilding it under the corrected rule, rather than hunting for damaged rows.

Where the cleaning is written down is also settled. Under ELT, where source data lands in the store first and gets transformed inside it, a table in this layer is a query. What was cleaned and how stays readable as SQL, so it can be checked by reading rather than by asking someone.

The test for moving to the mart layer is whether the values here have become trustworthy. Four things get checked.

1. It passes validation. Keys do not collide, required columns are not empty, values fall inside a possible range. One row with a negative payment amount will quietly reduce a total in the mart.
2. Column names and types are settled. Renaming a column here later breaks every mart table referencing it at once, so naming decisions finish at this layer and get reviewed before anything downstream is built on them.
3. One row means one thing. If payments and refunds share a table and are distinguished only by the sign of the amount, counting that table directly in the mart books refunds as revenue. Events that mean different things get separate tables.
4. What was excluded is written down. Without that record, nobody can explain six months later why the mart's figure sits 3 percent below the source.

There are also times not to move on. While the source-side structure is still shifting, hold at this layer and postpone the mart. When a source starts sending one extra column, the difference between fixing only this layer and fixing five mart tables alongside it is considerable.

"Let's do that exclusion in staging. The same condition is sitting in six queries right now."

A proposal to gather the cleaning into one place.

"Don't aggregate in staging. It mixes the layers up."

Keeping this layer inside its role.

"We can drop this — it rebuilds from the source."

Confirming that this layer is reproducible.

At Pagedeck, Theo keeps one table in this layer per source. There is a naming rule too: the table name is prefixed with stg, so anyone reading it knows from the name alone that this is not the final table.

Mia does not query this layer directly. She comes down one level only when a mart number looks wrong. Having the two layers separated means a problem sorts itself into a cleaning-rule problem or an aggregation problem immediately, without searching the whole data pipeline, which is the practical payoff of the split.

Treating it as a scratch space is common. The word intermediate invites that reading. But the tables here persist and get rebuilt daily. Being disposable and being safe to discard are different claims: mart tables take this layer as their input, so deleting it stops everything above.

The second is thinking that aggregating here saves work later. It is already the layer that cleans things, so going one step further is tempting. But once aggregation arrives, a table in this layer no longer corresponds to one source, and from that moment it gets hard to trace what needs rebuilding when a source changes. The reason for having layers disappears, the same calculation scatters across tables again, and meetings go back to receiving two numbers.