In a dbt source definition, what do database, schema and identifier control?

## The compiled relation Every `source()` call in dbt resolves to a three-part relation on most warehouses: ``` database . schema . identifier ``` The two arguments you pass — `source('jaffle_shop', 'orders')` — are *logical* names. The three YAML keys are what maps those logical names onto the physical object. Understanding the defaults is the whole question. ## The defaults - **`database`** defaults to the database configured for the current target in your connection profile. Set it explicitly when raw data lands in a separate database from the one dbt writes to, which is common on Snowflake (`RAW` vs `ANALYTICS`) and Redshift-style setups. On warehouses with a two-part namespace the key is generally not used. - **`schema`** defaults to the **source's `name`**. This is the one people misremember; they assume it falls back to the target schema, which would be nonsense — the target schema is where dbt *writes*, not where raw data sits. - **`identifier`** defaults to the **table's `name`**. So the minimal declaration below resolves `source('jaffle_shop', 'orders')` to `<target_database>.jaffle_shop.orders`: ```yaml sources: - name: jaffle_shop tables: - name: orders ``` ## When you override each key **database** — the landing zone is physically separate. `database: raw` is the archetype. It also lets a single project read from several databases, one per source system. **schema** — the loader picked the schema and you do not like it, or it differs from the friendly name. An ingestion tool may write to `fivetran_jaffle_shop_public`; you declare `name: jaffle_shop` with `schema: fivetran_jaffle_shop_public` and every model still says `source('jaffle_shop', ...)`. If the schema differs per environment, the value can be templated from a variable or an environment variable so dev points at a sample schema and prod at the real one. **identifier** — the physical table name is versioned, prefixed, reserved, or awkward: `orders_v2`, `tbl_orders`, `ORDERS$RAW`. Declare `name: orders` with `identifier: orders_v2`. When the upstream team ships `orders_v3`, you change one line and every downstream model follows. ## Why this indirection is the point The logical names are the contract your SQL is written against; the physical names are the warehouse's business. Keeping them separate means an upstream rename, a loader migration, or a database reorganisation is a YAML edit rather than a repo-wide search. It also keeps model code readable — `source('salesforce', 'account')` says more than `SFDC_PROD_REPL.RAW_SFDC_V2.ACCOUNT__C`. ## Other keys that live alongside them A source block carries more than location. `description` documents the system; `loader` records what wrote the data (a free-text label that shows in docs); `tables` can each carry `description`, `columns` with tests, `loaded_at_field` and a `freshness` block. Source-level defaults cascade to tables, so setting `database` and `schema` once on the source covers every table beneath it unless a table overrides them. ## Casing and quoting Warehouses disagree about identifier case. Snowflake upper-cases unquoted identifiers, Postgres lower-cases them, BigQuery is case-sensitive for table names. If a raw table was created with quotes and mixed case, the plain value in YAML may not match. The `quoting` config (settable at the source or table level for `database`, `schema` and `identifier`) controls whether dbt wraps each part in quotes when it compiles. A source that resolves to a relation the warehouse insists does not exist is nearly always a casing or quoting problem, and the fastest diagnosis is to compile the model and read the exact SQL dbt generated. ## Diagnosing a bad resolution When a source-based model fails with "relation does not exist", do not guess. Run a compile and open the compiled SQL, or query the generated manifest — the compiled `FROM` clause shows precisely which three parts dbt assembled. Nine times in ten the answer is that `schema` was omitted and silently defaulted to the source name, or that the physical table has a suffix the `identifier` key was never told about. ## The rule to carry away Source name and table name are what your code says. Database, schema and identifier are where the data actually is. If the two ever have to be the same string, you have simply accepted the defaults.