How to validate data schemas in ELT: step by step

João Barros 29 de July de 2026 4 min read

Schema validation in ELT is ensuring that source data has the expected structure before being transformed at the destination. This prevents loads from failing, reports showing incorrect values, and facilitates pipeline maintenance.

Prerequisites

Account with a cluster or service that supports SQL/SQL-like (e.g.: Databricks, Synapse, Snowflake).
Source files in JSON/CSV or a staging table.
Basic SQL knowledge and some tool to run scripts (notebook or CLI).

Step 1: Define the reference schema

The first step is to have a reference schema (what we expect). It can be a JSON file with fields and types, or a metadata table. This schema is used to compare with the incoming data and decide whether the load should proceed, alert, or apply correction rules.

{
  "table": "clientes",
  "columns": [
    {"name":"id", "type":"INTEGER", "nullable":false},
    {"name":"nome", "type":"STRING", "nullable":false},
    {"name":"email", "type":"STRING", "nullable":true},
    {"name":"created_at", "type":"TIMESTAMP", "nullable":false}
  ]
}

Step 2: Infer or read the source data schema

Extract the current schema from the files or the source table. In many environments (e.g.: Spark/Databricks) it is easy to infer the schema; in others, consult the staging table definition.

-- SQL example to get columns from a staging table (Postgres/MySQL/Snowflake have variations)
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'staging_clientes';

Step 3: Compare schemas and identify discrepancies

With the reference schema and the inferred schema, compare field by field. Look for differences: missing fields, incompatible types, different nullable settings, or unexpected new fields. Implementing a simple comparison function allows automating decisions.

-- SQL pseudocode to identify missing columns
SELECT r.name AS expected, s.column_name AS actual
FROM reference_schema r
LEFT JOIN information_schema.columns s
  ON r.name = s.column_name
WHERE s.column_name IS NULL;

Step 4: Automatic correction rules (casting and filling)

Decide rules to automatically fix common issues: convert types (e.g.: STRING to INTEGER with TRY_CAST), fill null values with defaults, ignore additional fields. These rules keep the load running without manual intervention when appropriate.

-- SQL/DBT-like example to apply safe casting
SELECT
  TRY_CAST(id AS INTEGER) AS id,
  nome,
  NULLIF(email, '') AS email,
  TRY_CAST(created_at AS TIMESTAMP) AS created_at
FROM staging_clientes;

Step 5: Tagging and auditing discrepancies

Record metadata about the validation: rows with failed casting, fields filled by default, or columns ignored. Creating an audit table facilitates troubleshooting and reprocessing.

-- Example insert into the audit table
INSERT INTO schema_audits(table_name, issue_type, detail, detected_at)
VALUES('clientes','missing_column','email missing in source', CURRENT_TIMESTAMP);

Step 6: Integrate validation into the ELT pipeline

Make validation the initial step of the ELT: (1) extract to staging, (2) validate schema, (3) apply corrections, (4) load to the destination. Automate with jobs that fail on critical levels and alert on minor discrepancies.

-- Pseudoflow orchestration
1. Extract -> staging_clientes
2. Run validate_schema('clientes')
   -> returns status and audit rows
3a. If status = FAIL then alert and stop
3b. If status = WARN then apply corrections and continue
4. Load to production_clientes

Verify the result

Confirm that the destination has the columns with the expected types and that the audit table contains any discrepancies. Tests to run: simple query to check types and nulls; count rows with failed casting; review audit entries.

-- Quick checks
SELECT COUNT(*) FROM production_clientes;
SELECT COUNT(*) FROM production_clientes WHERE id IS NULL;
SELECT * FROM schema_audits WHERE table_name='clientes' ORDER BY detected_at DESC LIMIT 10;

Conclusion

Validating schemas in ELT reduces failures and ensures that transformations are applied to data with a known structure. Next steps: implement automated schema regression tests and alerts via e-mail/Slack. Tip: start with simple rules (casting and defaults) and enrich the audit as needed.