Skip to main content

Mapping project folders to warehouse schemas

Folders organize your code. Schemas (or datasets, in BigQuery) organize the tables and views that code builds in your warehouse. You can map folders to schemas so staging, intermediate, and marts are easy to find. dbt prefixes each schema with your target schema, so development doesn’t overwrite production.

Map folders to custom schemas​

By default, dbt builds every model in your target schema, which comes from the environment in dbt platform or from the profile when you run locally (for example, development, CI, or production). To give the models in a folder their own schema, set +schema on that folder. dbt calls this a custom schema, which doesn't replace the target schema. Instead it joins the two as <target_schema>_<custom_schema>, so development and production build into different schemas. Refer to Custom schemas.

We recommend naming your custom schemas after your project folders, so staging, intermediate, and marts models are easy to find in your warehouse. For example, set +schema: staging, +schema: intermediate, and +schema: marts. If your team already uses different names, use those instead.

For example, using analytics_prod and alice_dev as target schemas, dbt builds each folder into these schemas:

FolderConfigProduction (analytics_prod)Development (alice_dev)
models/staging/+schema: staginganalytics_prod_stagingalice_dev_staging
models/intermediate/+schema: intermediateanalytics_prod_intermediatealice_dev_intermediate
models/marts/+schema: martsanalytics_prod_martsalice_dev_marts

Sources point at tables that already exist, typically in raw. dbt reads those tables; it does not build them. For more information, refer to Staging and Sources.

note

The Jaffle Shop example can load seed data into raw when load_source_data is enabled. Those seeds use +schema: raw and land in the raw schema.

Set custom schemas in dbt_project.yml​

When you set +schema on the folder in dbt_project.yml, every model in that folder inherits it. Configure a schema on an individual model only when that model should use a different schema.

dbt_project.yml
models:
  jaffle_shop:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: table
      +schema: marts

The jaffle_shop key must match the name in your dbt_project.yml. The Jaffle Shop example has staging and marts only; the intermediate block is unused until you add that folder.

This guide sets defaults on folders instead of on every model. Refer to The rest of the project.

Use only the custom schema in production​

To use marts instead of analytics_prod_marts in production, use the built-in generate_schema_name_for_env pattern. It uses the custom schema alone when target.name is prod. In every other environment, it ignores custom schemas and builds all models in your target schema (alice_dev). Don't change the default macro to drop the target schema everywhere, or development runs can write to the same schemas as production.

Use bronze, silver, and gold schema names​

If your warehouse uses bronze, silver, and gold schema names, those names map to the same folders previously discussed. You don't need a different dbt layout.

The following table maps those names to the folders in this guide:

Warehouse nameIn this guide
BronzeSources in raw (read-only)
SilverStaging and intermediate
GoldMarts

Keep staging, intermediate, and marts as the custom schema names in this guide so they match the folders. If your warehouse requires silver and gold as schema names, set +schema to those names instead.

If you set +schema: silver on both staging and intermediate, those models share one schema and you lose a separate intermediate schema. To keep that separation, use two custom schema names.

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading