Transformar dados com desnormalização e pivôs

Remodelar a estrutura de dados é uma parte fundamental da construção de soluções analíticas eficazes. Desnormalização, pivotar e despivotar são técnicas de transformação que ajudam a reorganizar dados entre formatos normalizados e agregados — cada um servindo necessidades analíticas e de modelação diferentes.

Nesta unidade, aprende a achatar tabelas normalizadas para consultas mais rápidas, rodar linhas em colunas para análise cruzada e inverter esse processo quando precisa de normalizar conjuntos de dados amplos.

Compreender a desnormalização

Bases de dados normalizadas reduzem a redundância dos dados ao dividir a informação em múltiplas tabelas relacionadas. Embora este design funcione bem para sistemas transacionais, cria desafios para a análise de dados. As consultas precisam unir muitas tabelas, o que abranda o desempenho e aumenta a complexidade.

A desnormalização resolve estes desafios ao achatar tabelas relacionadas em menos tabelas mais largas. Combina os dados das tabelas de dimensões diretamente nas tabelas de factos, eliminando a necessidade de junções durante a execução da consulta.

Considere uma base de dados de vendas com tabelas separadas para encomendas, clientes e produtos. Uma consulta normalizada requer múltiplas 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 esta informação 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 à tabela desnormalizada são executadas mais rapidamente porque não são necessárias junções. Esta abordagem é comum na construção de esquemas estrela e data marts para ferramentas de inteligência de negócio. A contrapartida é o aumento do armazenamento e a necessidade de refrescar as tabelas desnormalizadas quando os dados de origem mudam.

Converter linhas em colunas

O pivoting transforma valores únicos de uma coluna em colunas separadas, convertendo um formato de tabela "longa" num formato "largo". Esta transformação é particularmente útil na preparação de dados para relatórios em formato de tabela cruzada ou dashboards.

A cláusula PIVOT requer uma função agregada porque múltiplas linhas de origem podem corresponder à mesma célula no resultado obtido pela rotação de dados. Especificas quais valores de coluna se convertem em novos nomes de coluna usando as cláusulas FOR e IN.

Considere os dados trimestrais de vendas armazenados num 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;

Esta consulta devolve a receita como linhas separadas para cada trimestre. Para comparar os trimestres lado a lado, pivote 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 em colunas, tornando as comparações ano para ano mais simples:

Pode aplicar múltiplas funções agregadas numa ú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)
);

Isto cria colunas tanto para a soma como para a média das receitas de cada trimestre.

Despivotar colunas em linhas

O despivotamento realiza a transformação inversa — converte as colunas de volta em linhas. Utilize o despivotamento quando receber dados em formato largo que precisam de normalização para análise ou ao preparar dados para sistemas que necessitam de um formato longo.

A UNPIVOT cláusula transforma colunas especificadas em pares valor-nome. Defines uma coluna para guardar os valores e outra para guardar os nomes originais das colunas.

-- 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 defeito, UNPIVOT exclui valores nulos. Para os incluir, especifique INCLUDE NULLS:

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

Também pode atribuir alias personalizados aos nomes das colunas despivotadas:

-- 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`
  )
);

Escolha a transformação certa

Cada transformação serve necessidades analíticas específicas. Use a desnormalização quando precisar de um desempenho rápido de consultas para dashboards e relatórios. Escolha "pivot" quando os analistas precisam de comparações lado a lado entre categorias. Aplica o unpivot quando precisares de normalizar dados amplos para agregação ou pipelines de aprendizagem automática.

Estas transformações frequentemente funcionam em conjunto em pipelines de dados. Pode despivotar os dados de origem para padronizar o seu formato, aplicar transformações e depois pivotar os resultados para a apresentação final — ou desnormalizar a saída em tabelas otimizadas para consumidores posteriores.