The 3-Environment Design for Your Database (DEV vs CI vs PROD)

A three-environment design gives your database a dedicated place for each stage of a change: development, CI, and production. Each developer gets their own schema, prefixed dev_ plus their name, where they build and test without overwriting anyone else. A CI schema, where CI stands for continuous integration, is a shared pre-production space that an automated workflow rebuilds every time a pull request is opened. Production is the set of schemas your reports and BI tools actually read from. All three can live in one database, separated by schema, and a tool like dbt can route each run to the right place from a single configuration. Most teams already have this idea in their code repository and are missing it in the database itself.

The high level picture

Picture a typical data flow. Sources land in a centralized raw zone, move through a few layers, maybe a three layer model or a medallion structure, and end up in reporting.

All of that is production. For most teams, it is also all they have. There might be a few random schemas where people experiment, but nothing formal and no consistent process for the work that happens before production.

The design I recommend adds two things underneath that flow:

• Development schemas, one per developer, following a shared naming convention such as dev_ plus first initial and last name
• A CI schema, where CI stands for continuous integration. Some teams call this UAT, test, or release. Either way it is a third environment that sits between individual developers and production.

In this example everything sits in one database, separated by schema. You can split it across databases later if you need to. I like to start simple and break it out as the team grows.

What it looks like in the database

Here is how this reads in an analytics database once it is in place:

• Production is three schemas: staging, warehouse and marts
• Development is one schema per person, such as dev_mkahan
• CI is a single ci schema

With admin permissions you see all of it. In practice most of this would be restricted by user and role, so a developer sees their own schema and the production objects they are allowed to read.

The prefix does real work here. Because every dev schema starts with dev_, they group together instead of scattering across the database by people's names. You can add as many as you need and the layout stays readable.

Development: one schema per developer

Tie the code to the database

When I ask teams about their development environment, they go straight to the code. They have a main branch and a dev branch, or a central codebase and a working copy.

When I ask about development environments in the database, they often don't have one. That gap is common, and closing it is the whole point. Pair your development code with a matching place in the database so both the code and the data stay isolated while you work.

Why a shared dev space breaks down

I have run into this on my own teams and seen it on client teams. Everyone deploys to one shared development area, and two things happen:

• You overwrite someone else's changes, or they overwrite yours
• You wait your turn to refresh a table because someone else is mid-test

With a dedicated schema per developer, each person deploys to their own isolated spot. Everyone can select from the same raw data, but nobody interferes with anyone else.

Three layers, one schema

Production has a schema per layer, but dev has only one. How does that work?

When you deploy to production, you break the layers out into their own schemas. When you deploy to dev or CI, you consolidate everything into the one schema for that environment, stacked together.

Two reasons I recommend it:

• It avoids fan out. A schema per layer per developer gets messy fast.
• Nobody else needs to see it. Development is for the developer. The structure matters far less there than it does in production.

dbt handles this cleanly. It understands the context of the run: if the target is production it breaks the layers out, and if the target is development or CI it consolidates them. The configuration is further down.

The result is that you are never testing straight on production tables, and you are not winging it either. You have a strategy and a naming convention for how things get built before they ship.

CI: the automated double check

Why a second pre-production environment

If development already isolates and tests changes, why add another environment before production?

Because people make mistakes. Sometimes we think we tested something, or say we did, when we missed a piece. A second pass, ideally an automated one, catches what the first pass skipped.

That is why I call it CI. Continuous integration implies automation, and this is where the workflow features built into version control platforms earn their keep.

The workflow that runs it

In a GitHub repository, workflows live in the .github/workflows directory. The one I use for this is ci.yml, and it is about 35 lines. A trimmed version looks like this:

name: CI

on:
  pull_request:
    branches: [main]

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
      - run: pip install dbt-snowflake
      - run: dbt deps
      - run: dbt build --target ci
        env:
          DBT_PROFILES_DIR: .
          DBT_PASSWORD: ${{ secrets.DBT_PASSWORD }}

It listens for pull requests into main. When one opens, it builds and deploys every dbt model to the ci target, which maps to the ci schema in the database.

Those 35 lines protect a lot. You built and tested in your own schema, you open a pull request, and the project rebuilds itself from scratch in CI before anyone merges. It is a simple automation that most teams are missing, and it isn't hard to add once you see the steps.

Putting it together with dbt

Three pieces make dbt route each run to the right place. The nice part is that you set this once and then stop thinking about it.

1. Custom schemas in dbt_project.yml

First, tell dbt which production schema each folder of models belongs to:

models:
  my_project:
    staging:
      +schema: staging
    warehouse:
      +schema: warehouse
    marts:
      +schema: marts

On its own this would apply in every environment. We only want it in production.

2. The generate_schema_name macro

dbt decides the schema for every model through a macro called generate_schema_name. Override it so the custom schema is used only when the target is production:

{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if target.name == 'prod' and custom_schema_name is not none -%}
        {{ custom_schema_name | trim }}
    {%- else -%}
        {{ default_schema }}
    {%- endif -%}
{%- endmacro %}

If the target is prod, the layers break out into staging, warehouse and marts. If it is anything else, every model lands in the default schema from the credentials.

3. Targets in profiles.yml

The last piece is the credentials file. Each target sets its own default schema, which is where the macro sends everything outside of production:

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      database: analytics
      schema: dev_mkahan
      # account, user, password, role, warehouse ...
    ci:
      type: snowflake
      database: analytics
      schema: ci
      # same connection fields, CI service credentials
    prod:
      type: snowflake
      database: analytics
      schema: staging
      # same connection fields, production credentials

The ci target sets its default schema to ci, so the workflow above only has to pass --target ci. A developer's target sets it to their own dev_ schema, which sends every object there.

Follow this approach, or something close to it, and the database goes from scattered and random to a well oiled machine that works right alongside your Git project.

Related reading

• dbt Environments vs Targets: What's the Difference?
• How Data Teams Maintain Production (with high quality)
• Data Automation (CI/CD) with a Real Life Example
• The 4-Role Security Model for Your Data Warehouse

Final takeaway

This is the environment layout I set up with most teams when they first adopt dbt or stand up a new warehouse, and the gap is always the same: the code has stages, the database doesn't. Add the three kinds of schemas and the one configuration that routes to them, and the database starts mirroring your Git workflow.