Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageExternal Tables

External Tables

External Tables

In shortAn external table lets you query files in an external stage as a read-only table, without loading them. Snowflake stores only metadata about the files.

Data path · Query in place
Source
Files in an external stage (your or a partner's bucket)
Trigger
A query; new files appear after a metadata refresh (event notification or manual)
Processor
Your warehouse, which reads the files at query time
Destination
None: results only, nothing is loaded
Compute and billing
Warehouse credits per query; automatic file registration has a small serverless cost; the files are billed by your cloud provider

The problem

A logistics partner writes Parquet files into its own S3 bucket every night, and the contract says the data stays there. IsmaFrost still wants to query it with SQL.

How it works

External table. The partner's Parquet files stay in the partner's bucket. A cloud event notification tells the external table EXT_DELIVERIES that a new file arrived, and the table refreshes its file list. At query time the external table gives the planner the list of files, and your warehouse reads those files from the bucket. An optional materialized view stores a frequently used slice inside Snowflake.PARTNER'S BUCKET · FILES STAY THEREISMAFROST ACCOUNTCLOUD EVENTNew file arrivedtriggers AUTO_REFRESHFILESParquet filesdate=2026-10-25/part-0.parquetdate=2026-10-26/part-0.parquet…EXTERNAL TABLEEXT_DELIVERIESa list of files,no data storedCOMPUTEWarehousereads the filesat query timeMATERIALIZED VIEWHot slicestored in Snowflake(optional)which files

PARTNER'S BUCKET · FILES STAY THERE

CLOUD EVENTNew file arrivedtriggers AUTO_REFRESH

FILESParquet filesdate=2026-10-25/part-0.parquetdate=2026-10-26/part-0.parquet…

ISMAFROST ACCOUNT

EXTERNAL TABLEEXT_DELIVERIESa list of files, no data stored

which
files

COMPUTEWarehousereads the files at query time

MATERIALIZED VIEWHot slicestored in Snowflake (optional)

Figure 4.13 The external table holds a file list; the bytes stay in the partner's bucket and your warehouse reads them at query time. A materialized view can store a frequently used slice inside Snowflake.

Every row exposes a VALUE VARIANT column with the record. The other columns are expressions over VALUE or over the file path. A partition column is an expression on METADATA$FILENAME, and filtering on it lets Snowflake skip whole folders, which is the main performance lever.

Example: last night's deliveries

-- Files are laid out as date=YYYY-MM-DD/part-0.parquet under the stage path
CREATE EXTERNAL TABLE sales_db.raw.ext_deliveries (
  delivery_date DATE   AS TO_DATE(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'date=', 2), '/', 1)),
  shipment_id   STRING AS (VALUE:shipment_id::STRING),
  weight_kg     NUMBER AS (VALUE:weight_kg::NUMBER)
)
  PARTITION BY (delivery_date)
  LOCATION = @sales_db.raw.partner_stage/
  FILE_FORMAT = (TYPE = PARQUET)
  AUTO_REFRESH = TRUE;

ALTER EXTERNAL TABLE sales_db.raw.ext_deliveries REFRESH;  -- manual refresh when no events

SELECT delivery_date, COUNT(*), SUM(weight_kg)
FROM sales_db.raw.ext_deliveries
WHERE delivery_date = '2026-10-26'      -- prunes to one folder
GROUP BY delivery_date;

Expected resultAlice gets one row for 26 October. Only the files under date=2026-10-26/ are read, because the filter is on the partition column.

Watch out
  • Read-only: no INSERT, UPDATE or DELETE.
  • No Time Travel or Fail-safe: the files aren't in Snowflake storage.
  • New files stay invisible until the metadata is refreshed.

Which option to use

External table

The files must stay in place and are queried occasionally. Add a materialized view (Enterprise) for a hot slice.

Load into a table

The data is queried heavily and repeatedly. Native tables prune better and get Time Travel.

Iceberg table (Part 6)

You need writes, or other engines must share the same open-format table. For heavy queries on Parquet, Snowflake recommends Iceberg over external tables.

Exam takeaway
  • External table = metadata in Snowflake, data in your bucket, read-only.
  • Refresh metadata (automatically through events, or manually) to see new files.
  • No Time Travel, no Fail-safe, no Snowflake storage charge for the files.