Multi-team dev schemas with dbt Core

Without per-developer schemas, every dbt run overwrites production tables. Here's the pattern most mature analytics teams use, adapted to OrcheStack's single-PostgreSQL setup.

The problem

Picture this without dev schemas: three engineers all run dbt run pointing at the production marts schema. Alice iterates on a new fct_orders model; Bob starts a migration on the same model; Ayoade builds a dashboard that queries it. At any given moment, nobody knows whether marts.fct_orders contains Alice's version, Bob's migration, or the last version that actually worked. Metabase dashboards break. Analysts see inconsistent numbers. The team blames each other instead of the toolchain.

The root cause: everyone writing to the same schema. Fix: give each developer their own schema.

The dev-schema pattern

Each engineer gets a personal schema in the warehouse — dev_ayoade, dev_alice, dev_bob. They run dbt locally with --target dev, which writes to that personal schema. Production runs happen via OrcheStack's nightly DAG using --target prod, which writes to the shared marts schema.

Flow:

LOCAL  : Alice → dbt run --target dev → writes to dev_alice.fct_orders
LOCAL  : Bob   → dbt run --target dev → writes to dev_bob.fct_orders
PROD   : DAG   → dbt run --target prod → writes to marts.fct_orders  (merged code)

Metabase, OpenMetadata, and stakeholders only look at marts. Nothing they see moves unless a PR merges to main and the nightly DAG runs.

Admin creates the dev schemas

From pgAdmin (or psql on the host), the platform admin runs this once per engineer:

-- Create a personal dev schema for each engineer
CREATE SCHEMA dev_ayoade AUTHORIZATION dbt_user;
CREATE SCHEMA dev_alice  AUTHORIZATION dbt_user;
CREATE SCHEMA dev_bob    AUTHORIZATION dbt_user;

-- Grant the dbt_user full control over each schema
GRANT ALL ON SCHEMA dev_ayoade TO dbt_user;
GRANT ALL ON SCHEMA dev_alice  TO dbt_user;
GRANT ALL ON SCHEMA dev_bob    TO dbt_user;

-- Optionally grant read access to analysts for inspection
GRANT USAGE ON SCHEMA dev_ayoade TO analyst_user;
GRANT SELECT ON ALL TABLES IN SCHEMA dev_ayoade TO analyst_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA dev_ayoade
  GRANT SELECT ON TABLES TO analyst_user;

Each developer's profiles.yml

Each engineer puts a profiles.yml on their laptop at ~/.dbt/profiles.yml (or in the project root — dbt finds both). The file is per-machine; never commit it.

OrcheStack:
  target: dev                    # default when you just type 'dbt run'
  outputs:
    dev:
      type: postgres
      host: OrcheStack.acme.ng     # or the IP / localhost for self-host
      user: dbt_user
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5432
      dbname: OrcheStack
      schema: dev_ayoade         # ← your personal schema
      threads: 4
    prod:
      type: postgres
      host: OrcheStack.acme.ng
      user: dbt_user
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5432
      dbname: OrcheStack
      schema: marts              # ← shared production schema
      threads: 8

Each engineer replaces dev_ayoade with their own schema name.

Set DBT_PASSWORD in your shell's .env or ~/.bashrc — never paste it into profiles.yml directly.

Local development workflow

Once the profile is configured, the daily loop is:

# Iterate on your model — writes to dev_ayoade.fct_orders
dbt run --select fct_orders

# Test it locally against your dev data
dbt test --select fct_orders

# When you're happy, push to a branch
git checkout -b feature/new-orders-mart
git commit -am "Refactor fct_orders to include returns"
git push origin feature/new-orders-mart

Notice: you never typed --target prod. Default is dev. Production is unreachable by accident.

Promoting to production

Open a pull request on GitHub. The team reviews the model — was the transformation right? Did tests pass?

Once merged to main, the next OrcheStack nightly DAG run does:

git pull origin main
dbt build --target prod

Which rebuilds marts.fct_orders with the new code. Metabase dashboards refresh on their own cadence and show the new data.

If the DAG fails (tests didn't pass against real data, for example), marts.fct_orders stays unchanged — Metabase keeps showing the last good version until someone fixes and re-merges. Failing safely is the whole point.

Naming conventions

Gotchas

• profiles.yml in the repo. Do not commit it. Commit profiles.yml.example as a template and list profiles.yml in .gitignore.
• Forgotten dev schemas. Old engineers leave; their dev schemas accumulate. Periodically run DROP SCHEMA dev_<leaver> CASCADE;.
• Running --target prod locally by mistake. Painful when it happens. Defend against it by giving the dbt_user only SELECT on marts for developer-facing PostgreSQL roles; the nightly DAG uses a different user with write access.
• Schema per branch (Snowflake-only pattern) doesn't translate to PostgreSQL. Stick with per-engineer schemas.