0072: Adopt Redshift Streaming Ingestion for low-latency TUNE event freshness

STATUS

Accepted

Blocked. The recommendation stands, but it cannot be implemented yet: the TUNE stream (us-west-2) and Redshift (us-east-2) are in different Regions, and streaming ingestion has no cross-Region path. Clearing this depends on TUNE relocating or re-producing the stream into us-east-2 (see Current blockers).

Scope: This ADR sits under shared/ intentionally. It is a data-architecture decision about how the TUNE affiliate-tracking event stream lands in the shared Redshift warehouse, alongside the other Redshift and data-warehouse ADRs (federated queries, data retention, warehouse networking). It changes the ingestion path, not the downstream analytical models.

CONTEXT

The current pipeline for TUNE affiliate-tracking events is nominally "streaming" but behaves like a slow micro-batch. Events flow Kinesis Firehose → S3 → MWAA (Airflow) → Redshift (COPY) → dbt, and end-to-end freshness — from an event arriving on the stream to being queryable in a modeled table — runs to roughly 30–90+ minutes. The business target is reliable 1–5 minute end-to-end freshness.

The latency is not one slow component. It is the sum of four buffering/scheduling boundaries laid end to end, where each stage waits for the previous stage to hand off a batch, so delays compound rather than overlap:

Stage                              Typical window
Firehose S3 buffering              1–15 min   (60–900s buffer)
S3 → MWAA DAG trigger              15–60 min  (schedule interval)
MWAA DAG run → COPY                2–10 min   (task queue + load)
dbt transformation run             10–30 min  (scheduled + runtime)
--------------------------------------------------------------
Cumulative to queryable            ~30–90+ min

Illustrative, pending live metrics. These figures are typical/default values for this topology, not measured numbers — AWS access was deferred during the evaluation. They should be replaced with actuals from the Firehose buffering config, the MWAA DAG schedule, SYS_LOAD_HISTORY, and dbt run logs. The shape of the problem holds regardless of the exact values.

The two dominant costs — the DAG schedule interval and the dbt run cadence — are polling boundaries, not compute. The data mostly sits waiting for the next scheduled tick, which is why it feels like micro-batch and why adding compute barely helps. Any real fix has to remove boundaries, not go faster within them.

Data profile. TUNE postback events (click and conversion actions) arrive as wide, sparse JSON: ~80 possible fields, with individual records carrying a subset (event_id, event_timestamp, offer_id, affiliate_id, payout/revenue, device/geo attributes, etc.). Records are well under the 1 MiB Kinesis record cap — an ideal fit for landing the raw payload in a SUPER column and shredding it downstream.

Account/region facts that constrain the solution. The stream adaction_kstream lives in producer account 216817067527 in us-west-2; the Redshift cluster is in account 981558345463 in us-east-2. So the two differ on both axes — account and Region. Same-Region co-location is not incidental — it is a hard requirement, because Redshift streaming ingestion (and the cross-account role-chaining it needs) must stay same-Region. Today it is not met, which is the first of the two blockers below.

Current blockers

Two blockers must be cleared before Option A can be configured. Both stem from where the stream lives today, and both ultimately depend on TUNE.

1. Region mismatch (hard blocker). The TUNE stream is in us-west-2 (producer account 216817067527), but the Redshift cluster is in us-east-2 (account 981558345463). Redshift streaming ingestion — and cross-account role-chaining for it — must stay same-Region; there is no cross-Region path. As things stand, Option A cannot be wired up at all.
2. Moving the stream depends on TUNE. Clearing blocker 1 means the stream has to originate in us-east-2, and we do not own the producer — TUNE does. Two resolution paths, both requiring TUNE to repoint or re-produce:
   • TUNE moves/recreates the KDS in us-east-2. Keeps today's cross-account shape (stream in the TUNE/producer account, Redshift in 981558345463), so the role-chaining design still applies — just same-Region.
   • Create a new KDS in us-east-2 inside the AdGem account where Redshift already lives (981558345463), and have TUNE produce into it. This clears both blockers at once — same-Region and same-account — so the cross-account role-chaining is no longer needed, collapsing to a plain single-account streaming-ingestion setup. This is the simpler end state if TUNE is willing to produce to a stream we own.

Until one of these paths is agreed and executed, this ADR stays Proposed and the PoC cannot run.

Considered Options

Four patterns were evaluated, ordered by fit to a 1–5 minute target:

• A. Redshift Streaming Ingestion — Redshift reads directly from adaction_kstream as a native Kinesis Data Streams consumer into an AUTO REFRESH materialized view; no S3, no MWAA, no new services. — DECISION
• B. Direct-to-Redshift file loading (Firehose → Redshift, or Redshift auto-copy / COPY JOB on new S3 files) — keeps S3, deletes the MWAA hop.
• C. In-flight transformation via AWS Glue Streaming ETL or Amazon Managed Service for Apache Flink between the stream and Redshift.
• D. Dual-path hybrid — fan the stream to a fast store (DynamoDB/Redis) for sub-second operational reads and Redshift for analytics.

DECISION

Adopt Option A: add Redshift Streaming Ingestion as a parallel consumer of adaction_kstream, and leave the existing Firehose → S3 delivery untouched as an archive/replay backup.

Redshift acts as a native Kinesis Data Streams consumer. An external schema points at the stream; an AUTO REFRESH materialized view lands records — the raw payload parsed once into a SUPER column — and downstream materialized views / dbt models shred and model it. There is no S3 hop, no MWAA orchestration, and no new service on the hot path. AWS characterizes this path as latency "in the order of seconds," comfortably inside the 1–5 minute target with headroom to spare.

This is deliberately additive and reversible, not a rip-and-replace. The Firehose → S3 path stays exactly as it is and becomes the archive/replay backup, which also covers backfills beyond the stream's retention window. If streaming ingestion does not hold up under load, we drop the materialized views and nothing about the existing pipeline has changed.

This decision is contingent on first clearing the two blockers above — the stream must end up in us-east-2, since streaming ingestion has no cross-Region path. The account topology then determines the auth shape:

• If the stream stays in a separate (TUNE/producer) account but moves to us-east-2, the consumer uses AWS's supported cross-account IAM role-chaining: a Kinesis-read role in the producer account trusts a streaming role attached to the Redshift cluster in 981558345463, and the external schema references both role ARNs comma-separated (no spaces) as the chain.
• If instead a new stream is created in us-east-2 inside the same account as Redshift (981558345463, the AdGem account), there is no cross-account hop at all — a single streaming role on the cluster reads the stream directly, which is the simpler end state.

A load-bearing design rule: keep the streaming materialized view thin. Land the raw payload as SUPER and skip unparseable records (via CAN_JSON_PARSE) so malformed data never blocks a refresh. All typing, validation, and business logic move to a downstream MV or dbt model, because business-logic errors in the streaming MV itself are not skipped and can block the refresh.

                         adaction_kstream (KDS)
                     acct 216817067527 · us-west-2
                                 |
            +--------------------+---------------------+
            |                                          |
   Firehose → S3  (UNCHANGED)              Redshift Streaming Ingestion (NEW)
   archive / replay / backfill             acct 981558345463 · us-west-2
   (existing MWAA/dbt path stays)          cross-account role-chaining
                                                       |
                                        kstream_raw_mv  (AUTO REFRESH, thin)
                                        raw payload → SUPER, skip bad records
                                                       |
                                        downstream MV / dbt: shred + validate
                                        (typed columns, business rules, tests)

Why the alternatives were not chosen

• B. Direct load / auto-copy. The lowest-effort change and a reasonable fallback, but it is still S3-bound: the Firehose 60–900s buffer interval sets a latency floor before loading even begins, so the best case lands near the bottom of the 1–5 min target rather than in seconds, and it still doesn't address the dbt cadence. Kept in mind as a fallback if Option A's refresh cannot keep pace, since auto-copy (COPY JOB) is now GA and monitorable via SYS_COPY_JOB.
• C. Glue Streaming / Managed Flink. Over-engineered for a 1–5 min target — it buys sub-minute latency the requirement doesn't need, at the price of an always-on distributed runtime (estimated $150–650+/mo baseline, to verify) plus a new operational surface (checkpointing, scaling, code deploys, on-call) and a Scala/PyFlink skill demand the SQL/dbt-centric team doesn't have. Documented to bound the trade-space; revisit only if genuine in-flight windowed aggregation/enrichment becomes a hard requirement.
• D. Dual-path hybrid. Solves a problem not stated in the requirement. It is warranted only if a genuine operational use case needs single-digit-millisecond reads (live fraud checks, real-time per-affiliate counters), which is distinct from the analytical freshness this decision targets. It adds a second datastore to keep consistent and is the least reversible option. Would reopen only if such a use case is confirmed.

CONSEQUENCES

• Positive outcomes

  • Meets the target with headroom. Seconds-scale freshness against a 1–5 minute requirement.
  • No new services and no net-new infra cost. Cost is standard Redshift compute plus existing KDS charges; there is no dedicated streaming-ingestion line item and no MWAA dependency on the hot path.
  • Exactly-once ingestion via stream + shard + sequence-number idempotency — a data-quality upgrade over the current at-least-once COPY path.
  • Low, reversible migration. Additive; the existing backup path is untouched, so rollback is dropping the MVs.
  • Stays in the team's toolchain. Transformation modeling remains in SQL/dbt — no Scala/PyFlink skill gap.
  • Malformed-record safe. Oversized/bad records are skipped to SYS_STREAM_SCAN_ERRORS and the refresh still succeeds (given the thin-MV discipline).

• Negative outcomes / what we accept

  • No published refresh-interval SLA. Auto-refresh competes with cluster workload for resources; on a busy cluster we must confirm refresh keeps pace under representative load. This is a PoC + monitoring question, not a guarantee.
  • Thin-MV discipline is mandatory. Validation and business logic must live downstream of the streaming MV, or business-logic errors will block refresh. This is a real constraint on how the ingestion layer is written.
  • Bounded replay window. The default 24 h KDS retention caps replay/backfill from the stream; longer backfills rely on the retained S3 archive.
  • One-time cross-account IAM setup. Two roles plus a trust policy across the two accounts (role-chaining).

Risks

• Refresh cannot keep pace under load. The seconds-latency claim is AWS-documented but not yet proven for our volume, shard count, and cluster/RPU sizing. If auto-refresh lags under normal analytical query load, the target is missed. Mitigation: prove it in the PoC under representative load before committing; Option B (auto-copy) is the documented fallback.
• Streaming-MV logic creep. If validation/business logic leaks into the streaming MV over time, a bad record or logic error can silently block ingestion. Mitigation: enforce the thin-MV rule in review; validate strictly downstream.
• Stream retention too short for a real backfill/audit need. If compliance or replay requires more than 24 h, the stream is the wrong source. Mitigation: the S3 archive is retained precisely for this and remains the backfill/audit source of record.
• Region mismatch is a live blocker, not just a config risk. The stream is in us-west-2 and Redshift in us-east-2, and there is no cross-Region streaming-ingestion path, so Option A cannot even be configured until the stream is relocated to us-east-2 (see Current blockers). This depends on TUNE and is the top risk to the decision being actionable at all.
• Cross-account/region misconfiguration once unblocked. Even after the stream is in us-east-2, role-chaining and same-Region are hard requirements; a mistake surfaces as silent no-data or an auth failure. Mitigation: confirm the stream and cluster are both in us-east-2, choose the auth shape per the resolution path taken, and validate row counts against the S3 archive during the PoC.
• Estimates not yet confirmed. Cost and current-pipeline latency figures are illustrative pending live metrics; firm numbers are blocked on AWS access (see open questions).

Proof-of-concept plan (design only, not yet executed)

The PoC is fully designed but unexecuted (AWS access deferred); identifiers below are placeholders to confirm against the live accounts. It cannot start until the region blocker is cleared and the stream is in us-east-2 (see Current blockers); the steps below assume that has happened and describe the cross-account variant.

1. Producer account (216817067527) — Kinesis-read role. A role granting read on adaction_kstream (kinesis:GetRecords, GetShardIterator, DescribeStream, DescribeStreamSummary, ListShards, ListStreams), with a trust policy allowing the Redshift-account streaming role to assume it.
2. Redshift account (981558345463) — streaming role that chains in. A role attached to the Redshift cluster/workgroup, trusted by the Redshift service, holding sts:AssumeRole targeting the producer role above.
3. External schema over the stream — role-chaining via comma-separated ARNs (<redshift-role>,<producer-role>, no spaces), REGION 'us-east-2'. (Same-account variant drops the chaining and the producer role — see Appendix.)
4. Streaming MV — land raw as SUPER, keep it thin. Store the whole payload as SUPER (parsed once via JSON_PARSE, preferred over per-column JSON_EXTRACT which re-parses each field); guard with CAN_JSON_PARSE so unparseable records are skipped and never block refresh.
5. Shred + validate downstream (second MV or dbt) — project typed columns from the SUPER payload and apply business rules here, not in the streaming MV.
6. Validate & measure — reconcile exactly-once against the S3 archive for a sample window; watch SYS_STREAM_SCAN_ERRORS for skipped records; measure observed refresh lag under representative cluster load.

PoC success criteria: (1) records queryable within seconds of stream arrival; (2) row counts reconcile with the S3 archive; (3) auto-refresh keeps pace during normal analytical query load; (4) malformed records land in the error table without stalling refresh.

NOTES

References

• Source evaluation: "Streaming Ingestion Architecture Evaluation — Cutting streaming latency from tens of minutes to seconds" (2026-07-16). The recommendation here is Option A from that evaluation; the PoC plan corresponds to its section 6.
• AWS Redshift Developer Guide — streaming ingestion (native KDS consumer, AUTO REFRESH materialized views, SUPER/JSON_PARSE, CAN_JSON_PARSE, cross-account role-chaining, SYS_STREAM_SCAN_ERRORS), accessed 2026-07-16.
• AWS Big Data Blog — Redshift streaming ingestion patterns, accessed 2026-07-16.
• AWS Kinesis Data Firehose / Redshift docs — Firehose-to-Redshift staging (S3 + COPY, 60–900s buffer) and Redshift auto-copy (COPY JOB, SYS_COPY_JOB) GA status.
• Related shared ADRs: data-warehouse networking, data retention policy, federated queries.

• PR #178: docs(adr): propose Redshift Streaming Ingestion for low-latency TUNE …

Original Author

Victor Gonzalez

Approval date

Approved by

Appendix

Reference SQL (to adapt against live accounts)

Assumes the region/account blockers are resolved and the stream is in us-east-2. Two variants depending on which resolution path is taken.

External schema over the stream — cross-account variant (stream in a separate producer account, us-east-2), role chaining:

CREATE EXTERNAL SCHEMA kstream_ext
FROM KINESIS
-- chain: <redshift-role>,<producer-role> (no spaces)
IAM_ROLE 'arn:aws:iam::981558345463:role/RedshiftStreamingRole,arn:aws:iam::<producer-acct>:role/KinesisReadRole'
REGION 'us-east-2';

External schema — same-account variant (new stream in 981558345463/us-east-2, no chaining):

CREATE EXTERNAL SCHEMA kstream_ext
FROM KINESIS
IAM_ROLE 'arn:aws:iam::981558345463:role/RedshiftStreamingRole'
REGION 'us-east-2';

Kinesis-read role permissions (producer account — cross-account variant only):

{
  "Effect": "Allow",
  "Action": [
    "kinesis:GetRecords", "kinesis:GetShardIterator",
    "kinesis:DescribeStream", "kinesis:DescribeStreamSummary",
    "kinesis:ListShards", "kinesis:ListStreams"
  ],
  "Resource": "arn:aws:kinesis:us-east-2:<producer-acct>:stream/adaction_kstream"
}

Streaming materialized view (thin — raw payload to SUPER, skip bad records):

CREATE MATERIALIZED VIEW kstream_raw_mv
AUTO REFRESH YES AS
SELECT
  approximate_arrival_timestamp,
  partition_key,
  shard_id,
  sequence_number,
  -- raw JSON payload, parsed once into SUPER
  CASE WHEN CAN_JSON_PARSE(FROM_VARBYTE(kinesis_data, 'utf-8'))
       THEN JSON_PARSE(FROM_VARBYTE(kinesis_data, 'utf-8'))
  END AS payload
FROM kstream_ext.adaction_kstream;

Downstream shred + validate (second MV or dbt model — typing and business rules live here):

SELECT
  payload.event_id::varchar        AS event_id,
  payload.action::varchar          AS action,
  payload.offer_id::bigint         AS offer_id,
  payload.affiliate_id::bigint     AS affiliate_id,
  payload.payout::decimal(12,4)    AS payout,
  payload.revenue::decimal(12,4)   AS revenue,
  TIMESTAMP 'epoch' + payload.event_timestamp::bigint * INTERVAL '1 second' AS event_ts
FROM kstream_raw_mv
WHERE payload IS NOT NULL;

Comparison matrix

Dimension	Current (Firehose→S3→MWAA)	A · Redshift Streaming	B · Direct load / auto-copy	C · Glue / Flink	D · Dual-path
End-to-end latency	~30–90+ min	Seconds	1–15 min	<1 min	<1 sec (fast path)
Meets 1–5 min target	No	Yes (headroom)	Marginal	Yes (overshoots)	Yes (overshoots)
Net-new infra cost	MWAA env (~$350–450/mo est.)	~$0 (reuses compute + KDS)	~$0	~$150–650+/mo est.	Store + Lambda
New services to operate	—	None	None	Stream processor	Processor + datastore
Migration effort	—	Low	Very low	High	High
Delivery guarantee	At-least-once (COPY)	Exactly-once	COPY-managed	Exactly-once (Flink)	At-least-once
In-flight transforms	Post-load (dbt)	Limited (keep MV thin)	Post-load	Full	Full
Keeps S3 archive	Yes	Yes (unchanged)	Yes	Optional	Optional
Reversibility	—	High (additive)	High	Medium	Low

Cost figures are estimates pending live-metric and current-pricing confirmation. Latency figures for A/B derive from AWS-documented characteristics; C/D from published list-price service behavior.

Open questions

1. Stream relocation to us-east-2 — BLOCKING. Which resolution path do we take: TUNE moves/recreates the KDS in us-east-2 (stays cross-account), or a new KDS in us-east-2 inside the AdGem/Redshift account 981558345463 (same-account, no chaining)? Both depend on TUNE agreeing to move or re-produce. Nothing else can proceed until this is settled.
2. Live metrics — BLOCKING. Stream volume/throughput, shard count, Redshift cluster/RPU sizing, and current DAG + dbt schedules. Needed to firm up cost and to prove refresh keeps pace. Blocked on AWS access.
3. Exactly-once required? Option A provides it natively; if strictly required, it is a point in A's favor over D (at-least-once).
4. Data-quality / validation rules? Determines what moves into the downstream shred layer vs. dbt tests. The streaming MV stays thin regardless.
5. Compliance / audit retention? If >24 h replay is required, the retained S3 archive is the backfill source, not the stream.
6. Confirmed target. 1–5 min is set for this evaluation; any use case needing sub-second (live fraud checks, real-time counters) would reopen Option D.