CREATE TABLE: Types, Keys & Constraints

# CREATE TABLE: Types, Keys & Constraints Pipelines land data into tables you define. Get the definition right once; every downstream query inherits it. ```sql 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.