Using External Tables
What are External Tables?
-
✅ External Tables allow you to query data stored outside of Snowflake (e.g., in S3 or Azure Blob Storage) without loading it into Snowflake tables.
-
✅ They’re like pointers to the data in your external storage.
-
✅ Key Benefits:
- 💰 Cost-effective – no storage fees in Snowflake.
- 🔄 Dynamic – data changes in external storage are automatically visible.
- 🧩 Supports semi-structured formats – JSON, Parquet, Avro.
Step-by-Step: Creating & Querying External Tables
1️⃣ Create a Storage Integration
This securely connects Snowflake to your S3 bucket.
-- Loading Structured File
CREATE OR REPLACE STORAGE INTEGRATION s3_int_secure
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::248864054957:role/secure-load-bucket-role'
STORAGE_ALLOWED_LOCATIONS = ('s3://secure-load-bucket/raw_data/');
- ✅ To validate the integration:
DESC STORAGE INTEGRATION s3_int_secure;
🔐 This will return:
STORAGE_AWS_IAM_USER_ARNSTORAGE_AWS_EXTERNAL_IDYou'll need these to configure permissions in AWS IAM.
2️⃣ Create a File Format
This defines how to parse your files.
CREATE OR REPLACE FILE FORMAT json_file_format
TYPE = JSON
STRIP_OUTER_ARRAY = TRUE;
- ✅
STRIP_OUTER_ARRAY = TRUEis required when your JSON file is an array of objects like:
[
{ "id": 1, "name": "Anjali" },
{ "id": 2, "name": "Ravi" }
]
3️⃣ Create an External Stage
Points to the JSON file path in your S3 bucket.
CREATE OR REPLACE STAGE ext_s3_stage_ext_table
URL = 's3://secure-load-bucket/raw_data/json'
STORAGE_INTEGRATION = s3_int_secure
FILE_FORMAT = json_file_format;
- ✅ Verify the stage content:
LIST @ext_s3_stage_ext_table;
Preview sample content:
SELECT $1
FROM @ext_s3_stage_ext_table
(FILE_FORMAT => 'json_file_format');
4️⃣ Create the External Table
CREATE OR REPLACE EXTERNAL TABLE ext_tab_json_external (
customer_data VARIANT AS $1
)
WITH LOCATION = @ext_s3_stage_ext_table
FILE_FORMAT = json_file_format
AUTO_REFRESH = FALSE;
-
✅ This table uses:
VARIANT AS $1: to project the full JSON object into one column.AUTO_REFRESH = FALSE: requires manual metadata refresh using:
ALTER EXTERNAL TABLE ext_tab_json_external REFRESH;
5️⃣ Query the External Table
SELECT * FROM ext_tab_json_external;
To access nested fields:
SELECT
customer_data:id::INT AS id,
customer_data:name::STRING AS name,
customer_data:address.city::STRING AS city
FROM ext_tab_json_external;
6️⃣ Optional: Load into Snowflake Table for Performance
CREATE OR REPLACE TABLE ext_table_json (json_customer_data VARIANT);
COPY INTO ext_table_json
FROM @ext_s3_stage_ext_table
FILE_FORMAT = json_file_format;
SELECT * FROM ext_table_json;
SELECT
json_customer_data:id::INT AS id,
json_customer_data:name::STRING AS name,
json_customer_data:address.city::STRING AS city
FROM ext_table_json;
🧠 Understanding the VALUE Column
When querying or previewing external tables, you may see a VALUE column.
🔍 VALUE = internal Snowflake alias for the raw row from file
- ✅ It equals
$1(the entire row of JSON)
If you define:
customer_data VARIANT AS $1
Then customer_data and VALUE will be identical.
⚡ Performance Best Practices
To optimize the performance of External Tables in Snowflake, especially when working with large volumes of semi-structured data stored in cloud buckets (like S3), apply the following best practices:
✅ 1️⃣ Partition External Data in Cloud Storage
Organize your files in partition-friendly folder structures, especially by fields you frequently filter on (e.g. date, region, type).
🟢 Recommended Folder Format (S3, Azure, or GCS):
s3://my-bucket/data/year=2024/month=07/day=04/yourfile.json
⛔ Avoid this (Snowflake cannot infer partition info):
s3://my-bucket/data/2024/07/04/yourfile.json
❗ Snowflake requires folders in the key=value format for partition pruning to work.
✅ 2️⃣ Define Partition Columns in External Table
Use the METADATA$DIRECTORY function to extract folder names.
CREATE EXTERNAL TABLE my_ext_table_partitioned (
data VARIANT,
year STRING AS (METADATA$DIRECTORY), -- returns 'year=2024'
month STRING AS (METADATA$DIRECTORY), -- returns 'month=07'
day STRING AS (METADATA$DIRECTORY) -- returns 'day=04'
)
PARTITION BY (year, month, day)
WITH LOCATION = @my_stage
FILE_FORMAT = json_file_format;
📌 Each partition column will hold values like:
'year=2024''month=07''day=04'
✅ 3️⃣ Filter by Partition Columns in Queries
To take advantage of partition pruning (i.e. scanning only relevant files):
SELECT data:id
FROM my_ext_table_partitioned
WHERE year = 'year=2024' AND month = 'month=07';
🧠 Important:
- You must include the full string (
'year=2024'), not just'2024'. - Partition pruning only works if these conditions are directly in the WHERE clause (no functions applied).
✅ 4️⃣ Use AUTO_REFRESH = FALSE Unless Necessary
For most external tables, it’s best to manually refresh metadata:
ALTER EXTERNAL TABLE my_ext_table_partitioned REFRESH;
This avoids unnecessary background processing and cost.
✅ 5️⃣ Consider Materialized Views or Internal Tables for Heavy Queries
If you frequently query the data, consider copying it into an internal table:
CREATE OR REPLACE TABLE internal_copy AS
SELECT *
FROM my_ext_table_partitioned
WHERE year = 'year=2024';
Or create a materialized view with clustering:
CREATE MATERIALIZED VIEW mv_sales
CLUSTER BY (data:id)
AS
SELECT data:id, data:name
FROM my_ext_table_partitioned;
✅ 6️⃣ Avoid Excessive Flattening
Use LATERAL FLATTEN only after filtering the data:
SELECT f.value:product_id, f.value:qty
FROM my_ext_table_partitioned,
LATERAL FLATTEN(input => data:items) f
WHERE year = 'year=2024';
✅ 7️⃣ Project Only Required Columns
Instead of SELECT *, always write explicit projections:
SELECT data:name, data:email
FROM my_ext_table_partitioned;
This reduces data scanned and improves query speed.
✅ Summary
| Best Practice | Description |
|---|---|
key=value folders | Required for Snowflake partition pruning |
METADATA$DIRECTORY | Extracts partition values from folder names |
Use PARTITION BY | Enables Snowflake to skip irrelevant partitions |
Use full values ('year=2024') | Required when filtering partition columns |
| Flatten only when needed | Avoid unnecessary cost by flattening after filters |
Use COPY INTO or materialized views | For optimized performance on repeated queries |
- ✅ Use Materialized Views If You Need Clustering
CREATE MATERIALIZED VIEW my_mv CLUSTER BY (data:id)
AS
SELECT data:id::INT AS id, data:name::STRING AS name
FROM my_ext_table_json;
-
✅ **Avoid SELECT *** Explicitly project only the needed fields.
-
✅ Avoid Unnecessary Flattening Use
LATERAL FLATTENonly when needed, and filter early.
📘 Summary Table
| Step | Command / Example |
|---|---|
| Storage Integration | CREATE STORAGE INTEGRATION |
| External Stage | CREATE STAGE |
| File Format | CREATE FILE FORMAT |
| External Table | CREATE EXTERNAL TABLE |
| Query | SELECT * FROM ext_table |
| Access Nested Data | data:field or FLATTEN() |
| Materialize/Load | COPY INTO or CREATE TABLE AS |
✅ Final Notes
- You can reuse the same Storage Integration for JSON, CSV, Parquet files.
- Use different file formats and stages for each format.
- Use FILE_PATTERN if formats are mixed in one folder.
- Always use
PARSE_JSON()orVARIANT AS $1when dealing with nested or arrayed data.