dbt tests vs Soda vs Great Expectations

The short answer

Already running dbt and your data is modelled? Use dbt tests. They cost nothing extra to operate, they run in the same command as your build, and a failing test blocks the model that depends on it. The four built-ins cover more ground than people expect.

Need checks on data dbt doesn't model — raw sources, files, a landing zone? Use Soda. It scans anything it can connect to, without requiring a transformation project to exist first, and its YAML is readable by people who don't write SQL.

Doing validation inside a Python pipeline, or need distributional checks? Use Great Expectations. Nothing else has its vocabulary — KL divergence, quantile ranges, column pair relationships — and it produces a structured result object you can branch on.

These are not mutually exclusive, and treating them as rivals is the most common mistake. A very ordinary setup is Soda or GX at the ingest boundary, where bad data arrives, and dbt tests on the models downstream, where bad logic is introduced. They catch different failures.

Side by side

The same check in all three

Nothing clarifies the difference faster than writing one check three times. Here's "the status column is never null and only ever holds four values."

dbt

# models/schema.yml
version: 2
models:
  - name: orders
    columns:
      - name: status
        tests:
          - not_null
          - accepted_values:
              values: ['paid', 'pending', 'refunded', 'cancelled']

Runs as dbt test --select orders. dbt compiles each test to a SQL query that must return zero rows, executes it in the warehouse, and fails the build if it doesn't. The test is versioned next to the model it protects, which is the real reason teams like it.

Soda

# checks/orders.yml
checks for orders:
  - missing_count(status) = 0
  - invalid_count(status) = 0:
      valid values: ['paid', 'pending', 'refunded', 'cancelled']

Runs as soda scan -d warehouse -c configuration.yml checks/orders.yml. Soda translates the metrics into SQL and pushes them down. No model, no transformation project, no dependency graph — it just needs a connection and a table name.

Great Expectations

# expectation suite (JSON form)
{
  "expectation_suite_name": "orders.suite",
  "expectations": [
    {
      "expectation_type": "expect_column_values_to_not_be_null",
      "kwargs": { "column": "status" }
    },
    {
      "expectation_type": "expect_column_values_to_be_in_set",
      "kwargs": {
        "column": "status",
        "value_set": ["paid", "pending", "refunded", "cancelled"]
      }
    }
  ]
}

Runs through a checkpoint. More ceremony for the same two assertions — but the suite is a data structure, so you can generate it, diff it, and reason about it programmatically in a way the other two don't really support.

The part most comparisons skip: nested data

All three are column-oriented. They assume a table with columns, and every example in every tutorial is a flat table. That's fine when your data arrives as rows — and a problem the moment it arrives as JSON.

If you're ingesting webhook payloads, event streams, or an API response, your data looks more like this:

{
  "order": {
    "id": "ord_8f2c1a4b9e07",
    "customer": { "email": "ada@example.com" },
    "line_items": [
      { "sku": "TSHIRT-BLK-M", "quantity": 2, "price_cents": 1899 },
      { "sku": "MUG-01", "quantity": 1, "price_cents": "2500" }
    ]
  }
}

None of the three can assert anything about line_items[].price_cents until that array has been exploded into rows and the struct fields promoted to columns. So the real sequence is: land the raw payload, flatten it, then write checks. The flattening step is where the interesting decisions live, and no data-quality tool makes it for you.

Two things about that payload are worth noticing, because they're the kind of thing a flat schema hides. One price_cents is the string "2500" rather than a number — a type inconsistency that will silently coerce or silently fail depending on your warehouse. And the meaning of "how often is this field present?" changes depending on whether you're counting orders or line items. A field present in 100% of line items might be present in only 60% of orders.

The one tool in this space that does work on the payload as it arrives is JSON Schema. It validates structure before the data is loaded, which makes it a genuinely different control: it rejects bad payloads at the door rather than reporting bad rows after the fact. It's also the least discussed of the four, which is a shame.

What none of them will tell you

All three are assertion languages. They check what you tell them to check, which means they're only as good as your knowledge of the data — and the hard part of data quality is usually not writing the check, it's knowing which check to write. Staring at an empty schema.yml is the actual bottleneck.

The other shared blind spot: a check that passes on your sample is not a check that will pass in production. An observed range of 0–119 in a 2,000-row sample is an observation, not a constraint. Tools that generate checks from data — including this one — have to be honest about that distinction, or they hand you a suite that pages someone at 3am in week two.

Generate all three from a sample

If you're at the "empty schema.yml" stage, you can skip the blank page. Treivu profiles a sample file in your browser, shows you what's actually in it — real presence rates, observed types, where they're inconsistent — and proposes the checks the evidence supports. You accept the ones you want and export to whichever of these you're using.

It also generates the flattening SQL, which is the step the other tools assume you've already done. Nothing is uploaded; the profiling runs in the browser.

Common questions

Can I use more than one?

Yes, and many teams should. The usual split is a scanner at the ingest boundary and dbt tests on the models. The cost is two vocabularies to maintain, so keep the boundary checks few and structural, and let dbt carry the business logic.

Is Great Expectations overkill?

For "is this column ever null", yes. Its value shows up when you need statistical assertions or a validation result you can act on programmatically. If your checks are all expressible in SodaCL, GX's setup cost buys you very little.

Do dbt's four built-in tests really cover most cases?

More than you'd expect — not_null, unique, accepted_values, and relationships catch the majority of real incidents, because most data incidents are missing rows, duplicated rows, or a broken join key. Reach for dbt_utils when you need ranges or expressions, and write a custom generic test when the same non-trivial assertion shows up a third time.

What about migrating off GX Cloud?

An existing expectation_suite.json can be imported here and diffed against a fresh profile of the same data, which tells you which expectations still match reality before you port them anywhere.