Introduction
Struggling with data quality in your pipelines is a miserable experience. As modern data environments scale, catching and resolving pipeline issues before they break your downstream analytics is a constant, exhausting battle for data teams.
This is exactly why we need data quality assurance in data engineering. Whether building in-house or partnering with external data engineering consulting services, this practice centers on the systematic validation of data pipeline code (transformations) and the underlying datasets (their quality, structure, and business logic) so that downstream systems receive clean, accurate, and reliable information.
Why we validate early and validate often in data engineering
In data engineering, the principle of "validate early, validate often" emphasizes the importance of integrating validation checks throughout the entire data pipeline process rather than deferring them to the final stages. This approach ensures that issues are detected and addressed as soon as they arise, minimizing the risk of propagating errors through the pipeline and reducing the cost and effort required to fix them.
This is also in line with the 1:10:100 rule, conceptualized by George Labovitz and Yu Sang Chang, which states that:
- The cost of preventing poor data quality at source is $1 per record.
- The cost of remediation after it is created is $10 per record.
- The cost of failure (i.e. doing nothing) is $100 per record.
But what are we actually testing in our data pipelines?
What to test in data quality engineering?
At a very high level, we can distinguish two main areas of testing:
- Transformations - code that can be defined using Python or SQL for example.
- Data - the output of the source system or the result of our transformations.
Most common mistakes in data transformations
As correctly mentioned in Smoke Test Your Data Pipelines First, programming data transformations is a minefield of stupid mistakes. Here are some examples:
- Trying to access a column that’s missing from the table produced by the upstream step in the pipeline.
- Trying to access a column that’s missing from the data frame produced in the line of code right above.
- Trying to select from a table that doesn’t exist.
- Trying to call a function that doesn’t exist.
- Forgetting to include a required argument to a function.
- Trying to perform arithmetic on a string column.
When confronted with one of these Stupid Mistakes, fixing the problem is almost always trivial. We often discover these mistakes in one of a few ways:
- Manual testing on a cluster: We manually test our code. Depending on the tools we’re using, this often requires deploying our code to a cluster, getting access to data, waiting a long time to chug through an actual dataset, and manually inspecting the outputs. However, some frameworks, such as Apache Spark, use the concept of lazy evaluation, which allows you to test the code without having to load and process the dataset (it still requires a cluster and access to the data).
- "Testing in prod" : We deploy our code to production and catch the mistakes when the pipeline fails. I’ve been there, and I’m not here to judge you.
- Painstaking unit tests: This is What We Are Told To Do, but it’s often a waste of time. For transformations that operate on tables with many columns, most of the effort in writing the unit test goes into enumerating all the input and output columns, not verifying business logic. These tests then slow down further development because, when we want to make a small change, like pulling in a new column, we end up needing to change ten different sites in our code.
Fortunately, modern data frameworks have made this process much easier. Tools like dbt now include built-in unit testing, allowing developers to test SQL models with sample data without writing large amounts of custom testing code. Similarly, SQLMesh checks SQL logic before it is deployed, helping identify syntax errors, invalid references, and other issues early, reducing the risk of failures in production.
“Integration” tests in data quality engineering
Integration testing in data engineering focuses on "external" testing of the application itself running in a simulated environment to verify that overall results match expectations when unit tests fall short.
What is quite challenging to test with unit tests is creating test cases that would answer questions like:
- Will my code work if the input data are malformed?
- What happens if the input data are not there yet?
- Does the app de-duplicate data in the proper way?
- Does the code properly infer the schema of CSV/JSON files?
- is the application idempotent? (do re-runs create the same output as one run?)
One solution to the challenges listed above is to focus not on unit testing, but on "external" testing of the application itself - running the application in the simulated environment and checking that the results match the expectations of the given test case. These tests are similar to integration tests in web services, where we simulate the client's input call and check how the state within the service changes and what result is returned to the user.
Running these tests has become much simpler in recent years. Developers no longer have to rely exclusively on slow and expensive cloud staging environments. Instead, they can use local-first tools such as Testcontainers to launch temporary databases on demand and LocalStack to provide local versions of cloud services like Amazon S3, making it faster and more cost-effective to test both on local machines and in CI/CD pipelines.
Read Integration tests of the Spark application to see an example of this type of testing using Spark.
Implementing data quality assurance (DQA)
Data quality assurance tests validate data reliability by checking both source data and transformed outputs. In data quality engineering, these validations are split into technical checks (focusing on schema, types, and constraints) and business-related checks (focusing on business rules).
As mentioned before, data quality tests can be used to check:
- source data, the quality or shape of which we rarely have any control over;
- data that is the result of our transformations. Yes, one way to test transformations is to make assumptions about the data set and then document those assumptions in the form of written tests.
In general, these tests can be divided into two groups:
- technical, focusing on the structural integrity and correctness of the data from a technical perspective;
- business-related, focusing on the relevance and accuracy of data in the context of business rules and processes.
Technical checks
Technical data quality checks ensure that the data adheres to predefined rules and constraints. These tests typically include:
- Uniqueness Test: Ensures no duplicate values in key fields.
- Null Check: Verifies that fields meant to have data are not NULL.
- Data Type Validation: Ensures that data conforms to expected data types (e.g., date, integer).
- Range Check: Validates that numeric values fall within a specified range.
- Format Check: Checks if data follows a specific pattern or format (e.g., phone numbers).
- Foreign Key Constraint Check: Ensures referential integrity by verifying that foreign key values exist in the referenced table.
Because these tests verify the correct operation of data pipelines, they are the responsibility of data engineers.
Examples:
Ensure that the primary key field customer_id in the customers table contains unique values with no duplicates:
SELECT
customer_id,
COUNT(*)
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;
Verify that the email column in the users table doesn’t contain any NULL values:
SELECT
COUNT(*)
FROM users
WHERE email IS NULL;
Confirm that the age column in the patients table contains values between 0 and 120:
SELECT
COUNT(*)
FROM patients
WHERE age < 0 OR age > 120;
Validate that the phone_number column follows the format (XXX) XXX-XXXX:
SELECT
contact_id,
phone_number
FROM contacts
WHERE phone_number NOT LIKE "(\d{3}) \d{3}-\d{4}";Business data quality engineering checks
Business-related data quality assurance tests verify that dataset outputs accurately support business operations, decision-making, and downstream reporting requirements.
They ensure that the data supports business operations and decision-making. These tests typically include:
- Completeness Check: Verifies all necessary data fields contain values (e.g., every product has a category).
- Consistency Check: Validates that data is logically consistent within and across datasets (e.g., total amount matches the sum of item amounts).
- Timeliness Check: Confirms that data is current and relevant, adhering to time-related constraints (e.g., delivery dates within expected time frames).
- Accuracy Check: Ensures calculated values are correct (e.g., sales amount equals quantity multiplied by price).
- Referential Integrity Check: Validates relationships between datasets (e.g., all orders have valid customer IDs).
- Business Rule Validation: Ensures data adheres to specific business rules (e.g., discounts do not exceed a certain percentage).
QA engineers, data stewards, or analytics engineers may be responsible for this type of testing, as it requires a good understanding of business needs and constant contact with data stakeholders and/or consumers. They could act as data stewards who manage data quality through continuous validation of business assumptions.
Examples:
Verify that all products in the products table have a non-empty category fields:
SELECT
COUNT(*)
FROM products
WHERE category IS NULL OR category = '';
Ensure that the total_amount in the invoices table equals the sum of item_amount in the invoice_items table for each invoice:
SELECT
i.invoice_id
FROM invoices AS i
LEFT JOIN (
SELECT invoice_id, SUM(item_amount) AS total_item_amount
FROM invoice_items
GROUP BY invoice_id
) AS ii ON i.invoice_id = ii.invoice_id
WHERE i.total_amount <> ii.total_item_amount;
Verify that the delivery_date in the orders table is within 7 days of the order_date:
SELECT *
FROM orders
WHERE DATEDIFF(day, order_date, delivery_date) > 7;
Ensure that the sales_amount in the sales table matches the quantity multiplied by price for each item:
SELECT
sale_id,
sales_amount,
quantity * price AS calculated_amount
FROM sales
WHERE sales_amount <> quantity * price;
Validate that the discount in the sales table does not exceed 50% of the original_price:
SELECT *
FROM sales
WHERE discount > (0.5 * original_price);When to test data?
Typically, we test the data after it has been transformed, i.e. after it has already been saved to the tables. But what if we want to run tests before this potentially faulty data is made available to end users, such as an application or analyst team?
Historically, one solution was to introduce a staging layer. Instead of the following architecture:
.webp)
We could have:
.webp)
This approach isolates unvalidated data before it reaches production. However, in data quality engineering, introducing an additional layer for testing, which is basically a copy of the next one, increases costs and lengthens the time it takes to deliver data to customers. After all, we do additional computations, need more storage space, etc.
Today, many modern data platforms achieve the same goal without permanently duplicating datasets. When optimizing infrastructure as part of broader cloud strategy & consulting initiatives, techniques such as Write-Audit-Publish (WAP), zero-copy cloning, and data contracts allow data to be validated before publication while avoiding much of the storage and operational overhead of traditional staging layers.
Technologies for data quality testing
Rather than building custom frameworks that manually execute SQL queries, persist results to tables, and trigger basic Slack alerts, many teams now rely on purpose-built testing tools. These tools allow developers to define, execute, and monitor validation suites using simple YAML configuration files and other declarative configurations.
- dbt tests
Especially when combined with the calogica/dbt-expectations and dbt-labs/dbt-utils packages, which guarantee an impressive list of out-of-the-box tests, and elementary data for data observability and triggering notifications and alerts.
Docs: https://docs.getdbt.com/docs/build/data-tests - Soda
Similar functionality to dbt test, extended with the above-mentioned packages. dbt test is probably a natural choice if we are already using dbt to model the warehouse. If there is no need to use dbt, one can consider Soda.
Docs: https://docs.soda.io/soda/quick-start-sip.html
- Great Expectations
It's a powerful and extensible framework, but it has a steep learning curve. If your project involves complex data pipelines across multiple environments, requires extensive customization, or your team is not SQL-centric (think data scientists doing statistical quality checks in Python Jupiter notebooks), Great Expectations may be a better fit compared to dbt tests or Soda.
Docs: https://docs.greatexpectations.io/docs/
Data observability
Beyond testing, many organizations complement data quality assurance with data observability platforms that continuously monitor data freshness, volume, schema drift, and anomalous data patterns in production. While tests verify expected conditions, observability tools help identify unexpected issues that arise in live data pipelines.
Conclusion
Implementing data quality assurance across both transformation logic and raw datasets is essential for scaling modern data operations. By adopting early validation practices and utilizing modern testing tools, organizations eliminate pipeline failures and build lasting trust in their analytics
Reach out to our engineering team to design a resilient data quality architecture tailored to your stack.
Additional Resources
Worth reading:
