Data Quality Tools

dbt ships 4 generic data tests: unique, not_null, accepted_values and relationships, declared in the YAML next to the model they protect. A singular test is a .sql file whose select returns the failing rows, so any rule you can write as a query becomes a test. dbt test runs them, and with store_failures dbt saves the failing rows to a table so you can look at them instead of rerunning the query.

2 more checks sit in the same project. Source freshness takes warn_after and error_after thresholds and a loaded_at_field, and runs with dbt freshness. Unit tests, available from dbt v1.8, check a model's logic against small hand-written inputs before it is built, which is a different job from checking real data: they run in development and CI, and only SQL models can be unit tested.

The strength of dbt tests is that they cost nothing to add to a project you already run, and they execute in the warehouse next to the data. The limit is the same fact seen from the other side: they check what dbt builds. A raw file that fails before it is loaded, or a Spark job that never touches dbt, needs a check somewhere else. The package dbt-expectations adds more than 60 tests in the style of Great Expectations, written as SQL. The dbt interview questions cover how tests and sources come up in interviews, including severities.

GX Core is a Python library released under the Apache 2.0 license. Its unit is the Expectation, a verifiable assertion about data, and a set of them is an Expectation Suite. The rest of the model says where the data comes from: a Data Source connects to a store, a Data Asset is a table or query result in it, and a Batch Definition tells GX how to slice the asset into the Batch it validates. A Validation Definition ties a Batch Definition to a suite, and a Checkpoint runs several of them and can trigger actions such as an alert, with results written to Data Docs.

That structure is more setup than a YAML file, and it pays off when validation is part of a Python program: a task in an orchestrator that validates a dataframe before writing it, or a check that gates a file drop. Expectations can run against SQL tables and Spark DataFrames, so one vocabulary covers both. The cost is that the checks are code, so whoever reviews them must read Python.

Soda expresses checks in SodaCL, a YAML-based language. A file lists checks per dataset: row_count between 10 and 1000, missing_count(birth_date) = 0, duplicate_count(phone) = 0, freshness (start_date) < 1d, plus schema checks that warn or fail when columns go missing or change type. A scan returns pass or fail per check, with an error when a check can't run, and a check can be set to warn instead of fail.

The current Soda Core v4 is described by its repository as a data quality and data contract verification engine: a contract is a YAML file for one dataset that covers its schema and its data, verified with soda contract verify locally or in a pipeline, and also through Soda Cloud. That shape suits teams where analysts or data owners read and edit the rules, because the file reads like a specification and needs no Python. Check which major version a tutorial uses: v3 and v4 install different packages.

Deequ is an Apache 2.0 library, built on Apache Spark, for defining unit tests for data on large datasets. Its research paper, Automating large-scale data quality verification (Schelter et al., PVLDB 2018), describes computing the metrics for all constraints in few passes over the data. Besides constraints it offers a metrics repository, profiling, anomaly detection over the metrics' history, and constraint suggestion that proposes checks from the data. PyDeequ is its Python interface.

Pandera is an MIT-licensed Python framework that validates dataframes at runtime, for pandas, Polars, Dask, Modin, Ibis and PySpark. A schema is either a DataFrameSchema object or a DataFrameModel class, written like a dataclass, and validation with lazy=True runs every rule before raising so the error report lists all failures, split into schema problems and data problems.

Both live inside the job that handles the data, so the check runs on the same cluster or process, with no second system to schedule. Neither draws a dashboard for you. They fit pipelines written in Spark or Python where the cheapest place to stop bad data is the transform itself.

AWS Glue Data Quality is a managed, serverless service built on Deequ. Rules are written in the Data Quality Definition Language (DQDL), it ships more than 25 out-of-the-box rule types, and a data quality score is the percentage of rules that pass. It can recommend rules from your data and detect anomalies with machine learning, and it runs either on tables in the Glue Data Catalog or inside Glue ETL jobs, where it can also identify the failing records.

Snowflake attaches checks to tables with data metric functions (DMFs). A DMF measures one attribute and returns a value, and an expectation pairs it with a pass condition. The feature requires Enterprise Edition, runs on serverless compute billed in credits, and by default evaluates once an hour per table. Its anomaly detection covers volume and freshness.

In Databricks pipelines, an expectation is a constraint on a dataset with 3 failure actions. The default, warn, leaves invalid rows in place and counts them in metrics. Drop removes them before the write, and fail stops the update when a single record violates it. The fail action records no metrics, because the update fails. These checks are the shortest path when the data already lives on one platform, and the least portable: the rules do not move to another warehouse.

A check that must find zero failures is the wrong rule for many columns. Take a orders table of 2,000,000 rows where customer_id is missing on a few guest checkouts. A single test can carry 2 thresholds, warn_if and error_if, each a comparison on the count of failing rows. Set warn_if: ">2,000" (0.1% of the table) and error_if: ">20,000" (1%).

dbt reads error_if first, then warn_if, so 4,300 failing rows is 0.215% of the table: above the warn line of 2,000, below the error line of 20,000, and the test returns "warn". The pipeline continues and the owner is told. The dbt documentation on severity gives the rules. Great Expectations has a similar knob in its mostly argument. Soda and Databricks offer warn levels too.

A threshold needs a baseline, so choose it from what the column looks like on a healthy day rather than from a round number. Pair every not-null or uniqueness check with a row count check as well. A test that asserts no row is bad passes on an empty table, so a load that wrote nothing looks perfect. A check such as Soda's row_count between 10 and 1000 closes that gap.

Every tool here checks what someone thought to write down. A rule fires on the failure you imagined, and the one you did not imagine passes. Anomaly detection on volume and freshness covers some of that gap by learning a range from history; Deequ and Glue offer it, and so does Snowflake. The wider practice is described in the guide to data observability. The broader discipline of deciding what quality means for a table is in data quality for data engineers.

Checks also need an owner and a place in the run. A failing test nobody reads is a log line. Decide who is told, which failures block the load and which only warn, and where in the DAG the check runs, as part of the pipeline design described in the data pipeline architecture guide. This is also what interviewers probe: how a pipeline detects bad data, and what it does next. Practise those answers with data pipeline interview questions and data pipeline practice problems, and see how the design round asks for them in the system design round.