Transforme dados com desnormalização e pivôs

A reformulação da estrutura de dados é uma parte fundamental da criação de soluções de análise eficazes. Desnormalização, pivot e unpivot são técnicas de transformação que ajudam você a reorganizar dados entre formatos normalizados e agregados — cada uma atendendo a diferentes necessidades analíticas e de modelagem.

Nesta unidade, você aprenderá a nivelar tabelas normalizadas para consultas mais rápidas, girar linhas em colunas para análise entre tabelas e reverter esse processo quando precisar normalizar conjuntos de dados largos.

Entender a desnormalização

Bancos de dados normalizados reduzem a redundância de dados dividindo informações entre várias tabelas relacionadas. Embora esse design funcione bem para sistemas transacionais, ele cria desafios para análise. As consultas devem unir muitas tabelas, o que reduz o desempenho e aumenta a complexidade.

A desnormalização aborda esses desafios ao nivelar tabelas relacionadas em menos tabelas, mais amplas. Você combina dados de tabelas de dimensão diretamente em tabelas de fatos, eliminando a necessidade de junções no momento da consulta.

Considere um banco de dados de vendas com tabelas separadas para pedidos, clientes e produtos. Uma consulta normalizada requer várias junções:

-- Normalized query with multiple joins
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Uma tabela desnormalizada armazena essas informações em conjunto:

-- Create a denormalized orders table
CREATE OR REPLACE TABLE sales.orders_denormalized AS
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

As consultas na tabela desnormalizada são executadas mais rapidamente porque nenhuma junção é necessária. Essa abordagem é comum ao criar esquemas estrela e data marts para ferramentas de business intelligence. A compensação é o aumento do armazenamento e a necessidade de atualizar tabelas desnormalizadas quando os dados de origem são alterados.

Transformar linhas em colunas

O pivoteamento transforma valores únicos de uma coluna em colunas separadas, convertendo um formato de tabela "longo" em um formato "largo". Essa transformação é muito útil ao preparar dados para relatórios ou painéis em tabelas cruzadas.

A cláusula PIVOT requer uma função de agregação porque várias linhas de origem podem ser mapeadas para a mesma célula no resultado pivotado. Especifique quais valores de coluna se tornam novos nomes de coluna usando as cláusulas FOR e IN.

Considere os dados de vendas trimestrais armazenados em um formato longo:

-- Create sample sales data
CREATE OR REPLACE TEMP VIEW quarterly_sales(year, quarter, region, revenue) AS
VALUES (2024, 1, 'East', 150000),
       (2024, 2, 'East', 175000),
       (2024, 3, 'East', 160000),
       (2024, 4, 'East', 200000),
       (2024, 1, 'West', 180000),
       (2024, 2, 'West', 165000),
       (2024, 3, 'West', 190000),
       (2024, 4, 'West', 210000),
       (2025, 1, 'East', 165000),
       (2025, 2, 'East', 185000);

-- View data in long format
SELECT * FROM quarterly_sales;

Essa consulta retorna a receita como linhas separadas para cada trimestre. Para comparar trimestres lado a lado, dinamize os dados:

-- Pivot quarters into columns
SELECT year, region, Q1, Q2, Q3, Q4
FROM quarterly_sales
PIVOT (
  SUM(revenue)
  FOR quarter IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4)
);

O resultado mostra a receita trimestral de cada região como colunas, tornando as comparações ano a ano simples:

Você pode aplicar várias funções de agregação em uma única operação de pivô:

-- Pivot with multiple aggregations
SELECT year, Q1_total, Q1_avg, Q2_total, Q2_avg
FROM (SELECT year, quarter, revenue FROM quarterly_sales)
PIVOT (
  SUM(revenue) AS total,
  AVG(revenue) AS avg
  FOR quarter IN (1 AS Q1, 2 AS Q2)
);

Isso cria colunas para a soma e a média da receita de cada trimestre.

Transformar colunas em linhas

O unpivot realiza a transformação inversa: ele converte as colunas de volta em linhas. Use a transformação ao receber dados de formato largo que precisam de normalização para análise ou ao preparar dados para sistemas que esperam um formato longo.

A UNPIVOT cláusula transforma colunas especificadas em pares de nome-valor. Você define uma coluna para manter os valores e outra para manter os nomes de coluna originais.

-- Create wide-format data
CREATE OR REPLACE TEMP VIEW regional_targets(region, jan, feb, mar, apr) AS
VALUES ('North', 50000, 55000, 52000, 58000),
       ('South', 45000, 48000, 51000, 53000),
       ('East',  60000, 62000, 65000, 68000);

-- Unpivot monthly columns into rows
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (jan, feb, mar, apr)
);

O resultado converte cada coluna mensal em linhas:

Por padrão, UNPIVOT exclui valores nulos. Para incluí-los, especifique INCLUDE NULLS:

-- Unpivot while keeping null values
SELECT *
FROM regional_targets
UNPIVOT INCLUDE NULLS (
  target_amount FOR month IN (jan, feb, mar, apr)
);

Você também pode atribuir aliases personalizados aos nomes de colunas transformadas.

-- Unpivot with custom month labels
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (
    jan AS `January`,
    feb AS `February`,
    mar AS `March`,
    apr AS `April`
  )
);

Escolher a transformação certa

Cada transformação atende a necessidades analíticas específicas. Use a desnormalização quando precisar de um desempenho de consulta rápido para dashboards e relatórios. Escolha o pivô quando os analistas precisarem de comparações lado a lado entre categorias. Aplique unpivot quando precisar normalizar dados largos para pipelines de agregação ou machine learning.

Essas transformações geralmente funcionam juntas em pipelines de dados. Você pode transformar dados de origem para padronizar o formato, aplicar transformações e, depois, dinamizar os resultados da apresentação final (ou transformar a saída em tabelas otimizadas para consumidores downstream).