Your userland: test on real data, safely
Your own sandbox in the warehouse. Real production data to read, your own schema to write to.
Alice can build, test and document models. One question is left: where does she try a change before it goes live?
The trap: everyone in the same place
customers lands there.customers not found, a downstream ref still uses the old name.The idea, in three points
- Everyone gets their own sandbox. Alice builds into
dbt_alice, Thomas intodbt_thomas. Each profile names its own schema. (The shared role could still write into a teammate's: for a hard wall, use one role per person.) - One role, two permissions.
USERLAND_ROLEreads production:RAW_DB(your sources) andPROD_DB(the built models). It writes only intoUSERLAND, so it can't damage production, even by accident. - Two files make it work.
profiles.ymldeclares your sandbox, and one macro routes every write by target.
USERLAND.dbt_alice.Setup 0 · read access to production
Steps 1 and 2 created USERLAND_ROLE, USERLAND and the RAW_DB grants. Userland adds read access to production's built models. An admin runs this once per schema (a trial has no PROD_DB: skip it):
GRANT USAGE ON DATABASE PROD_DB TO ROLE USERLAND_ROLE; GRANT USAGE ON SCHEMA PROD_DB. GRANT SELECT ON ALL TABLES IN SCHEMA PROD_MARTS TO ROLE USERLAND_ ROLE; DB. GRANT SELECT ON FUTURE TABLES IN SCHEMA PROD_MARTS TO ROLE USERLAND_ ROLE; DB. MARTS TO ROLE USERLAND_ ROLE; -- tables created later too
Same lines for PROD_DB.STAGING, with VIEWS instead of TABLES. Schemas holding personal data simply get no grant.
Setup 1 · declare the target in profiles.yml
It's the userland target from step 1, trimmed to the lines that matter here:
# ~/. my_project: target: userland # the one Alice runs by default outputs: userland: # Alice's personal sandbox role: USERLAND_dbt/ profiles. yml, on Alice's laptop ROLE database: USERLAND schema: dbt_alice # ...plus account, user, login and warehouse from step 1 # no pre_prod, no pro: those targets don't live on a laptop
The laptop file holds personal targets only (next lesson adds local, for DuckDB). CI keeps pre_prod in ci/profiles.yml (step 12), with secrets in CI variables, and Airflow defines pro (part 3). Both names matter: the macro below routes on target.name.
Setup 2 · one macro routes the writes
Step 4 gave each layer its own schema with +schema (staging, intermediate, marts). dbt's default rule glues it onto the target's schema: dbt_alice_staging for you, analytics_staging in production (step 5). This macro changes the rule: production gets plain STAGING (MARTS...), and every personal target goes back to plain dbt_alice. dbt calls it for every model, passing the folder's +schema as custom_schema_name:
-- macros/generate_ {% macro generate_schema_ name. sql schema_ {%- if target.name in ('pro', 'pre_prod') -%} {{ (name( custom_ schema_ name, node) -%} custom_ {# deployed: the model's declared schema #} {%- else -%} {{ target.schema_ name or target. schema) | trim }} schema }} {# userland or local (you): YOUR personal schema #} {%- endif -%} {%- endmacro %}
Read it as one sentence: if I'm running a deployed target, use the model's declared schema, otherwise use mine. Then:
- Any other target, new or misspelled, gets its own schema, never the shared ones.
- A model with no
+schemafalls back totarget.schema:PROD_DB.ANALYTICSinpro. | trimstrips stray spaces from the name.node(the model) goes unused.- Step 5's macro still picks the database. This one picks the schema.
in ('pro', 'pre_prod')?real declared schema
USERLAND.dbt_alice
Run against your userland
# build on real prod data, into your own schema (userland is the default anyway) dbt run --target userland # from your laptop this fails, and it should: there is no pro target here dbt run --target pro # The profile 'my_project' does not have a target named 'pro'.
Back to the trap. dbt checks every ref() when it reads the project, before it builds anything. So Alice's first userland run after the rename stops at once: a model still refs customers. She fixes it on her branch, and nobody else ever sees it.
To check logic, a slice of production is enough. Filter in staging, in your sandbox only:
-- models/staging/ FROM {{ source(stg_ orders. sql, the last lines 'raw', 'orders') }} {% if target.name == 'userland' %} WHERE created_at >= dateadd(day, -3, current_date()) {% endif %}
On dbt 1.10+, --sample="3 days" does this with no code, for models and sources that declare an event_time (their date column, under config:).
Build only what you changed: --defer
Your schema starts empty, so building only fct_orders fails: dbt_alice.stg_orders doesn't exist. With --defer, a ref to a model you didn't select or build reads production's table instead. dbt finds it in production's manifest.json (step 9), which the production job saves for you to download. A trial has no production run: skip --defer and build everything.
# fetch production's manifest, here from S3 with the AWS command-line tool mkdir -p prod-artifacts aws s3 cp s3:/ # build fct_orders only; every other ref reads PROD_DB dbt build --select fct_orders --defer --state prod-artifacts// your-dbt-artifacts/ pro/ manifest. json prod-artifacts/
Add prod-artifacts/ to .gitignore. Step 10's CI uses the same folder.
The promotion path: how a change travels
The three targets are stages, each with its own runner: you, CI, Airflow. They follow step 11's branches. While a merge request into branch pre_prod is open, CI builds the change with target pre_prod. Merging pre_prod into pro later hands it to Airflow, target pro:

