dbt Tests vs Great Expectations: Which Data Quality Framework Fits a Small Data Team

The damage rarely happens in the pipeline. It happens in a Tuesday operations meeting, when the Ops Director looks at the on-time-delivery chart, says "that isn't right, we shipped 40 more than that", and everyone in the room quietly stops trusting the screen. You will fix the underlying row in an hour. Rebuilding the trust takes two quarters.

With a two-to-four-person data team you cannot answer that with a data quality programme — there is no capacity for a stewardship council or a tool that needs its own owner. dbt's built-in tests and Great Expectations dominate this conversation, and they are not competitors in the way the comparison usually implies. They solve overlapping but genuinely different problems.

dbt data tests are SQL assertions declared alongside your transformation models — select statements that return failing rows, where zero rows means the assertion held. A generic test is a line of YAML against a column (unique, not_null) reused everywhere. A singular test is a .sql file containing any query that returns rows you consider wrong — a negative net weight, a despatch dated before its order, a plant code not on the approved list. That flexibility is the point: most checks that would have caught your worst incident are business rules, not schema rules.

Three configs turn dbt tests from a CI toy into an operational control: severity is error or warn; error_if and warn_if take comparison expressions, so severity error with error_if ">1000" and warn_if ">10" gives a tolerance band rather than a binary — which is why dbt tests survive contact with real, slightly-dirty data. The dbt-utils package adds accepted_range, expression_is_true, not_null_proportion, unique_combination_of_columns and recency. (dbt 1.8's unit tests are a different thing — validating SQL logic against mock inputs before materialisation, for development, not production monitoring.)

Great Expectations (GX Core) is a standalone Python validation framework. It does not need dbt, a warehouse or a transformation layer. Be careful with anything written about it more than about eighteen months ago — GX 1.0 was a rewrite, not a release, and moved configuration from YAML into Python.

The architecture is a chain of small objects: a Data Context holds configuration and results; a Data Source and Data Asset point at records; a Batch Definition retrieves them; Expectations are grouped into a Suite; a Validation Definition binds a Batch to a Suite; a Checkpoint runs it and fires Actions. The capability dbt structurally cannot match is reach — GX Core connects to SQL backends (BigQuery, Databricks SQL, Microsoft Fabric, SQL Server, PostgreSQL, Redshift, Snowflake) and, separately, to filesystem data such as .csv and .parquet read through pandas or Spark, before it ever reaches a warehouse.

If the data is already in your warehouse and expressible as SQL, dbt tests do the job for a fraction of the effort. If the problem is upstream — files, APIs, or a source your transformation layer never touches — dbt cannot help and GX can.

dbt tests cost almost nothing marginally: they live in the same repository, run in the same command, fail in the same log and alert through the same channel as the pipeline you already run. Great Expectations costs a genuine ongoing commitment — since 1.0 the suites are Python code, so they need a repository, a review process, a runtime and someone who keeps the library version current.

Both also fail silently, and you should know how. dbt tests: a column with no test declared has no coverage and looks identical in the logs to one that passed; a warn-severity test that everyone has learned to ignore; a test disabled "temporarily"; and store_failures tables nobody reads. GX: a Checkpoint that is not scheduled validates nothing while the Data Docs still show last month's confident green.

Three things have changed here, and they change the recommendation. dbt runs natively in Fabric now, in preview — the dbt job item compiles and executes a dbt project inside Fabric with native scheduling, monitoring and lineage, no local install. To run dbt Core yourself, the Microsoft-maintained dbt-fabric adapter targets Fabric Data Warehouse (not the read-only SQL analytics endpoint) and authenticates via Microsoft Entra. Great Expectations runs in Fabric notebooks, supporting Fabric as a SQL dialect through add_fabric with Entra credentials.

And Fabric may make both unnecessary for part of your estate. Materialized Lake Views support data-quality constraints written into the view definition — a CHECK with ON MISMATCH FAIL (stop the refresh at the first violation, the default) or ON MISMATCH DROP (remove the offending rows and continue, with the dropped count in the lineage view). That is materially different from both frameworks: it acts at write time, so a violating row never reaches the gold table rather than being reported afterwards. For a two-person team on a Spark SQL medallion, constraints plus the data-quality report plus Data Activator alerting may be the whole programme.

There is no universal winner — only a decision shaped by two variables: how big your team is, and where your bad data actually enters.

• Already running dbt, problems in-warehouse: use dbt tests, and stop — source freshness on every source, not_null and unique on every key, relationships on every foreign key, and five to ten singular tests for the business rules that have burned you
• Bad data arriving as files or API payloads: add GX at the ingestion boundary only — one Checkpoint, one suite per inbound feed, validating before the file lands
• Transformations built as Materialized Lake Views: start with MLV constraints, and for a two-person team that may be the whole programme
• Four or more people with a named platform owner: dbt tests as the default, plus GX at the ingestion boundary

Below four people, running both is how a data quality effort quietly dies — two codebases, two schedules, two on-call surfaces, and nobody with time to maintain either.

Neither framework tells you what "correct" is — both check assertions someone wrote, so a missing rule is a silent gap. Detection is not prevention: dbt tests run after materialisation and GX Checkpoints when scheduled, so a bad row can reach a dashboard before a test catches it (which is why write-time MLV constraints matter). Alert fatigue kills more programmes than false negatives — a team of three can absorb roughly one genuine data-quality alert a day, and tests do not create the ownership that acts on it.

Two more: GX's version history is a maintenance liability — the 0.18-to-1.0 rewrite invalidated tutorials, wikis and forum answers, so old code silently stops working. And neither fixes the source system: if the ERP allows a despatch to be posted against a closed order, the real fix is a master-data or process control at source, not a downstream test.

Five things, in order — none requires a tool decision on day one:

• List your last five data incidents — the times a number was wrong and someone noticed — and for each, write the single SQL query that would have caught it
• Work out where each incident entered; if four of five were in-warehouse, your answer is dbt tests
• Put source freshness on every source this week — the highest return per hour of anything here, and it catches the pipeline that simply stopped
• Name the human who receives failures: one person, one channel, one expected response time
• Pick the ten tests you will actually maintain — keys, foreign keys, freshness and the business rules from step one, all at error

If your silver and gold layers already run as Materialized Lake Views, add constraints there before evaluating a framework at all. We build governed Microsoft Fabric estates where quality checks live inside the pipeline rather than beside it — tests that fail loudly and an owner who acts on them.