Zero-ETL DSQL to Redshift (almost)

Aurora DSQL doesn’t ship with the official zero-ETL feature yet, but its CDC stream + Kinesis + Lambda + Redshift Serverless gets you to the same place in seconds. Working code, AGPL-licensed.

Companion repo: github.com/jaingxyz/dsql-redshift-cdc-pipeline — CloudFormation template, Lambda processor, schemas, simulator, and six categories of analytical queries.

The problem

Your operational database serves checkouts. Your analytics warehouse answers business questions. Let analytical queries run against the OLTP path and you take down checkout for everyone. Wait for nightly batch ETL and your dashboards are 24 hours stale — on Black Friday, 24 hours is forever. Change Data Capture (CDC) is the bridge. What follows is the pipeline end-to-end on Aurora DSQL’s new preview CDC feature, with Kinesis, Lambda, and Redshift Serverless filling in the rest.

Black Friday scenario

It’s 11:58 PM on Thanksgiving. Your e-commerce platform is about to face its biggest traffic spike of the year. Your CEO wants a live dashboard showing sales velocity. Your fraud team needs real-time anomaly detection. Your inventory manager is monitoring stock levels across 47 warehouses. Your marketing team is ready to fire promo codes based on cart abandonment.

If you’re still on a legacy OLTP database, it’s already at 80% CPU just handling checkouts. Aurora DSQL handles the operational layer differently — it scales horizontally without the capacity-planning ritual (AWS overview). But you still need a clean separation between the OLTP path and analytics.

The Architecture

Customer Action  →  Aurora DSQL  →  CDC Stream  →  Kinesis  →  Lambda  →  Redshift
   (checkout)     (transactions)   (changes)     (transport)  (transform)  (analytics)

Every order, every cart update, every inventory adjustment flows through this pipeline in seconds — not hours.

Why not zero-ETL?

AWS offers a managed zero-ETL integration from Aurora MySQL and PostgreSQL into Redshift — point, click, done. DSQL isn’t on that list yet. Until it is, DSQL’s own docs point you at Kinesis, which is what this post is. The Lambda step is also where you’d put any in-flight transformation, filtering, or fan-out — things zero-ETL doesn’t do.

Why each component matters

Aurora DSQL handles the operational workload. It’s built for the messy reality of e-commerce: thousands of concurrent shoppers, flash sales, distributed inventory, multi-region orders. DSQL’s distributed architecture means a checkout in Tokyo doesn’t slow down a checkout in São Paulo.

The CDC Stream is DSQL’s native change capture. Every INSERT, UPDATE, and DELETE produces a JSON event with the row’s state. (In the current public preview, INSERT and UPDATE both arrive as op: "c"; the design here handles that transparently — see "Public preview semantics" below.) No triggers. No polling. No application-level dual-writes that drift out of sync.

Kinesis Data Streams (in on-demand mode) is the buffer that decouples everything. If Redshift goes down for maintenance, events queue up in Kinesis (this sample retains 24 hours; Kinesis on-demand supports up to 365 days). If a Lambda function fails, it retries from the last checkpoint. The operational database keeps humming along, oblivious.

Lambda transforms CDC events into Redshift-friendly inserts. It can enrich data. Filter sensitive fields. Route different event types to different tables. Or fan out to multiple destinations.

Amazon Redshift Serverless is where the magic happens for analytics. Columnar storage compresses typical workloads several-fold over row-oriented stores. Massively parallel processing scans large fact tables fast. And it auto-pauses when no one is querying.

Quick aside on why I built this

This started as a personal exercise. Aurora DSQL’s CDC feature went into public preview, I’d never deployed Redshift Serverless, and I wanted a realistic-shaped pipeline to learn both at once. E-commerce was the use case I picked because the unlocks below are genuinely well-documented in industry write-ups (Walmart on Kafka-driven replenishment, Klaviyo’s cart-abandonment benchmarks, BookMyShow’s Redshift modernization, Spotify’s event delivery, Stripe on real-time fraud) — they aren’t hypothetical, just not mine. Treat this post as “this is what other teams have built; here’s what it looks like end-to-end on the new DSQL → Redshift path.”

The repo and post are both AI-assisted. The integration test caught three real bugs the static reviewers missed; the README’s “Common pitfalls” section documents what they were. Worth a read before adapting the pattern.

What This Unlocks for E-Commerce

1. Real-time sales dashboards

Imagine a CEO opening the dashboard at 12:00 AM Black Friday: orders per minute by region, top-selling SKUs refreshing every 30 seconds, revenue versus last year in real time, conversion funnel from view → cart → checkout. None of these queries touch the operational database. They run against Redshift, where columnar storage and MPP make sub-second aggregation routine. (For a real-world Redshift analytics modernization, BookMyShow’s case study reports 80% cost savings on analytics after moving onto a similar architecture.)

2. Fraud detection that actually catches fraud

How many PlayStations does a fraudster receive before your batch job runs at 2 AM? With CDC, the fraud model sees every transaction within seconds. Card used in 3 countries in 5 minutes? Flag it. New account, $5,000 first order, expedited shipping? Flag it. Cart pattern matches a known bot signature? Block it. (Stripe reports AI-driven fraud systems at major financial institutions hit 91% detection rates versus 65–70% for rule-based systems — the gap is largely a data-freshness story.)

3. Dynamic inventory management

When the same red sweater is selling out in three regions simultaneously, you need to know now, not tomorrow morning. The CDC pipeline feeds inventory changes into the warehouse, where ML models forecast demand using both real-time and historical data, automated reorder triggers fire when velocity exceeds historical patterns, and cross-region rebalancing happens before stockouts cascade. (Walmart described their version of this as processing “tens of billions of messages from close to 100 million SKUs in less than three hours” via Kafka + CDC. The shape of the pipeline — change events feeding a planning engine — is what this sample mirrors at a much smaller scale.)

4. Personalization without the lag

“Products similar to what you viewed yesterday” → “products customers like you bought in the last hour.” That’s the difference CDC makes for a recommendation engine. Every product view, cart addition, and purchase flows through CDC into the warehouse, where collaborative filtering algorithms have access to fresh behavioral data instead of yesterday’s snapshot. (Spotify’s event delivery system moves over 8 million events/sec at peak — 350 TB/day — for exactly this reason: personalization needs fresh signals.)

5. Cart abandonment recovery

A shopper adds $300 worth of items to their cart, then disappears. Baymard’s 14-year tracking pegs the global cart abandonment rate at 70.19% — abandonment is the largest single revenue leak in e-commerce. Traditional systems wait until the next day’s batch job to send a recovery email; by then the shopper has bought from a competitor. With CDC: cart update events flow into the warehouse in real time, a simple query identifies abandoned carts, and an automated trigger sends a personalized recovery email within the hour. (Klaviyo’s 2024 benchmark — across 143K abandoned-cart flows — reports an average $3.65 revenue per recipient on this single flow, “37.74% higher than the next-best flow.”)

6. A/B testing at the speed of thought

Is the new “Buy Now” button converting better than the old one? With CDC, the answer is in the warehouse within minutes of deployment — statistical significance calculated in real time, segment-level analysis (mobile vs desktop, new vs returning), early stopping when results are clear. The big experimentation platforms (Spotify Confidence is one example) all live downstream of pipelines that look exactly like this one. No waiting until tomorrow to make today’s decision.

The Architectural Win: Separation of Concerns

The deepest reason this pattern works is architectural cleanliness. Your operational database has one job: serve customers reliably. Your analytics warehouse has a different job: answer business questions quickly.

When you mix them, both suffer:

• Analytics queries lock rows that customers need
• OLTP indexes optimize the wrong access patterns for analytics
• A long-running report can take down the checkout experience
• DBAs become the bottleneck for every business question

By separating them with CDC:

• The operational team owns DSQL — their SLA is checkout latency
• The analytics team owns Redshift — their SLA is query freshness and speed
• Neither team blocks the other
• Both can scale independently based on their workload patterns

What’s Actually Built

The companion repository — github.com/jaingxyz/dsql-redshift-cdc-pipeline — contains the full working pipeline. 100% serverless at every layer:

• Aurora DSQL cluster with native CDC streaming — distributed by design, no instances to manage
• Kinesis Data Streams in on-demand mode — pay per GB ingested, auto-scales without shard management
• Lambda CDC processor that loads into Redshift via parameterized SQL through the Data API, polling each statement to FINISHED so failures actually surface
• Order simulator that drives realistic e-commerce activity against DSQL for end-to-end testing
• Optional always-on simulator — ECS Fargate stack with an AWS Budget guardrail; off by default, switch on if you want a continuously-flowing pipeline to demo
• Redshift Serverless workgroup with an append-only cdc_events log and current-state views per source table
• IAM roles scoped to least-privilege for each component

End-to-end latency from a row change in DSQL to an insert in Redshift: typically under 10 seconds.

The cost story

Idle cost is near-zero:

• Lambda: $0 when not invoked
• Kinesis on-demand: pennies per GB, no idle shard charges
• Redshift Serverless: auto-pauses when no queries run
• DSQL: pay for active compute and storage only

A quiet dev environment stays in hobby-project budget territory; a small one I tore down twice this week ran a few dollars total. Production scales linearly with traffic — no over-provisioning, no capacity planning meetings, no “let’s just leave it running over the weekend just in case.”

Three things worth calling out

1. Public preview semantics

During DSQL CDC’s public preview, both INSERT and UPDATE arrive as op: "c". The companion repo handles this with append-only writes plus window-function views that pick the latest event per primary key. The pattern works today and continues to work when DSQL CDC reaches GA and adds a separate u op type — no code changes needed.

2. The SUPER column trick

The cdc_events table has one SUPER column that absorbs every source table's payload. New tables in DSQL require zero Lambda changes — only a new view downstream. This is the single biggest leverage point in the design.

3. Build it with AI coding assistants

AWS publishes purpose-built tooling that makes this kind of work dramatically faster:

• The **databases-on-aws plugin** in [awslabs/agent-plugins](https://github.com/awslabs/agent-plugins) contains a dsql agent skill that activates on phrases like "Aurora DSQL" or "DSQL schema" and steers schema design toward DSQL-friendly patterns.
• The Aurora DSQL MCP server in [awslabs/mcp](https://github.com/awslabs/mcp/tree/main/src/aurora-dsql-mcp-server) has a dsql_lint tool that catches DSQL-incompatible SQL before you run it. The schema in this sample was validated with it.
• The Redshift MCP server in [awslabs/mcp](https://github.com/awslabs/mcp/tree/main/src/redshift-mcp-server) lets the assistant run queries against your warehouse during development, so you can verify CDC events are landing where you expect.

For production workloads, AWS recently launched the Agent Toolkit for AWS as the successor — it adds IAM condition keys to distinguish agent actions from human actions and full CloudTrail visibility. The standalone repos remain great for experimentation.

When This Pattern Doesn’t Fit

CDC isn’t the right tool for every job. Pure OLTP applications with no analytical needs don’t need this overhead. Tiny datasets (under a few GB) might be fine with periodic full exports. Strict consistency requirements between source and analytics may need synchronous replication instead. Heavy transformations at ingestion might warrant a stream processor like Flink instead of a Lambda.

For e-commerce running real money on real traffic, though, CDC is a foundational capability — and the references above (Walmart, Klaviyo, Spotify, BookMyShow) are evidence the pipeline shape holds up at scale.

Takeaways

Once a CDC pipeline is in place, the warehouse becomes a platform: marketing attribution, finance close, ML training, ad-hoc engineering debugging — all on the same fresh data, none of it burdening the database that takes customer orders.

The architectural cleanliness is the real prize. Your operational team owns checkout latency. Your analytics team owns query freshness. Neither blocks the other. And both can scale independently with their own workload patterns.

If you build it from this sample: read the README’s “Common pitfalls” first. The integration test caught three bugs that no static review surfaced — they’re fixed in the code, but the same shapes will likely show up if you adapt the pattern elsewhere.

Get the code

Repository: github.com/jaingxyz/dsql-redshift-cdc-pipeline

The repo includes the DSQL source schema, the Redshift target schema with current-state views, the Lambda CDC processor (parameterized SQL, no string concatenation), an order simulator that drives realistic e-commerce activity, and six categories of analytical queries — sales velocity, fraud signals, inventory surge detection, abandoned cart recovery, customer LTV, and pipeline health.

AGPL-3.0 licensed. Reference sample, not production code.

Further reading

• Getting started with Change Data Capture in Amazon Aurora DSQL — the foundational AWS blog post on DSQL CDC.
• DSQL CDC documentation — authoritative reference for CDC streams and ordering semantics.
• Accelerating SQL analytics with the Amazon Redshift MCP server — bringing Redshift into AI coding workflows.

If this was useful, clap a few times so others can find it, and follow if you’d like more notes on AWS data infra. Questions or pushback welcome — leave a response below or open an issue on the GitHub repo.

Written with AI assistance and reviewed by a human before publication.