Medallion Layer Contracts for ERP Data

Every quirks guide in this library names conversions ERP data needs before it can be reported on: a Julian date that is a six-digit number, an amount whose decimal point lives in a configuration table, a status that is a two-character code, a boolean that is the letter T. None of those is controversial. What breaks platforms is not the conversion — it is that nobody said where the conversion happens, so it happens in two places, or in the wrong one.

Failure mode one: too late.The conversion lands in the report. Three dashboards decode the same Julian date, one of them handles the century offset differently, and you now have three answers to a question with one answer. Nothing failed; the numbers just don't agree, and the arbitration meeting is about arithmetic rather than about the business.

Failure mode two: too early.The conversion lands in bronze, and the landed copy is now “clean” and no longer the source's truth. You can't run a control total against it, because the numbers have been rescaled and the dates reshaped, so the one check that catches delete drift and value drift stops working — the reconciliation check in the data quality checklist. And when the conversion turns out to be wrong, the raw value is gone and the fix is a re-extraction rather than a rebuild.

The contract.Every transformation has exactly one home layer, and which layer it is follows from what the transformation does: preserve, convert, or decide. Bronze preserves the source's truth so it stays checkable. Silver converts representations so they are converted once and identically. Gold decides what the business means, because a decision is the one thing the two layers below it promised not to make.

One note on platform. Everything here is stated in Databricks terms — Delta tables, a Unity Catalog three-level namespace, Databricks SQL — because that is what this library builds on. The contract itself is platform-independent and applies unchanged on Snowflake; only the object names change.

The promise. Bronze is a faithful copy of what the source currently holds. Source column names, source representations: a Julian date stays a Julian number, a 'T'stays the letter T, an eight-character DATS string stays eight characters, an unscaled integer stays unscaled. If you can't reconstruct the source row from the bronze row, bronze has already broken its promise.

Four things bronze does do. It deduplicates a landed batch to one row per key, on the feed's watermark, because several change rows for one key in one file is an artifact of the delivery rather than a fact about the source. It translates the feed's delete signal into a physical delete — the standing rule of deletes & change tracking, restated rather than reinvented here: bronze reflects the current state of the source, and keeping deleted rows around “for history” is the mistake that starts the drift. It accommodates the platform where it has to — a namespaced EWM table name has to become a legal identifier — and it documents the map. And it records its own load metadata.

What bronze does not do: filter. Every client lands, every company code lands, every operating unit lands. MANDT, DataAreaId, CONO, ORG_ID and subsidiary are columns to carry, not predicates to apply. A bronze layer that holds one company can't answer whether the extract was complete, and the day the business adds a legal entity, the pipeline silently continues not seeing it.

History belongs to the table format, not to stale rows. “Current state” and “we need history” are not in tension: Delta Change Data Feed and time travel hold the versions, underneath a bronze layer that tells the truth about what exists right now. What that does not give you is business history: a table version records when the pipeline learned something, not when the business changed it. That is the same distinction the source history guide draws between a connector's physical capture of prior rows and the conformed-dimension SCD2 that gold models deliberately. The two are not interchangeable, and only the second is a modeling decision — one this page assigns to gold, below.

Why the promise pays. Two things depend on it. Source reconciliation — a row count and a summed measure on both sides, which only compares if both sides are in the same representation. And rebuilds: when a conversion rule is found to be wrong, silver is rebuilt from bronze in an afternoon instead of re-extracted from an ERP whose DBA team schedules windows by the month.

The promise.Every representation an ERP uses for storage rather than for meaning is converted here, exactly once, and every consumer downstream inherits the same answer. Silver is where this library's standing “convert once, never in the query” rule is actually implemented.

Across the eight references the work falls into five families, and the reason they are worth naming as families is that each one recurs with a different disguise per ERP.

1. Typed dates. A JD Edwards CYYDDD number, an SAP DATS character field whose '00000000' means unset, an Infor M3 numeric YYYYMMDD whose 0 means no date, a Dynamics 365 1900-01-01 that means never. All four become a DATE, and all four resolve their unset value to NULL before the cast rather than after it — an unset value that survives the cast becomes a real date in the 1900s and lands in a MIN.

2. Scaled numbers. JD Edwards implied decimals, scaled by the display decimals the configuration defines; SAP amounts and quantities, which are meaningless without the currency and unit columns stored beside them. The failure here is the quietest one on the page: the distribution is well-behaved and the number is simply wrong by a factor of ten to the n.

3. Decoded codes.JD Edwards UDCs, Infor M3's CSYTAB, Oracle EBS's FND lookups, Dynamics 365 enums, NetSuite's internal ids where you expected text. One rule covers all of them: keep the code and the decode. The decode is for the reader; the code is what the ERP stores, what a support ticket quotes, and what the next join needs. Dropping it in favour of the pretty label is a conversion that loses information.

4. Canonical booleans and join-ready keys. NetSuite's 'T' / 'F' strings become booleans. SAP's ALPHA leading zeros are decided once and applied everywhere. EWM's two GUID encodings are normalized to one. Oracle EBS's _TL translation joins are pinned to a single language, which is a key decision more than a text one — unpinned, the join multiplies every row by the number of installed languages.

5. The tenant column, carried and pinned. It is in the key, in every join, and in every group-by from here down. Silver is where that becomes structural rather than a habit each analyst has to remember.

What silver still is: source-aligned. Silver's EKKO is still EKKO. The entities are cleaned and conformed versions of the source's own objects, one row per business key, at current state — not a star schema, and not renamed into business language. Two mergers are legitimate here because the source split one thing across two tables for its own reasons: EWM's open and confirmed warehouse tasks, and JD Edwards' open and history sales lines. Both are unions of one entity, not modeling.

The failure to check for. A raw artifact that survived into silver has a signature that names its source — a “date” of 126236, a numeric 0, an '00000000', a 1900-01-01, a lone 'T'. Domain-check the outputs of every conversion: check 4 of the data quality checklist is exactly this contract, enforced.

The promise. Gold is the modeled layer: facts at a declared grain, conformed dimensions, surrogate keys, and business language. It is also the only layer allowed to make a decision — which is what makes it worth defending in both directions.

Facts and their grain. Movements, order lines, receipts and shipments become transaction facts; period-end balances become snapshot facts, whose measures are semi-additive — they add across products and sites and never across days, which is the whole subject of inventory snapshot patterns.

Conformed dimensions. This is where eight item masters become one dim_item, with a surrogate key and a declared source-system column, so a portfolio spanning two ERPs can be counted once. Nothing in silver can do this: silver is source-aligned by contract, and a conformed dimension is a modeling decision about what “an item” means across systems that disagree.

Naming is a house convention. The fct_ and dim_prefixes used across this library's pattern pages are a house choice, not a published rule — the dimensional-modeling literature specifies grain, fact types, conformance, and surrogate keys, and is silent on prefixes, singular versus plural, and what a conformed-dimension registry must record. Pick one convention, write it down, and enforce it in review; this library writes fct_.

The decision switches, implemented. Every metric in the supply chain KPI dictionary is a formula plus a set of authored switches — which date basis, which grain, which exclusions. Gold is where those switches are made explicit and implemented; silver is where they would have been made invisibly.

The discipline runs both ways. A representation fix in a gold view means silver failed: if a dashboard query is still decoding a status or scaling an amount, that conversion is being repeated and will eventually be repeated differently. And a business rule in silver is worse, because it is invisible: filtering SAP's deletion-flagged lines out of a silver entity looks like tidying, and it is a modeling decision smuggled into a layer that promised not to make any. Silver types the flag. Gold decides what it means.

The three layer contracts above are platform-independent. This section is not: it records what Databricks actually does when you build a star schema on it, tested rather than assumed, because several of the findings below produce a wrong number instead of an error.

Declared keys are documentation, not enforcement. A declared PRIMARY KEY accepted two rows carrying the same key value, and a declared FOREIGN KEY held a row whose parent does not exist — verified in one workspace on 2026-08-27. NOT NULL and CHECK are the exceptions: both rejected the write, with a named error. The house rule follows from that split. Declare the primary and foreign keys anyway, and without RELY— the declarations are the only machine-readable record of the star's edges, and the modeling and lineage tools read them — then enforce the integrity they describe with tests that run after the load, which is what the data quality checklist is for.

Never add RELY to a constraint the data does not satisfy. RELY tells the optimizer to trust an unenforced constraint, and on data that does not satisfy it the optimizer duly returns a wrong answer. A primary key declared RELY on a table holding one key value twice returned three values from SELECT DISTINCT, where two exist; a count and a sum taken through a RELY foreign key with a single orphan came back wrong, with no error and no warning — verified in one workspace on 2026-08-27. Two things narrow that, and both belong in the rule. On the foreign-key case the wrong answer appeared only when the query referenced no column from the joined dimension — the optimizer eliminates the join it has been told is safe — and that is exactly the shape of a summary tile, which counts a fact and reads nothing off the dimension it filters through; projecting a dimension column on the same tables returned the correct answer. That precondition is about join elimination and does not travel to the primary-key case, which is a single-table probe with no join to eliminate. And on genuinely clean data RELY is free performance. So the prohibition is not costless: it buys correctness on a query shape you do not control, and pays for it with an optimization you would otherwise have.

Surrogate keys are identity columns, not hash keys. Identity columns behaved as documented, including under the MERGE loads a Type 2 dimension is maintained with — verified in one workspace on 2026-08-27. The hash alternative fails in a way that has nothing to do with hash strength: a hash taken over concatenated natural keys returned one key for the pair ('12', '3') and the pair ('1', '23') — verified in one workspace on 2026-08-27 — because the concatenation produces the same string before the hash ever runs. The collision is in the concatenation, so a stronger hash cannot cure it. Where a hash key is unavoidable, delimit the components and coalesce each one to a sentinel first, and note what that leaves open: the delimiter is safe only while it cannot appear inside a key component, which is a property of your data and is untested here.

Say ISO per function, not per platform. The week-number functions return ISO 8601 values, and dayofweek()is Sunday-first — verified in one workspace on 2026-08-27 — so “the platform is ISO” is a claim about one function at a time. It has a direct consequence for dim_date: an ISO week_of_year sitting beside a calendar year_numberfiles 2025-12-29 as “2025, week 1”, which is a week of the following ISO week-year. The date dimension needs an ISO week-year column of its own beside the calendar year, or every week-over-week comparison that crosses a year end quietly moves a week.

Which of this is published, and which is ours. Grain-first sequencing, the fact-table types with their density properties, attribute-level conformance, the surrogate-key rule and the three columns a Type 2 dimension has to add are published dimensional-modeling doctrine. Two things above are not. Every dimension carrying an unknown or not-applicable row is required, but the -1 key value this library uses for it is a house choice; and Type 2 as the default, with Type 1 only where the business explicitly disclaims history, is a house judgment about which cost is worse. That is the same distinction the naming paragraph above draws for prefixes, and it is worth drawing every time: state the doctrine as doctrine and the choice as a choice.

Nobody publishes a number. A gold wide or aggregate object starts as a view and is materialized only for performance — which leaves the question of when. Across the first-party documentation of the four query engines examined and the guidance of the leading transformation tool, no numeric promotion threshold exists: not a query frequency, not a latency target, not a data volume, not a cost ratio. Every cue located is qualitative — pre-compute an expensive or frequently used query, switch when you notice a performance problem. That is a confirmed absence on the surfaces read, and it is worth keeping separate from what has not been read: the foundational academic treatments of this exact trade-off are paywalled, so they are unread rather than silent.

Query shape gates before economics gets a vote. Ask first whether the object can be maintained incrementally at all. Three vendors' restriction lists independently name the same three constructs: non-deterministic functions, an aggregate nested inside a further aggregation, and window functions. What each list does about them is not the same thing, so the agreement is on which shapes are hard and not on a consequence you can carry between engines — one vendor refuses to create the object at all, one refuses to refresh it incrementally, and one only bars a particular refresh mode and falls back to a documented alternative. Read your own engine's list for which of the three it does. On Databricks the observed dividing line for incremental-refresh eligibility was row-preserving versus aggregated output rather than join versus aggregate — a single-table filtered projection was refused where a two-table join carrying a GROUP BY was eligible — verified in one workspace on 2026-08-27. Read eligibility as structural, not as a runtime promise: it is decided before the cost model runs, so an eligible object can still be fully recomputed at volume.

The monitoring-string trap. A pipeline event log emitted COMPLETE_RECOMPUTE where the documentation gives FULL_RECOMPUTE, so a monitor filtering on the documented string would have matched nothing — verified in one workspace on 2026-08-27, on one object on one date. Treat the specific string as an observation to check in your own workspace rather than as a contract. What generalizes is the habit: a monitor keyed to a documented string is worth testing against a real event before anyone depends on it.

The economic test is a ratio, measured after the fact. Reads in a window times the cost per read, against the measured maintenance plus storage over that same window. Two properties make it a measurement rather than a forecast. Maintenance cost is driven by the change rate of the base tables and not by their size, so the same view over a slow-moving dimension and over a high-velocity fact is a different decision. And no vendor examined documents a way to estimate maintenance cost in advance; one says so outright. The honest trigger is therefore a time-boxed trial with a demotion rule written before the promotion, not a calculation.

The ladder runs in order, and deliberately without numbers, because any number here would be invented. Start with the shape gate above. Then a repeated-read gate: the view — or the queries an optimizer would rewrite onto it — runs often enough in the window, and a named consumer has it on a critical path. Then a material-per-read-cost gate, stated in a proxy unit such as task time or bytes scanned and never in currency. Then the ratio itself, which is satisfiable only by promoting, measuring both sides for one window and demoting if the inequality fails. Three operational floors close it out: a maximum tolerable staleness accepted in writing by a named consumer, since a refresh failure is an availability failure and not merely a freshness one; a refresh-failure alert that exists before the promotion rather than after the first incident; and a standing demotion rule, so the object goes back to being a view when the reads stop. Every floor is yours to set, and the point of writing them down before you promote is that you can be held to them afterwards.

Check the read side first. The query result cache holds for 24 hours and is invalidated by any update to the underlying tables; an identical re-run was served with no warehouse work at all — verified in one workspace on 2026-08-27. The dashboard cache invalidates differently: it can serve results up to 24 hours old after the data has changed, and serving from it does not start the warehouse. Between them they remove much of the repeated-read cost promotion is usually reached for, so measure whether the expensive read is real — a genuinely expensive query, or a cache-miss problem — before promoting anything. And the floors you do set are authored numbers with a provenance, which is the same discipline the checklist applies to data quality thresholds.

The two vocabularies do not meet. dbt's published layers are staging, intermediate and marts. Bronze, silver and gold appear nowhere in that guidance, so any mapping between the two is yours to author and to defend — it is not a correspondence dbt endorses. It is also not one-to-one. dbt staging is one-to-one with the source tables and prohibits the joins and aggregations that deduplication and entity resolution require, so a faithful silver is staging plus the base models dbt provides for reading raw and joining afterwards. Say the mismatch out loud rather than renaming folders until it disappears.

The direction agrees; the end state differs. dbt's rule of thumb is view first: start with a view, build it into a table once the view is too slow to query, make it incremental once the table is too slow to build. That is the same direction as the promotion rule above, and it ends somewhere else. This library keeps its wide and aggregate gold objects as views until the promotion test says otherwise, and promotes them to scheduled jobs rather than to materialized views, because a scheduled job gives an attributable compute cost and an audit trail of what ran when. That is a house choice on our own cost and audit reasoning, and it is stated as one.

Three things that do not do what they read like. merge, the default incremental strategy, behaves like an append when unique_key is omitted, so a load that reads like an upsert quietly accumulates duplicates. insert_overwrite replaces the whole table when partition_by is absent, rather than the partitions you meant. And model constraints are narrower than they look: only not-null and check are enforced, only on table and incremental models, only where the model declares an enforced contract with an explicit data type on every column — and dbt applies them with an ALTER after the table exists, so the failing rows are already in the warehouse when the run reports the failure.

Snapshots, and which strategy owns the effective date. dbt snapshots implement Type 2 history, and the strategy decides where the effective-from date comes from. Under the timestampstrategy it is taken from the source's own updated-at column; only under the check strategy is it assigned by the snapshot run. For the parameters a source overwrites in place — delivery-tolerance parameters and a plan-version governance table, which silver has to build because no source system holds their history — the requirement is precisely a lakehouse-assigned effective date, so check satisfies it and timestamp, which is the recommended default, silently does not.

One version note. Recent adapter versions raise the minimum runtime for every incremental model regardless of strategy — v1.11 requires DBR 12.2 LTS or higher. Settle the pin before the first incremental model is written, not after.

SAP's representations are the ones this library documents in most detail, and every one of them has an obvious layer. The client column is data, so it survives bronze; the character dates and the leading zeros are representations, so silver settles them; the deletion flags are judgments, so gold makes them.

SAP — bronze must preserve

MANDT, as part of the key rather than a filter — the landing MERGE in the extraction guide dedupes staged batches on the full key including it, and its DELETE branch is what keeps bronze at current state. Dates stay eight-character strings and identifiers keep their zeros; converting either here is what makes a reconciliation to source stop agreeing.

SAP — silver must convert

DATS characters become real dates with '00000000' resolved to NULL first; the leading zeros on NUMC and ALPHA identifiers are decided once, one way, for MARA and KNA1 alike, because half a platform storing them padded and half stripped is a join failure waiting for a quarter-end; and amounts are read beside their currency and unit columns rather than summed bare (character dates, keep the zeros). One release note belongs in the same layer: on S/4HANA a classic name may no longer be the storage, so silver decides once whether an entity reads the physical table or the compatibility view, and says which (the classic name may no longer be the storage).

SAP — the misplaced-fix trap

Filtering LOEKZ- or LVORM-flagged rows out of silver. A deletion indicator on an EKPO line under its EKKO header, or the LVORM flag on a MARA material, is a business fact, and whether a flagged line counts against open purchase-order value depends on the question. Silver types the flag; gold decides what it means, once, in the model.

Dates and times arrive as character fields →Identifiers are strings — keep the zeros →The classic name may no longer be the storage →

EWM is the reference where the layer question is asked twice: once about names, because slashes are not legal identifiers, and once about the warehouse task, which lives in two tables and is one entity.

EWM — bronze must preserve

The delivered namespaced content, landed one-to-one under a documented rename — /SCWM/… as scwm_…. Assert that map one-to-one where you can, so a collision can't silently merge two tables. The rename is a naming accommodation, not a transformation: the columns and their encodings are still exactly what EWM published (namespaced names in a lakehouse). The one thing bronze here has to do beyond landing is honor the DELETE on /SCWM/ORDIM_O: confirming a task removes it from the open table, so a feed that ignores deletes shows every task ever created as still open — and the silver union below then double-counts every confirmed task (raw table CDC and the open-task problem).

EWM — silver must convert

GUIDs normalized to one encoding, so the RAW16 keys on stock and handling units and the CHAR22 form on the product master meet as a conversion rather than as an equality that quietly matches nothing (two encodings). And the open and confirmed warehouse-task tables — /SCWM/ORDIM_O and /SCWM/ORDIM_C — are unioned here into one task entity carrying a status column, since a confirmed task is the same task, moved. The confirmed table is keyed one column deeper — TAPOS, the task item — so the union reconciles that grain explicitly rather than assuming the two keys match (confirming a task deletes it).

EWM — the misplaced-fix trap

Doing that union per dashboard. Throughput reports it one way, cycle time another, an open-work tile a third, and the three disagree about how many tasks the warehouse ran. The GUID-encoding choice goes the same way when it is left to the consumer: stock views built on /SCWM/AQUA and /SCWM/HUHDR each re-decide which encoding to convert to, and the two that chose differently join to nothing.

Namespaced names in a lakehouse →GUID keys, and the two encodings →Confirming a warehouse task deletes it →Raw table CDC and the open-task problem →

JD Edwards is the clearest case on the page for “once, in silver”, because both of its encodings are silent: a Julian date and an unscaled amount are perfectly well-formed numbers, and nothing downstream will raise a thing.

JDE — bronze must preserve

The CYYDDD numbers, the unscaled integers, the blanks and zeros standing in for NULL, and the open and history sales tables kept apart: F4211 and F42119 are two tables in the source, so they are two tables in bronze, which is what lets a control total be run against either one.

JDE — silver must convert

Julian to DATE and implied decimals scaled by their display decimals (CYYDDD, implied decimals); UDC codes decoded once against F0005 with the code kept beside the text; item and address identifiers resolved to the internal key the transaction tables join on (ITM, AN8) against F4101 and F0101, and the right-justified character keys — a business unit, a UDC DRKY — settled on one padding rather than trimmed in half the queries. And the union that bronze declined to make happens here: one order-line entity spanning open and closed lines, so a report never has to know that a sales update moved the row (F4211 → F42119).

JDE — the misplaced-fix trap

The Julian decode written into the report. Three dashboards, three decodes, three answers — and the one that forgot the century offset looks the most plausible of the three, because it returns a date.

Julian dates (CYYDDD) →Implied decimals →Sales history (F4211 → F42119) →

Dynamics 365 arrives through a managed feed, which makes bronze's job small and specific, and leaves silver with three conversions that are each one line and never optional.

D365 — bronze must preserve

DataAreaId on every row, RecId as the join spine, and current state: the feed's IsDelete soft delete is applied as a physical delete on landing, which is the standing rule of the deletes guide rather than anything new here (incremental & soft deletes). Enums stay integers and unset dates stay 1900-01-01.

D365 — silver must convert

Integer enums decoded to their labels, with the integer kept; the 1900-01-01 sentinel NULLed before any MIN, AVG or date arithmetic reads it as a real day; and InventDim resolved, so a quantity on InventTrans or a line on SalesLine carries its site, warehouse and batch as columns rather than as a dimension id nobody joined (enums, InventDim). Item attributes come off InventTable in the same pass.

D365 — the misplaced-fix trap

Treating the enum decode as a report concern — a CASE ladder in a dashboard, copied to the next dashboard. Enums are extensible per deployment, so the copies drift apart at the exact moment an ISV adds a member, and only one of them gets fixed.

Enums (option sets) →InventDim (inventory dimensions) →Incremental & soft deletes →

M3 is the one reference where the source itself publishes multiple versions of a record, so the current row is a choice made on arrival: the Data Lake carries every variation of every record, and landing means picking one.

M3 — bronze must preserve

The landed objects deduped to the highest variation per key, with the delete indicator honored so a removed record leaves bronze too — the same current-state rule every other feed on this page follows, reached by a different mechanism (variations & deletes). Column names keep their two-character prefixes and dates stay numbers.

M3 — silver must convert

Numeric YYYYMMDD to DATE with 0 resolved to NULL; CONO pinned into every key and join; CSYTAB decodes joined once, filtered to one language, with the code kept (CSYTAB); the prefix-and-alias question answered once, so MMITNO on MITMAS, MBITNO on MITBAL and MLITNO on MITLOC arrive under one name a modeler can join on (prefixes & aliases, CSYTAB). Status ladders keep their two-character code and gain a decoded band beside it, and CUGEX extension fields are joined in here rather than left for whoever needs them.

M3 — the misplaced-fix trap

Deciding in silver which status band counts as “open”. The ladder is a representation and belongs here; where the line is drawn on it is a business rule, and two departments will draw it differently on purpose.

Column prefixes & field aliases →CSYTAB (generic code table) →Data Lake variations & deletes →

Oracle EBS gives silver more work than any other reference on this page — translations, lookups and flexfields are three separate decode families — and gives gold the library's clearest conformed-dimension job.

EBS — bronze must preserve

The base tables as extracted, every operating unit's rows included: a landed _ALL table is striped, not filtered, and pre-filtering it in bronze quietly makes the copy unreconcilable to a source-side count. WHO columns are typed and carried — and never promoted to a watermark, because they are bulk-stamped by batch jobs and stamp nothing at all on a hard delete (WHO columns).

EBS — silver must convert

_TL translation joins pinned to exactly one language, or every row multiplies by the number of installed languages; FND_LOOKUP_VALUES decodes applied once, so a coded column like ITEM_TYPE on MTL_SYSTEM_ITEMS_B carries its label beside the code it stores; key flexfields resolved so the item number on that same table is a column rather than a configuration-defined SEGMENT position (_B / _TL pairs, key flexfields). And the org grain is documented in the entity's own naming, because ORG_ID and ORGANIZATION_ID are two different organizations and a column called org_id in silver tells a modeler nothing (two orgs).

EBS — the misplaced-fix trap

Leaving the four-layer TCA customer model for the report to walk. Party, account, account site and site use — HZ_PARTIES and HZ_CUST_ACCOUNTS among them — collapse into one dim_customerin gold, once, with the grain declared. Four joins repeated per dashboard is four chances to pick a different layer as “the customer”.

_B / _TL / _V / _VL →Key flexfields (SEGMENTn) →ORG_ID vs ORGANIZATION_ID →

Fusion is the reference where one layer's contract has to be restated honestly rather than assumed: there is no SQL door to the source database, so bronze is defined against what BICC delivered rather than against the tables themselves.

Fusion — bronze must preserve

The extract as it arrived — PVO attribute headers and all, not renamed on the way in — with the prune-window re-delivery made harmless by deduping on the primary key and merging idempotently, so the same rows arriving twice change nothing (incremental & deletes). The Active Primary Key Extract's live-key files are staged beside the data, because they are the only delete signal the path has.

Fusion — silver must convert

PVO attribute names renamed back to documented table columns, once, at this boundary — InventoryItemId to INVENTORY_ITEM_ID — so a model built on INV_MATERIAL_TXNS or DOO_FULFILL_LINES_ALLis written against the documented schema rather than against a view object's naming (BICC headers are PVO attributes). Date-effective _Frows are collapsed to current state with the choice named in the entity, since “current” is one of several defensible readings of an effectivity window (date-effective _F tables). Set-ID reference data is resolved and the business-unit stripe is carried through.

Fusion — the misplaced-fix trap

Assuming the bronze contract can be verified the way it is everywhere else. There is no SQL path to the source, so a source-side control total is not available to run — the comparison is against BICC's own extract counts and manifests. Say so in the pipeline's documentation rather than letting a reconciliation check imply a guarantee it can't make.

BICC headers are PVO attributes →Date-effective _F tables →Incremental & deletes →

NetSuite puts every document in one table family, which makes the header/line split the single most valuable thing silver can do — and the single most commonly repeated thing when it doesn't.

NetSuite — bronze must preserve

The spine as one family — transaction and transactionline as landed, with lowercase identifiers exactly as the source renders them, subsidiary carried, and 'T' / 'F' still strings. The deletedrecord ledger lands beside them as a staleness detector: it publishes no internal id, so it names which tables need a refresh rather than which rows to delete, which is the deletes guide's reading of it.

NetSuite — silver must convert

'T' and 'F' to real booleans, because a naive truthiness test passes for both; internal ids joined to their list records once, with the id kept, including on item; and the spine split into a header entity and a line entity by applying the mainline convention exactly once ('T'/'F' booleans, internal ids, not display text, one table family holds every document). Custom fields are carried through as they are found rather than hard-coded, since they exist in one account and not the next.

NetSuite — the misplaced-fix trap

Filtering mainline per report. Every analyst writes the predicate slightly differently, and the ones who omit it double-count a document against its own lines. Downstream, the record map's document types — sales order, fulfillment, invoice, work order — become facts in gold by selecting on the spine's type value, which only works if the grain was settled first.

Booleans arrive as 'T' and 'F' strings →One table family holds every document →Internal ids, not display text →

Nothing new here — every cell restates a section above. Read the second and third columns together: what bronze preserves is what silver still has available to convert, and the fourth column is what happens when the conversion is done anywhere else.

Read down the third column and the five conversion families are all that is there, wearing eight sets of clothes.

The eight sections above are worked examples. The general case is three questions, asked in order, about a transformation someone is proposing to add.

1. After it, can you still reconcile bronze to source with a control total? If not, it is too early. Rescaling an amount or reshaping a date in bronze breaks the one check that catches delete drift and value drift, and it throws away the raw value you would need to fix the rule later.

2. Would every report implement it identically? If yes — every reader of this column wants the same DATE, the same scaled amount, the same boolean — it belongs in silver. If the answer depends on the business question being asked, it belongs in gold, and it belongs there as a named decision rather than as a predicate someone happened to write.

3. Does it change how a value is represented, or which rows exist? Representation is silver: same rows, same meaning, a legible form. Row semantics — a filter, a business exclusion, a merge of two different entities into one grain, a status range called “open” — is gold. Two row-level moves stay in silver anyway, for the same reason: reassembling one entity the source split across two tables (EWM's open and confirmed tasks, JD Edwards' open and history sales lines) introduces no entity that wasn't already there, and collapsing a source's own versioning to the current row (Fusion's date-effective _F rows) only delivers the current state bronze already promised. The distinction is sharp enough to use on a pull request, and the checks that verify each layer kept to it are laid out in where each check runs: bronze, silver, gold.

One skeleton makes the silver half of that concrete. It is deliberately keyless: this library publishes real, keyed SQL on each reference's own quirks guide, which is more useful than a generic version, and reprinting it here would create a second copy to keep in sync.

-- The silver contract in one shape: read bronze exactly as it landed, convert
-- every representation once, and change nothing about which rows exist.
-- Placeholders only. The real column names, the real conversion expressions and
-- the decode tables are on each reference's own quirks guide, which is where
-- this library keeps its keyed SQL -- a generic version of it would be worse.
CREATE OR REPLACE VIEW <catalog>.<silver_schema>.<entity> AS
SELECT
  -- 1. The tenant / company column, carried and never dropped.
  b.<partition_column>,
  -- 2. Typed dates. The raw form differs by source -- a character DATS string,
  --    a CYYDDD number, a numeric YYYYMMDD, an ISO date with a sentinel -- and
  --    the unset value becomes NULL before the cast, never after it.
  CAST(<date_conversion_expression> AS DATE) AS <date_column>,
  -- 3. Scaled numbers, read with the scale and the unit the source keeps
  --    beside them.
  b.<amount_column> / <implied_decimal_scale> AS <amount_column>,
  b.<currency_or_unit_column>,
  -- 4. Decoded codes -- the code AND the decode, never the decode alone, so a
  --    downstream model can still join on what the ERP actually stores.
  b.<code_column>,
  d.<decode_text_column> AS <code_column>_text,
  -- 5. Canonical booleans and join-ready keys.
  <boolean_conversion_expression> AS <flag_column>,
  <key_normalization_expression> AS <business_key>
FROM <catalog>.<bronze_schema>.<source_table> AS b
LEFT JOIN <catalog>.<bronze_schema>.<decode_table> AS d
  ON  d.<partition_column> = b.<partition_column>
  AND d.<decode_key_column> = b.<code_column>
  AND d.<language_column> = '<language>';

-- Note what is not here: no WHERE clause on a status, a deletion flag or a
-- document type. A filter is a business rule, and business rules are gold's.
-- Silver types the flag; gold decides what it means.