Table Types and Views
1. Table Types and Views
Now that you know how micro-partitions work, we will walk through Snowflake table types and views in a logical order.2. Table Types and Views
We start with permanent, transient, and temporary tables (where Time Travel and Fail-safe differ), then specialized table types, then standard and materialized views, and finally secure views for sharing. A later chapter goes deeper on operational recovery with Time Travel and cloning; here we only need the feature names to explain each object.3. Permanent, Transient and Temporary
Every table that gets created in Snowflake has a type, of which there are three: permanent, transient and temporary. Think of the three table types as a sliding scale between protection and cost. The default is permanent. At one end, permanent tables give you the full stack: Time Travel plus Fail-safe, a 7-day recovery window managed by Snowflake. On the Enterprise Edition you get up to 90 days of Time Travel; on Standard, 1 day. Temporary tables sit at the other end: they only exist for the duration of your session and vanish when you log out. Transient tables land in the middle, keeping up to a day of Time Travel but dropping Fail-safe entirely. The more protection you need, the more storage it costs. Transient and temporary tables have no Fail-safe, which is exactly why they're cheaper to store.4. Permanent, Transient and Temporary
If you're building staging tables or intermediate results you don't need long-term protection on, transient is the right default. Beyond permanent, transient, and temporary, Snowflake has four more specialized table types.5. Dynamic, External, and Hybrid
Dynamic tables automate the refresh work you'd otherwise give to a scheduled job. You write a query, set a target lag which is how far behind the source data you're willing to be and Snowflake keeps the result current. Both Time Travel and Fail-safe apply. External tables are the other extreme. The data stays in cloud storage and is never loaded into Snowflake. You query it in place and it is read-only. No Time Travel, no Fail-safe.6. Dynamic, External, and Hybrid
Hybrid tables are designed for workloads that mix high-frequency row-level writes with analytical reads think an operational application that also needs to run reports. While Time Travel is supported; Fail-safe is not.7. Apache Iceberg™
Apache Iceberg™ is an open table format, a standard that lets multiple processing engines like Spark, Trino, and Snowflake all work with the same data without copying it. Snowflake gives you two variants. With a Snowflake-managed catalog, Snowflake handles the metadata and Time Travel works as you'd expect but there's no Fail-safe.8. Apache Iceberg™
With an externally-managed catalog, the catalog lives outside Snowflake — in AWS Glue, for example — but the table itself can be read from and written to from Snowflake's side. Time Travel is still supported in both cases but Fail-safe is not. Reach for Iceberg when the data lake is your source of truth and you need multiple engines working against the same tables without duplication.9. Time Travel and Fail-safe (with table types)
Time Travel lets you query or recover data as it existed within the retention window. Fail-safe is a separate, short safety net after that window. Which features apply depends on the table type and edition, which is why we introduce the names here and return to operational examples in the data-protection chapter.10. Standard View and Materialized View
Snowflake has two types of views: standard and materialized. Both can be created as secure: a property on the view, not a third “kind” in the same sense. A standard view is a stored query: each use runs a fresh query against the base tables, with no extra persistent result storage. A materialized view stores precomputed results and refreshes as the data changes. Her we trade storage and serverless maintenance for faster reads when the same expensive pattern runs often and data changes slowly.11. Secure View
A secure view can be applied to a standard or materialized view. The important change is that with a secure view the view definition is not exposed to the consumer, your business logic stays private. When the Snowy Peak team shares data with an insurance partner, they expose a risk score view. The partner sees the scores, not the calculation behind them. Secure views are the mechanism that makes external sharing safe without giving partners a window into your logic. There's a small performance cost, so only use them when the privacy benefit justifies it.12. Let's practice!
Time to put what you've learned about views into practice.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.