Skip to main content

Environment management

This guide shows how to set up development, staging, and production environments in MotherDuck. Data environments behave differently from application environments, so it starts with how they relate, then walks through the setup: databases split by responsibility, a service account and Duckling per workload, read-only access to production data, and dbt targets for promotion and rollback.

How data environments work​

In application software, each environment is a self-contained copy: development code runs against development data, staging code against staging data, and so on. Data stacks don't line up that way. Environments are staggered across the stack.

  • Ingestion runs real development, staging, and production environments. Each one tests the extract-and-load code against its own small or sampled dataset.
  • Transformation (dbt and similar tools) reads production data in place in every phase. Its development and CI runs read the same production tables that production does, and write to isolated outputs. There's no separate "transformation staging data".
  • Consumption (analysts, dashboards, and data apps) reads the production models the same way.

The reason is correctness. Your models and dashboards have to hold up against real production data and all of its edge cases. A clean, synthetic dataset hides the cases that break in production, so testing transformations against production data is the goal, not a compromise.

Sources
SaaS apps
APIs
Databases
Files
Ingestion
prod
staging
dev
Transformation
prod
stagingoptional
dev
Consumption
prod
stagingoptional
dev

Code promotes up each stage's dev, staging, and prod ladder. Data flows only from a stage's production output into the next stage, so transformation and consumption build on real production data. Hover a stage for detail.

note

Regulated or sensitive data is the exception. When developers can't read production data directly, create a sanitized or masked copy of the production source and grant access to that copy instead. The rest of this guide still applies, with the sanitized database in place of raw production data.

Isolate by workload, not only by stage​

MotherDuck gives each user and service account its own Duckling for compute. That's finer-grained than one shared warehouse per stage: a developer iterating on models, a CI job validating a pull request, and a production dashboard each run on separate compute, so none of them slows the others down. This is what makes reading production data in every phase practical, because a heavy development query can't degrade production serving.

For sizing, read scaling, and routing, see workload scaling patterns.

Separate databases by responsibility​

MotherDuck grants access at the database level, so split your data by responsibility into separate databases:

DatabaseHoldsAccess
rawSource data landed by ingestionRead-only to transformation
transformModels built by transformationWrite for the transformation workload
martsCurated tables for consumersRead-only to analysts, dashboards, and apps

Give each workload its own service account and token​

Create a service account for each workload not tied to a person, and give each one its own token. Every service account gets its own Duckling, secrets, and optional read scaling pool, so compute and credentials stay isolated.

SecretToken ownerUsed by
MOTHERDUCK_TOKEN_DEVDeveloper or transform_dev service accountLocal development
MOTHERDUCK_TOKEN_CItransform_ci service accountPull request and merge jobs
MOTHERDUCK_TOKEN_PRODtransform_prod service accountScheduled production builds and serving

Store each token in your secret manager or CI/CD environment. Keep the production token out of local .env files and lower-environment CI jobs.

Grant read-only access to production data​

Transformation reads production raw data in place. Publish the production raw database as a read-only share and grant it to the accounts that build models.

Run this as the account that owns raw:

CREATE OR REPLACE SHARE raw_prod FROM raw (
    ACCESS RESTRICTED,
    UPDATE AUTOMATIC
);

GRANT READ ON SHARE raw_prod TO transform_dev, transform_ci, transform_prod;

UPDATE AUTOMATIC keeps the share in sync, so every phase reads the latest production data.

Each transformation account attaches the share once. Because MotherDuck saves attachments to your workspace, later connections reuse it:

ATTACH 'md:_share/raw/<share_token>' AS raw;

Publish marts the same way and grant it to the accounts that power your dashboards and apps.

Configure dbt targets​

Map dbt targets to your transformation workloads. Each target writes models to its own database and reads sources from the attached raw share.

profiles.yml:

transform:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: "md:transform_dev"
      schema: "{{ env_var('DBT_SCHEMA', 'dev') }}"

    ci:
      type: duckdb
      path: "md:transform_ci"
      schema: "{{ env_var('DBT_SCHEMA', 'ci') }}"

    prod:
      type: duckdb
      path: "md:transform"
      schema: prod

These paths use MotherDuck's default workspace attach mode, not single mode, so each dbt run sees both its write database and the read-only raw share. Point your dbt sources at raw so every target reads the same production data:

sources:
  - name: raw
    database: raw
    schema: main
    tables:
      - name: orders
      - name: customers

Then set your MotherDuck token to the correct environment, for example MOTHERDUCK_TOKEN="$MOTHERDUCK_TOKEN_CI" and build against the intended target:

dbt build --target dev
dbt build --target ci
dbt build --target prod

The dev and ci targets also set their schema from the DBT_SCHEMA environment variable, so it's easy to recognize what profile the data was written from. Each CI run can have its own schema:

export DBT_SCHEMA=PR123
dbt build --target ci

So the models for PR 123 build into transform_ci.ci_pr123. In CI/CD, give each job only the token for the environment it runs. A pull request job receives MOTHERDUCK_TOKEN_CI, and a release job receives MOTHERDUCK_TOKEN_PROD behind your deployment system's approval controls.

Promote and roll back​

Promote transformation logic by running the same dbt project against the next target, not by copying data between environments. The code moves forward while production data stays in place.

Before a release, take a named snapshot of the production database so you can roll back:

CREATE SNAPSHOT transform_before_release_2026_06_01 OF transform;

To restore the production database to that snapshot:

ALTER DATABASE transform
SET SNAPSHOT TO (SNAPSHOT_NAME 'transform_before_release_2026_06_01');

Named snapshots are durable recovery points available on the Business plan. When a lower environment must not read production data, share a sanitized copy instead: build a masked version of raw in a separate database, share that database, and grant it to the development accounts.

Related content​