Streaming Ingestion from Kinesis in 15 Minutes

Redshift streaming ingestion lets a materialized view read directly from an Amazon Kinesis Data Stream or an Amazon MSK topic. There is no Firehose, no S3 landing zone and no COPY; the view consumes the stream and you query it. Latency from a record being written to the stream to it being queryable is typically seconds. This is the quickest path to near-real-time dashboards on Redshift, and it takes about 15 minutes to set up.

We will use a Kinesis Data Stream named clickstream carrying JSON events like:

{"event_id": "e7a1...", "user_id": 1042, "page": "/pricing", "ts": "2026-08-21T14:05:11Z"}

1. IAM: let Redshift read the stream

Attach a role to the Redshift cluster or Serverless namespace with permission to read the stream:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": [
      "kinesis:DescribeStreamSummary",
      "kinesis:GetShardIterator",
      "kinesis:GetRecords",
      "kinesis:DescribeStream",
      "kinesis:ListShards",
      "kinesis:ListStreams"
    ],
    "Resource": "arn:aws:kinesis:us-east-1:123456789012:stream/clickstream"
  }]
}

The role's trust policy must allow redshift.amazonaws.com (and redshift-serverless.amazonaws.com for Serverless) to assume it.

2. Create the external schema

CREATE EXTERNAL SCHEMA kinesis_src
FROM KINESIS
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftKinesisRole';

Every stream the role can see now appears as a "table" in kinesis_src. For MSK the schema is FROM MSK with the cluster ARN and an authentication clause.

3. Create the materialized view

The stream exposes a fixed set of columns: approximate_arrival_timestamp, partition_key, shard_id, sequence_number, refresh_time, and the payload as kinesis_data (VARBYTE). Parse it in the view:

CREATE MATERIALIZED VIEW clickstream_raw
DISTKEY (user_id)
SORTKEY (event_ts)
AUTO REFRESH YES
AS
SELECT
  approximate_arrival_timestamp,
  shard_id,
  sequence_number,
  refresh_time,
  JSON_PARSE(FROM_VARBYTE(kinesis_data, 'utf-8')) AS payload,
  JSON_EXTRACT_PATH_TEXT(FROM_VARBYTE(kinesis_data, 'utf-8'), 'event_id')::VARCHAR(64)     AS event_id,
  JSON_EXTRACT_PATH_TEXT(FROM_VARBYTE(kinesis_data, 'utf-8'), 'user_id')::BIGINT           AS user_id,
  JSON_EXTRACT_PATH_TEXT(FROM_VARBYTE(kinesis_data, 'utf-8'), 'page')::VARCHAR(512)        AS page,
  JSON_EXTRACT_PATH_TEXT(FROM_VARBYTE(kinesis_data, 'utf-8'), 'ts')::TIMESTAMP             AS event_ts
FROM kinesis_src.clickstream
WHERE CAN_JSON_PARSE(kinesis_data);

Points worth noting:

• AUTO REFRESH YES makes Redshift poll the stream and append new records continuously. Without it you refresh by hand or on a schedule with REFRESH MATERIALIZED VIEW clickstream_raw;.
• CAN_JSON_PARSE in the WHERE keeps a malformed record from failing the whole refresh. Put a second view without the filter over the same stream if you need to capture bad records.
• Keeping the payload SUPER column alongside the extracted fields means a new field in the event does not require a view change; query it with payload.new_field.
• A streaming materialized view can only read the stream; it cannot join to other tables. Joins happen in a second, normal materialized view or in dbt on top of it.

The first refresh reads from the oldest record the stream still retains, so a stream with seven days of retention brings seven days of events into the view. If you only want recent data, filter on approximate_arrival_timestamp in the view definition; for history older than the stream's retention, load the archived copy from S3 with COPY.

4. Query it

SELECT DATE_TRUNC('minute', event_ts) AS minute, page, COUNT(*) AS views
FROM clickstream_raw
WHERE event_ts > DATEADD(hour, -1, GETDATE())
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

Check that refreshes are happening and how far behind they are:

SELECT mv_name, refresh_type, status, start_time, end_time
FROM SYS_MV_REFRESH_HISTORY
WHERE mv_name = 'clickstream_raw'
ORDER BY start_time DESC
LIMIT 10;

SELECT * FROM SYS_STREAM_SCAN_STATES
WHERE mv_name = 'clickstream_raw'
ORDER BY record_time DESC
LIMIT 10;

SYS_STREAM_SCAN_STATES shows per-shard progress and the latest approximate_arrival_timestamp consumed; the gap between that and now is your ingestion lag.

5. Late and duplicate records

Kinesis delivers at-least-once. Producers retry, shards resplit, and a record can land in the view twice with different sequence numbers. The streaming view itself should stay raw and append-only; deduplicate in the next layer:

CREATE MATERIALIZED VIEW clickstream_events
AUTO REFRESH YES
AS
SELECT event_id, user_id, page, event_ts,
       MIN(approximate_arrival_timestamp) AS first_seen
FROM clickstream_raw
GROUP BY event_id, user_id, page, event_ts;

For late events (an event_ts older than the window a dashboard already displayed), decide per use case: either aggregate on approximate_arrival_timestamp so the dashboard is stable, or aggregate on event_ts and accept that the last few minutes can be restated. Do not try to do both in one view.

6. Keep the view from growing forever

A streaming materialized view retains everything it has ingested. For a clickstream that is untenable. The standard pattern is:

1. Keep the streaming view as a short-window buffer.
2. Periodically INSERT INTO events_history SELECT ... FROM clickstream_raw WHERE event_ts < <cutoff> into a normal table with proper sort keys.
3. Drop and recreate the streaming view on a schedule (the stream position is re-established), or size the stream's retention and the view's window together.

Scheduled queries in Query Editor v2 handle steps 2 and 3 without an external orchestrator.

Costs

Streaming ingestion has no separate price. Auto-refresh consumes compute: on Serverless, a continuously refreshing view keeps the workgroup from idling, so the cost is roughly the base RPU rate for the hours the stream is active. Compare that to Firehose-to-S3-to-COPY, which is cheaper at low volume but adds minutes of latency and two more services to operate. For real-time use cases the streaming view is usually the right trade; for a stream that is only queried once a day, Firehose to S3 plus an auto-copy job is cheaper.

If you need this in production with MSK, schema registry payloads, or a deduplication model tuned for your event volumes, see our Zero-ETL & Real-Time Ingestion service.