From Operational to Analytical, Without Choosing Sides
Part 2a: A serverless CDC pattern for hot, cold, and the queries that span both
In Part 1 I built a CDC pipeline that streams every row change in Aurora DSQL into Redshift Serverless, in seconds, with no batch jobs and no servers to babysit. That post ended where most CDC posts end: events are landing, current state is queryable, mission accomplished.
It was not. The hot Redshift table was about to run into a problem real production data has every day: it grows forever. After a few weeks of an e-commerce simulator running at one event per second,
cdc_events had passed a million rows and queries against it were noticeably slower. Run that for a year and the bill, and the latency, both start to hurt. The natural next experiment: where do the cold rows go, and can the same query layer keep working when they leave?This post is about the answer I landed on. It is not a "here is what I built" walkthrough; it is a pattern worth considering for your own workloads, written in the form of "here is the shape of the problem, and here is one way to solve it that scales without changing the architecture."
The shape of the problem comes up almost everywhere I look. An operational database holds the live transactional state. Some of that state needs to be analyzed: dashboards over the last hour, cohorts over the last quarter, anomaly detection on a rolling window, audit logs that go back forever, ML features both online and offline. The first answer is usually to query the operational store directly, which works until it does not. The next answer is a nightly batch into a warehouse, which works until "nightly" becomes "five-minutely" and then becomes "real-time," at which point you have built a CDC pipeline whether you meant to or not.
The serverless, cost-aware, scale-anywhere shape: keep the recent slice in something fast and writable; tee everything into something cheap and immutable; expose a single query surface over both. Write your application against the unified surface and the storage tier becomes invisible to the queries that matter. Tune the boundary between hot and cold for your latency-versus-cost trade-off. Let everything underneath scale on its own, because everything underneath is serverless - start to finish, including the source database: Aurora DSQL, Kinesis on-demand, Firehose, Lambda, S3 Tables, Redshift Serverless. The thing that changes as the workload grows is the dollars on the bill, not the architecture diagram.
This is one shape, not the shape. There are workloads where it is overkill, and probably workloads where it is underkill. Push back on the assumptions, the boundary choices, the costs I am claiming. The companion repo and the experimentation log are linked at the end, and the comment thread under this post is open.
I use Aurora DSQL as the source database here because it is the AWS source database I happen to be running, and because its same-day cost response is the part of the architecture that surprises me. The pattern survives substitution: swap DSQL for Postgres-on-RDS plus Debezium plus Kafka, or for Aurora MySQL plus DMS, and most of the diagram still applies. The hot-Lambda, the cold tee, the unified view layer, and the safety-checked tiering all live downstream of whatever fills your CDC stream. Costs change, ownership of the source DB changes, but the shape does not.
That sounds abstract. The next section makes it concrete by walking through six use cases I think are real for almost anyone running an operational database, and the shape of the SQL each one writes. Tell me which of them match your reality, and which of them I am wrong about.
Six use cases that all want this shape
The naming is generic on purpose. Replace "order" with "claim" if you sell insurance, with "ticket" if you sell help-desk software, with "shipment" if you sell logistics. The point is the shape, and the shape is the same.
1. Operational dashboards over recent activity. The thing operations leaders refresh every morning. How many orders went paid in the last hour, what's the conversion funnel today, which products are moving. The window is small (an hour, a day). The latency target is single-digit seconds. Hot-only. The query is against the current-state view:
1
2
3
4
SELECT status, COUNT(*) AS orders, SUM(total_cents)/100.0 AS revenue
FROM orders_unified
WHERE last_change_at >= GETDATE() - INTERVAL '1 hour'
GROUP BY status;
The view is
orders_unified, not orders_current, but for a query whose WHERE clause is "the last hour" the unified layer prunes to hot automatically. Same SQL works whether your account has 1 day of cold or 10 years of cold. That's the property: the storage tier is invisible to the query.2. Historical trend analysis. The thing analysts run on Mondays. Year-over-year revenue, customer behavior over a 6-month window, retention curves. The data is always old. Cold-only.
1
2
3
4
5
6
7
8
9
SELECT
DATE_TRUNC('month', last_change_at) AS month,
COUNT(DISTINCT customer_id) AS active_customers,
SUM(total_cents) / 100.0 AS revenue
FROM orders_unified
WHERE status = 'paid'
AND last_change_at < GETDATE() - INTERVAL '30 days'
AND last_change_at >= GETDATE() - INTERVAL '13 months'
GROUP BY 1 ORDER BY 1;
The same
_unified views work here. The window predicate flips to "older than 30 days," so the view satisfies the query entirely from cold (Iceberg). Iceberg's column store and predicate pushdown make this scan cheap; the alternative (scanning Redshift's hot table for thirteen months of data) would be both expensive and slow.A cohort retention query - first-month-of-activity bucketed against subsequent-month-of-activity - wants the raw event log instead, because cohorts care about when a primary key first appeared, not its current state. That's
cdc_events_all with a window function over commit_timestamp. The shape is the same; the data source is the same. The only thing that changes is which side of the unified layer the predicate naturally selects.3. Audit trail and replay. "Show me every state change for this order." "We had a bug between 2 PM and 3 PM yesterday, replay everything that happened." The query is against the raw event log, not the current-state views, because what you want is the history of a primary key, not its latest state.
1
2
3
4
5
SELECT operation, commit_timestamp, event_data
FROM cdc_events_all
WHERE source_table = 'orders'
AND record_id = 'ord_abc123'
ORDER BY commit_timestamp;
The append-only design buys this for free. Every change to every row, forever, both stores spanning the timeline. Cost: the storage of those events, which is what S3 Tables exists to be cheap about.
4. ML features, online and offline. Online: a recommendation service needs the customer's last 30 days of activity at request time. Hot. Offline: a model trains on 18 months of order patterns. Cold. Same feature definition, two storage tiers:
1
2
3
4
5
-- Online (hot): low-latency, fresh
SELECT customer_id, COUNT(*) AS orders_30d, SUM(total_cents) AS spend_30d
FROM orders_unified
WHERE last_change_at >= GETDATE() - INTERVAL '30 days'
GROUP BY customer_id;
The same query, with the predicate moved to a wider window, becomes an offline training set. You write the feature once.
5. Reconciliation when something goes wrong. A downstream system reports "I'm missing orders from Tuesday." The hot path may have already pruned them. Cold has them.
1
2
3
4
5
6
SELECT record_id, MAX(commit_timestamp) AS last_change
FROM cdc_events_all
WHERE source_table = 'orders'
AND commit_timestamp >= '2026-06-06'
AND commit_timestamp < '2026-06-07'
GROUP BY record_id;
This is the query that turns "we lost data" into "no, here it is, here's exactly what was true at 2 PM Tuesday." It's the reason every operational system should have an append-only event log somewhere, even if it never gets queried for analytics.
6. Real-time alerting. A high-value order from a new account, an unusual burst from a single customer, a sudden change in conversion rate. The alerting engine watches a hot view; it does not care about cold.
1
2
3
4
5
6
7
-- Card-testing signal: many small orders, one customer, last 5 minutes
SELECT customer_id, COUNT(*) AS n, SUM(total_cents)/100.0 AS dollars
FROM orders_unified
WHERE status = 'paid'
AND last_change_at >= GETDATE() - INTERVAL '5 minutes'
GROUP BY customer_id
HAVING COUNT(*) >= 5;
A separate Lambda runs this every minute and pages on results. Same view layer, different query cadence, no extra infrastructure.
The thread through all six: one set of view definitions (
orders_unified, customers_unified, cdc_events_all), six different access patterns, three different storage tiers transparently selected by the WHERE clauses. Add a seventh use case tomorrow and the work is SQL, not infrastructure.If you want to skip ahead and get this set up against your own data, the patterns behind these six use cases are encoded as installable skills at jaingxyz/aws-data-skills - three relevant ones for this post:
cdc-streaming-pipeline for the hot side, firehose-iceberg-pipeline for the cold side, and lakehouse-redshift for the unified query layer. Load them into Claude Code or any Anthropic-skill-aware agent and the agent will walk you through the same architecture against your own DSQL cluster, your own Kinesis stream, your own schema. The skills are also staged for submission to the AWS Agent Toolkit , the open-source toolkit of "AWS-supported MCP servers, skills, and plugins to help AI agents build on AWS"; that submission process is itself another post.If one of those felt missing, forced, or the SQL on yours would actually look different - please say so. The two I'm least confident in for a non-e-commerce reader are cohorts and ML features; if your domain reshapes them, the comment thread, repo issues, or an email all reach me.
The next section walks through how the cold-path tee actually works, and what changes (or does not) when the workload grows by three orders of magnitude. There are choices in there I am still experimenting with, and I will flag them as we get to them.
The cold path, and what scaling actually means
The cold path is a tee. The same Kinesis stream that feeds the hot Lambda also feeds an Amazon Data Firehose delivery stream. Firehose buffers for sixty seconds (or sixty-four megabytes, whichever fills first), runs each batch through a small transform Lambda that reshapes the raw DSQL CDC envelope into the destination column shape, and writes the result into an Apache Iceberg table on Amazon S3 Tables. Redshift Serverless reads that Iceberg table through an external schema; a
cdc_events_all view UNIONs hot and cold; and the *_unified views you saw in the SQL above sit on top of that.The pruning that made the use-case queries work earlier is not magic - it is just a late-binding view (the
WITH NO SCHEMA BINDING clause is required because the cold side is an external table) that UNIONs hot and cold with date predicates on each side:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE VIEW cdc_events_all AS
SELECT source_table, operation, record_id,
event_data, -- already SUPER on hot
commit_timestamp, ingested_at,
'hot'::VARCHAR(4) AS source_store
FROM public.cdc_events
WHERE commit_timestamp >= DATEADD(hour, -24, GETDATE())
UNION ALL
SELECT source_table, operation, record_id,
JSON_PARSE(event_data) AS event_data, -- string -> SUPER on cold
commit_timestamp, ingested_at,
'cold'::VARCHAR(4) AS source_store
FROM cold.cdc_events_archive
WHERE commit_timestamp < DATEADD(hour, -24, GETDATE())
WITH NO SCHEMA BINDING;
The
JSON_PARSE on the cold leg is the load-bearing line. Hot stores event_data as the SUPER type natively; cold stores it as a JSON string (Iceberg's column types do not include SUPER). Without JSON_PARSE rebinding the cold values to SUPER on the way out, the two legs have different types for the same column and the UNION fails.When a downstream query like
WHERE last_change_at >= GETDATE() - INTERVAL '1 hour' lands on this view, Redshift's planner pushes the predicate into both legs of the UNION; the cold leg's predicate (commit_timestamp < NOW() - 24h) is contradicted by the upstream one (>= NOW() - 1h), so the cold scan is pruned to zero rows. The reverse happens for queries deep in history: the hot leg's predicate is contradicted, and Redshift skips it. The 24-hour boundary in the view is what makes this work cheaply - and it is also the knob you turn for your latency-versus-cost trade-off, which I'll come back to.That is the whole cold path: a CloudFormation template (~570 lines, of which roughly four hundred are resources), one transform Lambda (~145 lines, ~90 of code), and a SQL file with the views (~200 lines). Code and a deploy script are in the companion repo . Skipping the line-by-line - what's actually interesting is what changes when the workload grows.
At one event per second (the simulator's default, and what I have been running) the pipeline is mostly idle. Kinesis on-demand provisions one shard equivalent. Lambda invocations come in batches of a few records every five seconds. Firehose flushes every minute, regardless of byte count, because sixty seconds is the configured floor. S3 Tables stores parquet files of a few hundred kilobytes each; auto-compaction keeps them tidy. The cost shape at this scale is roughly: a few dollars for the Kinesis on-demand stream's data charge, single-digit dollars for Lambda invocations, low-single-digit dollars for Firehose, cents for S3 Tables storage at the data volumes involved, and Redshift Serverless RPU-hours that vary with how much you query. The actual bill is something I am still calibrating against the Cost Explorer view - I'll add the measured number to the companion repo once I have a clean week.
At one thousand events per second I have not measured, but the architecture does not need to change. Kinesis on-demand auto-scales to handle up to double the peak write throughput observed in the previous 30 days , and splits hot shards within fifteen minutes when individual shards see more than 500 KB/s. Lambda concurrency rises and falls with the event-source-mapping batch math; the consumer is already chunking inside each invocation to stay under the Redshift Data API's per-statement parameter cap, so nothing in the consumer code shifts. Firehose continues buffering at the same hints; flushes simply hit the byte ceiling more often than the time ceiling. The cold side runs in
AppendOnly mode against an Iceberg table , which Firehose auto-scales with throughput; Glue Data Catalog handles compaction of the resulting parquet files in the background. Redshift Serverless scales its RPUs up automatically when concurrent query load demands it.At one million events per second the same components remain in the picture, but two things would deserve a hard look. First, Kinesis on-demand has a per-second ingestion ceiling that some accounts hit at high throughput; you would either request a limit raise or split into multiple streams. Second, the consumer Lambda's "all events into one table" pattern starts to feel like one shard of work; you might split by
source_table so that orders and audit-logs do not contend for the same write path. Neither change requires touching the cold path or the view layer. What I am still uncertain about is Firehose-to-Iceberg compaction at the very high end; I have not pushed S3 Tables there. If you have, tell me what you saw.That last paragraph is the kind of thing this whole post is about. There is a measured part (the low end), a reasoned-through part (the middle), and a still-curious part (the high end). Too many serverless-scaling claims are written as if everything had been load-tested at every order of magnitude. Mine have not. The architectural property I am claiming is not "I tested this at a million events per second" or "nothing changes at any scale." It is closer to "the components I chose scale through small parameter tweaks, not redesigns." At a million events per second I would be raising a Kinesis quota and considering splitting events by source table - both small operational changes against the same architecture, not rewrites. If you have evidence the small-tweak property breaks somewhere, push back.
The cost shape that comes out of this is the part worth lingering on. Every layer scales to zero when traffic does. Aurora DSQL - the source database, and the only fully serverless relational database I know of that does this - has no idle compute charge; you pay for the queries you ran. Kinesis on-demand has no per-shard idle charge. Redshift Serverless auto-pauses. Lambda is zero-when-idle. S3 Tables charges for the bytes you store and a small monthly per-table fee. Firehose has a per-record minimum but at these rates it is in the cents-per-month range. The bill scales with traffic, not with capacity you held in reserve, and when traffic dips, the bill dips on the same day, automatically.
That property - same-day cost response - does not exist on a provisioned stack. A reserved RDS instance costs the same on Saturday at 3 AM as it does on Tuesday at noon. A provisioned Redshift cluster bills for cluster-hours regardless of whether anyone queried it. Treating the database tier as the cost floor is a habit; the components above already moved past it, and DSQL means the database tier moves with them.
The next section walks through the unified view layer in slightly more detail, because the boundary between hot and cold is not actually a fixed property of the architecture; it is a knob you turn for your own latency-versus-cost trade-off, and getting the knob right is most of what this section was building toward.
The boundary is a knob
I picked twenty-four hours for the hot/cold split, and that is a choice, not a property of the architecture. Two literals carry it: the
INTERVAL '24 hours' in the cdc_events_all view above, and a RetentionHours: 24 parameter on the tiering CloudFormation stack (more on the tiering side in Part 2b). Change those two, redeploy, and the boundary moves.I have seen teams describe picking anywhere from an hour to a month for the equivalent boundary, depending on their query patterns. Twenty-four hours happens to match the most common operational-dashboard horizon I think about - "today vs. yesterday", "since this morning" - and the cadence of the daily prune job. Other choices are reasonable for other workloads.
What does moving the boundary cost or save?
Shorter hot window (one hour, six hours). Hot stays small, so queries against it stay fast even at high write rates. Storage in Redshift Serverless is cheaper, since less of the data sits there. The cost is that any query whose predicate falls just outside the window pays Iceberg-scan cost on Spectrum - fine for analyst queries, less great for an alerting Lambda that wants single-digit-millisecond response on the last fifteen minutes. If your workload's "hot" question is genuinely "the last hour", a one-hour window is the right answer.
Default 24-hour window. What I picked. Most queries land entirely in hot or entirely in cold; the cross-boundary cases are rare enough that the UNION's per-leg predicate handles them efficiently. The SQL examples earlier all assume this window.
Longer hot window (seven days, thirty days). Hot grows. Redshift queries against it get more expensive. But cross-boundary queries effectively disappear because the operational and the dashboard windows both fit in hot. If your cold path is mostly for archival and reconciliation, not for analytics, longer hot makes the architecture feel more like "Redshift with a backup" - fine, just don't pretend the cold path is paying for itself with query traffic.
The thing I find interesting about this is that the boundary moves without changing application code. The
*_unified views still expose the same columns. SQL written against orders_unified does not care whether the boundary is one hour or one month; it just runs faster or slower, and the bill is what it is. That is the architectural property I was claiming earlier about scale: storage decisions are decoupled from query decisions, and you can re-tune them as the workload evolves.A more interesting future knob, that I have not built yet: what if the boundary were per-table instead of global? Orders might want a 24-hour hot window because that is the operational horizon. Customer profiles might want a thirty-day hot window because they get queried more often in dashboards. The unified view layer accommodates this - it is just a different
INTERVAL per UNION leg per table. I have not done it because the simulator hasn't given me a workload that demands it; if your shop has multiple source tables with materially different access patterns, that is the experiment I would run next, and I would want to hear how it goes.Where this is going
I am writing this while the pipeline is still running. The simulator continues to drive events; the cold archive grows; the unified views keep returning rows; and the tiering prune has fired on its schedule. That is the part I have measured. Part 2b takes up the question of what continued, unattended firing actually looks like.
What I have not done, and what I am still curious about:
- Per-table boundaries. The single global twenty-four hours is a starting point. Tables with materially different access patterns probably want different windows. The unified view already accommodates this; I have not exercised it.
- Alarm-gated tiering. The daily prune currently checks "did the cold archive receive any rows for the same window" before deleting from hot. That is a coarse safety check. A finer one would gate the prune on Firehose's flush-lag CloudWatch metric being healthy. I have a sketch of this for Part 2b but have not deployed it.
- Real cost numbers. I have a soak running. After a clean week of the schedule firing on its own, I will publish the actual Cost Explorer breakdown in the companion repo so the cost section of this post can be replaced with measured numbers instead of component reasoning.
- Higher-throughput validation. Everything in the scaling section above one event per second is reasoned through, not measured. If you have run a similar pattern at one thousand or one million events per second, I would benefit from your data more than from anything I am about to make up.
Part 2b picks up the tiering side: the Step Functions state machine that prunes hot, the cold-side safety check, the unattended soak, what "passing" looks like when nobody is watching. The pattern in this post is the architecture; the part 2b post is what it takes to leave it running.
Two repos carry the work forward. The companion code is at jaingxyz/dsql-redshift-cdc-pipeline - clone, deploy, and the architecture stands up against your account. The encoded patterns are at jaingxyz/aws-data-skills - five Anthropic-format skills that an AI coding agent (Claude Code, Cursor, anything skill-aware) can load on demand to walk through the same setup against your own data. Two of them (
dsql-to-analytics-pipeline and streaming-into-data-lake) are also staged for submission to the AWS Agent Toolkit , so once they pass the AWS MCP team's evaluation gate they will be vended through the AWS MCP server registry and accessible to anyone building on AWS without needing to clone my fork.The comment thread under this post, repo issues, or an email all reach me. If something here is wrong, please tell me; if something here was useful, I would also like to know.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article