Course contents

The Analytics Engineer’s Field Guidedbt 3: ship it like a teamStep 14: Userland

Userland

Part 2 · dbt · step 14

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

1
Alice mergesThe team shares one dev schema. Her rename of customers lands there.
2
A model breakscustomers not found, a downstream ref still uses the old name.
3
Dependencies failTwo reports built on it error out.
4
Everyone is blockedThe whole team debugs one environment at once.
⏱️ Hours lost, from one merge.
· · ·

The idea, in three points

  • Everyone gets their own sandbox. Alice builds into dbt_alice, Thomas into dbt_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_ROLE reads production: RAW_DB (your sources) and PROD_DB (the built models). It writes only into USERLAND, so it can't damage production, even by accident.
  • Two files make it work. profiles.yml declares your sandbox, and one macro routes every write by target.
Read from prod, write to your own schema
RAW_DB, PROD_DB
production · read-only
USERLAND
your schema · dbt_alice
No copy of production's data. Your sources read live production tables, and every model you build lands in 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.MARTS TO ROLE USERLAND_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA PROD_DB.MARTS TO ROLE USERLAND_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA PROD_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:

# ~/.dbt/profiles.yml, on Alice's laptop
my_project:
  target: userland              # the one Alice runs by default
  outputs:
    userland:               # Alice's personal sandbox
      role: USERLAND_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_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}

  {%- if target.name in ('pro', 'pre_prod') -%}
      {{ (custom_schema_name or target.schema) | trim }}   {# deployed: the model's declared schema #}

  {%- else -%}
      {{ target.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 +schema falls back to target.schema: PROD_DB.ANALYTICS in pro.
  • | trim strips stray spaces from the name. node (the model) goes unused.
  • Step 5's macro still picks the database. This one picks the schema.
target: userland
target: pre_prod
target: pro
in ('pro', 'pre_prod')?
yes
real declared schema
no
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/stg_orders.sql, the last lines
FROM {{ source('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://your-dbt-artifacts/pro/manifest.json prod-artifacts/

# build fct_orders only; every other ref reads PROD_DB
dbt build --select fct_orders --defer --state 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:

userland
you build, by hand
→
merge request
CI builds into pre_prod
→
⏰
pro
Airflow, on schedule
a model never jumps a stage: no laptop writes to pro, no untested SQL reaches the schedule

Lesson quiz

  1. In a userland, where do your models read from and write to?

    1. Read and write in your own schema, on a copy of production
    2. Read a nightly clone of production, write to your schema
    3. Read live production tables, write to your USERLAND schema
    4. Read production, write to the shared pre_prod schemas
  2. What actually stops a userland run from damaging production?

    1. dbt refuses to run any model whose target database is PROD_DB
    2. USERLAND_ROLE can read production but has no write grant on it
    3. generate_schema_name blocks every write outside your own schema
    4. Production tables are locked while any userland run is active
  3. In userland, Alice's fct_orders shows three days of revenue, while production's shows a whole year. Her stg_orders is the lesson's version. Is something broken?

    1. No: stg_orders keeps 3 days on the userland target
    2. Yes: --defer read an old copy of production's table
    3. Yes: USERLAND_ROLE can't read RAW_DB rows past 3 days
    4. Yes: generate_schema_name dropped the older rows