What you will be able to do
- Create and maintain external tables, including partitioning and metadata refresh
- Choose Iceberg table storage and catalog options, and migrate Delta external tables to Iceberg
- Decide when a view should be secure, and when a materialized view is worth its cost
1.External tables: querying files you keep in an external stage
An external table lets you query files in an external stage as if they were a table in Snowflake. Snowflake doesn't store or manage the stage. It keeps only file-level metadata, such as file names and version identifiers. External tables can read any format that COPY INTO <table> supports, except XML. They are read-only: you can't run DML on them, but you can query them, join them, and build views on them. You can also protect them with masking policies and row access policies.
To create one, you only need to know the file format and record format of the data, not its schema. Every external table has a VALUE column of type VARIANT that holds one row of the file, plus the METADATA$FILENAME and METADATA$FILE_ROW_NUMBER pseudocolumns. If you know the schema, you can define typed virtual columns as expressions on VALUE. Snowflake then checks that the data's types match them. You can add or drop columns with ALTER TABLE, but you can't drop VALUE or the two pseudocolumns.
Snowflake strongly recommends partitioning external tables. Partitioning requires the files to be organized under logical paths, for example by date or country. Partition columns are expressions over METADATA$FILENAME, defined with CREATE EXTERNAL TABLE … PARTITION BY. Snowflake adds partitions automatically whenever the metadata is refreshed. If you would rather add partitions yourself, for example to stay in sync with AWS Glue or Apache Hive, declare the partitions as user-specified and add each one with ALTER EXTERNAL TABLE … ADD PARTITION. You can't change the partitioning method after the table is created, and tables with user-specified partitions can't be refreshed automatically.
Checkpoint 1 of 7· Fill the gap
You want to add partitions yourself with ALTER EXTERNAL TABLE … ADD PARTITION instead of having Snowflake derive them on refresh. What completes the definition?
CREATE EXTERNAL TABLE
<table_name>
( <part_col_name> <col_type> AS <part_expr> )
[ , ... ]
[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
PARTITION_TYPE = ?
..PARTITION_TYPE = USER_SPECIFIED is required for manually managed partitions. Without it, Snowflake computes partitions from the expressions each time it refreshes.
Source: docs.snowflake.comThe metadata has to be refreshed to match the files actually in the stage. A refresh adds new files, updates changed ones, and removes files that are gone. Automatic refresh relies on your cloud provider's event notifications, and its overhead shows up on your bill as Snowpipe charges. A manual refresh with ALTER EXTERNAL TABLE … REFRESH is billed as cloud services, except for Delta Lake tables, which use a warehouse. External tables don't support storage versioning, such as S3 versioning. External tables on Delta Lake (TABLE_FORMAT = DELTA) can't refresh automatically, and that feature will be deprecated; Snowflake recommends Iceberg tables instead.
Checkpoint 2 of 7· Put it in order
Put the setup steps for an auto-refreshing external table on Amazon S3 in order.
- 1.Manually refresh the metadata once more to catch changes since the first refresh
- 2.Configure an event notification for the S3 bucket
- 3.Manually refresh the metadata to sync and verify the definition
- 4.Create a named stage that references the S3 bucket
- 5.Grant query access on the external table to additional roles
- 6.Create the external table on that stage
The second manual refresh picks up files that changed between the first refresh and the moment notifications were configured. After that, S3 events keep the metadata current.
“Thereafter, the S3 event notifications trigger the metadata refresh automatically.”Source: docs.snowflake.com
Checkpoint 3 of 7· Exam question
A data engineer runs `CREATE TEMPORARY TABLE orders AS SELECT * FROM raw_orders WHERE load_date = CURRENT_DATE()` in a schema that already contains a permanent table named ORDERS. What happens when the same session then runs `SELECT * FROM orders`?
Correct answer: D — The query returns the temporary table's data, and the permanent ORDERS table stays hidden until the temporary one is dropped or the session ends.
- A. Incorrect. Temporary tables live in a session-scoped namespace and can reuse a name, so creation succeeds without OR REPLACE and the permanent table is not required to be dropped.
- B. Incorrect. A temporary table is a separate object and never modifies the permanent table, and Fail-safe is not used for session cleanup of temporary objects.
- C. Incorrect. Unqualified names resolve to the temporary table first, and there is no hidden per-session schema that has to be addressed to reach it.
- D. Correct. A temporary table takes precedence over a permanent table of the same name within its own session, so the permanent table is masked but remains untouched and visible to other sessions.
Sources1
2.Apache Iceberg tables: external volumes and catalogs
Iceberg tables suit data lakes that you can't, or don't want to, store in Snowflake. They use the Apache Iceberg open table format on Parquet files, which supports ACID transactions, schema evolution, hidden partitioning, and table snapshots. Unlike external tables, they behave like ordinary Snowflake tables in performance and query semantics.
When you create an Iceberg table, you make two choices. The first is where the files live: in Snowflake storage (EXTERNAL_VOLUME = SNOWFLAKE_MANAGED), or in cloud storage you manage, reached through an external volume. An external volume is an account-level object that holds an IAM entity for your storage, and one volume can serve many tables. The second choice is the catalog. Snowflake can be the catalog, or a catalog integration can connect Snowflake to an external catalog such as AWS Glue or Snowflake Open Catalog. For a remote REST catalog, a catalog-linked database automatically discovers the catalog's tables and stays in sync with them.
| Aspect | Snowflake as catalog | External catalog (via catalog integration) |
|---|---|---|
| Platform support | Full Snowflake platform support | Limited Snowflake platform support |
| Read / write | Yes / Yes | Yes / Yes |
| Lifecycle maintenance (e.g. compaction) | Handled by Snowflake; can be disabled | Not listed |
| Replication for tables | Supported | Not listed |
| Open Catalog integration | Sync table so other engines can query it | Query or write tables managed by Open Catalog |
Snowflake bills Iceberg tables for warehouse compute and cloud services. If the files are on an external volume you manage, your cloud provider bills you for storage, not Snowflake. To migrate a Delta external table, follow these steps. Look up its location with SHOW EXTERNAL TABLES. Create an external volume whose STORAGE_BASE_URL points to that location. Create a catalog integration for Delta. Create the Iceberg table with a BASE_LOCATION relative to the volume. Finally, drop the external table.
CREATE ICEBERG TABLE my_delta_table_1 BASE_LOCATION = 'delta-ext-table-1' EXTERNAL_VOLUME = 'delta_migration_ext_vol' CATALOG = 'delta_catalog_integration';Checkpoint 4 of 7· Match them up
Match each Iceberg building block to its role
Tap a term, then the definition that fits it.
The external volume controls where the files live, and the catalog integration controls who tracks the table metadata. The two are configured separately.
“An external volume is a named, account-level Snowflake object that you use to connect Snowflake to your external cloud storage for Iceberg tables.”Source: docs.snowflake.com
Sources2
3.Views, secure views, and materialized views
A view stores a query, not data, and you can define one over any table type, including external tables. Two variants change what a view protects and how much it costs.
A secure view hides two things. The first is the view definition. On a normal view, the query text is visible to other users, while on a secure view only users granted the role that owns the view can see it. The second is the underlying data. Some internal optimizations on normal views could let user code, such as UDFs, reach rows the view is meant to hide. Secure views skip those optimizations, which is why they can run more slowly. Use them for data privacy, not for views that exist only to make queries easier to write. Both regular and materialized views can be made secure.
Checkpoint 5 of 7· Check yourself
A team wants to make every convenience view in a reporting schema SECURE, "just to be safe". None of the views restrict sensitive data. What does the guidance say?
Secure views give up some optimizations to protect data, so using them only for convenience costs performance and gains nothing.
“Secure views should not be used for views that are defined solely for query convenience”Source: docs.snowflake.com
A materialized view stores its results, so querying it is fast but the stored results cost money. Create one only when all three conditions hold: the results don't change often, they are used much more often than they change, and the query is expensive in time, credits, or intermediate storage. Use a regular view if any of these is true: the results change often, they are rarely used, or the query is cheap to run again. Storage cost matters too. If the results are rarely read, the speed gain may not cover it. Materialized views over external tables are a common case, because querying external data directly can be slow. Refresh the external table's metadata so the materialized view reflects the current set of files.
Checkpoint 6 of 7· Check yourself
Which workload best justifies a materialized view instead of a regular view?
A materialized view pays off when results change rarely, are read often, and are expensive to compute. Hiding data calls for a secure view.
“The results of the view are used often (typically significantly more often than the query results change).”Source: docs.snowflake.com
Checkpoint 7 of 7· Exam question
An analytics team wants to run SQL directly over Parquet files in an Amazon S3 external stage without loading them into Snowflake. Select TWO statements that accurately describe external tables.(Select 2)
Correct answers: D, E — Each row exposes a VALUE variant column holding the file record, and METADATA$FILENAME identifies the staged file it came from.; A materialized view can be created over the external table to speed up repeated queries against the staged files in S3.
- A. Incorrect. External tables are read-only, so DML statements are rejected and the staged files can only be changed outside Snowflake.
- B. Incorrect. The data stays in your cloud storage outside Snowflake's control, so Time Travel and Fail-safe do not apply to external table contents.
- C. Incorrect. External tables only store file metadata in Snowflake, and each query still reads the files from the external stage location.
- D. Correct. External tables always return the semi-structured VALUE column plus pseudocolumns such as METADATA$FILENAME, which let queries trace rows back to files.
- E. Correct. Materialized views are supported on external tables and store precomputed results inside Snowflake, which improves performance for repeated queries.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.You can fix bad rows in an external table with UPDATE or DELETE, the same as in a native table.Why is that wrong?
External tables are read-only. Change the underlying files instead, then refresh the metadata.
Covered in External tables: querying files you keep in an external stage
2.Every permanent Iceberg table gets Snowflake Fail-safe, because Snowflake manages it.Why is that wrong?
Only tables stored in Snowflake storage (SNOWFLAKE_MANAGED) get Fail-safe. For tables on an external volume you manage, data protection and recovery are your responsibility.
Covered in Apache Iceberg tables: external volumes and catalogs
3.Making a view secure is only about hiding its SQL text.Why is that wrong?
Secure views also turn off internal optimizations that could expose hidden data to user code, such as UDFs. That is the privacy guarantee, and it is also why they can be slower.
Covered in Views, secure views, and materialized views
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“query data stored in an external stage as if the data were inside a table in Snowflake”
↩︎ External tables: querying files you keep in an external stage“External tables can access data stored in any format that the COPY INTO <table> command supports, except XML.”
↩︎ External tables: querying files you keep in an external stage“You can protect an external table by using a masking policy and a row access policy.”
↩︎ External tables: querying files you keep in an external stage“We strongly recommend partitioning your external tables”
↩︎ External tables: querying files you keep in an external stage“After an external table is created, the method by which partitions are added can’t be changed.”
↩︎ External tables: querying files you keep in an external stage“Automatically refreshing an external table with user-defined partitions isn’t supported.”
↩︎ External tables: querying files you keep in an external stage“This overhead charge appears as Snowpipe charges in your billing statement”
↩︎ External tables: querying files you keep in an external stage“External tables don’t support storage versioning”
↩︎ External tables: querying files you keep in an external stage“This feature is still supported but will be deprecated in a future release.”
↩︎ External tables: querying files you keep in an external stage“You can also create views against external tables.”
↩︎ Views, secure views, and materialized views“External tables are read-only.”
↩︎ Exam trap 1“Thereafter, the S3 event notifications trigger the metadata refresh automatically.”
↩︎ Checkpoint - 2.
“They are ideal for existing data lakes that you cannot, or choose not to, store in Snowflake.”
↩︎ Apache Iceberg tables: external volumes and catalogs“A single external volume can support one or more Iceberg tables.”
↩︎ Apache Iceberg tables: external volumes and catalogs“you need a catalog integration if your table is managed by AWS Glue.”
↩︎ Apache Iceberg tables: external volumes and catalogs“An Iceberg table that uses an external catalog provides limited Snowflake platform support.”
↩︎ Apache Iceberg tables: external volumes and catalogs“Snowflake handles all lifecycle maintenance, such as compaction, for the table.”
↩︎ Apache Iceberg tables: external volumes and catalogs“Snowflake bills your account for virtual warehouse (compute) usage and cloud services when you work with Iceberg tables.”
↩︎ Apache Iceberg tables: external volumes and catalogs“Permanent tables are protected by Fail-safe. Snowflake bills you for this storage.”
↩︎ Exam trap 2“You’re responsible for data protection and recovery. Snowflake doesn’t provide Fail-safe for these tables, and your cloud provider bills you for storage.”
↩︎ Prediction“An external volume is a named, account-level Snowflake object that you use to connect Snowflake to your external cloud storage for Iceberg tables.”
↩︎ Checkpoint - 3.
“the view definition and details are visible only to authorized users”
↩︎ Views, secure views, and materialized views“Views should be defined as secure when they are specifically designated for data privacy”
↩︎ Views, secure views, and materialized views“This topic covers concepts and syntax for defining views and materialized views as secure.”
↩︎ Views, secure views, and materialized views“Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.”
↩︎ Exam trap 3“Secure views should not be used for views that are defined solely for query convenience”
↩︎ Checkpoint - 4.
“The query results from the view don’t change often.”
↩︎ Views, secure views, and materialized views“the cost of storing the materialized view is a factor”
↩︎ Views, secure views, and materialized views“The results of the view are used often (typically significantly more often than the query results change).”
↩︎ Checkpoint