Database / Snowflake Interview Questions
What is the difference between a Snowflake View, Materialized View, and Dynamic Table?
These three object types all provide an abstraction layer over raw tables, but they differ fundamentally in whether they store data physically, how they refresh, and what query patterns they support.
| Dimension | Regular View | Materialized View | Dynamic Table |
|---|---|---|---|
| Physical data stored | No — virtual | Yes | Yes |
| Auto-refreshed | N/A — always current | Yes, incrementally by Snowflake | Yes, based on TARGET_LAG |
| Refresh trigger | On query (inline rewrite) | Base table DML (incremental) | Snowflake scheduler (lag-based) |
| Multi-table joins | Yes | No — single base table only | Yes |
| Pipeline chaining | No | No | Yes — chain DTs together |
| Extra storage cost | None | Yes | Yes |
| Background compute cost | None | Serverless (incremental) | Warehouse or serverless |
| Best for | Simple aliases, access control | Repeated aggregate on one table | Multi-step ELT pipelines |
Choosing between them: use a View when you only need a query abstraction or access restriction with no performance concern. Use a Materialized View when you need fast repeated reads of a single-table aggregate with predictable change frequency. Use a Dynamic Table when you need declarative, refreshed pipeline results across multiple joined tables or multi-step transformation chains.
Which of the three object types — View, Materialized View, or Dynamic Table — stores no physical data and executes SQL inline at query time?
You need a transformation that joins five tables and must stay fresh within 5 minutes. Which object type is best suited?
More Related questions...
