From Operational to Analytical, Part 2b: Letting It Run
A scheduled, unattended prune of the hot side of a CDC pipeline — gated on the cold archive having actually received the rows about to be deleted. Walks through the safety-check pattern, three design choices that distinguish a working version from a naive one, and a one-week soak.
Part 2a ended with an architecture that worked. The hot table in Redshift Serverless was being fed continuously from Aurora DSQL via Kinesis and a small Lambda. The cold archive in Apache Iceberg was being teed off the same Kinesis stream via Firehose. The unified view layer pruned predicates to whichever tier the query window selected. Six use cases ran against a single set of view definitions. None of that depended on me being awake.
The part that did depend on me being awake was the hot table. Left to its own, the hot side of the unified view grew without limit. After a few weeks of the simulator running, it had passed two million rows; queries were measurably slower; the bill was no longer dominated by query traffic but by the storage of data that no one queried anymore because it had cold copies. The pattern had a tail it could not manage by itself.
This post is about the part that closes that loop: a scheduled, unattended prune of the hot side, gated on the cold archive having actually received the rows about to be deleted. The pattern generalizes beyond this stack - anywhere you have an operational store with an archive behind it, the same shape applies. Don't delete until you can prove the data is somewhere else. That's the substance. The implementation is the easy part; the safety reasoning is the part I want feedback on most.
The reason this matters is that "delete from hot when traffic dips" is the kind of operational chore that gets manually performed for the first six months of a project and then becomes a pager-duty thing for the next ten years. Or it gets automated badly, with a cron job that DELETEs by date and silently overruns its budget the first day Firehose has a hiccup. The version of this pattern walked through here has explicit guards against both failure modes; whether the guards are sufficient is the question.
What the pattern asks of you architecturally is small: schedule something, give it credentials to query both stores, give it the authority to delete from one of them, and design the workflow so that nothing destructive happens unless an independent check passes first. It should run unattended for years, on the same primitives as the rest of the stack, scaling on its dollars and not on its complexity.
The components are unsurprising for anyone who has used AWS for scheduled workflows: EventBridge Scheduler fires a Step Functions state machine on a daily cadence; the state machine talks to Redshift via the Data API; an SNS topic catches abort and failure cases; an IAM role narrows everything to what it actually needs. None of these components have a cluster you provision. Idle, the entire tiering subsystem costs cents a month. The cadence and the retention horizon are parameters; the safety logic is not.
If your shop has done scheduled retention against a downstream archive on a different stack - Postgres into S3, Snowflake into Glacier, anywhere - the safety check pattern will be familiar, and the place I most want to know whether I got it right is the comment thread under this post.
The pattern: don't delete until you can prove the data is somewhere else
The shape is small enough to write in one sentence: every scheduled run computes a cutoff, asks the archive "do you have anything older than this cutoff", and only deletes from the operational store if the answer is yes. If the answer is no, the run aborts loudly and waits for a human to look at why the archive hasn't received what it should.
That sentence sounds obvious. It is also the version of the pattern that I have seen most often left out of scheduled-DELETE jobs in production systems. The naive shape (a one-line scheduled job that removes rows older than thirty days) does not ask a single question before running, and it has at least three failure modes a working tiering job has to dodge.
The archive is silently behind. A downstream pipe - replication, Firehose, a streaming job - has been failing for six hours. From the operational database's point of view, nothing is wrong. The cron job DELETEs the cutoff window. Six hours of data that was never archived is now gone. This is the failure mode you discover three days later when somebody runs a reconciliation query.
The cutoff drifts during the run. A long-running VACUUM or a serialized DELETE takes minutes; if the cutoff is computed inline in each statement, the DELETE's own cutoff arithmetic advances past the safety check's, and the DELETE sweeps in events the safety check never verified were archived. A silent failure mode at low load and an intermittent one at high load - exactly the kind of bug that does not surface in a small simulator but produces real data loss in production. (The fix is the next section's first design choice.)
The destructive step succeeds; the operator never finds out it ran. The job logs to CloudWatch. Nobody watches CloudWatch. After six months of green runs, the job aborts on a real problem; the abort goes unnoticed; the next time anyone looks, the operational database has six months of data nobody planned to keep. This is failure mode three: the absence of a signal is itself a failure.
The pattern dodges all three by doing three things, in order, on every run: pin the cutoff at the start of the run so the DELETE and the safety check are talking about the same window; ask the archive a question whose answer is verifiable evidence that the archive has the data; and arrange the workflow so that an abort produces a noisy alert in a place an operator actually reads, while a success produces nothing. Loud-when-wrong, quiet-when-right is the shape that lets unattended operation be trusted.
What "the archive has the data" actually means is the part that varies. In this stack, the archive is an Iceberg table fed by Firehose, and the safety check is "does the Iceberg table contain at least one row whose timestamp is older than the cutoff." That is a coarse question, deliberately. A finer question would be "does the Iceberg table contain a row for every primary key that the DELETE is about to remove from the operational store" - strictly safer, much more expensive at scale. The coarse question is enough for a daily cadence because Firehose is monotonic and a thousand-row sample is sufficient evidence that delivery is working. If your archive is not monotonic, you need a different question. This is one of the places I'd want feedback if you have built something similar against a different downstream.
Three design choices that made the difference for me
The implementation is a Step Functions state machine that EventBridge Scheduler invokes on a daily cadence. Three of the states submit destructive SQL via the Redshift Data API: a safety check that counts rows in the cold archive, a delete from hot, and a vacuum-plus-analyze to reclaim space. Everything else is plumbing - IAM roles, Secrets Manager wiring, an SNS topic for alerts, a CloudWatch log group. The CloudFormation lands at about six hundred lines; the state machine definition is a couple of hundred lines of JSON. The companion repo at jaingxyz/dsql-redshift-cdc-pipeline has the working version.
None of these choices has anything to do with Step Functions specifically; the same lessons apply to a Lambda that prunes DynamoDB, a Glue job that compacts Iceberg, or a Postgres
DELETE cron.Pin the cutoff at the start of the run. The cutoff is a timestamp like "now minus twenty-four hours." It is the value the safety check tests against, and the value the DELETE filters by. Compute it once at the beginning, and pass the resolved timestamp to every later step. Compute it inline twice - once in the safety query, once in the DELETE - and a long-running VACUUM between them lets the second clock-read advance past the first; the DELETE sweeps in events the safety check never verified were archived. The state machine in the repo has a
ResolveCutoff* cluster of states at the top (init, submit, wait, describe, choice, extract) whose only job is to fix the timestamp into the execution state, after which every later step references it through ResultPath . The cluster is a six-state idiom because the Redshift Data API is async - submit, poll until terminal, then extract the result - but the architectural intent is one step. Do the same in whatever you build.Treat the full ARN as canonical when the call path is destructive. Secrets Manager often accepts a secret name where it could ask for a full ARN; AWS APIs are tolerant of the shorthand in many places. The Redshift Data API's executeStatement is not one of those places - it requires the full ARN including the six-character random suffix Secrets Manager appends. The fix is to resolve the full ARN at deploy time (one
aws secretsmanager describe-secret in the deploy script) and pass it as a CloudFormation parameter into the state machine. The general lesson translates to any destructive automation calling AWS APIs: when the call is going to delete something, resolve identifiers to their fully-qualified form before the call, not during it. A canonicalization step at deploy is cheap; a partially-bound identifier in the call site is a foot-gun.Treat layered templating as a hazard. The Step Functions state machine builds SQL strings with States.Format , where the reserved-character escape rules say that a backslash followed by a single quote is the way to embed a literal single quote inside the template, and that backslashes in JSON strings must themselves be escaped. SQL's escape for the same character is two single quotes in a row. Putting SQL escapes inside a Step Functions template produces an invalid SQL string; the failure shows up at runtime, not at deploy. Any time templating is layered - JSON wrapping SQL, YAML wrapping JSON, shell wrapping anything - the outer layer's escape rules govern the literals. The lesson is to mentally run the outer interpreter against the inner string and check what comes out; if you cannot, simplify the layering until you can.
If you are building scheduled destructive automation against a different system, the cutoff-pinning rule and the layered-templating rule almost certainly apply. The ARN-canonicality rule is AWS-specific, but its shape - "resolve identifiers before the destructive call, not during it" - is general. Push back if you have run into a class of problem that these three do not cover; I would update the pattern.
If you are building it against the same stack the post describes, the cdc-streaming-pipeline and firehose-iceberg-pipeline skills in jaingxyz/aws-data-skills bake all three lessons into the deployment guidance an agent will follow when you ask it to set this up. That repo is the ratchet on whether anyone has to learn these by trial-and-error again.
What unattended operation actually means
Unattended is the wrong word for "set it and forget it." Nothing in production is forgotten; it is observed by an automated system instead of a human. The interesting question is what the automated system needs to look at to decide the pipeline is healthy, and the answer for this stack turns out to be three numbers and a count.
The state machine has been running on its daily schedule against a live deployment in
us-east-1. The simulator has been continuously driving change events into Aurora DSQL at roughly one event per second. The four signals I check, in order:Did every scheduled execution complete with status SUCCEEDED? A single
aws stepfunctions list-executions --status-filter FAILED query answers it. At the time of writing, every execution since the schedule was enabled - manual baseline plus daily auto-fires - has SUCCEEDED. None has FAILED, none has aborted via the safety check (which would also surface as a FAILED execution by design, since the abort-no-archive path explicitly fails after publishing the SNS alert; success-with-no-prune is not a state I want the operator to scroll past).Was the duration stable? Each execution's
stopDate - startDate should sit in a narrow band; a slowly-growing duration is the early signal that the prune is falling behind the simulator's write rate. The manual baseline took 31 seconds and pruned 1,704,793 rows (it ran against a multi-week backlog because the simulator had been running long before the schedule was enabled). The first auto-fire 16 minutes later took 31 seconds and pruned only the small slice of rows that had crossed the 24-hour cutoff in those 16 minutes. After a week of unattended operation the picture is the same: eight executions total, durations between 31.0 and 34.6 seconds (average 32.8), per-run row counts varying between roughly 147K and 1.7M depending on the window - because most of the wall-clock is the VACUUM of the freed space, not the DELETE itself. If the duration started climbing toward minutes, that is when I'd start asking whether the retention horizon is too long for the write rate, or whether VACUUM is running into something that throttles it. Eight days in, no climb.Does the hot table's oldest row match the last execution's cutoff? This is the cheapest invariant test of "the prune did what it was supposed to do." A query against
cdc_events for the minimum commit timestamp should return a value within a few seconds of the most recent run's cutoff. Right now hot's oldest commit is 2026-06-14 06:24:13.385 - matching the cutoff 2026-06-14 06:24:11 that the most recent auto-fire pinned, within two seconds. If the gap between "now minus 24 hours" and "hot's oldest" widens past a few minutes, either the schedule has stopped firing or the DELETE is leaving rows behind.How many SNS messages did the alert topic publish? Zero, on any healthy day. The state machine only publishes on AbortNoArchive (cold-side empty for the window) or FailWithAlarm (any state errored). A single CloudWatch metric query against
NumberOfMessagesPublished over the last seven days answers it. Anything non-zero means an operator should look - and because the topic also has an email subscriber, anything non-zero means an operator already has, in their inbox, the execution name and a one-line cause.The post you are reading was drafted on a one-week soak. Hot has 210,032 rows; cold has 1,759,428; eight executions in seven days, all SUCCEEDED; zero SNS publishes in the last fourteen days; zero failed executions; the cutoff invariant matches within two seconds on every run. The four signals are clean. The Cost Explorer breakdown that Part 2a §3 promised needs another billing-cycle pass to be honest about - I will publish it in the companion repo once the numbers are stable, rather than backfill component reasoning into this section.
The shape of the four signals generalizes. If you build scheduled destructive automation against a different stack, the equivalents are: an execution-status query, a duration trend, an invariant test of "what should be true after a successful run", and a count of any time the system tried to alert. Together they answer "is this healthy" in less time than reading a dashboard.
Closing
The two-part pattern is now: a serverless data architecture that holds operational and analytical state on the same primitives, scales through small parameter tweaks rather than redesigns, and runs unattended once you give it the right safety check. The companion repo at jaingxyz/dsql-redshift-cdc-pipeline deploys the whole thing into your account. The skills at jaingxyz/aws-data-skills hand the patterns to an AI coding agent that can apply them to your own data without you having to read either of these posts again.
This is where I pause the soak experiment for now and turn to what is next. Three threads I want to pull on, in roughly this order:
- Iceberg time-travel for reproducible ML training sets. Right now the ML-features example in Part 2a uses a windowed predicate over the unified view. That is fine for online inference, but a model trained on "the last 18 months as of today" is unreproducible by tomorrow. Iceberg has
VERSION AS OFandTIMESTAMP AS OFnatively; pinning a snapshot for the training read turns the cold archive into a versioned feature store for free. This is the experiment I would run first, and the one most likely to make Part 3. - Alarm-gated tiering. The current safety check asks "did the cold archive receive any rows for the same window." A finer one would gate the prune on Firehose's
DeliveryToS3.DataFreshnessbeing healthy. I have a sketch but have not deployed it. If the soak surfaces a case the coarse check misses, this becomes urgent. - Redshift data sharing for the unified view layer. A second team wanting live access to
cdc_eventsand the*_unifiedviews today would mean sharing workgroup credentials or copying data. Redshift datashares let the producer publish a named bundle of tables and views; a consumer namespace creates a database from the datashare and queries them live, on its own compute, without bytes leaving the producer's storage. I went and tested whether this works against the unified view layer specifically: the share accepts the regular table, the external (Spectrum) Iceberg table, and the late-binding views built on top of both. The producer side is comfortable with the whole stack; the part that still needs verification end-to-end is the consumer side - whether a consumer queryingcdc_events_allthrough a datashare actually resolves the external-schema leg without separate Glue catalog grants. That is the Part 3 experiment - take what I confirmed today and finish it across two namespaces.
If you build any of this for a workload of your own, the comment thread, repo issues, or an email all reach me. The pattern is one shape, and I would like to see what other shapes hold up under similar constraints.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article