dbt: custom_temp_schema – Walid Djebali

📘 What is `custom_temp_schema` in dbt?

When dbt runs models that require intermediate or temporary tables, it needs a place (schema) to store them.
By default, these go into the same schema as the final models — which can clutter production schemas during development or testing.

✅ `custom_temp_schema` lets you control where dbt creates temporary tables during execution:

Keeps temp tables out of your production schema.
Helps you isolate developer activity.
Can be set globally, per environment, or per model.

📦 Example: Setting `custom_temp_schema` in `dbt_project.yml`

# dbt_project.yml

name: my_project
version: 1.0.0
profile: my_profile

# Default behavior for all models
models:
  my_project:
    +schema: analytics        # Final output schema for models
    +custom_temp_schema: dbt_temp  # Temp tables go here during execution

🧱 Result: Schema resolution

Object Type	Target Schema
Final models	`analytics`
Temp/intermediate	`dbt_temp`

This applies to models that use intermediate relations (e.g., ephemeral models or materializations that build temp objects first).

🧪 Example: Used during ephemeral joins or CTE generation

Imagine this dbt model:

-- models/customer_orders.sql

select
  customer_id,
  count(*) as order_count
from {{ ref('stg_orders') }}
group by customer_id

If stg_orders is an ephemeral model, dbt may create a temporary CTE or even a temp table (depending on the adapter) to resolve dependencies.

With custom_temp_schema: dbt_temp, that object will not clutter analytics — it lands in dbt_temp.

🎯 Overriding at the model level

You can override custom_temp_schema for specific models too:

-- models/experiments/my_experimental_model.sql

{{ config(
  materialized='table',
  custom_temp_schema='experimental_temp'
) }}

select ...

This model’s temporary/intermediate tables will go to experimental_temp, even if the rest of the project uses dbt_temp.

🧼 Summary

Feature	Benefit
`custom_temp_schema`	Defines where dbt stores temp/intermediate tables
🧪 Used in dev/testing	Avoids polluting production schemas
🔧 Configurable scope	Can be set at project, folder, or model level
🧹 Keeps things clean	Helps maintain separation of dev vs prod assets

✅ In dbt, “temporary tables” can mean three things:

Concept	Description
CTEs (Common Table Expressions)	Inline temporary result sets (e.g. `WITH ...`) — no table is created in the database.
Temporary physical tables	Real tables created in the database but automatically dropped after the session ends. Only some databases support this.
Intermediate tables in a custom schema	Used by dbt internally during materializations (e.g., staging, rebuilding models). These are not marked as temporary, but they are intermediate.

🧱 Let’s break them down:

🔹 1. CTEs (Common Table Expressions)

WITH orders_per_customer AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM orders_per_customer

✅ Defined inline
🧹 Automatically discarded after the query ends
❌ Not visible in the database catalog
✅ Used heavily when you use materialized='ephemeral' in dbt

➡️ This is NOT affected by custom_temp_schema, because no table is created.

🔹 2. Temporary Physical Tables

Some databases (e.g. PostgreSQL, Snowflake, Databricks) support real temporary tables like:

CREATE TEMPORARY TABLE my_temp_table AS
SELECT ...

✅ Physically exists but auto-deleted when the session ends
✅ Often used by dbt internally during custom materializations
✅ Could be created in the schema defined by custom_temp_schema
❗ Some databases don’t support temp tables the same way (e.g., BigQuery treats them differently)

➡️ This can be affected by custom_temp_schema, depending on how the adapter and materialization work.

🔹 3. Intermediate Tables Created by dbt in Custom Temp Schemas

Even if a table is not a true TEMP TABLE, dbt might simulate temporary behavior by:

Creating an intermediate table (e.g. _tmp123_my_model)
Placing it in a custom schema (like dbt_temp)
Then swapping it into place after the build completes

# dbt_project.yml
models:
  my_project:
    +custom_temp_schema: dbt_temp

These are regular tables, but considered temporary in purpose (for build process only), not in SQL semantics.

🧪 Example: What Happens When You Use `custom_temp_schema`

Assume:

Final model is analytics.final_sales
You set +custom_temp_schema: dbt_temp

During execution:

dbt creates an intermediate table like dbt_temp.final_sales__dbt_tmp
Once the build is successful, it renames/moves this table to analytics.final_sales
The temp table is then dropped automatically by dbt

✅ This is dbt’s way of isolating temp/intermediate artifacts during builds.

✅ Summary Table

Type of Temp Structure	Created In Database?	Affected by `custom_temp_schema`?	Auto-Cleanup?	Use Case
CTE (WITH clause)	❌ No	❌ No	✅ Yes	Ephemeral logic
Temporary Table (`TEMP`)	✅ Yes (if supported)	✅ Yes (depends on adapter)	✅ Yes (session ends)	Internal queries, staging
Intermediate dbt Table	✅ Yes	✅ Yes	✅ Yes (dbt deletes or swaps)	Rebuilds, staging, concurrency handling

📘 What is custom_temp_schema in dbt?

✅ custom_temp_schema lets you control where dbt creates temporary tables during execution:

📦 Example: Setting custom_temp_schema in dbt_project.yml