# Environment management > Set up development, staging, and production data environments in MotherDuck with workload isolation, read-only access to production data, and dbt. 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. :::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](/concepts/hypertenancy/) 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](/concepts/scaling-patterns/). ## Separate databases by responsibility MotherDuck grants access at the database level, so split your data by responsibility into separate databases: | Database | Holds | Access | |---|---|---| | `raw` | Source data landed by ingestion | Read-only to transformation | | `transform` | Models built by transformation | Write for the transformation workload | | `marts` | Curated tables for consumers | Read-only to analysts, dashboards, and apps | ## Give each workload its own service account and token Create a [service account](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/) 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. | Secret | Token owner | Used by | |---|---|---| | `MOTHERDUCK_TOKEN_DEV` | Developer or `transform_dev` service account | Local development | | `MOTHERDUCK_TOKEN_CI` | `transform_ci` service account | Pull request and merge jobs | | `MOTHERDUCK_TOKEN_PROD` | `transform_prod` service account | Scheduled 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](/sql-reference/motherduck-sql-reference/create-share/) and grant it to the accounts that build models. Run this as the account that owns `raw`: ```sql 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: ```sql ATTACH 'md:_share/raw/' 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`: ```yaml 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](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/), 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: ```yaml 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: ```bash 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: ```bash 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](/concepts/snapshots/) of the production database so you can roll back: ```sql CREATE SNAPSHOT transform_before_release_2026_06_01 OF transform; ``` To restore the production database to that snapshot: ```sql 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 - [Workload scaling patterns](/concepts/scaling-patterns/) for choosing Duckling sizes, read scaling, and workload isolation - [Resource management](/concepts/resource-management/) for account, token, secret, Duckling, and database isolation boundaries - [Create and configure service accounts](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/) for service account setup and token creation - [CREATE SHARE](/sql-reference/motherduck-sql-reference/create-share/) and [GRANT READ ON SHARE](/sql-reference/motherduck-sql-reference/grant-access/) for read-only cross-account access - [Database snapshots](/concepts/snapshots/) and [Data recovery](/concepts/data-recovery/) for snapshot retention and rollback --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `POST https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. Payload: ```json { "page_path": "/key-tasks/data-warehousing/environment-management/", "page_title": "Environment management", "text": "", "source": "" } ``` `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed payloads, and `429` when rate-limited.