What a staging layer is actually for (and the four things it must never do)

Staging is the layer every methodology shares and nobody writes about, which is why it is where the strangest decisions get made.

The three jobs it does

1. It protects the source system

A warehouse load that reads directly from production holds locks, competes for I/O, and turns a slow transformation into a slow application. Staging reads the source once, as plainly as possible, and everything downstream reads staging. The production system has one cheap visitor per run rather than a dozen expensive ones.

2. It gives a failed load somewhere to restart from

Warehouse loads fail. A dimension lookup finds an unexpected NULL, a type conversion trips on one row in ten million, somebody renamed a column. Without staging, the fix means going back to the source and extracting again — and the source has moved on since. With staging, the extract is already done and the load restarts from the step that failed.

3. It is where you go when a number is wrong

Somebody says the revenue figure is off. The warehouse has resolved keys, applied history and summed things; the source system has been updated three times since. Staging is the only place holding the data as it was at the moment of the load, in the source’s own shape, so you can tell whether the warehouse mis-transformed it or the source sent it that way. That single property pays for the whole layer.

The four things it must never do

Every one of these is tempting, because staging is where the data is first in your hands and it feels efficient to tidy it on the way in. Each one quietly destroys one of the three jobs above.

It must not apply business rules

The moment staging filters out cancelled orders, or maps status codes to words, it stops being a copy of the source — and job 3 is gone. When the number is wrong you can no longer tell whether the rule or the source is to blame. Rules belong in the layer that is allowed to have opinions.

It must not resolve keys

Looking up surrogate keys during staging means staging depends on the warehouse being loaded first, which inverts the layer order and makes the restart point useless. Staging carries the source’s own keys; the warehouse load resolves them.

It must not rename columns

A staging column called what the source called it is one that can be traced back without a mapping document. Rename it there and every investigation starts with a translation step. Give things readable names in the warehouse, and keep the source’s names in staging where they are evidence.

It must not carry indexes or keys

This one is about performance rather than honesty, and it is counter-intuitive. A staging table is cleared and refilled every run, in bulk. On SQL Server, a heap with no indexes loaded with TABLOCK under the simple or bulk-logged recovery model is minimally logged; add a clustered index and every row goes through the transaction log. A primary key on a staging table also makes it refuse the duplicate rows the source legitimately sent, which is exactly the kind of thing you wanted to catch downstream rather than lose.

Types: keep them, with five exceptions

A staging column has the source column’s type. That is most of what “typed copy” means. There are a handful of types SQL Server cannot create or compare as they stand, and those are converted once, at the boundary, so nothing downstream ever meets them:

• hierarchyid, geography, geometry — CLR types; staged as text via .ToString()
• xml, sql_variant — staged as text via an explicit CONVERT
• timestamp / rowversion — eight bytes, not a date; staged as BINARY(8)

Everything else crosses unchanged. Widening a column in staging and not in the warehouse is how a load fails on a value too long for its target, so if a type must change, it changes the same way on both sides.

Truncate-and-reload, or persistent?

The classic staging table is emptied and refilled every run (TRUNCATE, falling back to DELETE for an account without ALTER rights, then a bulk insert). It holds the current extract and nothing older. That is the right default: cheap, simple, and enough for the three jobs.

A persistent staging area keeps every extract, usually with a load date, so you can see what the source said on any past run. That is genuinely useful for audit and for rebuilding history after a modelling mistake — and it is also what Medallion’s bronze and Data Vault’s raw vault are, under other names. If you find yourself wanting persistent staging, you are probably choosing one of those two.

Incremental staging — copying only rows newer than a high-water mark — is a performance choice, not a change of shape. Two things about it worth knowing: the watermark should be derived from the target (MAX(column)) rather than stored in a bookkeeping table, because a stored watermark and the real data drift apart the first time a run half-fails; and a created column and a modified column give opposite results — one misses every update, the other appends updates beside the rows they replace.

Staging, landing zone, bronze: what is the difference?

They overlap because they are answers to neighbouring problems. A landing zone is what you build when the modelling has not happened yet and you need the data somewhere safe to look at. Staging is what the design needs once it exists. Bronze is staging that promised never to forget.

Where it lives, and one detail about the log

Staging is usually a schema (stg) in the warehouse database, or a database of its own on the same server. On SQL Server the same server matters: the staging load reads the source with a three-part name ([SourceDb].[dbo].[Orders]), and that only resolves locally. A source on another server means a landing copy first, or a linked server, and both are decisions rather than defaults.

One small thing that saves an afternoon: put the load log — the table every load procedure writes a row into — in the warehouse schema, not staging’s, even though the staging procedures write to it too. One table then records a whole run. Split it by layer and you have two audit trails that have to be joined to answer “did last night work”.

Questions people ask