Raw-data and deduplication object contracts

This reference defines the stable interfaces around the EWE raw-data plane. It describes allowed shapes; it is not a provisioning runbook.

Raw-plane boundary

Concern	Contract
Database	Production raw objects live in NUMERA_EWE_PROD_RAW_DATA.
Source schemas	Endur uses ENDUR@EWE1HPDB1; CerQlar, clearing-bank-statement and archive feeds use their declared schemas.
Ingestion ownership	Each source loader owns and maintains its tables, pipes and internal transformation objects.
Administration	NUMERA_EWE_PROD_ADMINISTRATOR inherits loader roles for oversight.
Downstream access	NUMERA_EWE_PROD_RAW_DATA_READER is inherited by consuming transformer roles.
Reader grants	Enumerated source-table allow-list plus SELECT ON ALL/FUTURE VIEWS in consumed schemas.
Forbidden grant	Never grant downstream roles SELECT ON FUTURE TABLES; it exposes _RAW, duplicate sets, snapshots and scratch tables.
Metadata bridge	Loader-owned tasks snapshot role-scoped task/dynamic-table metadata into internal tables and expose only sanctioned views.
Exception	NUMERA_DEDUP_HEALTH is the documented account-administrator-owned exception because it reads SNOWFLAKE.ACCOUNT_USAGE.

All privilege probes must use USE SECONDARY ROLES NONE; otherwise an active secondary administrator role can turn a denial test into a false pass.

Consumer-facing object

The original source name—such as SIM or ADJUSTED_PRICES—is a view after V2/V3 cutover. It must:

• return only deduplicated rows;
• preserve required column names, order and types;
• project literal FALSE AS _IS_DUPLICATE where the legacy contract requires it;
• expose _DEDUP_FINISHED_AT without making an upstream dynamic table non-incremental;
• use COPY GRANTS on replacement, or reapply an independently captured grant set immediately; and
• be the only deduplication surface granted to ordinary downstream consumers.

V1 object contract

Object	Responsibility
Base table	Stores payload plus mutable _IS_DUPLICATE and _DEDUP_FINISHED_AT.
DEDUP_<TABLE> stream	Append-only scheduling signal; historically not consumed, so its offset did not advance.
DEDUP_<TABLE> task	Invokes LOGIC.RAW_DATA_DEDUP.
RAW_DATA_DEDUP	Derives the declared primary key, ranks by _INGESTED_AT DESC, and updates flags in batches.
GET_NEXT_DEDUP_BATCH	Chooses the next bounded ingestion-time batch.
RUN_ALL_DEDUP / RESET_STALE_STREAMS	Manual fan-out and legacy stream maintenance.

V1 mutates RAW and cannot be run as a harmless parallel safety mechanism beside a replacement dynamic table: its micro-partition rewrites can make incremental refresh reprocess large portions of the source.

V2 contracts

Single dynamic table

X_DEDUP contains full winning rows selected with ROW_NUMBER() = 1. X is a thin view over it, adding only compatibility metadata when necessary.

HIST/LIVE

X_DEDUP_HIST is a frozen plain table below the seam. X_DEDUP_LIVE is a bounded dynamic table above the seam. X combines the survivor sets without overlap. The LIVE predicate must bound arrival work; it must not use an unrelated value date merely because that date is part of the deduplication key.

V3 contracts

Single dynamic table

X_DUP contains losing row identities selected with ROW_NUMBER() > 1. X anti-joins RAW against it. This is a proposed forward shape for manageable tables.

HIST/LIVE

Object	Responsibility
X_RAW	Pristine append-only source and authoritative payload.
X_DUP_HIST	Frozen loser identities below the seam.
X_DUP_LIVE	Incrementally refreshed loser identities in the bounded live window.
X_DEDUP_COVERED_TO	Reader-safe consumed-to timestamp published from loader metadata snapshots.
X	Coverage-bounded RAW anti-joined against both duplicate sets.

Both duplicate sets contain _ID plus clustering/key columns needed for pruning. The anti-join's extra columns do not redefine row identity.

Dynamic-table requirements

• The dedup key and winner order are explicit.
• The source window is bounded only where the topology requires it.
• Required tables resolve to refresh_mode = INCREMENTAL and remain ACTIVE.
• No volatile refresh-time expression is projected into a dynamic-table SELECT.
• Target lag, warehouse and initialization mode are explicit.
• Initial refresh and steady-state refresh are verified separately.
• A failed metadata lookup cannot cause duplicate leakage.

Timestamp contract

Timestamp types differ by source and migration state. Never copy a conversion expression from another table without checking the declared input and output types. Predicates and coverage bounds must compare compatible absolute instants. If a wrapper blocks pruning, correct the stored timestamp or provide a typed metadata boundary rather than silently changing consumer semantics.