How to test data quality in ELT: step by step

João Barros 05 de July de 2026 5 min read

In an ELT pipeline, data is loaded into the data warehouse first and only transformed afterwards. That order brings speed, but also a risk: raw data lands without validation, so a null value or a duplicate row can travel all the way to your reports without anyone noticing. Testing data quality in ELT with simple SQL queries solves this — each test runs after the load and fails as soon as it finds something unexpected, before the data is consumed.

Prerequisites

Access to a SQL data warehouse (Snowflake, BigQuery, PostgreSQL, SQL Server, and others).
A table already loaded by the ELT process — in this example, stg_encomendas.
Basic SQL knowledge: SELECT, WHERE, GROUP BY and COUNT.

Step 1: Define the quality rules

Before writing any code, decide what "valid data" means for your table. Writing the rules out in words makes them easy to review with the business team. For stg_encomendas we will use four very common rules:

Not null: the id_encomenda column can never be empty.
Unique: no two rows can share the same id_encomenda.
Accepted values: the estado column can only be 'nova', 'paga' or 'cancelada'.
Freshness: there must be at least one order from the last 24 hours.

The core idea is always the same: each test is a query that returns the rows that break the rule. If the query returns zero rows, the test passes; if it returns rows, those rows are exactly the problems to fix.

Step 2: Test null and duplicate values

Start with the two most frequent tests in a data pipeline. The first counts how many id_encomenda values are null; the second looks for repeated keys, a classic error when a load runs twice.

-- Test 1: id_encomenda cannot be null
SELECT COUNT(*) AS falhas
FROM stg_encomendas
WHERE id_encomenda IS NULL;

-- Test 2: id_encomenda must be unique
SELECT id_encomenda, COUNT(*) AS repeticoes
FROM stg_encomendas
GROUP BY id_encomenda
HAVING COUNT(*) > 1;

If Test 1 returns a number greater than zero, or Test 2 returns any row, the load has a problem that should be investigated before moving on.

Step 3: Validate accepted values and integrity

Now make sure the estado column only contains expected values and that every order belongs to a customer that actually exists in the customers table.

-- Test 3: estado can only contain values from the list
SELECT DISTINCT estado
FROM stg_encomendas
WHERE estado NOT IN ('nova', 'paga', 'cancelada');

-- Test 4: referential integrity with the customers table
SELECT e.id_encomenda
FROM stg_encomendas AS e
LEFT JOIN stg_clientes AS c
  ON e.id_cliente = c.id_cliente
WHERE c.id_cliente IS NULL;

Test 3 reveals invalid states, such as a typo coming from the source. Test 4 shows orders with an id_cliente that matches no customer — a typical sign of data misaligned between tables.

Step 4: Test data freshness

A pipeline can finish without errors and still bring only old data. This test confirms that the latest load brought recent records.

-- Test 5: there must be orders from the last 24 hours
SELECT COUNT(*) AS encomendas_recentes
FROM stg_encomendas
WHERE data_encomenda >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR;

Here the logic is reversed: the test fails if the result is zero. Time interval syntax varies between warehouses, so adapt INTERVAL '24' HOUR to yours (for example, with DATEADD on SQL Server). Adjust the window to your pipeline's frequency too.

Step 5: Combine everything into a single test

To automate, combine the tests that return "failing rows" into one query with UNION ALL. Each row in the result becomes a failure to investigate, and an empty result means everything is fine.

SELECT 'nulo:id_encomenda' AS teste, CAST(id_encomenda AS VARCHAR) AS detalhe
FROM stg_encomendas
WHERE id_encomenda IS NULL

UNION ALL

SELECT 'estado_invalido' AS teste, estado AS detalhe
FROM stg_encomendas
WHERE estado NOT IN ('nova', 'paga', 'cancelada');

Save this query as the final step of your ELT — for example, in a dbt model, an Airflow task or a scheduled procedure. If it returns rows, make the pipeline stop or send an alert to the team, so nobody builds reports on wrong data.

Check the result

To confirm the tests work, insert an invalid row on purpose and run the queries again:

INSERT INTO stg_encomendas (id_encomenda, id_cliente, valor, data_encomenda, estado)
VALUES (NULL, 10, 25.0, CURRENT_TIMESTAMP, 'expedida');

After this insert, Test 1 should return 1 and Test 3 should show the state 'expedida', which is not in the allowed list. If that happens, the tests are catching problems as expected. Delete the test row at the end to leave the table as it was.

Conclusion

With just five simple queries you now validate nulls, duplicates, accepted values, referential integrity and freshness every time the ELT runs, catching errors before they reach the reports. The next step is to make these tests an automatic part of the pipeline and store each result in a history table, so you can track how quality evolves over time. Which of these rules would be the most critical in your own data?