Hot and Cold Tiering for Event Data: Snowflake, S3 Parquet and Snowpipe

Last updated: October 2026

High-volume event data has an awkward cost profile. It arrives continuously, it accumulates quickly, and most of it is rarely queried after the first week. Keeping all of it on the same fast, expensive storage and compute means paying peak prices for data that is mostly idle.

This article explains the tiered design we built for a programmatic advertising platform, described in our ad-tech case study. The client's 32-node Redshift cluster cost $47,000 a month, and long attribution queries from analysts were slowing down the ingestion pipeline that the bidding system depended on. The project figures come from that engagement. The configuration examples are illustrative, written for this article.

Find the line between hot and cold

The design rests on one measurement: how far back do queries actually reach? On this platform, most analytical queries touched only the last 7 days of data, yet 18 months of impression history sat on hot Redshift storage. About 95% of analyst queries fell inside the 7-day window.

We recommend measuring this from the query history before choosing a boundary. Base it on the date filters in the queries that actually run each day, and set the hot window to cover those queries with some margin. Quarterly reports and occasional investigations can tolerate slower queries on the cold tier.

Ingestion: Firehose to S3 to Snowpipe

The old pipeline ran Redshift COPY jobs every 15 minutes, and they sometimes fell hours behind. The replacement sends impression events to a Kinesis stream, where Firehose buffers them and writes Parquet files to S3, and Snowpipe loads each file into Snowflake as soon as S3 reports it. End-to-end latency stayed under three minutes at 8 billion events a day.

Kinesis Data Firehose was renamed Amazon Data Firehose in February 2024. Its APIs, CLI commands and CloudWatch metrics kept their names, so older documentation and scripts still apply. Three Firehose details matter for this design:

• Parquet conversion needs a schema. Firehose converts JSON records to Parquet using a table definition in the AWS Glue Data Catalog. Records in other formats have to be converted to JSON first, typically with a Lambda function.
• The buffer has a floor. With format conversion enabled, the buffer size cannot be set below 64 MB, and the default rises to 128 MB. The buffer interval can be set between 0 and 900 seconds. We used a 60-second interval with a 128 MB buffer, so Firehose writes a file every minute or every 128 MB, whichever comes first.
• Dynamic partitioning is a creation-time choice. It can only be enabled when the delivery stream is created and cannot be turned off later, so decide on the S3 prefix layout before building the stream.

Those settings line up well with Snowpipe. Snowflake recommends files of roughly 100 to 250 MB compressed and, for slower feeds, about one file per minute, and its documentation names Firehose with a 60-second interval as a suitable way to batch them. Sending smaller files more often does not reliably reduce latency.

-- One-time: let Snowflake read the bucket through an IAM role.
CREATE STORAGE INTEGRATION s3_events_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<account-id>:role/snowflake-events-read'
  STORAGE_ALLOWED_LOCATIONS = ('s3://<bucket>/impressions/');

CREATE STAGE raw.impressions_stage
  URL = 's3://<bucket>/impressions/'
  STORAGE_INTEGRATION = s3_events_int
  FILE_FORMAT = (TYPE = PARQUET);

CREATE PIPE raw.impressions_pipe
  AUTO_INGEST = TRUE
AS
  COPY INTO raw.impressions
  FROM @raw.impressions_stage
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

-- The notification_channel column holds the SQS queue ARN to
-- configure as the S3 bucket's event notification target.
SHOW PIPES LIKE 'IMPRESSIONS_PIPE' IN SCHEMA raw;

DESC INTEGRATION returns the IAM user ARN and external ID to put in the role's trust policy. MATCH_BY_COLUMN_NAME loads Parquet columns by name, so column order in the files does not matter, and table columns with no match are loaded as NULL. To create the target table from the files themselves, INFER_SCHEMA with CREATE TABLE ... USING TEMPLATE reads the Parquet schema directly.

Snowpipe pricing changed during 2025. Since 8 December 2025, Standard and Enterprise accounts pay a fixed 0.0037 credits per GB loaded, with no warehouse charge and no per-file fee. Parquet is billed on its stored size, while CSV and JSON are billed on their uncompressed size, which is another reason to land events as Parquet.

Monitoring the pipe

A pipe that stops loading does not raise an error in the place anyone is looking, so we check it on a schedule. SYSTEM$PIPE_STATUS returns the execution state, the number of pending files and the time of the last successful load, and COPY_HISTORY lists every file loaded or rejected over the last 14 days.

-- Execution state, pending file count, last ingest time.
SELECT SYSTEM$PIPE_STATUS('raw.impressions_pipe');

-- Files loaded or rejected in the last hour.
SELECT file_name, status, row_count, first_error_message, last_load_time
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
    TABLE_NAME => 'RAW.IMPRESSIONS',
    START_TIME => DATEADD(hour, -1, CURRENT_TIMESTAMP())))
ORDER BY last_load_time DESC;

For history beyond 14 days, use the ACCOUNT_USAGE views. PIPE_USAGE_HISTORY shows the cost per pipe for up to a year. A pipe that stays paused for more than 14 days goes stale, which is worth knowing before pausing one for a long maintenance window.

The cold tier: Parquet on S3 with external tables

Data older than seven days moves to S3 as Parquet, partitioned by date and ad network, and is exposed through a Snowflake external table. Analysts query it with the same SQL as the hot tables. Queries are slower, and storage is much cheaper: moving the cold data to S3 cut storage costs for it by roughly 80%.

Partition columns on an external table are expressions over METADATA$FILENAME, which contains the file's path within the stage. Extracting the values with a regular expression keeps the definition working even if the stage prefix changes.

CREATE OR REPLACE EXTERNAL TABLE cold.impressions (
    event_date    DATE   AS TO_DATE(REGEXP_SUBSTR(METADATA$FILENAME, 'event_date=([0-9-]+)', 1, 1, 'e', 1)),
    ad_network_id NUMBER AS TO_NUMBER(REGEXP_SUBSTR(METADATA$FILENAME, 'ad_network_id=([0-9]+)', 1, 1, 'e', 1)),
    impression_id VARCHAR       AS (VALUE:impression_id::VARCHAR),
    campaign_id   NUMBER        AS (VALUE:campaign_id::NUMBER),
    event_ts      TIMESTAMP_NTZ AS (VALUE:event_ts::TIMESTAMP_NTZ)
)
PARTITION BY (event_date, ad_network_id)
LOCATION = @cold.impressions_archive/
AUTO_REFRESH = TRUE
FILE_FORMAT = (TYPE = PARQUET);

-- Filters on partition columns prune whole folders.
SELECT campaign_id, COUNT(*) AS impressions
FROM cold.impressions
WHERE event_date BETWEEN '2026-04-01' AND '2026-06-30'
GROUP BY campaign_id;

AUTO_REFRESH is on by default for external tables. On S3 it relies on the bucket sending event notifications to the SQS queue shown in the notification_channel column of SHOW EXTERNAL TABLES. Snowflake now also supports Apache Iceberg tables, which are worth evaluating for a cold tier that other engines need to read as well.

Moving 18 months of history out of Redshift

The historical backfill was a one-time Redshift UNLOAD to S3 in Parquet. UNLOAD can partition its output into Hive-style folders, which matches the external table layout above.

UNLOAD ('SELECT impression_id, campaign_id, event_ts,
                 event_date, ad_network_id
          FROM analytics.impressions
          WHERE event_date < CURRENT_DATE - 7')
TO 's3://<bucket>/impressions-archive/'
IAM_ROLE 'arn:aws:iam::<account-id>:role/redshift-unload'
FORMAT AS PARQUET
PARTITION BY (event_date, ad_network_id)
MAXFILESIZE 256 MB;

Three UNLOAD behaviours caught our attention. Partition columns are left out of the files unless you add INCLUDE after PARTITION BY, so they exist only in the folder names, which is why the external table derives them from the path. TIMESTAMPTZ columns lose their time zone in Parquet, so normalise them to UTC before unloading. And MAXFILESIZE is rounded down to a multiple of 32 MB, the row group size, so 256 MB produces files of about 256 MB while 200 MB produces files of about 192 MB.

What changed for the team

Isolation solved the original problem. Analysts run their attribution queries on a dedicated Large warehouse, and ingestion runs on its own, so a long analytical query no longer delays bid data. Average analyst query time fell from about 20 minutes to about 45 seconds, and compute costs fell from $47,000 to $19,000 a month.

Before the project, the client had tried Redshift Concurrency Scaling to separate the workloads, and the on-demand charges added more than $40,000 a month during peak periods. For this workload, the numbers stopped favouring Concurrency Scaling at about four hours of burst usage a day, beyond which a dedicated Snowflake warehouse cost less. The data team had also been spending about 60% of its time on DISTKEY and SORTKEY tuning, VACUUM operations and WLM queue configuration. On Snowflake, micro-partition pruning took care of the physical layout without manual tuning.

Fresher data also changed what the business asked for. With events arriving within minutes instead of in 15-minute batches that sometimes backed up, the bidding optimisation team started building models that respond to hourly trends instead of daily ones.

One planning note: decommissioning took longer than building. The Snowflake pipeline was in production within six weeks, and the remaining four weeks went on validating data parity, migrating downstream BI reports and getting compliance sign-off that the historical export was complete. We recommend planning that tail explicitly from the start.