Data Modeling Fundamentals

-- Normalized schema (3NF): No data redundancy but requires many joins

-- Customers table
CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255) UNIQUE NOT NULL,
    country_id    INT REFERENCES countries(country_id)
);

-- Countries table (avoids repeating country names)
CREATE TABLE countries (
    country_id    SERIAL PRIMARY KEY,
    country_name  VARCHAR(100) NOT NULL,
    region        VARCHAR(50) NOT NULL
);

-- Orders table
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INT REFERENCES customers(customer_id),
    order_date    DATE NOT NULL,
    status        VARCHAR(20) NOT NULL
);

-- Order items (line items separate from orders)
CREATE TABLE order_items (
    item_id       SERIAL PRIMARY KEY,
    order_id      INT REFERENCES orders(order_id),
    product_id    INT REFERENCES products(product_id),
    quantity      INT NOT NULL,
    unit_price    DECIMAL(10,2) NOT NULL
);

-- To get "revenue by country" requires joining 4 tables:
SELECT
    co.country_name,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
JOIN countries co ON c.country_id = co.country_id
WHERE o.order_date >= '2025-01-01'
GROUP BY co.country_name;

-- Denormalized: Wide table with pre-joined data
-- Optimized for analytical queries — single table scan

CREATE TABLE analytics.orders_wide (
    order_id          BIGINT PRIMARY KEY,
    order_date        DATE NOT NULL,
    order_month       VARCHAR(7),       -- '2025-03'
    status            VARCHAR(20),

    -- Customer fields (denormalized from customers table)
    customer_id       BIGINT,
    customer_name     VARCHAR(100),
    customer_email    VARCHAR(255),
    customer_country  VARCHAR(100),
    customer_region   VARCHAR(50),

    -- Product fields (denormalized from products table)
    product_id        BIGINT,
    product_name      VARCHAR(200),
    product_category  VARCHAR(100),

    -- Metrics
    quantity          INT,
    unit_price        DECIMAL(10,2),
    total_amount      DECIMAL(12,2),
    discount_amount   DECIMAL(10,2) DEFAULT 0
);

-- Same query: "revenue by country" — now a simple single-table scan!
SELECT
    customer_country,
    SUM(total_amount) AS revenue
FROM analytics.orders_wide
WHERE order_date >= '2025-01-01'
GROUP BY customer_country;

-- No joins needed. Columnar warehouses only read the columns
-- actually referenced in the query, making this very fast.

-- Layer 1: Staging (stg_) — Clean and rename raw data
-- One staging model per source table
-- models/staging/stg_orders.sql
SELECT
    id AS order_id,
    customer_id,
    product_id,
    qty AS quantity,
    price AS unit_price,
    qty * price AS total_amount,
    CAST(created_at AS DATE) AS order_date,
    status,
    _loaded_at
FROM raw.app_orders
WHERE id IS NOT NULL;

-- Layer 2: Intermediate (int_) — Business logic and joins
-- models/intermediate/int_orders_enriched.sql
SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    c.country,
    c.segment,
    o.product_id,
    p.product_name,
    p.category,
    o.quantity,
    o.unit_price,
    o.total_amount,
    o.order_date,
    o.status
FROM staging.stg_orders o
LEFT JOIN staging.stg_customers c ON o.customer_id = c.customer_id
LEFT JOIN staging.stg_products p ON o.product_id = p.product_id;

-- Layer 3: Marts (fct_, dim_) — Final business-facing tables
-- models/marts/fct_orders.sql
SELECT
    order_id,
    customer_id,
    product_id,
    quantity,
    unit_price,
    total_amount,
    order_date,
    DATE_TRUNC('month', order_date) AS order_month,
    status,
    CASE
        WHEN total_amount > 500 THEN 'premium'
        WHEN total_amount > 100 THEN 'standard'
        ELSE 'basic'
    END AS order_tier
FROM intermediate.int_orders_enriched
WHERE status != 'cancelled';

-- Data Vault: Hub, Link, Satellite pattern

-- Hub: Stores unique business keys
CREATE TABLE vault.hub_customer (
    hub_customer_key    CHAR(32) PRIMARY KEY,  -- MD5 hash of business key
    customer_id         BIGINT NOT NULL,        -- Business key
    load_date           TIMESTAMP NOT NULL,
    record_source       VARCHAR(50) NOT NULL    -- Which source system
);

-- Satellite: Stores descriptive attributes with history
CREATE TABLE vault.sat_customer_details (
    hub_customer_key    CHAR(32) NOT NULL,
    load_date           TIMESTAMP NOT NULL,
    load_end_date       TIMESTAMP DEFAULT '9999-12-31',
    customer_name       VARCHAR(200),
    email               VARCHAR(255),
    city                VARCHAR(100),
    country             VARCHAR(100),
    record_source       VARCHAR(50),
    hash_diff           CHAR(32),  -- Hash of all attributes for change detection
    PRIMARY KEY (hub_customer_key, load_date)
);

-- Link: Stores relationships between hubs
CREATE TABLE vault.link_order_customer (
    link_order_customer_key  CHAR(32) PRIMARY KEY,
    hub_order_key            CHAR(32) NOT NULL,
    hub_customer_key         CHAR(32) NOT NULL,
    load_date                TIMESTAMP NOT NULL,
    record_source            VARCHAR(50) NOT NULL
);