Keep the shape of the source

In this chapter

• One source-aligned table per source table. The source’s grain and columns, reshaped only to fix an identified problem.
• Identity before deduplication. Three questions every row answers, and three things a repeated row can be.
• History, time and failure on purpose. Four history policies, the audit trail bronze already keeps, three clocks, and no row dropped silently.

What should a silver table look like before anybody integrates anything? AIMS (AI-ready, Meaning-first Silver) keeps the shape your source already has. Then it decides what that shape leaves open.

Two bars for silver, and the meaning level moves the higher one

Our post “Show Me Your Silver Layer” set two bars. It called a faithful copy of each source replication. And it said that “if silver holds one governed Customer fed by every system that knows one, the toolset is doing warehouse work.” Read literally, that demands one governed record per entity, decided before anything ships.

AIMS keeps the first bar and moves the second. What made the faithful copy replication? It was never the shape. It was that nothing in it said what the rows meant. On the five meaning levels AIMS uses to say how much a table tells its reader, that’s level 0, Typed. AIMS never publishes it.

Tie the table to its entity with a business key and its record source, the system the key is unique in. Give every reference its role and every critical column its business attribute. Critical columns are the ones that identify, relate, hold a code or hold sensitive data. The table is then conformed in meaning, with its shape intact.

The governed Customer becomes a golden record at level 4, built only when a specific business requirement needs one record across systems. That’s a deliberate revision of that post. Until someone asks, published tables that keep the source’s shape are complete silver.

One table per source table, reshaped only for an identified problem

The default fits in one line: one source-aligned table per source table, at the same grain, with the same columns, typed and renamed. The worked example has four, three from the order system and one from the sales system.

Wouldn’t a flat, pre-joined table be easier for a model? In a 2025 study by Kohita, it did worse on aggregate questions, because it invites double counting. So the source’s grain is the safer shape too.

Typing means the business kind, not the storage kind. A customer number is an identifier, so it keeps its leading zeros and nobody sums it. An order amount is a monetary amount, so it carries a scale and a currency column.

The default materialization is a view over persistent staging, the layer below silver that keeps every version a source delivered. So the view stores no second copy. A switch makes it a table when it’s read hard enough to earn one.

Restructuring needs an identified problem: an overloaded table holding unrelated record kinds, a repeating group that hides the grain (twelve monthly amount columns on one row), or one column holding three facts. Nothing else is split, merged, pivoted or flattened, because every restructuring is a decision somebody must keep defending.

Figure 5.1: one table per source table. The four source tables beside their source-aligned tables, with each one’s history policy beneath it.

Why does identity come before deduplication?

Every source-aligned table answers three questions before anything is removed.

• Which source record is this? The source primary key, or a hash of the whole row.
• Which business thing does it describe? The business key from its record source.
• Which version or event is this? The change sequence: the source’s change column, or else the load timestamp.

For the SalesOrder table, that’s its primary key, the company code and order number from record source awlt, and ChangedAt, when an order last changed. SalesOrderLine has no change column, so its versions follow the load timestamp.

Those answers make “duplicate” a precise word. A repeated row is one of three things, and only the first is a deduplication problem.

• A repeated delivery is the same record at the same version, sent twice. Removing it by source primary key and change sequence loses nothing.
• A legitimate version is the same record at a new change sequence, like an order moving from open to shipped. That’s history.
• A duplicate business identity is two records describing one business thing. Inside one record source, it’s a data quality finding. Across two, it’s a candidate for a match.

A blanket “keep the latest row per key” treats all three as the first. It discards the order’s history and silently picks a winner between two customers who never should have shared a number.

Choose the smallest history that answers the business question

History is a decision teams often make by accident. The template had a merge, so the table is current. AIMS makes the choice explicit, per table, from four history policies.

• Append keeps an immutable event or delivery.
• Current keeps the record as currently known.
• Versioned is the Type 2 pattern, one row per version.
• Snapshot keeps a dated copy, for a source that hands over pictures rather than changes.

In AIMS, the AI proposes each table’s policy from its entity class, ingest mode and change rate, and a person confirms it. In the worked example, Customer, SalesOrder and Account are versioned, each version with derived start and end dates, because their past matters. SalesOrderLine is current, because quantity corrections overwrite and persistent staging keeps the history.

Business history is never the storage engine’s time travel, which records when storage changed, not when the business did, and lasts only as long as a retention setting allows.

Bronze keeps the audit trail before silver starts

Bronze keeps what each source delivered. Its persistent staging keeps every version, insert-only and in the source’s shape. That’s your audit trail, and it exists before silver starts. A source-aligned table reads from it: typed, cleansed and deduplicated, with its columns mapped in the catalog. Give it a versioned or append history policy and silver is a cleansed persistent staging, history included. With current instead, it’s a typed, cleansed, deduplicated staging. Neither needs a vault to be auditable.

Three clocks, kept in three columns

Every change has three times: when the source changed the row, when the change took effect in the business, and when the lakehouse learned. Merge them and an as-of report goes wrong. Take a correction entered on the fifth, effective from the first and loaded on the sixth. Which date does a report as of the third need? It needs the effective date. No single “last updated” column holds it.

So AIMS names a column for each.

• The source change time is the source’s own stamp, like SalesOrder’s ChangedAt.
• The effective time comes from a source column or from a business rule a person approved.
• The load time is the load timestamp every load adds. Standing in for a missing change column, it still says only when the lakehouse learned.

In the worked example, no table or rule supplies an effective time, so none is invented.

Nothing is silently dropped

Every quality rule in AIMS carries one of three failure actions.

• Warn keeps the row, flags it in a quality column and counts it.
• Quarantine diverts the row, with the rules it failed, to a quarantine table and counts it.
• Block fails the load or the publish.

AIMS retires “drop”, and an existing drop becomes a quarantine. A silently dropped row is a lie in the row count. The totals look clean. As far as gold can tell, the order that failed its cast never happened. A quarantined row can still be counted, read and fixed.

The defaults follow the damage a bad row can do. Identity rules and cast failures quarantine, because a duplicate key corrupts every join and an unreadable amount corrupts every total. Referential rules warn, because a late reference is normal.

A delete policy says what a source’s delete does. The sales order table’s policy closes the version, so a deleted order keeps its past.

Under the schema evolution policy, a new source column arrives unmapped, tied to no business attribute yet. That’s a finding, not a failure. The policy keeps a removed column, marked deprecated, until a person retires it.

The structural answer	A vault takes the four tables apart into hubs, links and insert-only satellites, so quantity corrections become versions whether anyone needs them or not, where persistent staging had already kept every version.
What the agent reads	Each table’s source primary key, business key and change sequence, its history and delete policies, its three time columns and every rule’s failure action.
Yours to decide	Each table’s history policy and delete policy, and any rule whose failure action is block.

In DeltaVault today

Data tests are the nearest thing to failure actions that DeltaVault, our metadata catalog, ships today. Each test’s On Failure setting is Fail or Warn.