Staging Table Drift: How Primentra Catches a Column That Stopped Arriving

A staging table only makes one promise: one column per attribute. Whatever inserts rows into it, whether that's an SSIS package, a nightly export from another system, or a hand-written INSERT, is trusting that promise to still hold. Most of the time it does, because Primentra rebuilds the table itself every time you save the entity in the editor. But there are two doors around that save, and data landing through either one can stop arriving in a column without a single error being raised anywhere.

This is what that looks like from the inside, and what Primentra does about it. Real stored procedures from our own codebase, not a summary of the docs page.

The one path that stays safe

Every time you save an entity in the entity editor, the API calls usp_Staging_RebuildTable for it, wrapped in a try/catch that quietly does nothing if staging is not enabled. Add an attribute, rename one, delete one — the staging table follows along in the same request. There is no notification for this path, because there is nothing to notify about.

Two other paths change the same attributes and skip that call entirely:

How the check works

usp_Staging_GetDrift pulls the entity's current non-system attributes into one table variable and the staging table's actual columns from INFORMATION_SCHEMA.COLUMNS into another, then diffs them both ways:

-- 'missing' = attribute with no column, or the table doesn't exist at all
INSERT INTO @Drift (EntityId, ColumnName, DriftType)
SELECT a.EntityId, a.AttrName, 'missing'
FROM @Attrs a
JOIN @StagingEntities se ON se.EntityId = a.EntityId
WHERE se.TableExists = 0
   OR NOT EXISTS (SELECT 1 FROM @Cols c WHERE c.EntityId = a.EntityId AND c.ColName = a.AttrName);

-- 'orphan' = column with no matching attribute
INSERT INTO @Drift (EntityId, ColumnName, DriftType)
SELECT c.EntityId, c.ColName, 'orphan'
FROM @Cols c
WHERE NOT EXISTS (SELECT 1 FROM @Attrs a WHERE a.EntityId = c.EntityId AND a.AttrName = c.ColName);

A missing column is an error. Data for that field can never land, silently, batch after batch. An orphan column is only a warning: it is dead weight, not a broken pipe. Nothing checks the column's data type, and that is deliberate. Every staging column is declared NVARCHAR(MAX) no matter what the attribute's real type is — the actual type is applied later, when usp_Staging_ProcessBatch runs the import actions and merge modes covered in our piece on staging table load strategies. Comparing types here would just be comparing NVARCHAR(MAX) to itself.

A table that was dropped outright counts as drift too, even for an entity whose only attributes are the built-in Code and Name. That case has zero rows to report as missing, so the procedure treats a gone table as an automatic error rather than relying on a row count that can legitimately be zero.

Where you actually see it

Drift detection is admin-only, and it does not poll. It runs once when an administrator signs in, checking every staged entity in one call, and again whenever that admin opens an entity's Staging Config tab, so a change made moments earlier shows up immediately rather than waiting for the next login. A drifted entity turns up two places: a notification in the bell menu with a "Go to staging →" link, and a banner at the top of the Staging Config tab naming the exact columns involved.

The fetch that powers both is wrapped so a failure never becomes a toast or console noise. But it also never gets to claim a false all-clear. The app tracks whether a drift check has actually succeeded at least once, separately from what the last successful check found. If the fetch fails, that flag drops back to "unknown" rather than quietly reusing the last good answer. The Rebuild dialog reads that same flag: it will tell you plainly that the check could not be completed rather than promising there is nothing to change when it has no way to know that.

The rebuild

usp_Staging_RebuildTable runs the same comparison and then acts on it, column by column:

-- Add whatever the entity has and the table doesn't
SET @SQL = N'ALTER TABLE ' + @TableName + N' ADD ' + QUOTENAME(@ColName) + N' NVARCHAR(MAX) NULL;';
EXEC sp_executesql @SQL;

-- Drop whatever the table has and the entity doesn't
SET @SQL = N'ALTER TABLE ' + @TableName + N' DROP COLUMN ' + QUOTENAME(@ColName) + N';';
EXEC sp_executesql @SQL;

It is ALTER TABLE, not drop-and-recreate, so every staged row survives the rebuild — Ready rows, Error rows, all of it. The only casualty is the value sitting inside a column that gets dropped, and the confirmation dialog names those columns before you confirm anything, alongside the ones being added. It also syncs StagingFieldRules in the same call — a new column gets a default merge rule, a dropped one loses its rule row — so the field configuration never lags one step behind the table itself.

One case rebuild cannot fix: a stg table dropped outright, outside the app. There is no table left to ALTER. The fix there is to disable staging on the entity and enable it again, which recreates the table from scratch.

When this actually bites

In practice, drift shows up in two moments. The first is a bulk model import, moving a model between a test and a production install, or bringing in a large set of entities during an MDS migration, where staging was already switched on for some of those entities before the import ran. The second is a DBA reaching for SQL Server Management Studio to make a "quick" column change on a staging table instead of going through the entity editor, usually because the change felt too small to bother with the UI for.

Neither one throws an error at the time. The staging batch, whether it runs on the scheduler or gets triggered by hand, keeps running, keeps reporting rows as processed, and the missing field just stays empty forever. That is exactly the failure mode that is hardest to notice on your own and easiest to catch with a check that runs on every admin login.

If you are coming from Microsoft MDS: this has no equivalent there. Nothing in classic MDS compares a staging table's shape to its entity and tells you when the two have quietly parted ways — you find out when a value you know you loaded never shows up, then go digging through the error views and error codes or work down the batch-not-processing checklist to find out why. That gap is one of the smaller reasons we built our own staging tables the way we did.