How to validate data quality in a Lakehouse: step by step

This tutorial shows how to implement data quality validations in a Lakehouse to detect inconsistent schemas, null values or violated business rules. Ensuring quality in the Lakehouse prevents misleading analyses and failures in downstream pipelines.

Prerequisites

• Account with access to the Lakehouse in Microsoft Fabric or an environment compatible with Delta Lake.
• Source file (CSV/Parquet) uploaded to the storage associated with the Lakehouse.
• Permissions to create Delta tables and run PySpark/SQL in the workspace.
• Basic knowledge of SQL and PySpark.

Step 1: Define data quality rules (Data Quality Rules)

Before the code, list the rules you want to validate: required schema, non-null columns, values within range, uniqueness, and format checks (e.g., email). These rules will be applied to the Lakehouse Delta table.

Step 2: Create example Delta table

Create a simple Delta table with example data to test the validations. This helps reproduce common errors like incorrect types or null values.

-- SQL no endpoint do Lakehouse
CREATE TABLE IF NOT EXISTS vendas_delta (
  id STRING,
  data DATE,
  cliente_email STRING,
  valor DOUBLE
)
USING delta;

-- Inserir alguns registos de teste
INSERT INTO vendas_delta VALUES
('1','2026-10-01','cliente1@example.com',100.0),
('2',NULL,'cliente2@example',NULL),
('3','2026-10-03','',50.0);

Step 3: Validate schema and types with PySpark

Load the Delta table as a DataFrame and validate types and required columns. If there are discrepancies, log them for review.

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, when

spark = SparkSession.builder.getOrCreate()
df = spark.read.format('delta').table('vendas_delta')

# Verificar colunas esperadas
expected_cols = {'id','data','cliente_email','valor'}
missing = expected_cols - set(df.columns)
if missing:
    print('Colunas em falta:', missing)

# Validar tipos básicos (exemplo simples)
# Aqui assumimos que valor deve ser double e data deve ser date
# Converter e contar falhas
bad_valor = df.filter(col('valor').isNull())
print('Registos com valor nulo:', bad_valor.count())

bad_data = df.filter(col('data').isNull())
print('Registos com data nula:', bad_data.count())

Step 4: Validate business rules (uniqueness, format)

Apply specific checks: id uniqueness and email format. Use SQL or PySpark functions to find violations and generate a report.

# Unicidade de id
dups = df.groupBy('id').count().filter('count > 1')
print('IDs duplicados:', dups.count())

# Validação simples de email (contém @ e .)
from pyspark.sql.functions import length
bad_email = df.filter(~(col('cliente_email').rlike('^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')))
print('Emails inválidos:', bad_email.count())

Step 5: Record validation results in an audit table

Creating a Delta audit table allows tracking failures over time and automating alerts. Insert one row per failure with timestamp and error type.

-- Criar tabela de auditoria
CREATE TABLE IF NOT EXISTS dq_audit (
  ts TIMESTAMP,
  tabela STRING,
  tipo_erro STRING,
  detalhe STRING
)
USING delta;

-- Exemplo de inserção manual de um erro detectado
INSERT INTO dq_audit VALUES (current_timestamp(),'vendas_delta','EMAIL_INVALIDO','linha id 2: cliente2@example');

Step 6: Automate validations with a scheduled job

Place the validation script in a scheduled pipeline (e.g., scheduled Notebook in Fabric) to run after ETL loads. If failures are found, you can notify by email or register a remediation task.

# Pseudo-exemplo: no final do notebook
if bad_email.count() > 0 or bad_data.count() > 0:
    # regista resumo na tabela dq_audit
    spark.sql("INSERT INTO dq_audit VALUES (current_timestamp(),'vendas_delta','QUEBRAS_QUALIDADE','Verificar registos nulos ou emails')")
    # aqui podes adicionar integração com notificações externas

Check the result

Query the dq_audit table and the original table to confirm that failures were detected and recorded. Also validate that the scheduled job runs without errors and that the metrics (failure counts) match the test data.

SELECT * FROM dq_audit ORDER BY ts DESC LIMIT 10;

SELECT id, data, cliente_email, valor FROM vendas_delta LIMIT 20;

Conclusion

With these steps you implemented basic data quality validations in the Lakehouse, from schema checks to business rules and auditing. Next steps: expand rules (e.g., foreign key references to other tables), create automatic alerts and integrate with ETL pipelines. Tip: start simple and add rules as you encounter real issues — which validation was most useful for your team?