dbt / Basic: project and model foundations / Lesson 5
Materializations and configuration precedence
Choose how a model becomes a relation and review configuration scope before assuming persistence, freshness or cost.
On this page
Lesson goals
- Compare view, table, ephemeral and incremental responsibilities.
- Resolve configuration scope without assuming every property simply overwrites.
The same query can have different persistence behavior
A materialization tells dbt how to turn model logic into target objects or reusable SQL. A view stores a query definition; a table build stores a computed result; an ephemeral model is incorporated into downstream SQL rather than built as its own queryable relation. Incremental materialization adds change-processing responsibilities examined later. Adapter and database support matter, including materialized-view or platform-specific alternatives. Do not treat a list of framework materialization names as proof that every target implements every strategy. Choose from consumers, refresh needs, reproducibility and actual execution evidence.
A local config declares an intentional choice
The model fragment explicitly chooses a view for a lightweight staging projection. A view can expose current underlying rows when queried, but it does not make an upstream feed current or guarantee a stable historical report snapshot. Stacked complex views can push substantial work into downstream queries. Conversely, a table can serve repeated reads efficiently while becoming stale until rebuilt. These are workload tradeoffs rather than a universal “views bad, tables good” rule. The example is not executed, and its generated DDL and replacement behavior depend on the selected adapter.
{{ config(materialized='view') }}
select loan_id, branch_id, borrowed_at, returned_at
from {{ source('library', 'loans') }}Illustrative staging view configuration. It does not create a view or prove source freshness or target-specific DDL behavior.
Project paths apply only where the resource matches
The YAML fragment configures resources under the staging directory of library_analytics. The project-name namespace is significant; a misspelled path can leave intended models unaffected. The plus prefix marks resource configurations in this project-path context. More specific resource settings can take precedence, but merging behavior differs by property: tags, hooks and dictionary-like settings are not all simple scalar replacement. Inspect effective configuration in the appropriate project artifact and generation. In v1, an ignored or unused path can be easy to miss; do not assume a plausible YAML file necessarily changed the resource.
models:
library_analytics:
staging:
+materialized: view
+tags: ['staging']A dbt_project.yml fragment scoped by project and directory. It does not establish the final effective config of every model.
Ephemeral reuse is not a cached shared table
An ephemeral dependency is normally expanded as a CTE in consuming model SQL. If several consumers use it, its work can appear in several generated statements; it is not automatically one persisted intermediate computed once. Direct warehouse consumers cannot query an ephemeral relation that was never created. It also has feature restrictions, such as model-contract support, and adapter support must be verified. Use it where small reusable transformations justify that tradeoff. Debug both the source model and downstream compiled SQL so a convenient abstraction does not obscure a large or repeated computation.
Materialization changes alter the recovery and access contract
Changing a view to a table or rebuilding an incremental model can require new permissions, storage, runtime and downstream coordination. A full refresh is a data operation, not a harmless formatting option. Verify adapter-specific transactional and rename behavior rather than assuming a project-wide atomic swap. Preserve a known-good publication path and account for concurrent readers or writers. No dbt run, full refresh, table creation or permission change is performed here. Local validation checks configuration structure and scenario reasoning only, with no warehouse cost or performance result claimed.
Common errors and fixes
A project config path silently misses its model
- Why it happens
- The project namespace or directory scope does not match the resource.
- How to fix it
- Inspect the effective resource configuration and correct the path.
- Prevent it next time
- Review unused-path warnings and representative model config.
Ephemeral logic is assumed to execute once for all consumers
- Why it happens
- CTE expansion was confused with persisted shared storage.
- How to fix it
- Inspect downstream SQL and evaluate whether a persisted intermediate is warranted.
- Prevent it next time
- Account for reuse frequency and query complexity.
A materialization switch is treated as a no-risk code edit
- Why it happens
- Object lifecycle, permission and publication consequences were ignored.
- How to fix it
- Plan the target-specific change and recovery before execution.
- Prevent it next time
- Review data operations separately from template edits.
Check your understanding
1. Does changing materialized from view to table make the upstream loan feed current?
Show worked solution
No. It changes persistence and refresh behavior of the modeled result. Source delivery and freshness remain separate. A table can retain a consistent computed result but needs rebuilding or supported incremental logic to incorporate later source changes.
2. Why inspect compiled consumers of an ephemeral staging model?
Show worked solution
Its SQL is incorporated into each supported consumer rather than existing as a reusable physical relation. Repetition and complex joins can become visible only there. Verify adapter and feature support, and choose persistence from measured workload needs rather than assuming ephemeral means cached.
Key takeaways
- Materialization specifies object lifecycle as well as performance tradeoffs.
- Effective configuration must be reviewed in its real scope.