Skip to main content

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_ARN
  • STORAGE_AWS_EXTERNAL_ID

You'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 = TRUE is 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 PracticeDescription
key=value foldersRequired for Snowflake partition pruning
METADATA$DIRECTORYExtracts partition values from folder names
Use PARTITION BYEnables Snowflake to skip irrelevant partitions
Use full values ('year=2024')Required when filtering partition columns
Flatten only when neededAvoid unnecessary cost by flattening after filters
Use COPY INTO or materialized viewsFor 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 FLATTEN only when needed, and filter early.


📘 Summary Table​

StepCommand / Example
Storage IntegrationCREATE STORAGE INTEGRATION
External StageCREATE STAGE
File FormatCREATE FILE FORMAT
External TableCREATE EXTERNAL TABLE
QuerySELECT * FROM ext_table
Access Nested Datadata:field or FLATTEN()
Materialize/LoadCOPY 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() or VARIANT AS $1 when dealing with nested or arrayed data.