# Bq Schema Contract

> Define, version, and enforce schema contracts between data producers and consumers on BigQuery. Use when the user mentions a data contract, schema drift, a breaking change, a producer or consumer agreement, column deprecation, or asks how to stop upstream teams from breaking downstream tables. Also use when reviewing a schema change PR.

- Skill: `rk-chavali/bq-schema-contract` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add rk-chavali/bq-schema-contract`
- Raw SKILL.md: https://api.skillmd.com/api/skills/rk-chavali/bq-schema-contract/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: rk-chavali (https://skillmd.com/u/rk-chavali)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/rk-chavali/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

```yaml
# 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

| Change | Breaking | Notes |
| --- | --- | --- |
| Drop a column | Yes | requires notice period |
| Rename a column | Yes | it is a drop plus an add |
| Narrow a type, NUMERIC to INT64 | Yes | silently truncates |
| Widen a type, INT64 to NUMERIC | No | safe for readers |
| NULLABLE to REQUIRED | Yes | breaks existing writers |
| REQUIRED to NULLABLE | Yes for readers | consumers may not null-check |
| Add a nullable column | No | safe |
| Change the grain | Yes, the worst kind | every aggregate downstream is now wrong |
| Add a value to allowed_values | Yes if consumers have a CASE with no ELSE | announce it |
| Change partition column | Yes | breaks every partition filter |

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:

```bash
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:

```sql
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.