Why Staging Tables Earn Their Keep

Good staging tables give incoming data a place to wait before it becomes trusted data. They separate what the source delivered from what your application or report is ready to accept.

Land What Arrived Before Improving It

Raw landing should preserve the source representation where practical. If a date arrives as text, retaining that text helps explain a later conversion failure. Replacing it immediately with NULL destroys part of the evidence.

Keep source metadata beside the values. A batch identifier, source record reference, and extraction timestamp make investigation much easier. Preserve the original file separately when exact byte-level evidence matters.

CREATE TABLE #Landing
(
    BatchId int NOT NULL,
    SourceRowId int NOT NULL,
    AmountText nvarchar(50) NULL,
    SourceTimeText nvarchar(50) NULL,
    ExtractedAtUtc datetime2 NOT NULL,
    PRIMARY KEY (BatchId, SourceRowId)
);
INSERT #Landing VALUES
 (1, 1, N'12.50', N'2026-01-01T10:00:00', SYSUTCDATETIME()),
 (1, 2, N'unknown', N'2026-01-01T10:05:00', SYSUTCDATETIME());

This example uses a temporary table for a small demonstration. Production replay after a disconnected session needs durable storage. Choose retention and access controls appropriate to the data you are preserving.

Separate the Source Clock From the Load Clock

A source modification time describes an event in the source system. Extraction time describes when you collected it, and publication time describes when consumers could use it. Keep those meanings separate.

Do not overwrite an old source event time with today's load time just to make the row look current. That can distort reporting and incremental boundaries. Name time columns so their meaning and time zone are clear.

SELECT BatchId, SourceRowId, SourceTimeText,
       TRY_CONVERT(datetime2, SourceTimeText, 126) AS parsed_source_time,
       ExtractedAtUtc
FROM #Landing;

Parsing a timestamp does not establish its time zone. If the source supplies local time without an offset, record the agreed interpretation. Ambiguous daylight-saving transitions need a policy rather than an optimistic conversion.

Validate Before Promoting

Create a separate typed interpretation of the landed values. Check conversion, required fields, uniqueness, relationships, and business ranges. Keep rejected rows linked to their original batch and source reference.

SELECT BatchId, SourceRowId, AmountText,
       CASE WHEN NULLIF(LTRIM(RTRIM(AmountText)), N'') IS NULL
            THEN N'Missing amount'
            WHEN TRY_CONVERT(decimal(12,2), AmountText) IS NULL
            THEN N'Invalid amount' END AS rejection_reason
FROM #Landing
WHERE NULLIF(LTRIM(RTRIM(AmountText)), N'') IS NULL
   OR TRY_CONVERT(decimal(12,2), AmountText) IS NULL;

The rule above distinguishes missing text from text that cannot become the target number. Add domain checks after conversion. A valid decimal can still be an invalid amount for this particular business process.

Decide whether any rejected row blocks the whole batch. Partial acceptance is sometimes valid, but it must be visible and reconciled. Silent row loss is not a useful form of validation.

Make Publication a Deliberate Boundary

Consumers should know whether they are reading the previous complete batch or the next complete batch. Staging lets you prepare and validate without exposing half-finished work. The final publication mechanism depends on target size and concurrency needs.

SELECT BatchId, COUNT_BIG(*) AS landed_rows,
       SUM(CASE WHEN NULLIF(LTRIM(RTRIM(AmountText)), N'') IS NOT NULL
                     AND TRY_CONVERT(decimal(12,2), AmountText) IS NOT NULL
                THEN CONVERT(bigint, 1) ELSE CONVERT(bigint, 0) END) AS convertible_rows
FROM #Landing
GROUP BY BatchId;

This count checks only one conversion rule, not readiness for publication. Reconcile all accepted and rejected outcomes before marking a batch complete. Apply target changes and progress metadata transactionally when they share the same database.

Replay Without Asking the Source Again

A retained landing batch lets you correct a transformation and repeat it against the same input. That separates a code problem from a changing source. It also reduces unnecessary repeated extraction during investigation.

Store the transformation or rule version used for each attempt. A corrected rule may produce a different accepted set from the same raw batch. Keep both the reason and the resulting reconciliation visible.

Retries still need idempotent target behavior. Staging alone does not prevent duplicate invoices or repeated downstream messages. Use stable business keys and a clear committed-batch record.

Keep the Staging Area Manageable

Staging is not a permanent excuse to retain every sensitive source value forever. Define cleanup only after the required replay and investigation period. Protect access just as carefully as access to the final tables.

Index for the checks and joins that actually run, rather than cloning every production index. Monitor storage and cleanup failures. A staging area earns its place when it makes the load easier to explain and recover.

Staging is not unnecessary duplication, it is a boundary between arrival and acceptance.

This post was rewritten from scratch in September 2026. The original, published on 2012-03-05, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.