Data Engineer · Step 5 of 8 · 8 min
Data quality and testing
Write the checks that catch bad data before your users do.
Bad data is the usual failure
Pipelines rarely crash in dramatic ways. More often they run successfully and load wrong data: a column renamed upstream, a currency that changed, a day of missing rows. The job finishes green and the dashboard is quietly wrong.
Your aim is to make these problems loud and early, before an analyst presents them to the board.
The basic checks
Start with a few checks on every important table. Keys are unique and not null. Values are in an accepted set, such as order status. Foreign keys match a row in the related table. Row counts are within an expected range compared with yesterday. Data is fresh, meaning the latest timestamp is recent.
Tools like dbt let you declare these tests next to your models. Great Expectations and Soda are other common options. A simple SQL query that returns bad rows is also a test.
Contracts with the source
Many problems start with a change you did not know about. A data contract is an agreement with the team that produces data about its schema and meaning. It can be as simple as a shared document and an alert when the schema changes.
Test your code, too
Data tests check the data. Unit tests check your transformation logic with small, known inputs. Put both in version control and run them automatically before changes go live. This is the same discipline software engineers use, and employers look for it.
Try it
Write five tests for one table
- 1.Use the coffee shop tables from step 2, or any table you have.
- 2.Ask a chatbot the prompt below.
- 3.Run each query in DuckDB. A good test returns zero rows when the data is healthy.
- 4.Break the data on purpose, for example insert a duplicate, and confirm a test catches it.
Here is my table schema: [paste the CREATE TABLE statement, no real data]. Write five SQL data tests that each return the bad rows: unique key, not null, accepted values, a foreign key check, and freshness. Explain what each one protects against.