dbt model builds in dev but fails in prod with permission error
Fixes dev-to-prod permission gaps by diffing the grants of the dev and prod roles and applying the missing privileges in prod. Use when a model builds in dev but fails in prod on permissions. Not for prod failures with the same error as dev, which are code problems.
TL;DR
The prod role is missing a privilege the dev role has. Capture the exact denied object from the prod error, compare the two roles' grants, apply the missing grants in prod, and rerun the prod job.
Error
dbt model builds in dev but fails in prod with permission errorSteps
- Get the exact prod error message and note the denied schema, table, or operation. Expected: you know precisely what was denied.
- Compare the dev role's grants against the prod role's grants on that object. Expected: you find the privilege dev has and prod lacks.
- Apply the missing grant in prod with a privileged role. Expected: the grant succeeds.
- Rerun the prod job (or the failing model with
--target prod). Expected: the model builds. - Add the grant to your project's
grantsconfig or your infra-as-code so it persists. Expected: the next environment rebuild does not regress.
When to use
- A model is green in dev and red in prod with a permission error.
- You just promoted a model that touches a new schema or table.
When not to use
- Prod fails with the same error as dev (fix the code, not the grants).
- The prod failure is a missing relation rather than a denial.
Tool compatibility
- dbt Core 1.0 and later, all adapters. Grant mechanics are warehouse-specific.
Variant phrasings
Works in CI, fails in the scheduled prod job
Same pattern: the CI role and the job role have different grants.
Permission denied only on new tables
Future-table grants are missing; the role can read old tables but not new ones.
Why it happens
Dev and prod roles are provisioned separately and drift apart. A grant added for dev work never gets applied to prod, and the first prod run touching the new object fails.
Edge cases
- dbt's
grantsconfig only takes effect if the running role can grant; in prod that is often a different, more privileged role. - Snowflake future grants must be set per schema; new schemas need them re-applied.
- Some teams restrict prod DDL to a deploy role; the dbt Cloud job must assume that role.
Provenance
Resolved from the public thread: https://vectle.com/posts/pst_FInIrbClpl3CQgCmP-jxBQ