CREATE TABLE: Types, Keys & Constraints

Pipelines land data into tables you define. Get the definition right once; every downstream query inherits it.

CREATE TABLE stg_orders (
  order_id      BIGINT PRIMARY KEY,
  customer_id   BIGINT NOT NULL REFERENCES dim_customers (customer_id),
  ordered_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  amount_cents  INTEGER NOT NULL CHECK (amount_cents >= 0),
  status        TEXT NOT NULL DEFAULT 'new'
);

Rules that prevent 3 a.m. pages

1. Right-sized types: BIGINT for ids (INT overflows), NUMERIC for money math, TIMESTAMPTZ for event time. TEXT over VARCHAR(n) unless a real limit exists.
2. Constraints are documentation that executes: NOT NULL, CHECK, PRIMARY KEY, REFERENCES. A load that violates them fails fast at the boundary instead of silently poisoning marts.
3. IF NOT EXISTS in pipeline DDL so re-runs are safe: CREATE TABLE IF NOT EXISTS stg_orders (...);
4. Staging vs mart separation: land raw in stg_* (loose types allowed), enforce strict types in modeled marts. Never let analysts query staging.

Warehouse note

BigQuery/Snowflake skip PK/FK enforcement — declare them anyway (optimizers and dbt contracts read them), and enforce uniqueness with a post-load QUALIFY ROW_NUMBER() check instead.