Snowflake
Snowflake reads Send2Bucket data in two ways. For Amazon S3, Google Cloud Storage and S3-compatible destinations, you point a stage at the s2b/ prefix and either query the objects in place through an external table or load them into a native table with a VARIANT column. For Apache Iceberg and Amazon S3 Tables destinations, a catalog integration exposes the table directly. The record layout is in Delivery format.
This page follows Snowflake's documentation. We have not run these statements against Snowflake ourselves; if one fails for you, tell us and we will fix the page.
Each section below ends with a view named s2b.records with the same columns, signal, received_at, delivery_batch_id, resource and data, so the example queries run against any of them.
Connect a bucket
Amazon S3
Create a storage integration, which holds the IAM role Snowflake assumes:
CREATE STORAGE INTEGRATION s2b_s3
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<account_id>:role/snowflake-s2b-read'
STORAGE_ALLOWED_LOCATIONS = ('s3://<bucket>/<prefix>/s2b/');
DESC INTEGRATION s2b_s3;
DESC INTEGRATION returns STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID. Put them in the role's trust policy:
{
"Version": "2012-10-17",
"Statement": [{
"Effect": "Allow",
"Principal": {"AWS": "<STORAGE_AWS_IAM_USER_ARN>"},
"Action": "sts:AssumeRole",
"Condition": {"StringEquals": {"sts:ExternalId": "<STORAGE_AWS_EXTERNAL_ID>"}}
}]
}
Give the role read access: s3:GetObject and s3:GetObjectVersion on arn:aws:s3:::<bucket>/<prefix>/s2b/*, and s3:ListBucket and s3:GetBucketLocation on arn:aws:s3:::<bucket>. Use a separate role from the one Send2Bucket writes with.
Then a file format and a stage:
CREATE SCHEMA IF NOT EXISTS s2b;
CREATE FILE FORMAT s2b.ndjson_gz TYPE = JSON COMPRESSION = GZIP;
CREATE STAGE s2b.raw
STORAGE_INTEGRATION = s2b_s3
URL = 's3://<bucket>/<prefix>/s2b/'
FILE_FORMAT = s2b.ndjson_gz;
The role that creates the stage needs USAGE on the integration (GRANT USAGE ON INTEGRATION s2b_s3 TO ROLE <role>). Users who only query need USAGE on the stage.
Google Cloud Storage
CREATE STORAGE INTEGRATION s2b_gcs
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'GCS'
ENABLED = TRUE
STORAGE_ALLOWED_LOCATIONS = ('gcs://<bucket>/<prefix>/s2b/');
DESC STORAGE INTEGRATION s2b_gcs;
STORAGE_GCP_SERVICE_ACCOUNT in the output is the service account Snowflake uses for your account. On the bucket, grant it a custom role with storage.buckets.get, storage.objects.get and storage.objects.list. Then create the file format as for S3, and the stage with STORAGE_INTEGRATION = s2b_gcs and URL = 'gcs://<bucket>/<prefix>/s2b/'.
S3-compatible storage
S3-compatible stages take the endpoint and a key directly:
CREATE STAGE s2b.raw
URL = 's3compat://<bucket>/<prefix>/s2b/'
ENDPOINT = '<endpoint_host>'
CREDENTIALS = (AWS_KEY_ID = '<access_key_id>' AWS_SECRET_KEY = '<secret_access_key>')
FILE_FORMAT = s2b.ndjson_gz;
Snowflake requires the endpoint to use HTTPS with a valid certificate and virtual-hosted-style requests, and ENDPOINT is a host name without a port. See Working with Amazon S3-compatible storage for the vendors Snowflake lists as tested. Neither external-table auto-refresh nor Snowpipe auto-ingest works with S3-compatible stages, so refresh or load on a schedule.
Query objects in place
An external table lists the objects under the stage and reads them at query time. Partition columns come from the object key in METADATA$FILENAME. Counting path segments from the end (a negative SPLIT_PART index) works whatever depth your <prefix> has:
CREATE EXTERNAL TABLE s2b.raw_records (
stream_id VARCHAR AS SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', -4), '=', 2),
received_date DATE AS TO_DATE(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', -3), '=', 2)),
received_hour NUMBER AS TO_NUMBER(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', -2), '=', 2)),
signal VARCHAR AS (VALUE:signal::VARCHAR),
received_at TIMESTAMP_TZ AS (VALUE:received_at::TIMESTAMP_TZ),
delivery_batch_id VARCHAR AS (VALUE:delivery_batch_id::VARCHAR)
)
PARTITION BY (stream_id, received_date, received_hour)
LOCATION = @s2b.raw/
PATTERN = '.*stream=.*[.]ndjson[.]gz'
FILE_FORMAT = (FORMAT_NAME = 's2b.ndjson_gz')
AUTO_REFRESH = TRUE;
CREATE VIEW s2b.records AS
SELECT stream_id, received_date, received_hour, signal, received_at, delivery_batch_id,
VALUE:resource AS resource, VALUE:data AS data
FROM s2b.raw_records;
VALUEis the whole line as aVARIANT. A nested field isVALUE:data.event::VARCHAR: a colon after the column, dots below that. Field names are case-sensitive.PATTERNkeeps the destination's test objects unders2b/_verify/out of the table.- Filter on
received_dateandreceived_hour: Snowflake skips the objects in other partitions.
AUTO_REFRESH registers new objects from storage events. For S3, run SHOW EXTERNAL TABLES, copy the SQS ARN from notification_channel, and add an S3 event notification for all object-create events on <prefix>/s2b/ that sends to that queue. For Cloud Storage, auto-refresh needs a Pub/Sub notification integration; see Snowflake's Cloud Storage external table guide. Without events, run ALTER EXTERNAL TABLE s2b.raw_records REFRESH; on a schedule. Auto-refresh is billed like Snowpipe, by the number of files registered.
Load into a native table
Native tables are faster for repeated queries than reading gzip JSON through an external table. Load every object once, keep the envelope fields as columns and the nested parts as VARIANT:
CREATE TABLE s2b.records_native (
delivery_batch_id VARCHAR,
batch_id VARCHAR,
received_at TIMESTAMP_TZ,
signal VARCHAR,
resource VARIANT,
scope VARIANT,
data VARIANT,
file_name VARCHAR
);
Snowpipe (auto-ingest)
CREATE PIPE s2b.records_pipe AUTO_INGEST = TRUE AS
COPY INTO s2b.records_native
FROM (
SELECT $1:delivery_batch_id::VARCHAR, $1:batch_id::VARCHAR, $1:received_at::TIMESTAMP_TZ,
$1:signal::VARCHAR, $1:resource, $1:scope, $1:data, METADATA$FILENAME
FROM @s2b.raw
)
PATTERN = '.*stream=.*[.]ndjson[.]gz';
DESC PIPE s2b.records_pipe;
For S3, add an event notification on <prefix>/s2b/ to the SQS queue in the pipe's notification_channel, as for the external table. For Cloud Storage, create a Pub/Sub subscription and a notification integration and pass it as INTEGRATION = '<name>' on the pipe; see Automating Snowpipe for Google Cloud Storage. ALTER PIPE s2b.records_pipe REFRESH; queues objects staged in the last 7 days that the pipe has not loaded, which covers objects written before the notification existed.
Snowpipe runs on serverless compute, billed per GB loaded.
Scheduled COPY INTO
Without notifications (S3-compatible storage, or if you prefer a fixed schedule), run the same COPY INTO from a task:
CREATE TASK s2b.load_records
WAREHOUSE = <warehouse>
SCHEDULE = '60 MINUTE'
AS
COPY INTO s2b.records_native
FROM (
SELECT $1:delivery_batch_id::VARCHAR, $1:batch_id::VARCHAR, $1:received_at::TIMESTAMP_TZ,
$1:signal::VARCHAR, $1:resource, $1:scope, $1:data, METADATA$FILENAME
FROM @s2b.raw
)
PATTERN = '.*stream=.*[.]ndjson[.]gz';
ALTER TASK s2b.load_records RESUME;
Duplicates
Each object is one delivery batch, and Send2Bucket never changes an object once written. Snowflake keeps load metadata per file, 14 days for a pipe and 64 days for bulk COPY INTO on the target table, and skips files it has already loaded, so retries and reruns add nothing. If you reload by hand (with FORCE = TRUE, or after the metadata expired), copy into a staging table with the same columns and insert only the delivery batches that are not there yet:
INSERT INTO s2b.records_native
SELECT * FROM s2b.records_staging
WHERE delivery_batch_id NOT IN (SELECT delivery_batch_id FROM s2b.records_native);
Point the view at the native table:
CREATE OR REPLACE VIEW s2b.records AS
SELECT signal, received_at, delivery_batch_id, resource, data
FROM s2b.records_native;
Connect an Iceberg table
Iceberg destinations need no load. A catalog integration connects Snowflake to the same catalog Send2Bucket commits to, and Snowflake reads the table's Parquet files. The table is read-only from Snowflake's side.
Apache Iceberg (REST catalog)
CREATE CATALOG INTEGRATION s2b_iceberg
CATALOG_SOURCE = ICEBERG_REST
TABLE_FORMAT = ICEBERG
CATALOG_NAMESPACE = '<namespace>'
REST_CONFIG = (
CATALOG_URI = '<catalog_uri>'
CATALOG_NAME = '<warehouse>'
)
REST_AUTHENTICATION = (
TYPE = BEARER
BEARER_TOKEN = '<catalog_token>'
)
ENABLED = TRUE;
Use a read-only token. REST_AUTHENTICATION also takes TYPE = OAUTH with a client ID and secret, if your catalog issues those. SELECT SYSTEM$LIST_ICEBERG_TABLES_FROM_CATALOG('s2b_iceberg'); lists what the integration can see.
Snowflake then needs access to the table's data files. If your catalog vends storage credentials, add ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS to REST_CONFIG and skip the external volume; Snowflake supports vended credentials for tables stored in Amazon S3, Google Cloud Storage and Azure. Otherwise create an external volume on the warehouse location, with its own IAM role (DESC EXTERNAL VOLUME shows the IAM user and external ID for the role's trust policy, as for the storage integration):
CREATE EXTERNAL VOLUME s2b_iceberg_vol
STORAGE_LOCATIONS = ((
NAME = 's2b-warehouse'
STORAGE_PROVIDER = 'S3'
STORAGE_BASE_URL = 's3://<warehouse_bucket>/<path>/'
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<account_id>:role/snowflake-iceberg-read'
))
ALLOW_WRITES = FALSE;
Create the table and the view:
CREATE ICEBERG TABLE s2b.events_iceberg
EXTERNAL_VOLUME = 's2b_iceberg_vol'
CATALOG = 's2b_iceberg'
CATALOG_TABLE_NAME = '<table>'
AUTO_REFRESH = TRUE;
CREATE OR REPLACE VIEW s2b.records AS
SELECT "signal_type" AS signal, "ingested_at" AS received_at, "delivery_batch_id" AS delivery_batch_id,
PARSE_JSON("payload"):resource AS resource, PARSE_JSON("payload"):data AS data
FROM s2b.events_iceberg;
AUTO_REFRESH polls the catalog for new snapshots, every 30 seconds unless the integration sets REFRESH_INTERVAL_SECONDS. Send2Bucket's column names are lowercase; the double quotes stop Snowflake from upper-casing them.
Amazon S3 Tables
Snowflake connects to the S3 Tables Iceberg REST endpoint with SigV4 and vended credentials, so there is no external volume:
CREATE CATALOG INTEGRATION s2b_s3_tables
CATALOG_SOURCE = ICEBERG_REST
TABLE_FORMAT = ICEBERG
REST_CONFIG = (
CATALOG_URI = 'https://s3tables.<region>.amazonaws.com/iceberg'
CATALOG_API_TYPE = AWS_S3TABLES
ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS
CATALOG_NAME = 'arn:aws:s3tables:<region>:<account_id>:bucket/<table_bucket>'
)
REST_AUTHENTICATION = (
TYPE = SIGV4
SIGV4_IAM_ROLE = 'arn:aws:iam::<account_id>:role/snowflake-s3tables-read'
SIGV4_SIGNING_REGION = '<region>'
)
ENABLED = TRUE;
DESCRIBE CATALOG INTEGRATION s2b_s3_tables;
Trust the AWS_IAM_USER_ARN and AWS_EXTERNAL_ID from DESCRIBE in the role's trust policy, and give the role read access to the table bucket and its tables; Configure a catalog integration for Amazon S3 Tables has the policy. Then link a database to the namespace:
CREATE DATABASE s2b_tables
LINKED_CATALOG = (
CATALOG = 's2b_s3_tables'
ALLOWED_NAMESPACES = ('<namespace>')
);
CREATE OR REPLACE VIEW s2b.records AS
SELECT "signal_type" AS signal, "ingested_at" AS received_at, "delivery_batch_id" AS delivery_batch_id,
PARSE_JSON("payload"):resource AS resource, PARSE_JSON("payload"):data AS data
FROM s2b_tables."<namespace>"."<table>";
The linked database syncs with the catalog every 30 seconds by default and refreshes its tables automatically.
Example queries
Records per signal:
SELECT signal, COUNT(*) AS records
FROM s2b.records
GROUP BY signal
ORDER BY records DESC;
JSON events by name, with revenue from order.paid:
SELECT data:event::VARCHAR AS event,
COUNT(*) AS events,
SUM(data:cents::NUMBER) / 100 AS revenue_usd
FROM s2b.records
WHERE signal = 'event'
GROUP BY event
ORDER BY events DESC;
Log records by service and severity. LATERAL FLATTEN turns the resource's attribute list into rows, and the WHERE clause keeps the service.name entry:
SELECT a.value:value.stringValue::VARCHAR AS service,
r.data:severityText::VARCHAR AS severity,
COUNT(*) AS logs
FROM s2b.records r,
LATERAL FLATTEN(input => r.resource:attributes) a
WHERE r.signal = 'log'
AND a.value:key::VARCHAR = 'service.name'
GROUP BY service, severity;
The five slowest spans. Timestamps are nanosecond strings; NUMBER(38,0) holds them exactly:
SELECT data:name::VARCHAR AS span,
data:traceId::VARCHAR AS trace_id,
(data:endTimeUnixNano::NUMBER(38,0) - data:startTimeUnixNano::NUMBER(38,0)) / 1000000 AS duration_ms
FROM s2b.records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 5;
Notes
- Supabase Analytics Buckets are not covered. The catalog side would work with
TYPE = BEARER, but Supabase's S3 endpoint sits under a path (/storage/v1/s3), Snowflake's S3-compatible endpoint is a bare host name, and vended credentials are documented only for S3, Cloud Storage and Azure. Use DuckDB for that destination. - External tables re-read gzip JSON on every query. If you query the same days often, load into a native table, or add a materialized view over the external table (Enterprise Edition).
- Snowpipe's load history keys on path and file name. Send2Bucket never rewrites an object with different contents, so that is safe here; see Delivery semantics for the duplicates that can come from client retries.
- A delayed delivery lands under the hour it was received, not the hour of the records. Notification-driven loading picks it up whenever it arrives; a hand-written job that reads only the latest folders can miss it.
- For other engines, see the Querying overview.