Bq Schema Contract

BigQuery schema contract

A contract is a file in the producer's repo that the consumer can read and CI can enforce. If it is a wiki page, it is not a contract.

The contract file

# contracts/retail/fct_order.yaml
contract_version: 2
table: acme-shop-prod.mart_retail.fct_order
owner:
  team: data-platform
  slack: "#data-platform"
consumers:
  - team: finance-analytics
    usage: daily revenue dashboard
  - team: marketing-ops
    usage: attribution model
grain: one row per order_id
freshness:
  max_lag_minutes: 90
  measured_on: ordered_at
partition:
  column: ordered_at
  type: DAY
  require_filter: true
columns:
  - name: order_id
    type: STRING
    mode: REQUIRED
    description: Source order id. Primary key.
    stability: stable
  - name: order_total_usd
    type: NUMERIC
    mode: REQUIRED
    description: Order total in USD, excluding tax and shipping.
    stability: stable
  - name: payment_status
    type: STRING
    mode: NULLABLE
    description: One of paid, pending, refunded, voided.
    allowed_values: [paid, pending, refunded, voided]
    stability: stable
  - name: legacy_channel_code
    type: STRING
    mode: NULLABLE
    description: Deprecated. Use channel_id.
    stability: deprecated
    remove_after: "2026-12-01"
sla:
  availability: business days by 07:00 UTC
  breaking_change_notice_days: 30

What counts as breaking

Grain changes are the ones that do not throw an error and do not get caught by type checks. Call them out loudly.

Generate the contract from the live schema

Read references/execution-model.md. Do not ask a user to type out a schema you can read. With the MCP server, get_table_info returns columns, types, modes, partitioning, and clustering. Draft the contract from that, then ask only for the things the schema cannot tell you: the grain in plain language, the named consumers, and the freshness promise.

Before proposing a deprecation, find out who still reads the column. The scan query in this skill runs fine through execute_sql_readonly and gives you a real answer instead of a suggestion to go and look.

Writing the contract file is a repo change, not a warehouse change, so it goes in the PR as normal. Applying a schema change is a warehouse write: emit the DDL, never run it.

Enforcing in CI

Compare the live schema against the contract on every PR:

bq show --schema --format=prettyjson \
  acme-shop-prod:mart_retail.fct_order > /tmp/live_schema.json

python scripts/check_contract.py \
  --contract contracts/retail/fct_order.yaml \
  --live /tmp/live_schema.json \
  --fail-on breaking

The check should exit non-zero on a breaking change unless the PR carries an explicit contract-break-approved label and bumps contract_version.

Enforcing in the warehouse

Two layers, both cheap:

1. Dataform assertions on the producer side, so the producer breaks first and the consumer never sees bad rows.
2. An authorized view as the published surface. Consumers query the view, the view names columns explicitly, and adding a column to the base table cannot surprise anyone.

Deprecation flow

1. Mark the column stability: deprecated with a remove_after date at least 30 days out.
2. Add the deprecation note to the column description in BigQuery so it shows in the console.
3. Find who is still reading it:

SELECT user_email, COUNT(*) AS runs, MAX(creation_time) AS last_run
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND REGEXP_CONTAINS(query, r'legacy_channel_code')
GROUP BY user_email
ORDER BY runs DESC;

4. Only drop when that query returns nothing for a full cycle.

Rules

• A contract with no named consumer is not a contract, it is documentation. Ask who consumes it before writing one.
• Do not write a contract for every table. Write one for every table another team depends on, which is usually under ten percent of the warehouse.