Modelagem de dados: o que decide se sua query é simples ou impossível

• OLTP (sistema que a empresa usa): normalizado, escreve muito, cada dado em um só lugar.
• OLAP (analítico): dimensional, lê muito, redundância controlada para simplificar a pergunta.

Copiar o modelo do sistema transacional direto para o warehouse é o erro mais caro da área: 30 joins para responder "quanto vendemos por estado no mês".

-- DIMENSÃO
create table dim_cliente (
    cliente_sk    bigint      primary key,   -- surrogate
    cliente_id    varchar(50) not null,      -- chave natural da origem
    nome          varchar(200),
    uf            char(2),
    segmento      varchar(50),
    valido_de     date not null,
    valido_ate    date,                      -- null = versão atual
    is_atual      boolean not null
);

-- FATO: uma linha = um item de pedido
create table fct_item_pedido (
    item_pedido_id  bigint  primary key,
    data_sk         int     not null references dim_data(data_sk),
    cliente_sk      bigint  not null references dim_cliente(cliente_sk),
    produto_sk      bigint  not null references dim_produto(produto_sk),
    quantidade      int     not null,
    valor_unitario  numeric(12,2) not null,
    valor_total     numeric(12,2) not null,
    desconto        numeric(12,2) not null default 0
);

Fato guarda número e chave. Dimensão guarda texto e descrição. Se você está colocando nome_do_cliente na fato, provavelmente errou o desenho.

select
    d.ano,
    d.mes,
    c.uf,
    sum(f.valor_total)            as receita,
    count(distinct f.cliente_sk)  as clientes
from fct_item_pedido f
join dim_data    d on d.data_sk    = f.data_sk
join dim_cliente c on c.cliente_sk = f.cliente_sk
where d.ano = 2024
group by d.ano, d.mes, c.uf
order by receita desc;

Três joins, zero subquery. É esse o teste do modelo: uma pergunta comum deve caber em uma tela.

Declare em uma frase antes de criar a tabela:

"Uma linha de fct_item_pedido representa
 um produto dentro de um pedido, no momento da compra."

Se você misturar granularidades na mesma fato — item e pedido juntos — toda soma passa a contar em dobro. Precisa de outro nível? Outra tabela fato.

O cliente mudou de estado. Você quer que a venda antiga continue atribuída ao estado antigo — então versiona a dimensão em vez de sobrescrever:

-- fecha a versão antiga
update dim_cliente
   set valido_ate = current_date - 1,
       is_atual   = false
 where cliente_id = 'C-1001'
   and is_atual   = true;

-- abre a nova versão
insert into dim_cliente
    (cliente_sk, cliente_id, nome, uf, segmento, valido_de, valido_ate, is_atual)
values
    (nextval('seq_cliente_sk'), 'C-1001', 'Maria Silva', 'SP', 'varejo',
     current_date, null, true);

• Toda métrica precisa de um distinct para não duplicar.
• Duas áreas calculam "receita" de formas diferentes.
• Colunas campo1, obs, flag_2 sem significado documentado.
• Dimensão sem chave surrogate, apostando que a origem nunca vai mudar.
• Ninguém consegue explicar em uma frase o que uma linha da fato representa.