dbt Tests vs Great Expectations (2026)

TL;DR

dbt data tests are the better default for assertions on dbt-managed warehouse resources because they share the DAG, SQL context, selection syntax, and build lifecycle. Great Expectations is stronger for validation outside the dbt graph, especially Python or Spark dataframes, files, multi-stage pipelines, reusable Expectation Suites, and Checkpoint-driven result actions. Many mature teams use both at different boundaries, but each rule should have one authoritative owner.

Key Takeaways

• Choose dbt data tests when assertions belong to dbt models, sources, and the transformation DAG.
• Choose Great Expectations when validation must span dataframes, files, SQL systems, pipeline boundaries, and richer result workflows.
• Use the same fixture, rules, failure rows, and CI gate before comparing developer experience or diagnostics.
• Treat dbt failures as SQL queries that return bad rows and GX Expectations as validations against a defined Batch.
• Keep rule ownership explicit when both tools are used so the same contract does not drift in two formats.
• Measure warehouse cost, failed-row evidence, maintenance effort, and incident response instead of counting features.

Choosing dbt tests vs great expectations is primarily a question of validation scope. Use dbt data tests for SQL assertions attached to dbt models, sources, seeds, and snapshots. Use Great Expectations, now commonly called GX Core, when you need a Python validation framework across dataframes, files, SQL stores, or pipeline stages that do not belong to a dbt project.

Neither tool is universally more rigorous. A four-line dbt not_null test can be exactly the right production control, while a large GX deployment can be misplaced if every rule targets a dbt model. Conversely, forcing pre-ingestion file checks or in-memory dataframe validation through dbt creates unnecessary warehouse coupling.

This guide builds one orders fixture, applies the same four contracts in both tools, injects identical defects, and evaluates the operational consequences. If SQL assertions are new to you, review SQL for QA beginners and validating data integrity with SQL before extending the examples.

TL;DR: dbt tests vs Great Expectations

dbt data tests win when the data contract is inseparable from a model and should run with its lineage. Great Expectations wins when the contract must travel across runtime types or produce a validation workflow independent of transformation code. Use both only when the boundary is clear, such as GX checking an inbound file before loading and dbt testing the transformed marts afterward.

What You Will Build

You will create a small, reproducible evaluation rather than compare marketing pages:

• A DuckDB-backed dbt project with an orders_clean model.
• Four equivalent contracts: non-null IDs, unique IDs, allowed statuses, and non-negative totals.
• Generic dbt data tests using current data_tests and arguments syntax.
• A GX Core pandas Data Source, Data Asset, Batch Definition, Expectation Suite, and Validation Definition.
• One invalid row that makes both implementations exit nonzero.
• A CI job that keeps the two gates visible and separately diagnosable.

The fixture is intentionally tiny, so it does not prove performance. Its purpose is semantic comparison: you can inspect whether both tools reject the same records, expose useful evidence, and remain maintainable. For a wider test-data plan, use how to design a test data strategy.

Prerequisites

Use Python 3.12 in an isolated environment. The example pins dbt Core 1.10.22, dbt-duckdb 1.10.1, Great Expectations 1.19.1, and pandas 2.3.1. These versions use the modern dbt data_tests syntax and the GX Data Source, Data Asset, Batch Definition, and Validation Definition APIs. Do not install a dbt Core 2 prerelease into this evaluation.

Create the workspace and requirements.txt:

mkdir -p data-quality-poc/{models,seeds,tests/generic,profiles,gx}
cd data-quality-poc
python3.12 -m venv .venv
source .venv/bin/activate

dbt-core==1.10.22
dbt-duckdb==1.10.1
great-expectations==1.19.1
pandas==2.3.1

Install and verify every important package before creating rules:

python -m pip install --upgrade pip
python -m pip install -r requirements.txt
dbt --version
python -c "import great_expectations as gx, pandas; print(gx.__version__, pandas.__version__)"

Expect dbt to list Core 1.10.22 and the DuckDB adapter 1.10.1. The Python command must print 1.19.1 2.3.1. Commit requirements.txt; a floating installation makes later comparison results ambiguous.

Step 1: Define the actual validation boundary

Before writing syntax, classify each contract by where bad data first becomes actionable. A source freshness issue, malformed CSV, transformation regression, and business-rule violation can all look like data quality failures, but they belong to different owners and recovery paths.

dbt data tests execute SQL against resources in the dbt graph. A generic test is parameterized and reusable; a singular test is a SQL file whose query returns failing rows. Zero returned rows means pass. That model is easy to explain to analytics engineers because the compiled test is ordinary SQL and selection follows model lineage. dbt data tests are not dbt unit tests: unit tests exercise model logic against static inputs before materialization, while data tests inspect actual built relations or other project resources.

GX organizes a validation around a Data Source, Data Asset, Batch Definition, Expectation Suite, and Validation Definition. A Checkpoint can run Validation Definitions and trigger Actions in a production workflow. That vocabulary costs more up front, but it decouples the contract from a single transformation graph and supports runtime-selected batches.

Write a one-line owner beside every proposed rule. For this tutorial, dbt owns the post-transform orders_clean contract. GX owns the incoming CSV boundary. The assertions are equivalent for comparison, but a real deployment should avoid permanent duplication unless it deliberately defends two distinct stages.

Verify this step by making a boundary table with columns for asset, stage, owner, failure action, and recovery. If two teams both claim the same row-level rule, resolve that ambiguity before implementation.

Step 2: Create one deterministic orders fixture

Save this file as seeds/raw_orders.csv:

order_id,customer_id,order_total,status
1,101,25.00,paid
2,102,60.00,shipped
3,103,15.50,paid

Create dbt_project.yml at the project root:

name: dq_compare
version: "1.0.0"
config-version: 2
profile: dq_compare
model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
models:
  dq_compare:
    +materialized: table

Save the local profile as profiles/profiles.yml:

dq_compare:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: dq.duckdb
      threads: 4

Create models/orders_clean.sql:

select
  cast(order_id as integer) as order_id,
  cast(customer_id as integer) as customer_id,
  cast(order_total as decimal(12, 2)) as order_total,
  cast(status as varchar) as status
from {{ ref('raw_orders') }}

Seed and build only the model:

dbt seed --profiles-dir profiles
dbt run --select orders_clean --profiles-dir profiles
python - <<'PY'
import duckdb
rows = duckdb.connect("dq.duckdb").execute(
    "select count(*) from orders_clean"
).fetchone()[0]
assert rows == 3, rows
print("orders_clean rows:", rows)
PY

The final line should print orders_clean rows: 3. This check confirms the fixture, profile, seed, reference, and model all work before either test layer is blamed. The same discipline is useful when writing SQL to validate ETL.

Step 3: Implement dbt data tests

Create models/orders_clean.yml. Use data_tests, not the older alias tests, and place generic-test parameters under arguments:

version: 2

models:
  - name: orders_clean
    description: Clean orders used by the reporting layer.
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ["paid", "shipped", "cancelled"]
      - name: order_total
        data_tests:
          - non_negative

The first three tests ship with dbt. Define the fourth as tests/generic/non_negative.sql:

{% test non_negative(model, column_name) %}
select *
from {{ model }}
where {{ column_name }} < 0
{% endtest %}

This generic test accepts the relation and column that dbt passes from YAML. Its query returns only violating rows. Avoid a trailing semicolon in test SQL because adapters embed compiled statements in other SQL.

Parse before executing warehouse queries, then run only tests attached to the selected model:

dbt parse --profiles-dir profiles
dbt test --select orders_clean --profiles-dir profiles
test -f target/manifest.json
test -f target/run_results.json

Expect four passes. Open target/compiled when a test behaves unexpectedly; it shows the SQL produced after Jinja resolution. run_results.json is the stable machine-readable artifact for CI reporting, while console text is optimized for people.

Step 4: Weigh dbt's strengths and limits

dbt keeps assertions beside model properties, so a reviewer sees column documentation and tests in one pull request. Selectors let you test a model, tag, package, path, source, or graph neighborhood. dbt build also executes selected resources in DAG order and can skip downstream nodes when an upstream test fails. That tight coupling is valuable when a transformation should never publish suspect descendants.

The failure model is transparent. A test is a query for counterexamples, and --store-failures can materialize those records in an audit schema. Use that option carefully because failing rows can contain personal or regulated data, and each test's stored relation replaces its previous failures rather than forming an incident history. Configure severity, warn_if, or error_if only when the business accepts a threshold. Turning unexpected nulls into warnings to obtain a green pipeline is not risk management.

dbt becomes less natural before data reaches the warehouse, inside a Python transformation, or across a Spark dataframe that is not represented as a dbt resource. Packages expand the test catalog, but every package adds version and adapter compatibility work. SQL also makes some statistical or cross-runtime checks harder to reuse consistently.

Verify the graph behavior rather than assuming it:

dbt ls --select orders_clean --resource-type test --profiles-dir profiles
dbt build --select orders_clean --profiles-dir profiles

The first command should list four test nodes. The second should build the model and execute its attached tests successfully.

Step 5: Implement Great Expectations on the same fixture

Save the following as gx/check_orders.py. The script uses an Ephemeral Data Context because CI recreates the complete configuration each run. Production teams that want persisted suites, Validation Results, Checkpoints, and Data Docs should use a File Data Context or GX Cloud instead.

from pathlib import Path

import great_expectations as gx
import pandas as pd

project_root = Path(__file__).resolve().parents[1]
frame = pd.read_csv(project_root / "seeds" / "raw_orders.csv")

context = gx.get_context(mode="ephemeral")
data_source = context.data_sources.add_pandas(name="orders_pandas")
asset = data_source.add_dataframe_asset(name="raw_orders")
batch_definition = asset.add_batch_definition_whole_dataframe("whole_file")

suite = gx.ExpectationSuite(name="raw_orders_suite")
suite = context.suites.add(suite)
suite.add_expectation(
    gx.expectations.ExpectColumnValuesToNotBeNull(column="order_id")
)
suite.add_expectation(
    gx.expectations.ExpectColumnValuesToBeUnique(column="order_id")
)
suite.add_expectation(
    gx.expectations.ExpectColumnValuesToBeInSet(
        column="status", value_set=["paid", "shipped", "cancelled"]
    )
)
suite.add_expectation(
    gx.expectations.ExpectColumnValuesToBeBetween(
        column="order_total", min_value=0
    )
)

validation_definition = gx.ValidationDefinition(
    name="raw_orders_validation",
    data=batch_definition,
    suite=suite,
)
validation_definition = context.validation_definitions.add(validation_definition)
validation_result = validation_definition.run(
    batch_parameters={"dataframe": frame}
)

if not validation_result.success:
    print(validation_result)
    raise SystemExit(1)

print(f"GX PASS: {len(validation_result.results)} expectations")

Run it from the project root:

python gx/check_orders.py

Expect GX PASS: 4 expectations. The dataframe is supplied at runtime through Batch Parameters because it exists only in the current process. A Validation Definition associates that Batch Definition with the suite and returns a structured Validation Result.

Step 6: Weigh Great Expectations' strengths and limits

GX gives each validation concept an explicit object. That design supports pandas and Spark dataframes, files, and SQL data sources without forcing all data through dbt. Expectation Suites can describe an asset, Batch Definitions organize what slice is validated, Validation Definitions bind data to suites, and Checkpoints coordinate validations with Actions. Result formats range from a boolean-oriented response to detailed unexpected values and indices, subject to the chosen Expectation and execution engine.

This flexibility is useful at ingestion boundaries. A producer can deliver a CSV or dataframe, GX can reject it before warehouse loading, and an orchestrator can quarantine the object. Data Docs and stored Validation Results also serve consumers who need more context than a command exit code. Custom Expectations can encode domain language rather than expose a generic SQL condition everywhere.

The cost is framework surface area. Teams must learn GX terminology, decide how contexts and stores persist, version Python and execution-engine dependencies, manage credentials, and integrate Checkpoint outcomes into their orchestrator. A pandas proof of concept does not guarantee equivalent behavior or cost on Spark or a warehouse SQL engine. Expectations that scan a full table still consume real compute.

GX also has no native understanding of dbt lineage. You can pass dbt invocation IDs, model names, or environment identifiers as orchestration metadata, but GX will not automatically know that one failed asset should skip three downstream dbt models. If DAG-aware blocking is the core requirement, dbt remains the simpler owner.

Verify the script's independence by deleting dq.duckdb in a disposable copy and rerunning python gx/check_orders.py. It should still validate the CSV because this implementation does not depend on the warehouse.

Step 7: Compare dbt tests vs Great Expectations with one defect

A green fixture only proves that both tools tolerate expected data. Inject one row that violates the accepted-status and non-negative-total contracts. Preserve the original file, capture both exit codes, then restore it:

cp seeds/raw_orders.csv seeds/raw_orders.clean.csv
printf '4,104,-7.00,refunded\n' >> seeds/raw_orders.csv

set +e
(
  dbt seed --profiles-dir profiles &&
  dbt run --select orders_clean --profiles-dir profiles &&
  dbt test --select orders_clean --store-failures --profiles-dir profiles
)
dbt_status=$?
python gx/check_orders.py
gx_status=$?
set -e

mv seeds/raw_orders.clean.csv seeds/raw_orders.csv
test "$dbt_status" -ne 0
test "$gx_status" -ne 0
printf 'dbt=%s gx=%s\n' "$dbt_status" "$gx_status"

Both statuses must be nonzero. dbt should report failures for accepted_values and non_negative; the null and uniqueness tests should still pass. GX should print a Validation Result containing two unsuccessful Expectations before exiting 1.

Now compare diagnostic work. In dbt, inspect the compiled queries and the adapter's audit relations created by --store-failures. In GX, inspect each result's unexpected counts, values, and indices according to its result format. Ask an engineer to identify the bad row and rejected rule without seeing the injected line first. Record time, wrong turns, and missing evidence.

Do not compare only how many assertion types are available. Evaluate whether a failure reaches the correct owner, preserves safe evidence, points to the affected batch, and supports replay. The same incident can be cheap to detect and expensive to resolve.

Step 8: Compare CI, artifacts, and operating cost

Run the gates as separate steps so their outcomes remain attributable. This GitHub Actions workflow assumes the tutorial files and requirements.txt are committed:

name: data-quality-comparison

on:
  pull_request:
  workflow_dispatch:

jobs:
  validate-orders:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
          cache: pip
      - run: pip install -r requirements.txt
      - name: Build and test dbt resources
        run: |
          dbt seed --profiles-dir profiles
          dbt build --select orders_clean --profiles-dir profiles
      - name: Preserve dbt artifacts
        if: ${{ always() }}
        uses: actions/upload-artifact@v4
        with:
          name: dbt-artifacts
          path: |
            target/manifest.json
            target/run_results.json
          if-no-files-found: error
          retention-days: 14
      - name: Validate inbound orders with GX
        run: python gx/check_orders.py

The dbt step proves the transformed relation and attached graph tests. The GX step proves the inbound file contract. Uploading manifest.json and run_results.json preserves machine-readable dbt evidence without publishing stored bad rows. A persistent GX deployment should separately retain sanitized Validation Results and connect Checkpoint Actions to approved notification or incident systems.

Cost has three parts. First is execution: warehouse scans, dataframe memory, Spark jobs, and repeated rules. Second is platform work: credentials, metadata stores, artifacts, upgrades, and observability. Third is human effort: authoring, review, triage, and ownership. dbt is usually cheaper when the team already runs dbt and the assertion targets one model. GX can be cheaper when one portable suite prevents several custom validators across heterogeneous pipelines.

Test cancellation, a missing file, malformed types, and an unavailable database. A useful quality gate fails clearly when validation cannot run; it must not convert infrastructure errors into apparent data success.

Which Should You Choose for dbt tests vs Great Expectations

Choose dbt data tests when most contracts concern dbt-managed relations, analytics engineers own the rules, and test selection must follow model lineage. Primary-key checks, accepted dimensions, referential integrity, reconciliation SQL, and model-specific business assertions fit naturally. Use singular tests for one-off multi-relation conditions and generic tests for repeated patterns.

Choose Great Expectations when validation starts before warehouse loading, operates on pandas or Spark dataframes, spans storage technologies, or needs reusable suites and structured validation workflows outside dbt. GX also fits teams that want batch-oriented results and Checkpoint Actions to integrate with orchestration. Prove the target engine, not only pandas, before standardizing.

Use both when they guard different boundaries. GX can reject a vendor file at arrival; dbt can test the normalized staging model and downstream marts. Document the handoff so an inbound rule, transformation rule, and consumer contract do not become three drifting copies of the same condition. The testing data migrations guide and database constraint testing guide help separate storage guarantees from pipeline assertions.

A practical decision rule is simple:

• Existing dbt estate plus warehouse-model checks: start with dbt.
• Mixed Python, Spark, file, and SQL boundaries: pilot GX.
• Need DAG-aware build blocking: prefer dbt for that gate.
• Need validation before a relation exists: prefer GX.
• Need both: assign one owner and one remediation action to every rule.

Do not migrate merely because one library has more named assertions. Migrate when the current execution boundary, evidence, or ownership model causes measurable failures.

Troubleshooting

dbt says a generic test is undefined -> Confirm non_negative.sql is under tests/generic or macros, the block is named non_negative, and YAML uses the same name. Run dbt parse before querying data.

dbt accepts YAML but rejects test arguments -> Use dbt Core 1.10.5 or newer and nest parameters beneath arguments. Do not mix tests and data_tests on the same resource.

dbt test passes but the model was not rebuilt -> dbt test validates current relations; it does not materialize models. Use dbt build for DAG-ordered construction and testing, or run dbt run before dbt test as the tutorial does.

GX reports that a Data Source or suite already exists -> The sample intentionally creates a new Ephemeral Data Context per process. In a persistent context, retrieve existing objects or use the documented add-or-update operation rather than blindly adding duplicate names.

GX validates the wrong dataframe -> Pass the intended object using batch_parameters={"dataframe": frame} to the Validation Definition. A dataframe Batch Definition represents the whole runtime dataframe and does not discover a later variable automatically.

Both tools disagree on numeric results -> Compare inferred types, null handling, decimal precision, row conditions, and the exact stage being tested. The tutorial's dbt model casts to decimal(12, 2), while pandas may hold the CSV total as a floating type.

Interview Questions and Answers

Q: What is the core architectural difference between dbt data tests and Great Expectations?

dbt data tests are SQL assertions attached to resources in a transformation DAG. GX validates a Batch from a configured data asset against an Expectation Suite through a Validation Definition. That makes dbt graph-aware and GX runtime-flexible.

Q: How does a dbt data test decide pass or fail?

The compiled SQL selects records that violate the assertion. Zero returned rows means pass; returned rows produce a failure or warning according to configuration. I inspect compiled SQL and cautiously store failures when row evidence is needed.

Q: What is a Batch Definition in GX?

It describes how records from a Data Asset are organized for retrieval as a Batch. For a pandas dataframe, the whole dataframe is supplied at runtime through Batch Parameters. For files or SQL sources, definitions can represent other batching strategies supported by that asset.

Q: Why not put every data quality rule in both tools?

Duplicate rules create two syntaxes, two result streams, and uncertain ownership. Small semantic differences in types, null behavior, or stage can also produce contradictory outcomes. I duplicate only when two independent boundaries justify defense in depth, and I document one canonical contract.

Q: How would you evaluate performance?

I would replay representative partitions and concurrency against the real warehouse or Spark engine, then record bytes scanned, runtime, queueing, and peak memory. A three-row pandas demo cannot predict production cost. I would also test whether sampling or thresholds weaken detection.

Q: Can Great Expectations replace dbt tests?

It can express many equivalent data assertions, but it does not replace dbt's lineage-aware build semantics. Replacing dbt tests with GX adds orchestration and metadata integration work. I would do it only when cross-runtime validation benefits exceed that cost.

The interviewQnA field below contains concise model answers for additional practice.

Common Mistakes

• Calling dbt data tests unit tests, which confuses post-build data assertions with model logic tests.
• Comparing a dbt warehouse test with a GX pandas test and presenting runtime numbers as a product benchmark.
• Duplicating every contract in both systems without a canonical owner.
• Running dbt test against stale models and assuming it rebuilt them.
• Storing failed records without reviewing personal data, access, retention, and deletion.
• Using an Ephemeral GX context when persistent suites and Validation Results are required.
• Selecting a detailed result format for huge failures without bounding evidence volume.
• Hiding infrastructure errors behind warnings or shell commands that always exit zero.
• Letting random batch names, model names, or environment labels fragment history.
• Choosing a tool before identifying where bad data should be blocked and who can fix it.

Add acceptance tests for the quality system itself. Seed a known null, duplicate, invalid status, and negative total; confirm each expected rule fails; remove them; confirm the gate recovers. A validator that has never demonstrated a controlled failure is only configured, not proven.

Where To Go Next

Extend the fixture with cross-table reconciliation using writing SQL to validate ETL. Add database-level guarantees with testing database constraints, then keep those distinct from monitoring and pipeline assertions.

For a team rollout, start with ten critical contracts rather than hundreds of generated checks. Record business meaning, asset, boundary, severity, owner, evidence policy, and remediation. Run controlled failures in CI and in a production-like environment before expanding coverage.

Conclusion

The dbt tests vs Great Expectations decision becomes straightforward once you define the data boundary. dbt is the focused choice for SQL assertions that belong to dbt resources and must participate in the transformation graph. Great Expectations is the broader validation framework for batches that cross dataframe, file, SQL, and pipeline contexts.

Build the same rules against representative data, inject known defects, and compare recovery rather than screenshots. If both tools remain, give each contract one authoritative owner and use the second tool only where a separate boundary creates real defensive value.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• LocalStack vs Testcontainers AWS Integration Tests (2026)
• Smoke vs sanity vs regression (2026)
• Stress vs soak vs spike testing (2026)
• Testcontainers vs Docker Compose Integration Tests (2026)
• AI code review for Playwright tests (2026)
• Allure vs ReportPortal Test Reporting (2026)