Expected Schema Check Examples

The situation: A partner receives an ORDERS extract with four agreed columns and fixed types. The layout is closed, so Allow other fields is off: an unannounced column would itself be a breach.

Check configuration

Payload

{
    "description": "The ORDERS extract must match the agreed layout.",
    "rule": "expectedSchema",
    "fields": [],
    "container_id": 145,
    "filter": null,
    "properties": {"list": ["o_orderkey", "o_custkey", "o_orderstatus", "o_totalprice"], "allow_other_fields": false},
    "tags": ["schema", "contract"],
    "additional_metadata": {"jira": "DATA-9801"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}

Sample Data

What gets flagged

Every declared field is present with a type matching its profile, and no undeclared column exists. The check passes and no anomaly is created. This is the baseline the next two scenarios drift away from.

Flowchart

graph TD
    A["Read the container's structure"] --> B["Compare against the declaration"]
    B --> C{"All declared fields present<br/>with a matching type?"}
    C -->|No| E["Report a Shape Anomaly"]
    C -->|Yes| D{"Allow other fields off and<br/>an undeclared column exists?"}
    D -->|Yes| E
    D -->|No| F["Check passes"]

Equivalent SQL

-- The same question, asked of the catalog rather than the data.
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'ORDERS'
ORDER BY ordinal_position;

The situation: A release changed o_totalprice from a decimal to text. Every value still looks like a number, so no value-level check fires, but the downstream cast will fail on the first non-numeric string that arrives.

Check configuration

Payload

{
    "description": "The ORDERS extract must match the agreed layout.",
    "rule": "expectedSchema",
    "fields": [],
    "container_id": 145,
    "filter": null,
    "properties": {"list": ["o_orderkey", "o_custkey", "o_orderstatus", "o_totalprice"], "allow_other_fields": false},
    "tags": ["schema", "contract"],
    "additional_metadata": {"jira": "DATA-9801"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}

Sample Data

What gets flagged

The field count is unchanged, so a Field Count check stays green. Expected Schema compares the type the scan read against the field's profiled type and reports the mismatch as a single Shape Anomaly.

Flowchart

graph TD
    A["Read the container's structure"] --> B["Compare o_totalprice:<br/>declared Fractional, found String"]
    B --> C{"Do the types match?"}
    C -->|Yes| D["Check passes"]
    C -->|No| E["Report a Shape Anomaly<br/>for the container"]

Equivalent SQL

-- Confirming the type change against the catalog.
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'ORDERS' AND column_name = 'o_totalprice';
-- Returns String; the declaration says Fractional.

The situation: An upstream team added o_channel to the shared extract. The declared fields are all still there with the right types, but the layout is closed, so the addition breaks the contract the partner's loader depends on.

Check configuration

Payload

{
    "description": "The ORDERS extract must match the agreed layout.",
    "rule": "expectedSchema",
    "fields": [],
    "container_id": 145,
    "filter": null,
    "properties": {"list": ["o_orderkey", "o_custkey", "o_orderstatus", "o_totalprice"], "allow_other_fields": false},
    "tags": ["schema", "contract"],
    "additional_metadata": {"jira": "DATA-9801"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}

Sample Data

What gets flagged

Every declared field is intact, but o_channel was not declared and Allow other fields is off, so the check reports a Shape Anomaly. Had the toggle been on, the extra column would have been ignored and the check would have passed.

Flowchart

graph TD
    A["Read the container's structure"] --> B["All declared fields present<br/>with a matching type"]
    B --> C{"Allow other fields?"}
    C -->|On| D["Extras ignored.<br/>Check passes"]
    C -->|Off| E["o_channel is undeclared.<br/>Report a Shape Anomaly"]

Equivalent SQL

-- Listing what the container carries beyond the declaration.
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'ORDERS'
  AND column_name NOT IN ('o_orderkey', 'o_custkey', 'o_orderstatus', 'o_totalprice');
-- Returns o_channel.