All jobs

Add Model Grants to a dbt Project

Yes. An agent adds a grants config to your dbt marts so each build re-applies select for the reporting role, checks it in a local warehouse, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent reads the project before it touches dbt_project.yml. In a project like dbt-labs/jaffle-shop, that file names the project jaffle_shop at version 3.0.0, requires dbt 2.0.0 or later, points at dbt Cloud project 275557, materializes everything under models/staging as views and everything under models/marts as tables, and routes seeds to a raw schema when the load_source_data var is set. macros/generate_schema_name.sql puts seeds in raw, custom schemas under the target schema with a suffix on the prod target, and everything else in the target schema. There are seven staging models over the ecom source in models/staging/__sources.yml and seven marts, customers, orders, order_items, products, locations, supplies and metricflow_time_spine, each with a YAML file that carries its description, tests such as dbt_utils.expression_is_true, and an embedded semantic model with entities, dimensions and metrics. packages.yml pins dbt_utils 1.4.1 and dbt-audit-helper from Git, package-lock.yml fixes the commit, and .sqlfluff sets the Snowflake dialect. Nowhere in the project does the word grants appear: whoever reads the marts from a BI tool got their access by hand, and a rebuilt table on Snowflake loses it.

The agent adds the grants in the one place that covers every mart and nothing else: under models: jaffle_shop: marts: in dbt_project.yml, beside the existing +materialized: table, it sets +grants: with select: listing a grantee read from an environment variable, since the same project runs against Snowflake, BigQuery and Postgres in the three dbt Cloud jobs that .github/workflows/ci.yml and cd_prod.yml call, and a Snowflake role, a BigQuery group: principal and a Postgres role are spelled differently. The variable has a default so a fresh checkout parses. Staging views get no grants, because they are the project's internal layer and the YAML says so. For metricflow_time_spine, which is not a reporting table, it uses a per-model config block in models/marts/metricflow_time_spine.yml with an empty select list, and the pull request explains that a model-level grants replaces the project-level list unless the key is written with a leading +, which merges instead.

Then it proves the configuration instead of describing it. dbt parse with a throwaway profile writes target/manifest.json, and a short Python check reads every model node and prints its config.grants, so the record shows seven marts with a select grantee, one mart with an empty list and no staging model with any. For a real warehouse it installs PostgreSQL in the sandbox, creates a reporter role, generates the seed CSVs with jafgen the way Taskfile.yml does, runs dbt seed with load_source_data on and dbt build, then queries information_schema.role_table_grants for the reporter role. The agent also runs dbt build a second time and queries again, since the point of the config is that a rebuilt table keeps its access.

The agent keeps the rest of the project as it found it: the semantic models, the .sqlfluff rules, the pre-commit hooks in .pre-commit-config.yaml and the dbt Cloud job ids in the workflows are untouched, and the only edits outside dbt_project.yml are the one YAML override and a short section in README.md naming the environment variable to set per dbt Cloud environment.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add a grants configuration to this dbt project so the reporting role gets select on every mart after each build. Read dbt_project.yml, macros/generate_schema_name.sql, packages.yml, every YAML under models/marts and models/staging, the dbt Cloud workflows under .github/workflows, Taskfile.yml and README.md first. Put the grants under the marts block in dbt_project.yml with the grantee read from an environment variable that has a safe default, give the time spine model an empty select list with a per-model config and explain in the pull request how model-level grants replace or merge with the project-level list. Leave staging views without grants. Prove it: run dbt parse and print config.grants for every model node from target/manifest.json; then install PostgreSQL in the sandbox, create the reporting role, generate seeds with jafgen, run dbt seed with load_source_data on and dbt build, and query information_schema.role_table_grants before and after a second dbt build. Add a README section naming the variable per dbt Cloud environment. Open a pull request with the manifest check and the grant queries in its description.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request that changes dbt_project.yml, models/marts/metricflow_time_spine.yml and README.md. The description lists the seven marts that gain select for the reporting role, the one that is excluded and why, the environment variable to set in each dbt Cloud environment, and the merge rule for model-level overrides.

The run's record keeps the dbt parse output, the manifest check with one line per model and its grants, the PostgreSQL role creation, the dbt seed and both dbt build logs with the grant statements dbt issued after each mart, and the two role_table_grants queries, so a reviewer sees that a rebuilt table keeps its access without reading any SQL by hand.

Before merging, a reviewer sets the variable in the staging dbt Cloud environment, runs the staging job, and checks in the warehouse that the reporting role can select from the marts schema and nothing in the staging schema. A missing grant there means the variable is unset in that environment, which the README section now covers.

Run it on every Linear issue labelled data-access

In the console, an automation with a linear trigger on linear.issue.labeled and the label data-access starts one run per labelled issue in the Project's repository. The trigger adds the issue's text after the saved prompt, so an issue asking for a new BI role or for grants on one more mart carries that detail itself and the prompt stays the same. Changes made by an app integration never fire it, and the automation's max_runs_per_hour bounds a burst of labelling.

From your own server, the same job is one POST /o/{org}/sessions per issue, with the repository in resources and the issue's https://linear.app/<workspace>/issue/<ID> URL beside it so Rush reads the issue through your Linear connection. A webhook_url on the API key receives run.completed or run.failed for every run that key starts, with the session_url to read the pull request link from.

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

Why run it on Rush?

A grants config is four lines of YAML, and the ways it goes wrong are invisible in the diff: a grantee spelled for the wrong warehouse, a model-level grants that silently replaces the project list, a staging view that was not meant to be shared. The agent runs a real dbt build against a warehouse it installs in its own sandbox and reads the grants back from the catalog, which a laptop review of the YAML cannot do.

The second dbt build and the second query are what make the change worth merging, because the whole reason for the config is that dbt re-applies access every time it rebuilds a table. That evidence sits in the run's record with the commands that produced it, not in a sentence in the pull request.

Frequently asked questions

Does this work on Snowflake, BigQuery and Postgres at the same time?

The config does, the grantee does not. dbt issues the right grant statement per adapter, but a Snowflake role, a BigQuery principal such as group:analysts@example.com and a Postgres role are different strings, which is why the agent reads the grantee from an environment variable set per dbt Cloud environment rather than hard-coding one.

Will dbt revoke access I granted by hand?

For a model that has a grants config, dbt treats the list as the full set and revokes a grantee that is not in it, which is the behaviour that keeps access tidy. The pull request says so, and a role that must keep access belongs in the list, not in a manual grant that the next build removes.

Why not grant on the staging views too?

The staging layer is the project's internal cleaning step, materialized as views so it is cheap to rebuild. Granting on it would expose column renames and source quirks to BI users and double the surface the team has to keep stable. A reader who wants it adds a second +grants block under staging.

Can the agent test this without PostgreSQL?

The manifest check needs no warehouse: dbt parse resolves every config and the agent prints config.grants per node. The database run is what shows the grants surviving a rebuild, and PostgreSQL installs in the sandbox without Docker, so both checks happen in the same run.

More dbt jobs

More jobs like this: Turn a Linear label into a PR

Keep reading