Consultoria Data Warehouse – Arquitetura Sólida, Dados Confiáveis

Posicionamento

Um data warehouse é a memória analítica de uma organização. Ele reúne dados de diferentes sistemas de origem em uma forma unificada e confiável, disponibilizando-os para relatórios, análises e suporte à decisão. Parece simples — mas não é. Um DWH que sustente a operação diária exige uma arquitetura bem pensada, modelagem cuidadosa, processos de carga robustos e uma garantia de qualidade que encontre erros antes que eles apareçam nos relatórios. Sem essa base, a memória analítica rapidamente se torna um repositório confuso em que ninguém mais confia.

É exatamente aqui que está o meu foco. Construo data warehouses no SQL Server baseados em padrões comprovados: arquitetura em camadas para responsabilidades claras, modelos dimensionais Kimball para consultas performáticas e compreensíveis, Data Vault para historicização robusta e desacoplamento dos dados brutos, pipelines ETL automatizados com SSIS e T-SQL, e tSQLt para os testes automatizados da lógica de carga. O resultado é uma entrega ao SSAS Tabular e ao Power BI que oferece aos usuários finais relatórios rápidos e autoexplicativos.

Trabalho com SQL Server desde a versão 2000 — em projetos para clientes do setor público, na indústria, no setor de serviços e na consultoria. Essa amplitude ajuda: conheço as dificuldades típicas de crescimento de um DWH assim como as armadilhas que surgem em construções do zero. Sei quais compromissos cobram seu preço mais tarde e quais simplificações são legítimas. E consigo fazer ambos — uma construção nova do zero assim como a modernização direcionada de um sistema que foi crescendo ao longo do tempo.

Os clientes me procuram quando um DWH existente chegou ao seu limite: quando os tempos de carga ficam longos demais, quando os números divergem entre relatórios, quando ninguém mais sabe o que realmente acontece dentro de um pipeline ETL que foi crescendo. Ou quando um novo DWH precisa ser construído e feito direito desde o começo. Em ambos os casos, o trabalho começa pela compreensão dos requisitos de negócio, dos sistemas de origem e do panorama de dados existente — antes que uma única tabela seja criada.

O que Define um Data Warehouse

Um data warehouse é muito mais do que um banco de dados onde os relatórios são executados. É um sistema que transforma dados de diversas fontes em uma forma integrada, consistente e rastreável no tempo. Essas quatro propriedades — integração, consistência, orientação temporal e orientação temática — foram formuladas por William H. Inmon ainda na década de 1990 e continuam sendo a base de qualquer projeto sério de DWH.

Integração: uma visão unificada de todas as fontes

Na maioria das organizações, coexistem vários sistemas operacionais: um ERP para finanças, um CRM para clientes, um sistema de RH para dados de pessoal, além de soluções especializadas para departamentos específicos. Cada sistema tem suas próprias chaves, suas próprias nomenclaturas e sua própria lógica. O data warehouse une esses mundos e garante que um cliente seja o mesmo cliente em todas as fontes e que um produto seja o mesmo produto em todos os sistemas. Essa integração é um trabalho de negócio, não puramente técnico.

Historicização: acompanhar mudanças ao longo do tempo

Os sistemas operacionais se preocupam com o estado atual. O data warehouse se preocupa com a evolução ao longo do tempo. Qual era o preço de um produto há seis meses? Como a receita evoluiu mês a mês? Quais clientes estavam em qual segmento há um ano? Essas perguntas só podem ser respondidas se as mudanças forem historicizadas — com Slowly Changing Dimensions, com camadas insert-only ou com um Data Vault. O padrão correto depende do caso de uso.

Orientação temática: perguntas de negócio, não estruturas técnicas

Um data warehouse é estruturado segundo temas de negócio, não segundo as estruturas técnicas dos sistemas de origem. Em vez de tabelas como 'KDBZK' ou 'BWART', existem dimensões como 'Cliente' e 'Material' e tabelas de fatos como 'Receita' e 'Compras'. Essa estrutura orientada a temas torna o DWH legível para os usuários de negócio e diretamente utilizável em análises — sem a necessidade de conhecimento aprofundado dos sistemas de origem subjacentes.

• Integração de sistemas de origem heterogêneos por meio de chaves e conceitos unificados
• Historicização de mudanças para análises temporais
• Estrutura orientada a temas em vez de espelhar estruturas técnicas das fontes
• Consistência: um número, uma definição, um resultado
• Separação entre cargas operacionais e analíticas (OLTP vs. OLAP)
• Rastreabilidade: cada número pode ser rastreado até a origem

Consistência: uma verdade para todos os relatórios

Um dos pontos de dor mais frequentes em organizações sem um DWH unificado é a discussão do 'número do mês': cada departamento tem a sua própria receita, sua própria contagem de clientes, sua própria definição de 'ativo'. O data warehouse encerra essa discussão ao estabelecer uma única definição, acordada em conjunto, para cada indicador e ao alimentar todos os relatórios a partir da mesma fonte. Essa consistência é tecnicamente alcançável, mas exige que as definições de negócio sejam esclarecidas antes.

O valor de um DWH confiável não se manifesta imediatamente em dashboards vistosos, mas no trabalho diário: os relatórios são executados pontualmente, os números coincidem, as solicitações da direção são respondidas em minutos em vez de dias. Essa confiança nos dados é o verdadeiro valor de um data warehouse bem construído.

Arquitetura em Camadas no Data Warehouse

Um princípio comprovado para qualquer data warehouse é a separação consistente em camadas. Cada camada tem uma tarefa claramente definida, e as transições entre as camadas seguem regras estabelecidas. Essa separação torna o sistema compreensível, testável e manutenível: uma mudança na estrutura de uma fonte afeta idealmente apenas a camada mais baixa, não a modelagem de negócio; um novo requisito de entrega afeta apenas a camada superior, não o processo de carga.

A arquitetura em camadas de um data warehouse: área de staging para dados brutos das fontes, Core DWH com camada Data Vault ou ODS, modelo dimensional (esquema estrela) como camada de reporting e SSAS Tabular como camada semântica de entrega para o Power BI.

Área de staging: a zona de amortecimento

A área de staging é a primeira parada para todos os dados das fontes. Os dados chegam aqui exatamente como saem dos sistemas de origem — sem alterações, sem transformação de negócio. A área de staging é o buffer entre o mundo externo e o DWH propriamente dito. Ela desacopla os tempos de carga e permite processar os dados das fontes várias vezes sem precisar acessar novamente os sistemas de origem. Na prática, a área de staging é frequentemente esvaziada ou sobrescrita após cada execução — não é um armazenamento permanente, mas uma área de trabalho.

Core DWH: a base de dados confiável

O core é o coração do data warehouse. Aqui os dados das fontes são limpos, integrados e historicizados. Dependendo da decisão arquitetural, o core pode ser estruturado como um Operational Data Store (ODS), como uma camada Data Vault ou como um modelo normalizado. O que todas as variantes têm em comum é que os dados estão aqui completos, historicizados e consistentes — como a única versão confiável da verdade. A camada de reporting é alimentada a partir do core; o core em si não é um modelo de consulta direta para os usuários finais.

Camada de reporting: otimizada para análises

A camada de reporting contém os dados em um formato otimizado para análises. O padrão clássico para isso é o esquema estrela segundo Kimball: uma tabela de fatos central com os indicadores, cercada por tabelas de dimensão com os atributos descritivos. Esse modelo é eficiente para consultas relacionais, legível para os usuários finais e forma a base direta para o SSAS Tabular e o Power BI. Em alguns projetos, agregados adicionais ou visões pré-sumarizadas complementam a camada base para acelerar ainda mais as consultas mais frequentes.

Camada semântica: SSAS Tabular e Power BI

Acima da camada de reporting está a camada semântica. Em projetos de DWH baseados em SQL Server, isso é tipicamente o SSAS Tabular — um modelo analítico in-memory que se baseia nos dados do esquema estrela e centraliza os cálculos de negócio por meio de medidas DAX. O Power BI se conecta diretamente ao modelo Tabular ou importa os dados para um modelo próprio. Essa separação entre armazenamento de dados e camada semântica é um fator de qualidade decisivo: os indicadores de negócio são definidos uma vez, mantidos uma vez e são consistentes em todos os relatórios.

• Área de staging: dados brutos das fontes, recarregados diariamente, sem armazenamento permanente
• Core DWH: dados limpos, historicizados e integrados como base confiável
• Camada de reporting: esquema estrela Kimball, otimizado para consultas relacionais
• SSAS Tabular: camada semântica in-memory com medidas DAX centralizadas
• Power BI: relatórios e dashboards self-service baseados no modelo Tabular

Kimball e Modelagem Dimensional

A modelagem dimensional segundo Ralph Kimball é o padrão mais amplamente utilizado para a camada de reporting de um data warehouse. Sua ideia central é tão simples quanto eficaz: fatos mensuráveis — receitas, quantidades, custos — ficam nas tabelas de fatos; os contextos descritivos — clientes, produtos, datas, regiões — ficam nas tabelas de dimensão. A combinação de ambos produz o esquema estrela, que viabiliza qualquer análise com apenas dois ou três joins.

O charme do modelo Kimball está em seu desempenho e compreensibilidade. Um esquema estrela com chaves substitutas bem escolhidas é altamente otimizável para bancos de dados relacionais: a tabela de fatos cresce linearmente, as dimensões são relativamente pequenas e podem caber inteiramente no buffer pool do SQL Server. Consultas que agregam bilhões de linhas de fatos sobre poucas dimensões são executadas em tempo razoável mesmo em grandes volumes de dados.

Um esquema estrela clássico: a tabela de fatos 'Receita' no centro, vinculada às tabelas de dimensão Cliente, Produto, Data e Região por meio de chaves substitutas inteiras. Cada dimensão carrega atributos descritivos para drill-down e filtragem.

Tabelas de fatos: o que é medido

A tabela de fatos contém os indicadores do processo de negócio — receita, quantidade, custo, contagem — além de chaves estrangeiras para todas as dimensões associadas. Cada linha corresponde a um evento ou medição no nível de granularidade escolhido. A escolha do grain correto é uma das decisões mais importantes no design do DWH: muito grosseiro leva à perda de informação; muito fino resulta em uma tabela enorme com detalhes de pouca utilidade. Chaves substitutas como chaves estrangeiras inteiras, em vez das chaves de negócio originais, mantêm a tabela de fatos enxuta e independente de mudanças nos sistemas de origem.

-- Tabela de fatos para o processo de negocio 'Receita'.
-- Chaves substitutas (SK) como chaves estrangeiras INT em vez de chaves de negocio.
-- Grain: uma linha por item de pedido e data de entrega.
CREATE TABLE reporting.FactReceita
(
    -- Dimensao degenerada: numero do documento diretamente na tabela de fatos
    NumeroPedido      NVARCHAR(20)    NOT NULL,
    ItemPedido        SMALLINT        NOT NULL,

    -- Chaves estrangeiras substitutas para as dimensoes
    ClienteSK         INT             NOT NULL,
    ProdutoSK         INT             NOT NULL,
    DataEntregaSK     INT             NOT NULL,   -- referencia a DimData (YYYYMMDD)
    RegionSK          INT             NOT NULL,
    RepresentanteSK   INT             NOT NULL,

    -- Indicadores (fatos aditivos)
    QuantidadeEntregue DECIMAL(12, 3) NOT NULL DEFAULT 0,
    ReceitaLiquida     DECIMAL(14, 2) NOT NULL DEFAULT 0,
    ValorDesconto      DECIMAL(14, 2) NOT NULL DEFAULT 0,
    CustoFabricacao    DECIMAL(14, 2) NOT NULL DEFAULT 0,

    -- Colunas de auditoria
    ETL_TimestampCarga DATETIME2    NOT NULL DEFAULT SYSUTCDATETIME(),
    ETL_SistemaOrigem  NVARCHAR(50) NOT NULL,

    CONSTRAINT PK_FactReceita PRIMARY KEY NONCLUSTERED (NumeroPedido, ItemPedido),
    CONSTRAINT FK_FactReceita_Cliente  FOREIGN KEY (ClienteSK)    REFERENCES reporting.DimCliente  (ClienteSK),
    CONSTRAINT FK_FactReceita_Produto  FOREIGN KEY (ProdutoSK)    REFERENCES reporting.DimProduto  (ProdutoSK),
    CONSTRAINT FK_FactReceita_Data     FOREIGN KEY (DataEntregaSK) REFERENCES reporting.DimData    (DataSK)
);

-- Indice columnstore clusterizado para consultas analiticas em grandes volumes
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactReceita
    ON reporting.FactReceita;

O índice columnstore clusterizado viabiliza consultas analíticas sobre milhões de linhas em segundos. As chaves substitutas mantêm a tabela de fatos independente de mudanças nas chaves dos sistemas de origem.

Tabelas de dimensão: o contexto

As tabelas de dimensão descrevem os objetos sobre os quais se mede: clientes com endereço e segmento, produtos com categoria e fornecedor, dimensões de data com dia da semana, mês, trimestre e ano fiscal. Uma dimensão bem desenvolvida é larga — muitos atributos — e relativamente plana: em vez de normalizar hierarquias profundas (snowflake), o mundo clássico Kimball as desnormaliza na tabela de dimensão. Isso simplifica as consultas e aumenta o desempenho, pois nenhum join adicional é necessário.

Na prática, complemento o esquema estrela ou floco de neve conforme necessário com dimensões outrigger e tabelas de ponte para relacionamentos muitos-para-muitos — por exemplo, quando um pedido pode ter vários centros de custo ou um produto pode pertencer a várias categorias. Esses casos especiais requerem modelagem cuidadosa para evitar dupla contagem nos relatórios.

-- Consulta da receita liquida mensal por categoria de produto e regiao.
-- Star-join sobre quatro dimensoes; o indice columnstore torna o scan eficiente.
SELECT
    d.AnoFiscal,
    d.Mes,
    p.CategoriaProduto,
    r.NomeRegiao,
    SUM(f.ReceitaLiquida)   AS ReceitaLiquida,
    SUM(f.CustoFabricacao)  AS CustoFabricacao,
    SUM(f.ReceitaLiquida)
        - SUM(f.CustoFabricacao) AS MargemContribuicao,
    COUNT_BIG(*)             AS NumeroItens
FROM  reporting.FactReceita         f
JOIN  reporting.DimData             d  ON f.DataEntregaSK = d.DataSK
JOIN  reporting.DimProduto          p  ON f.ProdutoSK     = p.ProdutoSK
JOIN  reporting.DimRegion           r  ON f.RegionSK      = r.RegionSK
JOIN  reporting.DimCliente          k  ON f.ClienteSK     = k.ClienteSK
WHERE d.AnoFiscal       = 2024
  AND k.SegmentoCliente  = 'EmpresaB2B'
GROUP BY
    d.AnoFiscal, d.Mes,
    p.CategoriaProduto, r.NomeRegiao
ORDER BY
    d.Mes, p.CategoriaProduto;

O star-join sobre quatro dimensões é altamente otimizável internamente pelo SQL Server. O plano de execução utiliza hash joins e o índice columnstore para um scan rápido e paralelo da tabela de fatos.

Data Vault como Camada de Dados Brutos e Historicização

O Data Vault é uma abordagem de modelagem para a camada core do data warehouse, desenvolvida por Dan Linstedt na década de 1990. Ele separa de forma rigorosa a historicização dos dados brutos da modelagem de negócio, tornando o DWH mais ágil diante de mudanças nos sistemas de origem. Onde um ODS clássico precisa ser adaptado a cada mudança estrutural na fonte, um Data Vault absorve novas fontes e novos atributos sem necessidade de reestruturação.

O modelo Data Vault é composto por três tipos de tabela. Os hubs contêm as chaves de negócio das entidades — clientes, produtos, reservas — com chave hash, chave de negócio, timestamp de carga e identificador de origem. Os links conectam hubs e representam relacionamentos entre entidades. Os satélites armazenam todos os atributos descritivos dos hubs e links, com historicização completa: cada versão de um atributo é armazenada com a data de início de vigência e nunca é sobrescrita. Esse princípio insert-only torna o Data Vault auditável desde o primeiro dia de carga.

Em um projeto para um cliente do setor público na área de pesquisa, operamos uma camada de importação Data Vault em paralelo com um modelo de reporting baseado em Kimball. O Data Vault assumiu a historicização dos dados brutos: cada mudança nos sistemas de origem foi documentada no vault, independentemente de ser ou não relevante para o negócio. A partir do vault, as dimensões Kimball foram então derivadas conforme regras de negócio — uma separação limpa entre 'o que foi entregue' e 'o que significa do ponto de vista de negócio'.

Hubs, links e satélites na prática

Um hub para o tipo de entidade 'Cliente' contém exatamente uma linha por chave de negócio — independentemente de quantos sistemas de origem conhecem esse cliente. A chave hash é um hash SHA-1 ou MD5 sobre a chave de negócio e serve como chave substituta eficiente e estável em todo o vault. O satélite do hub de clientes armazena todos os atributos descritivos — nome, endereço, segmento — com data de carga, data de fim e identificador de origem. Se um atributo mudar, uma nova linha é inserida; a antiga é mantida. Assim é possível reconstruir, para qualquer ponto no tempo, como o registro estava naquele momento.

• Hubs: chaves de negócio e chaves hash, uma linha por entidade e fonte
• Links: relacionamentos entre hubs, historicizados e transversais às fontes
• Satélites: atributos descritivos, totalmente historicizados, insert-only
• Hash diff: comparação eficiente de mudanças sem verificação coluna a coluna
• Carga paralela: hubs, links e satélites podem ser carregados independentemente
• Extensibilidade ágil: novas fontes e atributos sem reestruturação

Carga ETL: Levando Dados ao Data Warehouse com Confiabilidade

ETL — Extract, Transform, Load — é o núcleo operacional de um data warehouse. Aqui os dados das fontes são coletados, transformados nas estruturas do DWH e carregados. Parece mecânico — mas não é: um pipeline ETL confiável precisa ser idempotente (execuções repetíveis sem efeitos colaterais), apto ao reprocessamento após falhas, rastreável por meio de logging, e capaz de lidar com dados reais das fontes — ou seja, com valores nulos, duplicatas, erros de codificação e valores inesperados.

Minha combinação preferida para projetos de DWH baseados em SQL Server é o SSIS para orquestração e transporte físico de dados, e Stored Procedures T-SQL para a transformação de negócio. O SSIS é eficiente para ler dados de diferentes fontes, transformá-los e carregá-los de forma confiável. A lógica de negócio — mapeamentos, lookups de chave, regras de limpeza — é, no entanto, mais legível, testável e manutenível em T-SQL do que em configurações de fluxo de dados SSIS. Onde grandes volumes de dados precisam ser movidos sem transformação complexa, substituo os pipelines SSIS por comandos T-SQL diretos via SQL Server Agent Job.

Um padrão recorrente nos meus projetos é a tabela de controle: uma tabela de metadados que contém todos os objetos a serem carregados, com informações de origem e destino, marca d'água incremental, status e timestamp da última execução. Um pipeline SSIS genérico ou uma Stored Procedure itera sobre essa tabela e executa a etapa de carga para cada objeto. Novas fontes são adicionadas com um novo registro na tabela de controle — sem nenhuma alteração no próprio pipeline ETL.

-- Stored Procedure como ponto de entrada para a execucao noturna de carga.
-- Controlada por uma tabela de metadados; erros sao registrados em log.
CREATE OR ALTER PROCEDURE etl.usp_IniciarCarga
    @CargaID        INT,
    @DataInicio     DATE = NULL   -- NULL = watermark de ctrl.Watermark
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @v_DataInicio   DATE;
    DECLARE @v_HoraInicio   DATETIME2 = SYSUTCDATETIME();

    -- Fallback para o watermark armazenado, caso nenhuma data seja passada
    IF @DataInicio IS NULL
        SELECT @v_DataInicio = MAX(UltimaDataCarga)
        FROM   ctrl.Watermark
        WHERE  CargaID = @CargaID;
    ELSE
        SET @v_DataInicio = @DataInicio;

    BEGIN TRY
        -- 1) Carregar area de staging (genericamente via tabela de controle)
        EXEC etl.usp_CarregarStaging @CargaID = @CargaID, @DataInicio = @v_DataInicio;

        -- 2) Preencher o core / camada Data Vault a partir do staging
        EXEC etl.usp_CarregarCore    @CargaID = @CargaID;

        -- 3) Atualizar dimensoes e tabelas de fatos de reporting
        EXEC etl.usp_CarregarReporting @CargaID = @CargaID;

        -- Avanca o watermark apos execucao bem-sucedida
        UPDATE ctrl.Watermark
        SET    UltimaDataCarga  = SYSUTCDATETIME(),
               UltimoStatus     = 'OK',
               UltimoInicio     = @v_HoraInicio
        WHERE  CargaID = @CargaID;

    END TRY
    BEGIN CATCH
        -- Registrar o erro sem corromper o status da execucao
        INSERT INTO ctrl.LogErro (CargaID, HoraErro, MensagemErro, Procedimento)
        VALUES (@CargaID, SYSUTCDATETIME(), ERROR_MESSAGE(), ERROR_PROCEDURE());

        UPDATE ctrl.Watermark
        SET    UltimoStatus = 'ERRO'
        WHERE  CargaID = @CargaID;

        THROW;   -- propagar o erro para o alerta
    END CATCH;
END;

A Stored Procedure de três etapas orquestra staging, core e reporting em um único ciclo. O watermark só é avançado após a conclusão bem-sucedida de todas as etapas — tornando a execução com segurança reprocessável.

A carga incremental é obrigatória em praticamente todo DWH de produção. Um full load de todos os dados das fontes a cada execução é viável para volumes reduzidos, mas inviável tanto em tempo quanto em recursos para tabelas grandes. O controle por watermark — uma marca que registra o estado da última execução bem-sucedida — é o padrão mais simples e robusto para carga incremental. Pode ser combinado com uma coluna de data de alteração na fonte ou com Change Data Capture (CDC) no SQL Server.

• Idempotência: cada execução entrega o mesmo resultado, independentemente de quantas vezes é executada
• Capacidade de reprocessamento: interrupção e reinício sem correções manuais
• Controle por watermark para carga incremental
• Log de erros centralizado e alertas em caso de falha
• Tabela de controle para processos de carga genéricos orientados a metadados
• SSIS para transporte de dados, T-SQL para transformação de negócio

SCD e Historicização: Preservando Mudanças ao Longo do Tempo

Os dados cadastrais mudam. Um cliente muda de endereço, altera seu segmento ou recebe um novo responsável. Um produto muda de categoria ou de preço. A forma como o data warehouse lida com essas mudanças é uma das decisões arquiteturais mais importantes — e tem impacto direto sobre quais perguntas históricas podem ser respondidas.

O padrão clássico para a historicização de dimensões é a Slowly Changing Dimension (SCD) em seus diferentes tipos. O SCD Tipo 1 simplesmente substitui o valor antigo — sem historicização, o estado atual sempre prevalece. O SCD Tipo 2 insere uma nova linha e encerra a antiga: assim é possível reconstruir, para qualquer receita histórica, em qual segmento o cliente estava naquele momento. O SCD Tipo 3 armazena o valor anterior em uma coluna adicional — útil para exatamente uma geração de profundidade, mas não para consultas históricas arbitrárias.

Na maioria dos meus projetos de DWH, o SCD Tipo 2 é dominante para os atributos relevantes de negócio. A implementação segue um padrão comprovado: cada linha de dimensão tem uma data de início de vigência, uma data de fim de vigência (muitas vezes uma data futura distante como '9999-12-31' para a linha atual) e um indicador da linha atual. O comando MERGE no T-SQL é a ferramenta mais eficiente para essa atualização.

-- Atualizacao SCD Tipo 2 da dimensao de clientes via MERGE.
-- Novos clientes sao inseridos; atributos alterados geram uma nova versao.
-- Linhas nao alteradas nao sao tocadas (sem UPDATE desnecessario).
MERGE reporting.DimCliente AS tgt
USING (
    -- Fonte: dados de staging limpos com hash diff para comparacao eficiente
    SELECT
        ClienteID,
        NomeCliente,
        SegmentoCliente,
        CodigoRegiao,
        -- Hash sobre todos os atributos SCD-2 para comparacao eficiente
        HASHBYTES('SHA1',
            CONCAT_WS('|', UPPER(TRIM(NomeCliente)),
                            UPPER(TRIM(SegmentoCliente)),
                            UPPER(TRIM(CodigoRegiao)))
        ) AS HashDiff
    FROM staging.Clientes
) AS src
ON tgt.ClienteID_BK = src.ClienteID
AND tgt.EhAtual = 1   -- verificar apenas contra a versao atual

-- Registro nao alterado: nao fazer nada (sem WHEN MATCHED sem condicao!)
WHEN MATCHED AND tgt.HashDiff = src.HashDiff THEN
    UPDATE SET tgt.ETL_UltimaVerificacao = SYSUTCDATETIME()   -- apenas heartbeat

-- Registro alterado: encerrar versao antiga
WHEN MATCHED AND tgt.HashDiff <> src.HashDiff THEN
    UPDATE SET
        tgt.VigenciaFim  = CAST(GETDATE() AS DATE),
        tgt.EhAtual      = 0

-- Novo registro: inserir diretamente
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ClienteID_BK, NomeCliente, SegmentoCliente, CodigoRegiao,
            VigenciaInicio, VigenciaFim, EhAtual, HashDiff)
    VALUES (src.ClienteID, src.NomeCliente, src.SegmentoCliente, src.CodigoRegiao,
            CAST(GETDATE() AS DATE), '9999-12-31', 1, src.HashDiff);

-- Inserir nova versao para registros alterados (segundo passo)
INSERT INTO reporting.DimCliente
    (ClienteID_BK, NomeCliente, SegmentoCliente, CodigoRegiao,
     VigenciaInicio, VigenciaFim, EhAtual, HashDiff)
SELECT
    src.ClienteID, src.NomeCliente, src.SegmentoCliente, src.CodigoRegiao,
    CAST(GETDATE() AS DATE), '9999-12-31', 1, src.HashDiff
FROM staging.Clientes src
JOIN reporting.DimCliente tgt
    ON tgt.ClienteID_BK = src.ClienteID
    AND tgt.EhAtual = 0                       -- encerrado agora
    AND tgt.VigenciaFim = CAST(GETDATE() AS DATE);  -- encerrado hoje

O MERGE em dois estágios seguido de INSERT é o padrão T-SQL mais consagrado para o SCD Tipo 2. A comparação por hash diff evita verificações coluna a coluna e é significativamente mais rápida para dimensões largas.

Historicização na tabela de fatos

As tabelas de fatos são tipicamente insert-only: novos eventos são adicionados, os existentes não são alterados. Isso não se aplica a todos os tipos de fato, porém. Fatos de snapshot periódico — como estoques mensais ou saldos de contas — são reescritos ou atualizados via upsert a cada execução. Fatos de transação, por outro lado, são eventos encerrados e nunca são alterados, no máximo corrigidos por um lançamento de estorno. Essa distinção é fundamental para a correção das análises históricas.

Um caso especial é o late-arriving fact: um lançamento chega ao DWH após a dimensão já ter sido avançada. Nesse caso, o processo ETL precisa garantir que a chave de dimensão histórica correta seja utilizada — ou seja, a versão da dimensão que era válida no momento do lançamento, e não a atual. Esse lookup pela chave substituta historicamente válida é resolvível em um modelo SCD Tipo 2 por meio do intervalo de data de vigência, mas requer implementação consciente.

Qualidade de Dados e Testes Automatizados com tSQLt

Qualidade de dados não é um extra opcional — é um pré-requisito fundamental. Um data warehouse em que as áreas de negócio não confiam simplesmente não é utilizado — independentemente de quão elegante seja a arquitetura e quão bonitos sejam os relatórios. A confiança surge da confiabilidade, e a confiabilidade surge de uma garantia de qualidade sistemática que encontra erros antes que eles apareçam nos relatórios.

Minha ferramenta de escolha para testes automatizados da lógica ETL é o tSQLt — um framework de testes unitários open-source para SQL Server que armazena os testes como Stored Procedures no próprio banco de dados. O tSQLt permite testar etapas de transformação individuais de forma isolada, substituindo dependências como tabelas de origem por fake tables. Isso possibilita cobrir sistematicamente casos extremos, valores nulos, duplicatas e casos especiais — sem precisar tocar dados de produção.

Em um projeto extenso de DWH para um cliente do setor público na área de pesquisa, construí o tSQLt para testes funcionais e de regressão e o integrei em um pipeline CI/CD com Jenkins. O resultado: cada alteração na lógica ETL era automaticamente verificada contra a bateria de testes existente. Erros de regressão — mudanças acidentais em indicadores causadas por modificações em etapas de transformação aparentemente inócuas — foram detectados de forma confiável antes de ir para produção.

-- Teste unitario tSQLt: verifica se um atributo de cliente alterado e corretamente
-- inserido como nova versao SCD Tipo 2 na DimCliente.
EXEC tSQLt.NewTestClass 'TestDimCliente';
GO

CREATE OR ALTER PROCEDURE TestDimCliente.[test SCD2 cria nova versao ao mudar segmento]
AS
BEGIN
    -- Arrange: criar fake tables para isolar dados de producao
    EXEC tSQLt.FakeTable 'staging.Clientes';
    EXEC tSQLt.FakeTable 'reporting.DimCliente';

    -- Estado inicial: um cliente no segmento 'PME'
    INSERT INTO reporting.DimCliente
        (ClienteSK, ClienteID_BK, NomeCliente, SegmentoCliente, VigenciaInicio, VigenciaFim, EhAtual)
    VALUES
        (1, 'C-001', 'Exemplo Ltda', 'PME', '2023-01-01', '9999-12-31', 1);

    -- Entrada no staging: cliente muda para segmento 'GrandeEmpresa'
    INSERT INTO staging.Clientes (ClienteID, NomeCliente, SegmentoCliente, CodigoRegiao)
    VALUES ('C-001', 'Exemplo Ltda', 'GrandeEmpresa', 'BR-SP');

    -- Act: executar o procedimento de carga SCD
    EXEC etl.usp_CarregarDimCliente;

    -- Assert: duas versoes presentes; versao antiga encerrada, nova ativa
    DECLARE @NumeroVersoes INT;
    SELECT @NumeroVersoes = COUNT(*) FROM reporting.DimCliente WHERE ClienteID_BK = 'C-001';
    EXEC tSQLt.AssertEquals 2, @NumeroVersoes, 'Esperado: 2 versoes para C-001';

    DECLARE @SegmentoAtual NVARCHAR(50);
    SELECT @SegmentoAtual = SegmentoCliente
    FROM   reporting.DimCliente
    WHERE  ClienteID_BK = 'C-001' AND EhAtual = 1;
    EXEC tSQLt.AssertEquals 'GrandeEmpresa', @SegmentoAtual, 'Novo segmento deve ser GrandeEmpresa';
END;
GO

-- Executar o teste
EXEC tSQLt.Run 'TestDimCliente';

O tSQLt isola completamente cada teste: as fake tables contêm apenas as linhas relevantes para o teste; os dados de produção permanecem intocados. Os testes são executados dentro de uma transação e revertidos automaticamente ao final.

Regras de qualidade de dados no ETL

Além dos testes unitários para a lógica ETL, as regras de qualidade de dados pertencem ao próprio processo de carga. Antes da transferência para o core, verifico tipicamente: campos obrigatórios presentes, tipos de dados corretos, referências a chaves válidas resolúveis, volumes de registros dentro do intervalo esperado. Registros que violam essas regras são movidos para uma tabela de quarentena com indicador de erro e mensagem. Assim, dados de origem de baixa qualidade não comprometem o restante da execução, mas ficam documentados de forma rastreável.

Em projetos com requisitos elevados de qualidade — como no setor público, onde os dados são utilizados para pesquisa e relatórios — complemento os testes técnicos com regras de plausibilidade de negócio: a soma dos itens confere com o total do cabeçalho? A quantidade está dentro de um intervalo realista? A data de entrega não é anterior à data do pedido? Essas regras são simples de formular e capturam problemas reais de dados de forma confiável.

• Testes unitários tSQLt para cada etapa de transformação ETL
• Testes de regressão protegem a lógica existente contra mudanças não intencionais
• Integração CI/CD: testes executados automaticamente a cada deployment (ex.: Jenkins)
• Tabelas de quarentena para dados de origem que não atendem aos critérios de qualidade
• Regras de plausibilidade: verificações de consistência de negócio no ETL
• Verificações de completude: volumes de registros dentro do intervalo esperado?

Entrega ao SSAS Tabular e Power BI

O modelo analítico é o último metro entre o data warehouse e o usuário final. O SQL Server Analysis Services Tabular é a escolha mais comprovada para projetos de DWH baseados em SQL Server: um modelo analítico in-memory que se baseia nos dados do esquema estrela relacional, centraliza os indicadores de negócio via medidas DAX e se integra perfeitamente ao Power BI. A grande vantagem do modelo Tabular em relação a uma importação direta no Power BI é a centralização: definições de medidas, relacionamentos, hierarquias e regras de segurança são definidos uma vez e são consistentes em todos os relatórios.

Em um projeto de construção de DWH do zero para uma empresa de engenharia e consultoria, construí o SSAS Tabular cobrindo as áreas de Finanças, Controladoria e RH. O modelo Tabular traduziu as estruturas relacionais do esquema estrela em um modelo semântico legível para os usuários finais: medidas nomeadas para todos os KPIs, hierarquias significativas nas dimensões, segurança em nível de linha para restringir o acesso às áreas de dados relevantes. O Power BI se conecta via Live Connection diretamente a esse modelo — os relatórios sempre utilizam a lógica central e verificada.

DAX — Data Analysis Expressions — é a linguagem de fórmulas do SSAS Tabular e do Power BI. Medidas DAX bem escritas são performáticas, claramente nomeadas e expressivas do ponto de vista de negócio. Medidas DAX mal escritas podem fazer um modelo Tabular engatinhar mesmo com volumes moderados de dados. Dou ênfase ao uso criterioso de CALCULATE, à evitação de transições de contexto desnecessárias e ao uso de opções de modo de armazenamento como DirectQuery para dados ao vivo e Import para agregados históricos.

Particionamento e estratégia de atualização

Para modelos Tabular grandes, uma estratégia bem pensada de particionamento e atualização é essencial. Em vez de recarregar o modelo inteiro a cada atualização, divido os grandes fatos em partições baseadas em tempo. Apenas as partições atuais são recarregadas a cada execução noturna; as partições históricas permanecem intocadas. Isso reduz significativamente o tempo de processamento e mantém o modelo dentro de uma janela de atualização gerenciável, mesmo com centenas de milhões de linhas.

• SSAS Tabular como camada semântica central: um modelo, todos os relatórios
• Medidas DAX para todos os KPIs de negócio: definidas uma vez, consistentes em todo lugar
• Hierarquias, pastas de exibição e nomenclatura para legibilidade pelo usuário final
• Segurança em nível de linha para restrição de acesso a dados baseada em perfis
• Particionamento para tempos de atualização curtos com grandes volumes de dados
• Power BI Live Connection para uso direto do modelo Tabular central

Performance no Data Warehouse

Performance é sempre um tema no data warehouse — mais cedo ou mais tarde. Um DWH recém-construído frequentemente funciona sem problemas; após dois anos de crescimento, cinco novas fontes e cem novos relatórios, isso pode mudar rapidamente. Problemas de performance em um DWH surgem em múltiplos níveis: na estrutura do banco de dados (índices ausentes ou incorretos, tabelas de fatos excessivamente largas), na lógica ETL (full loads desnecessários, operações linha a linha custosas), nas consultas (joins ineficientes, filtros ausentes) e no modelo Tabular (medidas DAX custosas, conceito de particionamento ausente).

No nível do banco de dados, os índices columnstore são o instrumento mais eficaz para consultas analíticas. Um índice columnstore clusterizado na tabela de fatos comprime os dados por coluna, habilita o processamento em lote e utiliza otimalmente o paralelismo da CPU moderna. Consultas que agregam bilhões de linhas são, portanto, ordens de magnitude mais rápidas do que com índices row-store clássicos. Complementarmente, tabelas particionadas ajudam a subdividir tabelas muito grandes em segmentos gerenciáveis e a limitar os scans às partições relevantes.

Performance ETL é uma disciplina própria. As causas mais comuns de tempos de carga lentos são: cursores desnecessários e operações RBAR (Row by Agonizing Row) em vez de operações SQL baseadas em conjuntos, índices ausentes em tabelas de staging para operações MERGE, atividade de logging desnecessária em transações em massa e controle de transações excessivamente granular. Nos meus projetos, reduzi tempos de execução ETL de várias horas para menos de uma hora simplesmente convertendo lógica de cursores em operações T-SQL baseadas em conjuntos e adicionando índices temporários direcionados durante o carregamento.

Tuning de consultas e análise do plano de execução

Diante de problemas concretos de performance, começo pelo plano de execução. O plano de execução real (não o estimado) mostra onde o SQL Server gasta tempo e recursos: quais operadores são custosos, onde surgem erros de estimativa por estatísticas desatualizadas, se um tipo de join é ótimo e onde estão as estatísticas de espera críticas. Padrões comuns são key lookups, que podem ser eliminados adicionando poucas colunas a um índice coberto, e table spools, que apontam para índices ausentes nas fontes de MERGE.

As estatísticas são a base sobre a qual o otimizador de consultas toma suas decisões. Estatísticas desatualizadas levam a decisões de plano baseadas em premissas incorretas sobre volumes de dados e distribuições de valores — às vezes com quedas dramáticas de performance. Em ambientes de DWH em produção, garanto atualizações regulares de estatísticas na janela de manutenção, combinadas com um monitoramento automatizado que chama atenção para planos com erros elevados de estimativa.

• Índice columnstore clusterizado em tabelas de fatos para consultas analíticas
• Particionamento de tabelas para grandes fatos e eficiência ETL
• Operações T-SQL baseadas em conjuntos em vez de cursores e RBAR
• Índices cobertos em tabelas de staging para operações MERGE eficientes
• Análise do plano de execução: plano real, estatísticas de espera, fatores de custo
• Atualizações regulares de estatísticas e manutenção de índices na janela de manutenção

Abordagem de Trabalho

Um projeto de data warehouse não começa com o primeiro CREATE TABLE, mas com a compreensão dos requisitos de negócio. Quais perguntas precisam ser respondidas? Quais sistemas de origem existem e quão confiáveis e completos são seus dados? Quais dados históricos estão disponíveis e qual profundidade histórica é realmente necessária? Essas perguntas parecem óbvias, mas na prática são frequentemente puladas — com o resultado de que meses após o início do projeto um modelo precisa ser refeito porque um requisito negligenciado tornou a decisão de design original obsoleta.

• Análise: examinar dados das fontes, avaliar qualidade dos dados, compreender requisitos de negócio
• Arquitetura: definir modelo em camadas, abordagem de modelagem e estratégia ETL
• Implementação: construção iterativa, começando pelas áreas temáticas críticas para o negócio
• Garantia de qualidade: testes tSQLt, regras de plausibilidade, conciliação de resultados
• Entrega: SSAS Tabular ou Power BI, com definições de medidas documentadas
• Operação: monitoramento, alertas de erro, conceito de manutenção e documentação

Trabalho remotamente, de forma híbrida ou presencialmente — conforme as necessidades do projeto e a preferência do cliente. Em projetos que já têm uma equipe, trabalho integrado: como capacidade adicional, como especialista em determinadas áreas temáticas ou como líder técnico que define a arquitetura e a direção enquanto a equipe conduz a implementação. Em projetos sem uma equipe de DWH existente, assumo a concepção e implementação completas, da arquitetura em camadas até a primeira entrega em produção.

Documentação para mim não é um apêndice, mas parte da entrega. Decisões de arquitetura, glossários de modelos, descrições ETL e manuais operacionais são entregues como parte do projeto — de forma que uma equipe possa estender e operar o DWH de forma autônoma após o término do projeto. Um data warehouse que apenas seu construtor entende não é um ativo, mas um risco.

Situações típicas de entrada: uma empresa tem um DWH que foi crescendo, está se tornando cada vez mais intensivo em manutenção e cujos tempos ETL estão aumentando. Ou um DWH não existe e os relatórios rodam diretamente nos bancos de dados de produção — com todos os problemas de performance e qualidade associados. Ou um DWH existente precisa ser estendido com novas áreas temáticas e não há ninguém na empresa que possa construir isso de forma estruturada. Em todas essas situações, crio clareza: o que está disponível, o que falta e qual é o próximo passo sensato.

Serviços Típicos de Data Warehouse

Minha oferta cobre o ciclo de vida completo de um data warehouse — da concepção à construção, ao desenvolvimento contínuo e à modernização. Dependendo da fase do projeto e da necessidade, trago diferentes focos.

• Concepção e arquitetura: modelo em camadas, abordagem de modelagem, estratégia ETL
• Modelagem dimensional Kimball: esquema estrela, tabelas de fatos, dimensões, hierarquias
• Modelagem Data Vault: hubs, links, satélites, chaves hash, lógica de carga
• Desenvolvimento ETL com SSIS e T-SQL: staging, core, reporting, incremental e idempotente
• Historicização SCD Tipo 2: baseada em MERGE, hash diff, versionamento completo
• Garantia de qualidade de dados: testes unitários tSQLt, mecanismos de quarentena, regras de plausibilidade
• Integração CI/CD: deployment via Jenkins ou Azure DevOps, execução automatizada de testes
• SSAS Tabular: construção do modelo, medidas DAX, segurança em nível de linha, particionamento, estratégia de atualização
• Análise e otimização de performance: índices, particionamento, tuning ETL, otimização de consultas
• Modernização de DWHs consolidados: revisão de arquitetura, consolidação, documentação
• Conexão e migração para Azure: ADF, Synapse, SSAS Tabular no Azure, Power BI Premium

Não entrego projetos padronizados, mas adapto meu trabalho ao projeto concreto. Isso começa com a compreensão do que realmente é necessário — e inclui explicitamente a conversa honesta sobre o que não é necessário e qual custo uma determinada decisão acarreta. Um DWH precisa ser proporcional ao requisito de negócio; a melhor arquitetura é a mais simples que atende ao requisito de forma confiável.

Minha experiência abrange desde pequenas empresas de médio porte com um punhado de sistemas de origem até grandes organizações com paisagens de dados complexas e heterogêneas. Conheço as dificuldades típicas de crescimento de ambos os mundos: o DWH de médio porte que se tornou uma rede de exportações Excel e correções manuais, assim como o DWH corporativo que sucumbe ao seu próprio peso de interfaces, lógicas de casos especiais e dívida técnica. Em ambos os casos, o caminho adiante é similar: análise, priorização, construção estruturada, garantia de qualidade.

Projetos de referência selecionados (anonimizados)

Perguntas frequentes sobre consultoria de Data Warehouse