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.
Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!
Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.
Invest now!!! Get Free equity stock (US, UK only)!
Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.
The Robinhood app makes it easy to trade stocks, crypto and more.
Webull! Receive free stock by signing up using the link: Webull signup.
More Related questions...
