Table Types and Views

Permanent, transient, temporary, external, Iceberg, and dynamic tables; standard, materialized, and secure views.

By now you have run CREATE TABLE a dozen times and it has always meant the same thing — a durable, recoverable relation that Snowflake keeps safe on your behalf. That is the sensible default, and for most of a warehouse it is the right one. But “a table” in Snowflake is not one thing wearing one lifecycle. Some tables you never want to pay recovery costs for because the data is a scratch copy you could rebuild in a minute. Some hold data that never enters Snowflake’s storage at all — they are a lens over files sitting in a cloud bucket. Some rebuild themselves on a schedule so you never write the refresh logic. And once you get to views, the same word covers a saved query, a self-maintaining cache, and a governance boundary that deliberately hides its own definition. This chapter is the map of that whole space: what each relation type actually is, what it costs, which edition gates it, and how to pick without guessing.

Permanent, transient, temporary: the recovery dial

Start with the three flavours of an ordinary table, because they differ in exactly one axis — how hard Snowflake works to keep your data recoverable — and that axis is the whole cost story.

A permanent table is the default. CREATE TABLE orders (...) gives you one. It gets Time Travel (up to 90 days on Enterprise and above, capped at 1 day on Standard) and, behind that, the 7-day Fail-safe window that only Snowflake support can reach into. Both of those keep older versions of your data around, and both of those are storage you pay for. That is exactly what you want for a finance mart or anything you cannot reconstruct — but it is dead weight for data you could rebuild trivially.

A transient table dials the recovery guarantees down. You ask for one explicitly:

CREATE TRANSIENT TABLE stg_orders (
  order_id   NUMBER,
  loaded_at  TIMESTAMP_NTZ
);

Two things change. Time Travel on a transient table maxes out at 1 day regardless of edition — you cannot set DATA_RETENTION_TIME_IN_DAYS above 1. And there is no Fail-safe at all. Once its (at most one-day) Time Travel window expires, the old data is gone, full stop — no seven-day grace, no support ticket that can bring it back. In exchange you stop paying for that Fail-safe storage. For staging tables, intermediate ELT steps, and anything you load-then-rebuild on the next run, transient is the honest choice: you are telling Snowflake “if this is lost, I will just reload it,” and Snowflake stops charging you to protect it.

A temporary table takes that idea to its limit — it exists only for your session:

CREATE TEMPORARY TABLE session_scratch (
  bucket  STRING,
  n       NUMBER
);

It behaves like a transient table (Time Travel up to 1 day, no Fail-safe) while it lives, but when your session ends — you disconnect, the worksheet closes, the connection drops — the table and its data vanish automatically. Nobody else can even see it; it is scoped to the one session that made it. There is a sharp edge worth internalizing: a temporary table can shadow a permanent one with the same name. If a permanent orders exists and you create a temporary orders in your session, every unqualified reference to orders for the rest of that session hits the temporary one. That is occasionally the point and occasionally a nasty surprise, so name temporaries distinctly when you can.

One convenience that catches people out in a good way: transience is inherited. Create a transient database or schema and every table created inside it defaults to transient.

CREATE TRANSIENT DATABASE scratch_db;
-- every table created in scratch_db is transient by default,
-- and cannot be made permanent

So if you have a whole sandbox database whose contents are all reproducible, make the database transient once and stop thinking about it per table. Note you cannot create a permanent table inside a transient database — the container’s guarantee is a floor the children can’t rise above.

The mental model for the whole trio is a single question: if this data disappeared, would I cry, shrug, or not even notice? Cry → permanent. Shrug, I’ll reload it → transient. It only mattered for this session → temporary. Storage cost follows that ranking directly, because storage cost is mostly the cost of keeping recoverable history around, and these three types keep progressively less of it.

External tables: a query over files you never loaded

Everything above stores data in Snowflake’s own storage. An external table does the opposite — it leaves the data as files in a stage (an S3, Azure, or GCS location) and gives you a table-shaped way to query them in place. Nothing is loaded; Snowflake reads the files at query time.

You point one at a stage and describe how to pull columns out of each file:

CREATE EXTERNAL TABLE ext_events (
  event_date DATE AS
    TO_DATE(SPLIT_PART(METADATA$FILENAME, '/', 3), 'YYYY-MM-DD'),
  user_id    STRING AS (VALUE:user_id::string),
  action     STRING AS (VALUE:action::string)
)
  PARTITION BY (event_date)
  LOCATION = @events_stage/logs/
  FILE_FORMAT = (TYPE = PARQUET)
  AUTO_REFRESH = TRUE;

A few things are doing work here. VALUE is the automatically-provided column holding each row’s raw contents, which you cast into typed columns exactly like the semi-structured navigation from the last chapter. METADATA$FILENAME exposes the file path, which lets you derive partition columns — here event_date comes from the folder name — so that a query with WHERE event_date = '2026-06-01' can prune to just the matching files instead of scanning the whole bucket. Snowflake keeps metadata about which files exist; when new files land you either call ALTER EXTERNAL TABLE ext_events REFRESH; yourself, or set AUTO_REFRESH = TRUE to have cloud notifications update that metadata as files arrive.

The trade-off is plain: external tables are read-only and generally slower than native tables, because the data isn’t in Snowflake’s optimized micro-partition format and doesn’t benefit from its statistics and pruning the same way. You reach for them when the data genuinely lives elsewhere — a data lake another team owns, files you want to query before deciding whether to ingest — and you want SQL access without a copy. If you find yourself querying an external table constantly, that is a signal to actually load it, or to reach for the next type.

Iceberg tables: open format, your choice of catalog

Iceberg tables answer a newer demand: teams want their data in an open table format — Apache Iceberg — so that Snowflake, Spark, Trino, and other engines can all read and write the same physical Parquet files with a shared notion of schema, snapshots, and transactions. Unlike an external table, an Iceberg table is a full-featured table you can write to and that supports Time Travel over Iceberg snapshots; the difference from a standard table is where the data and metadata live and who owns them.

There are two shapes, and the choice is about who runs the catalog — the component that tracks the table’s current state.

Snowflake-managed catalog. Snowflake owns the Iceberg metadata and manages the table for you. You get near-native performance and full read/write, and the files sit in your cloud storage in open Iceberg format, so other engines can still read them. Pick this when Snowflake is the primary writer but you want the open format for portability and to avoid lock-in.

The one new object it needs is an external volume — the same idea as the storage integration from chapter 05, but for table data rather than a stage: it holds the trust relationship to your bucket so Snowflake can write Parquet there.

CREATE OR REPLACE EXTERNAL VOLUME iceberg_vol
  STORAGE_LOCATIONS = (
    (
      NAME = 'orders-bucket'
      STORAGE_PROVIDER = 'S3'
      STORAGE_BASE_URL = 's3://bookshop-lakehouse/iceberg/'
      STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-iceberg'
    )
  );

CREATE OR REPLACE ICEBERG TABLE sales.public.orders_iceberg (
    o_orderkey   NUMBER,
    o_totalprice NUMBER(12,2),
    o_orderdate  DATE
)
  CATALOG = 'SNOWFLAKE'                    -- Snowflake owns the metadata
  EXTERNAL_VOLUME = 'iceberg_vol'
  BASE_LOCATION = 'orders/';

That is a fully writable table: INSERT, UPDATE, MERGE, and Time Travel all work, and Spark or Trino pointed at the same bucket sees the same Iceberg snapshots.

External catalog. Some other catalog (AWS Glue, an Iceberg REST catalog, and so on) is the source of truth, and Snowflake reads — and in some configurations writes — against it. CATALOG names a catalog integration instead of 'SNOWFLAKE', and Snowflake defers to it for the table’s current snapshot. Pick this when another platform already owns the lakehouse and Snowflake is one participant among several.

The “what and when” is enough for a foundations course: Iceberg tables are how you keep data in an open, multi-engine format without giving up SQL, Time Travel, or (in the Snowflake-managed case) most of the performance you would get from a native table. If your organization has committed to a lakehouse that several tools share, this is the table type that lets Snowflake join it as a first-class citizen rather than a read-only guest.

Dynamic tables: declarative pipelines that refresh themselves

The last table type is the most different in spirit. A dynamic table is defined by a query and a freshness target, and Snowflake keeps its contents up to date automatically — incrementally where it can — without you writing any of the refresh orchestration.

CREATE DYNAMIC TABLE daily_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  AS
    SELECT order_date, SUM(amount) AS total
    FROM orders
    GROUP BY order_date;

Two parameters carry the design. TARGET_LAG is your freshness contract — “keep this no more than an hour behind its sources” — and Snowflake schedules refreshes to honour it (you can also say TARGET_LAG = DOWNSTREAM to let a table refresh only as often as whatever depends on it needs). WAREHOUSE names the compute that does the refreshing. You never write an INSERT, a MERGE, or a schedule; you declare the result you want and how fresh, and the engine works out the rest, doing incremental updates when the query and change pattern allow and a full rebuild when they don’t.

It helps to place dynamic tables against the two things they replace:

  • Versus a materialized view (next section): a materialized view is limited to a single table with heavy restrictions on the query. A dynamic table can be an arbitrary query — joins, aggregations, multiple sources, chained one dynamic table onto another — making it the tool for real multi-step transformation pipelines, not just a cache over one table.
  • Versus a stream + task: before dynamic tables, the idiom for “keep a derived table current” was a stream (to capture changes on the source) feeding a task (to run a MERGE on a schedule) — powerful, but you hand-wrote the change logic, the merge, and the DAG, and you maintained all of it. A dynamic table collapses that into a declaration. You give up some fine-grained control, and you gain not having to build or debug the plumbing. For most incremental transformations, that is the trade you want.

We will meet streams and tasks properly later in the series; for now, know that dynamic tables are the modern, declarative default, and streams-and-tasks are the escape hatch for when you need to control exactly how the update happens.

Views: from saved SQL to governance boundary

Views share the word but span a real range. Take them in increasing order of what Snowflake does on your behalf.

Standard views: a query with a name

A standard view is just stored SQL. It holds no data; it runs its underlying query every time you select from it.

CREATE VIEW recent_orders AS
  SELECT order_id, customer_id, amount, order_date
  FROM orders
  WHERE order_date >= DATEADD(day, -30, CURRENT_DATE());

Query recent_orders and Snowflake substitutes the definition and executes it fresh against orders. That means the result is always current and the view costs nothing to store — but you pay the full query cost on every read, and a genuinely expensive definition is genuinely expensive every single time. Standard views are for naming, simplifying, and reshaping — a clean interface over messy base tables — not for making slow things fast.

A note you will trip over otherwise: Snowflake case-folds unquoted identifiers to uppercase. CREATE VIEW recent_orders stores the object as RECENT_ORDERS, and SELECT ... FROM recent_orders finds it because that reference folds too. It is only when you quote — CREATE VIEW "recent_orders" — that you pin a lowercase name that you must then always quote to reach. Leave identifiers unquoted and this never bites you; start quoting some and not others and it will.

Materialized views: a cache Snowflake keeps warm

A materialized view does store its results — it is a precomputed, persisted answer that Snowflake automatically keeps up to date as the base table changes. Reads hit the stored result and are fast; you don’t refresh it yourself.

CREATE MATERIALIZED VIEW mv_sales_by_region AS
  SELECT region, SUM(amount) AS total_sales
  FROM sales
  GROUP BY region;

Two hard facts gate this. First, materialized views are an Enterprise-edition (and above) feature — they do not exist on Standard. Second, they are heavily restricted compared to a normal view. A materialized view can reference only a single table — no joins. It cannot use UDFs, a HAVING clause, window functions, ORDER BY, LIMIT, or most subqueries; it is really meant for filters and simple aggregations over one table. And it is not free: Snowflake charges serverless compute to maintain it as the base table changes, so a materialized view over a table that churns constantly can cost more in maintenance than it saves in reads.

So when does a materialized view beat just building a table with the same query? When the underlying data changes often enough that a manually-refreshed table would go stale, but not so often that maintenance cost explodes — and when the query fits the restrictions. The classic fit is an expensive aggregation over a large, slowly-appended table that many dashboards read: the view stays current automatically, readers get precomputed speed, and you maintain no refresh job. When the query needs a join or a HAVING, the answer is no longer a materialized view — it is a dynamic table, which is exactly why that type exists.

Secure views: hiding the definition, on purpose

A secure view is about governance, not performance. It is a view whose definition is hidden and whose optimizer behaviour is deliberately constrained so it cannot leak the rows it is meant to protect.

CREATE SECURE VIEW customer_safe AS
  SELECT customer_id, region, signup_date  -- deliberately no PII columns
  FROM customers;

Two behaviours flip on. First, the definition is hidden: users who can query the view cannot see its SQL via GET_DDL or the information schema, so the logic that decides what they’re allowed to see isn’t itself exposed. Second — and this is the subtle one — Snowflake sets IS_SECURE on the view and turns off certain query optimizations for it. Normally the optimizer is aggressive about predicate pushdown: it will shove your WHERE conditions (and even user-defined functions in them) down underneath the view’s logic to prune data early. For an ordinary view that is pure speed. For a view meant to hide rows, it is a leak: a cleverly-crafted predicate or function could be pushed below the security filter and, through timing or error messages, reveal the existence or values of rows the user was never supposed to see. A secure view forbids exactly those pushdowns. The view’s own filtering always runs first; nothing the caller writes can sneak beneath it.

That safety has a cost: with those optimizations disabled, a secure view can run measurably slower than the same logic as a standard view. This is the trade you are explicitly making. Use secure views where the view is the security boundary — data you share with another account, or a masked slice of a table exposed to a broader audience — and accept the performance hit as the price of the guarantee. Do not slap SECURE on every view for tidiness; you would be paying the optimizer tax on views that guard nothing.

Choosing: a decision guide

Put it all together as one flow. The question is always “what is the nature of this data and this workload,” and the type falls out:

  • Durable data you cannot rebuild (facts, marts, anything of record) → permanent table. Pay for Time Travel and Fail-safe; that is what they are for.
  • Reproducible intermediate data (staging, ELT steps, scratch) → transient table — or a transient schema/database so you set it once. Keep the recovery cost off data you would just reload.
  • Session-local scratch that should evaporate on disconnect → temporary table. Watch the name-shadowing edge.
  • Data that lives in cloud files you don’t want to load → external table. Read-only, partition-pruned, refresh on notification. If you query it constantly, load it instead.
  • Data that must stay in an open, multi-engine format → Iceberg table. Snowflake-managed catalog when Snowflake is the primary writer; external catalog when another platform owns the lakehouse.
  • A derived result you want kept fresh automatically — this is the common one, so split it:
    • Simple filter/aggregation over one table, changing often, on Enterprise → materialized view (Snowflake maintains it; watch maintenance cost).
    • Joins, multiple sources, multi-step logic → dynamic table (declare TARGET_LAG, let it refresh incrementally). This is the modern default for pipelines.
    • Need to control the exact change-capture and merge → stream + task (the escape hatch, covered later).
  • Just a named, reshaped interface over base tables, always current, cheap to store → standard view. Remember you pay the query cost on every read.
  • A view that is itself a security boundary (sharing, masking) → secure view. Accept the optimizer-bypass cost as the price of not leaking rows.

Notice that “keep a derived result fresh” is the branch with the most options, and it is where teams most often pick wrong — reaching for a hand-built table refreshed by a nightly job when a dynamic table would have been three lines and always current, or for a materialized view whose query needs a join it can’t have. Ask first whether the thing has joins, then whether it must be automatic, and the branch resolves cleanly.

Final thoughts

The single most valuable habit this chapter can leave you with is to treat “what type of relation is this” as a deliberate design decision, not a default you never questioned. Every one of these types encodes an answer to a real question — how recoverable must this be, where does the data physically live, who else reads the format, how fresh must the result stay, is this a security boundary — and Snowflake charges you (in storage, in compute, or in performance) according to the answer you pick. A staging table left as permanent quietly pays for Fail-safe on data you’d happily reload. A dashboard aggregation left as a standard view re-runs an expensive query on every glance. A sharing view left un-secured leaks its own logic. None of these will throw an error; they will just cost you, silently, for as long as nobody looks.

So write the choice down. When the next engineer finds a transient table, they should be able to tell whether it’s transient because the data is genuinely reproducible upstream or because someone pasted a snippet without thinking. When they find a secure view, they should know it guards a real boundary, not that someone liked the keyword. The relation types are a small vocabulary — permanent, transient, temporary, external, Iceberg, dynamic; standard, materialized, secure — but used precisely they let the shape of your warehouse say what each piece of data is for. That legibility is worth as much as the cost savings.

Next: Micro-Partitions, Clustering, and Performance

Comments