I actually measured the processing time and cost of Snowflake External Tables, Tasks, and Snowpipe
This page has been translated by machine translation. View original
Introduction
I'm kasama from the Data Business Division.
There are several patterns for using S3 data in Snowflake, so this time I'd like to compare and verify the processing time and cost of each approach.
Prerequisites
Intended Use Case
This article assumes a bronze/silver/gold architecture's bronze layer (the raw data ingestion layer). The bronze table is referenced by subsequent dbt models in daily batch jobs.
The number of times the bronze table is referenced per day is the sum of the number of downstream models and the number of tests written in source. In this article, we calculate the monthly cost for cases where the reference count is 1, 5, or 20 times per day.
4 Configurations
| Configuration | How S3 data is handled | Compute resources and billing for ingestion | Tables measured |
|---|---|---|---|
| External Table | Not loaded into a Snowflake table. Reads S3 files on every reference | None | EXTERNAL_ORDERS_* |
| user-managed Task | Loads S3 files into a Snowflake table via COPY INTO in a Task | User-created Warehouse. Minimum 60 seconds billed each time it starts | USER_MANAGED_ORDERS_* |
| serverless Task | Loads S3 files into a Snowflake table via COPY INTO in a Task | Compute resources provided by Snowflake. Billed per second for actual usage | SERVERLESS_ORDERS_* |
| Snowpipe | Receives S3 event notifications and Snowpipe loads in the same way | Compute resources provided by Snowflake. Billed based on the amount of data ingested | SNOWPIPE_ORDERS_* |
The two Tasks execute the same COPY INTO. The difference is in billing: serverless Task has no 60-second minimum charge.
Implementation
The implementation code is stored on GitHub.
.
├── terraform/
│ ├── versions.tf
│ ├── providers.tf
│ ├── variables.tf
│ ├── s3.tf
│ ├── iam.tf
│ ├── database.tf
│ ├── stage.tf
│ ├── warehouse.tf
│ ├── table.tf
│ ├── task.tf
│ ├── pipe.tf
│ └── outputs.tf
├── scripts/
│ └── generate_data.py
└── sql/
├── 01_refresh_external.sql
├── 02_run_tasks.sql
├── 03_reset_snowpipe.sql
├── 04_reference.sql
├── 05_measure.sql
└── check.sql
The environment is built with Terraform, and all Snowflake operations are performed via SQL files. Python is used only for generating test data.
Terraform
iam.tf creates the IAM role for Snowflake to read S3. The trust policy sets the IAM user ARN and External ID obtained from the Storage Integration's describe_output. Permissions are limited to read-only access under cost-bench/. Since the Storage Integration uses the IAM role's ARN and the IAM role uses the Storage Integration's values, they reference each other. Therefore, the role ARN passed to the Storage Integration is assembled as a string from the account ID and role name in variables.tf. This approach allows creating both AWS and Snowflake resources in a single apply.
stage.tf creates the Storage Integration, CSV file format, and external stage. The stage specifies depends_on to ensure it is created after the IAM role and policy are ready.
s3.tf creates the S3 bucket for test data and the event notifications for Snowpipe. Notifications are sent per prefix of cost-bench/snowpipe/<size>/ to the SQS queue owned by the Pipe for that size.
warehouse.tf creates 4 Gen2 XS Warehouses: one for reference queries and three for user-managed Tasks, one per size. The reason for separating user-managed Task Warehouses by size is to retrieve per-size billing separately from WAREHOUSE_METERING_HISTORY.
table.tf creates external tables and destination tables for ingestion. The external table converts CSV columns 1–9 (value:c1 to value:c9) with type casting.
refresh_on_create = false is set because no data exists in S3 at creation time and the IAM role configuration may not yet be reflected. The file list is loaded with 01_refresh_external.sql after placing the test data.
task.tf creates user-managed Tasks and serverless Tasks. Both Tasks execute the same COPY INTO; the only difference is the compute resource specification. user-managed Task specifies a Warehouse in warehouse, while serverless Task specifies a range of compute resource sizes (XSMALL to SMALL).
FORCE = TRUE is added to COPY INTO. Since COPY INTO skips files that have already been ingested, this was specified to measure three times with the same files. Tasks are manually executed with EXECUTE TASK in 02_run_tasks.sql.
pipe.tf creates Pipes with AUTO_INGEST enabled, one per size. Each Pipe ingests files placed under snowpipe/<size>/ into SNOWPIPE_ORDERS_<size>.
versions.tf, providers.tf, variables.tf, database.tf, and outputs.tf only contain provider configuration, variables, database, and bucket name output, so their explanations are omitted. State is stored locally in terraform.tfstate.
Python
generate_data.py creates a 9-column CSV and uploads it to S3. It writes until one file reaches approximately 200MB, uploads it, deletes the local file, and then creates the next file. The random seed is fixed per size, so the same data is generated regardless of how many times it is run.
SQL
SQL files 01 through 04 are divided by measurement step. 02_run_tasks.sql truncates the destination tables and then executes the 6 Tasks with EXECUTE TASK. 04_reference.sql disables the result cache and then reads 12 tables with CTAS.
05_measure.sql creates a result table from Snowflake history. First, it stores the database creation time as the variable BENCH_START to use as the start time for aggregation.
Warehouse billing history is recorded hourly and persists by name even after the Warehouse is deleted. Therefore, only history from this timestamp onward is aggregated to avoid mixing in past billing from a Warehouse recreated with the same name.
Next, it aggregates reference seconds (REFERENCE_SEC), Task ingestion seconds (TASK_SEC), Snowpipe ingestion seconds (SNOWPIPE_SEC), and ingestion Credits (INGEST_CREDITS) into temporary tables, and joins them by configuration and size in RESULT. The last two SELECTs produce a results table and a monthly cost table from RESULT, so the entire file should be executed in the same session.
PIPE_USAGE_HISTORY returns an empty Pipe name if the PIPE_NAME argument is not specified. Therefore, Snowpipe billing is retrieved by calling the function for each Pipe individually.
Deployment
The following commands are executed in the 71_snowflake_ingestion_cost_bench directory.
1. Create the Environment
export AWS_PROFILE='<your-profile>'
export SNOWFLAKE_ORGANIZATION_NAME='<your-organization-name>'
export SNOWFLAKE_ACCOUNT_NAME='<your-account-name>'
export SNOWFLAKE_USER='<your-user>'
export SNOWFLAKE_AUTHENTICATOR='PROGRAMMATIC_ACCESS_TOKEN'
export SNOWFLAKE_TOKEN='<your-pat>'
terraform -chdir=terraform init
terraform -chdir=terraform apply
On the first apply, Pipe creation may fail because the Pipes are created before the IAM role configuration is reflected. In that case, wait about 30 seconds and apply again.
2. Create Test Data
Upload scripts/generate_data.py to AWS CloudShell and execute it. Running it in CloudShell allows uploading to S3 without using your local network connection.
python3 generate_data.py --bucket <bucket_name>
<bucket_name> can be confirmed with terraform -chdir=terraform output bucket_name. When executed, CSVs of 3 sizes are placed in s3://<bucket_name>/cost-bench/<size>/ as follows.
uploaded s3://<bucket_name>/cost-bench/10MB/orders_0000.csv (10,110,285 bytes)
10MB: 1 files, 10,110,285 bytes, 81,000 rows
uploaded s3://<bucket_name>/cost-bench/100MB/orders_0000.csv (100,029,855 bytes)
100MB: 1 files, 100,029,855 bytes, 795,000 rows
uploaded s3://<bucket_name>/cost-bench/1GB/orders_0000.csv (200,001,388 bytes)
uploaded s3://<bucket_name>/cost-bench/1GB/orders_0001.csv (200,102,463 bytes)
uploaded s3://<bucket_name>/cost-bench/1GB/orders_0002.csv (200,096,401 bytes)
uploaded s3://<bucket_name>/cost-bench/1GB/orders_0003.csv (200,104,362 bytes)
uploaded s3://<bucket_name>/cost-bench/1GB/orders_0004.csv (199,714,234 bytes)
1GB: 5 files, 1,000,018,848 bytes, 7,885,000 rows
Measurement
SQL files are executed in a Snowsight worksheet or with snow sql -f. For each configuration, ingestion and reference are executed in the following steps.
1. Load the File List into the External Table
Execute sql/01_refresh_external.sql. The external table references only the files loaded by this operation.
2. Ingest 3 Times with user-managed Task and serverless Task
Execute sql/02_run_tasks.sql 3 times. Allow 2.5 minutes between each run so the user-managed Task's Warehouse auto-suspends before the next run. Task completion is confirmed with sql/check.sql.
3. Ingest 3 Times with Snowpipe
Empty the destination table with sql/03_reset_snowpipe.sql, then copy the test data within S3 to the Snowpipe folder.
for SIZE in 10MB 100MB 1GB; do
aws s3 cp --recursive \
s3://<bucket_name>/cost-bench/${SIZE}/ \
s3://<bucket_name>/cost-bench/snowpipe/${SIZE}/run1/
done
Upon receiving the notification for the copied files, Snowpipe begins ingestion. When sql/check.sql confirms the row count matches the test data, repeat the same operation with the folder name changed to run2 and run3.
4. Reference Tables from 4 Configurations 3 Times
Execute sql/04_reference.sql 3 times. This SQL reads tables from all 4 configurations once each using the same CTAS (CREATE OR REPLACE TRANSIENT TABLE ... AS SELECT * FROM ...) as a dbt table model.
To prevent Warehouse startup time from being included in the first CTAS, the Warehouse is started before the CTAS.
5. Aggregate Results
Execute sql/05_measure.sql. To redo the measurement, run terraform destroy and then start again from step 1 of the deployment procedure.
Verification Results
Verification Conditions
Test data consists of 9-column uncompressed CSV files. Three sizes were prepared as the amount ingested into one table per day.
| Size | Number of Files | Per File | Row Count |
|---|---|---|---|
| 10MB | 1 | ~10MB | 81,000 |
| 100MB | 1 | ~100MB | 795,000 |
| 1GB | 5 | ~200MB | 7,885,000 |
- Warehouse: All Gen2 XS,
AUTO_SUSPENDset to 60 seconds - user-managed Task: Executed on a dedicated Warehouse per size
- serverless Task: Compute resource size range is XSMALL to SMALL
- Number of runs: Task execution, Snowpipe ingestion, and reference CTAS were each run 3 times per configuration and size combination; the average is shown
Each metric was derived from the following history sources.
| Metric | How it was derived |
|---|---|
| Task ingestion seconds | From SCHEDULED_TIME to COMPLETED_TIME in TASK_HISTORY |
| Snowpipe ingestion seconds | From the time the last file was placed in S3 (last_modified from LIST) to the time the last file finished ingestion (LAST_LOAD_TIME in COPY_HISTORY) |
| Reference seconds | TOTAL_ELAPSED_TIME from QUERY_HISTORY_BY_WAREHOUSE |
| Ingestion Credits | The sum of 3 runs from WAREHOUSE_METERING_HISTORY, SERVERLESS_TASK_HISTORY, and PIPE_USAGE_HISTORY, divided by 3 |
Snowpipe ingestion seconds also include the time for the notification to arrive at Snowpipe from S3.
Credits are billed per configuration as follows.
| Compute Resource | Configurations Used | Billing Mechanism |
|---|---|---|
| Warehouse | user-managed Task, reference | Billed per second while running. Minimum 60 seconds billed each time it starts or resumes. Gen2 XS is 1.35 credits/hour |
| serverless | serverless Task | Computes the time used by compute resources per second (rounding up fractions of a second) and multiplies by a factor of 0.9. When running on XS, it is 0.9 credits/hour |
| Snowpipe | Snowpipe | Multiplies the amount of data ingested by 0.0037 credits per GB |
The unit price figures are listed in the Snowflake Service Consumption Table. Gen2 XS at 1.35 credits/hour is in Table 1(b), and the serverless Task multiplier of 0.9 and Snowpipe's 0.0037 credits/GB are in Table 5.
Results by Configuration
When you run the entire sql/05_measure.sql, the second-to-last SELECT outputs ingestion seconds, ingestion Credits, and reference seconds per configuration and size. Since external tables do not perform ingestion, ingestion seconds will be null.
SELECT
DECODE(
CONFIG, 'EXTERNAL', '外部テーブル', 'USER_MANAGED', 'user-managed Task',
'SERVERLESS', 'serverless Task', 'SNOWPIPE', 'Snowpipe'
) AS "構成",
SIZE AS "サイズ",
INGEST_SEC AS "取り込み秒",
ROUND(INGEST_CREDITS, 6) AS "取り込みCredits",
REFERENCE_SEC AS "参照秒"
FROM RESULT
ORDER BY DECODE(SIZE, '10MB', 1, '100MB', 2, 3), DECODE(CONFIG, 'EXTERNAL', 1, 'USER_MANAGED', 2, 'SERVERLESS', 3, 4);
For external tables, the CTAS time was more than twice that of the 3 configurations that loaded data into Snowflake tables.
For user-managed Task, regardless of ingestion time (2 to 10 seconds), approximately 60 to 70 seconds worth of Credits are consumed. This is because the Warehouse continues running for 60 seconds after COPY INTO completes due to AUTO_SUSPEND, and billing continues during that time.
Snowpipe ingestion seconds were approximately 44 seconds even for 10MB. This is likely because a fixed amount of time is required from when the S3 notification arrives until ingestion begins, regardless of data size.
Monthly Cost per Table
The monthly cost was calculated using the following formula.
Reference Credits = Reference seconds × 1.35 ÷ 3600 (Gen2 XS 1.35 credits/hour converted to per-second rate)
Daily Credits = Ingestion Credits + Reference Credits × Number of references
Monthly cost (JPY) = Daily Credits × 688 JPY × 30 days
- References are assumed to be executed on a Warehouse that is already running for the dbt batch. Therefore, the reference cost is calculated based on the actual seconds elapsed.
- user-managed Task ingestion is assumed to start a dedicated Warehouse for the Task. The actual measured value including the 60-second wait time is used as-is.
- 1 credit is $4.30 for Enterprise Edition (AWS Tokyo), converted at $1 = 160 JPY to 688 JPY.
- Snowflake storage costs and Cloud Services fees are not included.
The last SELECT outputs the monthly cost (JPY) per configuration and size, broken down by number of references.
SELECT
DECODE(
CONFIG, 'EXTERNAL', '外部テーブル', 'USER_MANAGED', 'user-managed Task',
'SERVERLESS', 'serverless Task', 'SNOWPIPE', 'Snowpipe'
) AS "構成",
SIZE AS "サイズ",
ROUND((INGEST_CREDITS + 1 * REFERENCE_SEC * 1.35 / 3600) * 688 * 30) AS "月額(参照1回/日)",
ROUND((INGEST_CREDITS + 5 * REFERENCE_SEC * 1.35 / 3600) * 688 * 30) AS "月額(参照5回/日)",
ROUND((INGEST_CREDITS + 20 * REFERENCE_SEC * 1.35 / 3600) * 688 * 30) AS "月額(参照20回/日)"
FROM RESULT
ORDER BY DECODE(SIZE, '10MB', 1, '100MB', 2, 3), DECODE(CONFIG, 'EXTERNAL', 1, 'USER_MANAGED', 2, 'SERVERLESS', 3, 4);
The difference in monthly cost between external tables and serverless Task grows larger as the number of references and size increase. At 20 references/day, the monthly cost for external tables vs. serverless Task is 325 JPY vs. 142 JPY for 10MB, 1,486 JPY vs. 266 JPY for 100MB, and 2,616 JPY vs. 783 JPY for 1GB. The cause of this difference is the processing time per reference. Since all 4 configurations run references on the same Warehouse (Gen2 XS), reference costs are proportional to reference seconds. For 1GB, reference seconds are 16.9 seconds vs. 4.7 seconds, making the cost per reference approximately 4.36 JPY vs. approximately 1.21 JPY.
The difference between serverless Task and Snowpipe was within a few tens of JPY per month under any condition. user-managed Task, assuming a dedicated Warehouse is started, costs at least approximately 480 JPY per month regardless of size.
Conclusion
The characteristics of the 4 configurations are as follows. Monthly costs are per table and represent the range for 10MB to 1GB and 1 to 20 references per day.
| Configuration | Time for ingestion | Reference (CTAS) time: 10MB / 100MB / 1GB | Monthly cost | Copy of data to Snowflake | Source for Dynamic Table |
|---|---|---|---|---|---|
| External Table | No ingestion | 2.1 / 9.6 / 16.9 sec | 16–2,616 JPY | Not held | Cannot be used |
| user-managed Task | 2.4–9.6 sec | 0.9 / 1.4 / 4.7 sec | 479–1,256 JPY | Held | Can be used |
| serverless Task | 2.3–10.7 sec | 0.9 / 1.6 / 4.7 sec | 10–783 JPY | Held | Can be used |
| Snowpipe | 39.3–55.8 sec | 0.9 / 1.8 / 5.1 sec | 8–861 JPY | Held | Can be used |
For ingestion, if it's a batch job that runs at a fixed time, choose serverless Task. If it's an event-driven job that runs each time a file arrives, choose Snowpipe. Since the monthly costs of the two are nearly identical under any condition, the choice comes down to how the job is triggered.
The official documentation recommends serverless Task when the number of simultaneously running Tasks is small or they finish in a short time. Bronze layer ingestion like in this article, where a once-daily COPY INTO completes in around 10 seconds, fits this condition.
user-managed Task is recommended when multiple Tasks run simultaneously and make full use of the Warehouse. For example, a pipeline where one Warehouse is prepared for overnight batch jobs. COPY INTO Tasks for dozens of sources and subsequent aggregation Tasks are executed on this Warehouse during the same time window. Warehouse billing is determined by the number of seconds it is running, not by the number of simultaneously running Tasks. The additional cost of adding a Task to a running Warehouse is only the amount by which the Warehouse's running time is extended. Tasks requiring compute resources exceeding the equivalent of XXLARGE cannot be run as serverless Tasks and must be run as user-managed Tasks.
Snowpipe ingests files each time they arrive at the stage. It is suitable for cases such as writing application logs to S3 every few minutes and running downstream processing in the order they arrive. Snowpipe normally loads within 1 minute of detecting a file, but latency is not guaranteed. The 39.3 to 55.8 seconds measured from placing files in S3 to completion of ingestion in this test also fell within 1 minute.
External tables were not the cheapest configuration under any condition. Since the processing time per reference is more than twice that of other configurations, the monthly cost becomes higher than other configurations as the number of references and size increase. The official documentation also states that queries on external tables may be slower than on Snowflake tables. External tables are suited for cases where only a portion of large amounts of data accumulated in S3 needs to be referenced and the rest should not be loaded into Snowflake. Setting partitions by date means only files for the referenced date are read. However, since those files are parsed on every reference, the time to read one day's worth is the same as measured here—more than twice that of configurations that ingested the data. A rough guideline for file size is 256–512MB for Parquet and 16–256MB for other formats. For Parquet, Iceberg tables are recommended over external tables.
Dynamic Tables cannot use external tables as a source. If you use Dynamic Tables in the silver layer or beyond, choose a configuration that ingests data.
Closing
The results in this article were measured under specific conditions: 1 table, 9-column uncompressed CSV, and a Gen2 XS Warehouse. The factors that inform configuration choices vary depending on conditions such as file format, daily ingestion volume, number of references, and whether the Warehouse is shared with other workloads. Please treat this as one example for reference purposes.