Guia de Modelagem Dimensional

Exemplo: pedido e-commerce

-- Tabela fato: uma linha por item de pedido
CREATE TABLE fct_order_lines (
    order_line_sk       BIGINT PRIMARY KEY,    -- Surrogate key
    order_id            VARCHAR(50),           -- Natural key (dimensao degenerada)
    customer_sk         BIGINT REFERENCES dim_customers,
    product_sk          BIGINT REFERENCES dim_products,
    date_sk             INT REFERENCES dim_date,
    -- Medidas (agregáveis)
    quantity            INT,
    unit_price          DECIMAL(10,2),
    discount_amount     DECIMAL(10,2),
    line_total          DECIMAL(10,2)
);

-- Tabela dimensão: uma linha por cliente
CREATE TABLE dim_customers (
    customer_sk         BIGINT PRIMARY KEY,    -- Surrogate key
    customer_id         VARCHAR(50),           -- Natural key
    customer_name       VARCHAR(255),
    email               VARCHAR(255),
    segment             VARCHAR(50),           -- 'Enterprise', 'SMB', 'Consumer'
    acquisition_channel VARCHAR(50),
    created_at          TIMESTAMP
);

Exemplo: dimensão data

CREATE TABLE dim_date (
    date_sk             INT PRIMARY KEY,       -- formato YYYYMMDD
    date_actual         DATE NOT NULL,
    day_of_week         VARCHAR(10),           -- 'Monday', 'Tuesday', ...
    day_of_week_num     INT,                   -- 1-7
    day_of_month        INT,
    day_of_year         INT,
    week_of_year        INT,
    month_num           INT,
    month_name          VARCHAR(10),
    quarter_num         INT,
    quarter_name        VARCHAR(10),           -- 'Q1', 'Q2', ...
    year_num            INT,
    fiscal_year         INT,
    fiscal_quarter      INT,
    is_weekend          BOOLEAN,
    is_holiday          BOOLEAN,
    holiday_name        VARCHAR(50)
);

-- Uso: filtrar/agrupar facilmente por qualquer atributo de data
SELECT 
    d.month_name,
    d.year_num,
    SUM(f.order_total) as revenue
FROM fct_orders f
JOIN dim_date d ON f.order_date_sk = d.date_sk
WHERE d.is_weekend = FALSE
GROUP BY d.month_name, d.year_num;

Exemplo: accumulating snapshot (fulfillment)

CREATE TABLE fct_order_fulfillment (
    order_sk                BIGINT PRIMARY KEY,
    order_id                VARCHAR(50),
    customer_sk             BIGINT,
    -- Várias chaves de data (milestones)
    order_date_sk           INT,
    payment_date_sk         INT,
    ship_date_sk            INT,
    delivery_date_sk        INT,
    -- Lags calculados
    days_to_payment         INT,
    days_to_ship            INT,
    days_to_delivery        INT,
    -- Medidas
    order_total             DECIMAL(10,2),
    current_status          VARCHAR(50)
);

-- A linha é atualizada conforme o pedido avança
-- Inicialmente: só order_date_sk
-- Após pagamento: payment_date_sk preenchido, days_to_payment calculado
-- Após envio: ship_date_sk preenchido, days_to_ship calculado
-- Após entrega: delivery_date_sk preenchido, days_to_delivery calculado