dbt singular test passes locally but fails in dbt cloud

TL;DR

The test SQL assumes something true locally but false in dbt Cloud: the current date, the data in the target schema, or the dbt version. Read the test SQL for nondeterminism, compare the data both environments see, align versions, and rerun in dbt Cloud.

Error

dbt singular test passes locally but fails in dbt cloud

Steps

1. Open the singular test SQL in tests/ and look for nondeterministic expressions: current_date, now(), random functions, or unqualified table names. Expected: you list every environment-sensitive expression.
2. Compare what each side sees: query the relations the test reads in both your dev schema and the dbt Cloud target schema. Expected: you find the data difference (or rule it out).
3. Compare dbt versions: your local dbt --version against the dbt Cloud environment version. Expected: versions match, or you note the drift.
4. Make the test deterministic: pin dates, qualify schemas, remove randomness. Expected: the test SQL gives the same answer everywhere.
5. Rerun the test in dbt Cloud. Expected: it passes.

When to use

• A singular test passes with dbt test locally but fails in the dbt Cloud job.
• The test was recently added or recently started failing.

When not to use

• Generic tests fail in both places (a data or config problem, not an environment problem).
• The test fails at parse time (a SQL or YAML problem).

Tool compatibility

• dbt Core 1.0 and later, dbt Cloud. Singular tests live in tests/ as plain SQL.

Variant phrasings

Singular test fails in CI but passes locally

Same pattern with a CI runner instead of dbt Cloud; the environment diff is the hunt.

Test asserts on row counts that differ by environment

The test bakes in an assumption about data volume; make it relative or scoped.

Why it happens

Singular tests are raw SQL with no framework guardrails. Anything environment-specific in that SQL (dates, data, versions) makes the test pass in one place and fail in another.

Edge cases

• dbt Cloud runs with its own credentials and target schema; tests that assume your dev schema fail there.
• Timezone differences between your machine and dbt Cloud shift date boundaries.
• Tests that pass with --store-failures locally may behave differently when the failures schema differs.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_0mYl2HLMCj5gteRCbcpxyQ