How to create a data validation table in a Data Warehouse

João Barros 04 de November de 2026 4 min read

Let's create a data validation table in a Data Warehouse to record rules violated during ETL/ELT loads. This is useful to track errors, facilitate fixes and improve data quality over time.

Prerequisites

Data Warehouse environment (e.g.: SQL Server, Azure Synapse, Amazon Redshift) with permissions to create tables.
Simple ETL/ELT pattern (e.g.: Azure Data Factory, SSIS, dbt) to invoke validations.
Basic knowledge of SQL and the schema of source and target tables.

Step 1: Define the purpose and validation rules

Before creating the table, define which types of validations you want to record. Common examples: undesired null values, invalid types, out-of-range values, duplicate keys and references to missing dimension rows. Choose important fields to diagnose the error (e.g.: source_system, table_name, row_key, column_name, error_code, error_message).

Step 2: Create the validation table structure

Create a central table to store validation events. The table should be compact yet informative: include timestamps, source row identifiers and load context (batch_id or run_id).

CREATE TABLE validation_errors (
  validation_id BIGINT IDENTITY(1,1) PRIMARY KEY,
  run_id VARCHAR(100) NOT NULL,
  source_system VARCHAR(100),
  table_name VARCHAR(100),
  row_key VARCHAR(500),
  column_name VARCHAR(100),
  error_code VARCHAR(50),
  error_message VARCHAR(1000),
  detected_at DATETIME2 DEFAULT SYSUTCDATETIME(),
  severity VARCHAR(20) DEFAULT 'warning'
);

Adjust types and sizes according to your Data Warehouse. In columnar environments (e.g.: Redshift, Synapse dedicated SQL pool) you can tweak types for compression.

Step 3: Implement validations in the ETL/ELT pipeline

Implement the rules in the transformation step. In traditional ETL (SSIS) or in ELT (SQL after load), use queries that identify invalid rows and INSERT into the validation table instead of blocking the entire load.

-- Exemplo SQL para detectar nulos indevidos e inserir na tabela de validação
DECLARE @run_id VARCHAR(100) = 'run_20261104_01';

INSERT INTO validation_errors (run_id, source_system, table_name, row_key, column_name, error_code, error_message, severity)
SELECT
  @run_id,
  'syst_x',
  'stg_clientes',
  CAST(id AS VARCHAR(500)),
  'email',
  'NULL_EMAIL',
  'Email não pode ser nulo',
  'error'
FROM stg_clientes
WHERE email IS NULL;

For referential integrity validations, do LEFT JOINs and record rows without matches:

-- Exemplo: FK para dimensão de país
INSERT INTO validation_errors (run_id, source_system, table_name, row_key, column_name, error_code, error_message)
SELECT
  @run_id, 'syst_x', 'stg_clientes', CAST(c.id AS VARCHAR(500)), 'country_code', 'FK_MISSING_COUNTRY', 'Código de país não existe na dimensão'
FROM stg_clientes c
LEFT JOIN dim_country d ON c.country_code = d.country_code
WHERE c.country_code IS NOT NULL AND d.country_code IS NULL;

Step 4: Classify and manage errors

Use the severity field to distinguish 'error' (prevents target load) and 'warning' (is loaded but recorded). You can automate alerts when critical errors exist: send email, create a ticket or block dataset promotion to production.

-- Exemplo simples: contar erros críticos antes de proceder à carga final
IF EXISTS (SELECT 1 FROM validation_errors WHERE run_id = @run_id AND severity = 'error')
BEGIN
  THROW 50001, 'Existem erros críticos. Parar processo de carga.', 1;
END

Step 5: Enrich the table with metrics and history

Add additional columns if you need later analysis: resolved_by, resolved_at, note. Create views or reports in Power BI to monitor error trends by run, by table or by error type.

ALTER TABLE validation_errors
ADD resolved_by VARCHAR(100), resolved_at DATETIME2, note VARCHAR(2000);

Verify the result

To confirm, run verification queries: count errors by run_id, view sample reported rows and test scenarios that generate known errors. Examples:

-- Contagem por severidade
SELECT severity, COUNT(*) as cnt FROM validation_errors WHERE run_id = 'run_20261104_01' GROUP BY severity;

-- Exemplos de erros
SELECT TOP 20 * FROM validation_errors WHERE run_id = 'run_20261104_01' ORDER BY detected_at DESC;

Conclusion

A data validation table in a Data Warehouse enables monitoring and resolving quality issues without disrupting the whole pipeline. Next steps: automate alerts, create dashboards in Power BI and add automatic remediation routines for recurring errors. Tip: start with a few essential rules and expand as you learn which errors have the greatest impact.