External Tables — Definition & Use Cases
Traditionally, to query data using Snowflake, you had to physically ingest the data into Snowflake's proprietary storage format using COPY INTO or Snowpipe. This forces you to pay for Snowflake storage.
What if you have Petabytes of cold log data sitting in an AWS S3 Data Lake, and you only need to query it once a month? Moving that data into Snowflake is slow and expensive.
This is where External Tables come in. They allow Snowflake to act as a query engine over your external Data Lake.
Topics Covered
- What is an External Table?
- External vs Standard Tables
- Partitioning Architecture
- Common Use Cases
- Limitations
1. What is an External Table?
An External Table is a Snowflake object that stores file-level metadata about data files stored outside of Snowflake (in AWS S3, Azure Blob Storage, or GCP Cloud Storage).
When you run a SELECT query against an External Table, Snowflake's Virtual Warehouse reaches out to your cloud storage bucket, reads the raw files (CSV, JSON, Parquet), processes the data, and returns the result. The data itself never resides in Snowflake.
2. External vs Standard Tables
| Feature | Standard Table | External Table |
|---|---|---|
| Storage Location | Inside Snowflake (Proprietary format) | Outside Snowflake (AWS S3, Azure Blob, GCS) |
| Storage Cost | Paid to Snowflake (~$23/TB/mo) | Paid directly to AWS/Azure/GCP |
| Performance | Extremely Fast (Micro-partitions) | Slower (Limited by network and raw file format) |
| DML Operations | INSERT, UPDATE, DELETE allowed | Read-Only (Cannot insert or delete data via SQL) |
| Materialized Views | Supported | Supported (Crucial for performance) |
3. Hands-On: The Architecture
Let's assume an Indian Telecom company stores daily call detail records (CDRs) as Parquet files in AWS S3. We want to query these records from Snowflake.
Step 1: The External Stage
Before creating the table, the Account Admin must create an External Stage pointing to the S3 bucket.
(To create stages, verify your role by running SELECT CURRENT_ROLE();)
USE ROLE SYSADMIN;
CREATE OR REPLACE STAGE TELECOM_S3_STAGE
URL='s3://beems-telecom-data-lake/cdr/'
STORAGE_INTEGRATION = AWS_S3_INT
FILE_FORMAT = (TYPE = PARQUET);
Step 2: Creating the External Table
When creating an external table, you must define the schema by extracting the values from the raw VALUE variant column.
CREATE OR REPLACE EXTERNAL TABLE EXT_CALL_RECORDS (
-- Map the Parquet columns to Snowflake columns
CALL_ID VARCHAR AS (VALUE:call_id::VARCHAR),
CALLER_PHONE VARCHAR AS (VALUE:caller_phone::VARCHAR),
DURATION_SECONDS INT AS (VALUE:duration::INT),
CALL_DATE DATE AS (VALUE:call_date::DATE)
)
WITH LOCATION = @TELECOM_S3_STAGE
FILE_FORMAT = (TYPE = PARQUET)
AUTO_REFRESH = TRUE; -- Automatically refresh metadata when new files land in S3
Step 3: Querying the Table
To the end-user, it looks exactly like a normal table.
-- This query scans the S3 bucket directly!
SELECT
CALLER_PHONE,
SUM(DURATION_SECONDS) AS TOTAL_DURATION
FROM EXT_CALL_RECORDS
WHERE CALL_DATE = '2023-10-01'
GROUP BY CALLER_PHONE;
4. The Importance of Partitioning
If you run the query above, Snowflake will scan every single file in the S3 bucket to find records for '2023-10-01'. If you have 5 Petabytes of data, this query will take hours and burn massive compute credits.
To fix this, you must Partition your External Table. If your S3 bucket is organized into folders by date (e.g., s3://beems-telecom-data-lake/cdr/year=2023/month=10/day=01/), you can tell Snowflake to only scan files inside that specific folder.
-- Recreate the table with Partitioning
CREATE OR REPLACE EXTERNAL TABLE EXT_CALL_RECORDS (
-- Extract the date from the S3 folder path!
PARTITION_DATE DATE AS TO_DATE(SPLIT_PART(METADATA$FILENAME, '/', 3), 'YYYY-MM-DD'),
CALL_ID VARCHAR AS (VALUE:call_id::VARCHAR),
DURATION_SECONDS INT AS (VALUE:duration::INT)
)
PARTITION BY (PARTITION_DATE) -- Crucial for performance
WITH LOCATION = @TELECOM_S3_STAGE
FILE_FORMAT = (TYPE = PARQUET);
Now, when you add WHERE PARTITION_DATE = '2023-10-01', Snowflake skips 99.9% of the S3 bucket and only scans the relevant folder.
5. Common Use Cases
- The Data Lake Query Engine: Your data science team uses Databricks to write massive Parquet datasets to S3. You want business analysts to query that data using Snowflake SQL without duplicating the data.
- Cold Data Archival: You keep the last 1 year of sales data in a fast Standard Table. You offload the previous 10 years of sales data to an S3 bucket and put an External Table on top of it.
- Data Staging: Instead of using
COPY INTOto ingest raw JSON into a Snowflake table before cleaning it, you use an External Table to view the JSON directly in S3, and clean it during the ingestion into the Silver layer.
Limitations
- Read-Only: You cannot run
INSERT,UPDATE, orDELETEon an external table. - Performance: Querying S3 across the internet is inherently slower than querying Snowflake's internal micro-partitions. For high-performance BI dashboards, you must build a Materialized View on top of the External Table to cache the results inside Snowflake.
Summary
- External Tables allow Snowflake to query data sitting in AWS S3, Azure Blob, or GCP Cloud Storage.
- They are Read-Only and define schema over raw files (preferably Parquet).
- They save on storage costs but are slower than Standard Tables.
- You must use Partitioning (mapping S3 folder structures to a column) to ensure query performance.