Warehouse export

Every hour, a property's playback rollups are written to a bucket you own, one file per hour, ready for your warehouse to load.

What lands, and when

  • Two passes per hour. Hour H is written at H + 90 minutes as an early snapshot, and again at H + 25 hours as the final version, overwriting the same object. Rollups are keyed by the hour a session started, so the final pass includes sessions that were still running at the early one.
  • Gzipped CSV per RFC 4180, with a header row. hour is UTC ISO 8601 to the hour, 2026-09-30T14:00:00Z. startup_histogram is one field, its bucket counts joined by ; for the buckets up to 250, 500, 1000, 2000, 4000, 8000 ms and one above.
  • Rollups only. No session id and no viewer identifier is ever exported.
  • An hour with no playback writes no object.
  • Available on the Business and Enterprise editions.
  • The last run, and the storage provider’s error text when a run fails, are in the dashboard under Reports → Warehouse export.

Object layout

<prefix>/rollups/YYYY/MM/DD/HH.csv.gz

The key carries no property, so use a distinct prefix per property; a destination another property already uses is refused.

The date and hour are the UTC hour the rows describe, zero-padded. Columns, in order:

hour, property_id, media, player, core_version, platform, browser, os, country, stream_type, engine, sessions, plays, plays_raw, video_starts, watched_ms, watched_ms_raw, stall_ms, stalls, start_failures, playback_failures, exits_before_start, startup_ms_sum, startup_count, startup_histogram, media_ms, bitrate_media_sum, bitrate_media_ms, upscale_ms, seeks, seek_latency_ms_sum, ad_impressions, ad_completes, app

Bucket permissions

The credentials you give PB Play need to write objects under your prefix, and nothing else — not read, not list, not delete:

{
    "Statement": [
        {
            "Action": "s3:PutObject",
            "Effect": "Allow",
            "Resource": "arn:aws:s3:::<bucket>/<prefix>/*"
        }
    ],
    "Version": "2012-10-17"
}

S3-compatible stores work too: set a custom endpoint, and path-style addressing where the store needs it.

Loading into Snowflake

Because the final pass overwrites a file that has already been loaded, the two obvious loaders are both wrong. Snowpipe never reloads a file name it has seen, even when the file changed, so the final version of every hour would be missed. A plain COPY INTO the target table does reload a changed file, but appends it next to the early rows, counting the hour twice. The recipe below loads into a staging table and replaces each hour it finds.

Verify in a test database first. This recipe has not yet been run against a live Snowflake account; check it on a copy of your table before pointing it at production.

-- Snowflake reads with its own credentials (s3:GetObject and s3:ListBucket),
-- not the write-only key you gave PB Play. A storage integration works as well.
CREATE FILE FORMAT pbplay_csv
    TYPE = CSV SKIP_HEADER = 1 COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY = '"';

CREATE STAGE pbplay_rollups
    URL = 's3://<bucket>/<prefix>/rollups/'
    CREDENTIALS = (AWS_KEY_ID = '…' AWS_SECRET_KEY = '…')
    FILE_FORMAT = pbplay_csv;

CREATE TABLE pbplay_rollups (
    hour TIMESTAMP_TZ,
    property_id STRING,
    media STRING,
    player STRING,
    core_version STRING,
    platform STRING,
    browser STRING,
    os STRING,
    country STRING,
    stream_type STRING,
    engine STRING,
    sessions FLOAT,
    plays FLOAT,
    plays_raw NUMBER,
    video_starts FLOAT,
    watched_ms FLOAT,
    watched_ms_raw NUMBER,
    stall_ms FLOAT,
    stalls FLOAT,
    start_failures FLOAT,
    playback_failures FLOAT,
    exits_before_start FLOAT,
    startup_ms_sum FLOAT,
    startup_count FLOAT,
    startup_histogram STRING,
    media_ms FLOAT,
    bitrate_media_sum FLOAT,
    bitrate_media_ms FLOAT,
    upscale_ms FLOAT,
    seeks FLOAT,
    seek_latency_ms_sum FLOAT,
    ad_impressions FLOAT,
    ad_completes FLOAT,
    app FLOAT
);

-- Where each run's files land before they replace their hours. Never TRUNCATE it:
-- that would forget which files were loaded and reload every one each run.
CREATE TRANSIENT TABLE pbplay_rollups_incoming LIKE pbplay_rollups;

Then load hourly. Each run either commits whole or not at all. A run that commits replaces every property-hour it loaded; a failed run leaves the table as it was, and the next run picks up the same files:

CREATE TASK pbplay_rollups_load
    WAREHOUSE = <warehouse>
    SCHEDULE = '60 MINUTE'
AS
BEGIN
    -- One transaction: if any step fails, the COPY's record of loaded files rolls back
    -- with it, and the next run loads the same files again.
    BEGIN TRANSACTION;
    DELETE FROM pbplay_rollups_incoming;
    -- Loads files it has not seen, and files whose contents changed since: the final pass.
    COPY INTO pbplay_rollups_incoming FROM @pbplay_rollups PATTERN = '.*[.]csv[.]gz';
    -- One file is one whole property-hour, so its rows replace that hour's rows.
    DELETE FROM pbplay_rollups t
        USING (SELECT DISTINCT hour, property_id FROM pbplay_rollups_incoming) i
        WHERE t.hour = i.hour AND t.property_id = i.property_id;
    -- SELECT * is safe because the staging table is a LIKE clone: same columns, same order.
    INSERT INTO pbplay_rollups SELECT * FROM pbplay_rollups_incoming;
    COMMIT;
END;

ALTER TASK pbplay_rollups_load RESUME;

Snowflake remembers loaded files for 64 days; an older file that is rewritten or re-staged is outside that window and not loaded again. Exports are never rewritten after the final pass, so this only matters if you copy old files back into the stage.

Other warehouses

The layout is plain hour-partitioned gzipped CSV, so it loads unchanged into BigQuery, Redshift Spectrum, Databricks and DuckDB. The same rule holds everywhere: treat each object as the whole of its property-hour, and replace that hour when the object changes rather than appending.