jaingxyz/aws-data-skills / skills/dsql-to-analytics-pipeline
dsql-to-analytics-pipeline
VerifiedBuild a real-time analytics pipeline downstream of Aurora DSQL using the DSQL CDC stream. Covers consumer Lambda correctness, the append-only event log + ROW_NUMBER reconstruction pattern that absorbs unordered/duplicate delivery, Redshift Data API parameter-cap chunking, async statement polling, BisectBatchOnFunctionError, MaximumRecordAgeInSeconds, DLQ wiring, and the DSQL-preview gotcha that both INSERT and UPDATE arrive as op='c'. Triggers on phrases like: DSQL to analytics, DSQL to Redshift, analyze DSQL data, real-time DSQL analytics, DSQL CDC pipeline, Aurora DSQL change data capture, stream DSQL changes, replicate DSQL, DSQL to S3, DSQL to Iceberg, DSQL Kinesis Lambda, DSQL data warehouse.
Aurora DSQL to Analytics Pipeline Skill
This skill covers everything DOWNSTREAM of the DSQL CDC stream: how to wire
the stream into Kinesis, write a correct consumer Lambda, model events at
the analytics sink, and avoid the data-loss footguns that come with async
statement APIs and unordered delivery. Source-side DSQL concerns (DDL,
IAM auth, OCC, schema design) belong to the companion dsql skill.
Why this skill exists
In the Aurora DSQL CDC public preview, both INSERT and UPDATE arrive as
op='c'. Code that expects a separate 'u' op (the typical Debezium /
DMS shape) will silently treat every UPDATE as if no row existed, then
miss the latest state when downstream readers query a naive MAX(event_id)
view. The append-only + ROW_NUMBER-by-commit-timestamp pattern this skill
prescribes handles both cases correctly. There are several sibling
footguns (the Redshift Data API's 200-parameter cap, the async
execute_statement poll requirement, poison-record wedging) that cause
silent data loss when missed. Each one is called out below.
When to use
- You have an Aurora DSQL cluster and want the change events available for analytics in Redshift Serverless, S3 Tables (Iceberg), or both.
- You are writing the consumer Lambda that reads from the DSQL CDC Kinesis stream and lands rows in an analytics sink.
- You are choosing between an in-place upsert and an append-only event log for the sink table shape.
- You need the operational gotchas (batch sizes, retries, DLQ, async poll) before you ship.
When NOT to use
- You are working on the DSQL source cluster itself: DDL, IAM auth,
OCC retries, multi-tenant isolation, query plans. Use the companion
dsqlskill. - You are doing a one-shot bulk export from DSQL (not ongoing CDC).
Use a
COPY ... TOor pg_dump style flow; CDC is for change capture. - You need EMR / Glue / Spark jobs inside the stream. This skill scopes to Lambda + Redshift Data API + Firehose.
- The source is not Aurora DSQL. The reconstruction pattern still
applies, but several callouts (preview status, op='c'-only,
aws dsql create-stream, thedsql.amazonaws.comtrust principal) are DSQL-specific.
Architecture in one paragraph
DSQL cluster -> DSQL CDC stream (preview) -> Kinesis Data Stream ->
Lambda event source mapping -> parameterized INSERTs into a Redshift
Serverless append-only cdc_events table (or Firehose with an Iceberg
destination + transform Lambda) -> per-source-table *_current views
that reconstruct current state via
ROW_NUMBER() OVER (PARTITION BY record_id ORDER BY commit_timestamp DESC).
Reference Files
Load these as needed. Each file leads with the non-obvious lessons; AWS documentation paraphrasing is intentionally avoided.
references/cdc-stream-setup.md
When: MUST load before creating the DSQL CDC stream or wiring the
DSQL-to-Kinesis IAM trust.
Contains: Public-preview status callout, why there is no
CloudFormation resource type yet, idempotent aws dsql create-stream
get-streampolling, thedsql.amazonaws.comtrust principal withaws:SourceArnmatching<cluster-arn>/stream/*(NOT the bare cluster ARN), thec-and-d-only-in-preview gotcha, the CDC record envelope shape your consumer must parse.
references/append-only-pattern.md
When: MUST load before designing the sink schema. This is the
single most important design decision in the pipeline.
Contains: Why append-only beats in-place upsert under unordered
duplicate delivery, the cdc_events DDL with DISTKEY/SORTKEY rationale,
the *_current view template with ROW_NUMBER, the tombstone pattern
(operation <> 'd'), schema-drift absorption via SUPER.
references/lambda-consumer.md
When: MUST load before writing or reviewing the consumer Lambda.
Each pattern in here was earned by debugging a silent data-loss class.
Contains: Parameterized SQL only (named params via the Data API
Parameters= argument), the 200-parameter cap and ROWS_PER_CHUNK math,
async describe_statement polling with exponential backoff, re-raise on
failure to drive Kinesis retry, poison-record skipping vs raising,
Lambda timeout sizing, full handler skeleton.
references/event-source-mapping.yaml
When: Load when authoring the EventSourceMapping CFN resource.
Contains: Complete CFN snippet with BatchSize,
MaximumBatchingWindowInSeconds, MaximumRetryAttempts,
MaximumRecordAgeInSeconds, BisectBatchOnFunctionError,
DestinationConfig.OnFailure -> SQS DLQ, plus the DLQ resource and
the placeholder Lambda body that fails loudly if real code is not
deployed.
references/sink-redshift.md
When: Load when the sink is Redshift Serverless.
Contains: IAM policy with the redshift-data:* resource-must-be-*
quirk, redshift-serverless:GetCredentials for IAM-auth into the
workgroup, JSON_PARSE(:d) to land SUPER, event_data."col"::TYPE
projection rules, enable_case_sensitive_identifier for mixed-case
JSON keys, PartiQL unnest pattern for SUPER arrays.
references/sink-s3-iceberg.md
When: Load when the sink (or additional sink) is S3 Tables / Iceberg via Firehose. Contains: Why a transform Lambda is required (Firehose maps top-level JSON keys to Iceberg columns by name, so the CDC envelope must be flattened), pointer to the sibling streaming-into-data-lake skill that covers the Firehose-specific footguns end-to-end.
Cross-references to complementary skills
Each row tells you which skill owns a topic so you do not duplicate the guidance here.
| If you also need... | Load this skill |
|---|---|
| DSQL DDL, schema design, IAM auth, OCC, query plans | dsql (same plugin) |
| Validate SQL is DSQL-compatible before running it | dsql -> dsql_lint MCP tool |
| Land CDC into S3 Tables (Iceberg) via Firehose | streaming-into-data-lake (data-analytics plugin) for Firehose / Iceberg-specific footguns |
| Query a Redshift Serverless external schema over S3 | The Redshift external-schema skill (lakehouse-style) in your data-analytics plugin |
| Generic Kinesis / Lambda / SQS reference | The serverless skill in aws-core |
Quick start
- Stand up the source. Use the
dsqlskill to create the cluster and validate the schema withdsql_lint. - Create the CDC stream (cdc-stream-setup.md).
Kinesis stream + IAM role first, then
aws dsql create-stream, then poll untilACTIVE. There is no CFN type yet; script it. - Create the analytics sink schema
(append-only-pattern.md).
cdc_eventstable + one*_currentview per source table. Resist the urge to model in-place upserts. - Deploy the consumer Lambda + EventSourceMapping
(lambda-consumer.md,
event-source-mapping.yaml).
Set
BisectBatchOnFunctionError,MaximumRetryAttempts,MaximumRecordAgeInSeconds, and a DLQ. - Wire IAM for the sink (sink-redshift.md
for Redshift; sink-s3-iceberg.md for
Iceberg). Remember:
redshift-data:*only acceptsResource: "*". - Drive load and verify. A trickle of inserts at the source should
land as new rows in
cdc_events; the*_currentview should reflect the latest state per primary key within seconds.
Common workflows
Workflow 1: Add a new source table
The append-only sink absorbs new tables with NO DDL. The Lambda routes
all events to the same cdc_events table; only a new *_current view
is needed if downstream consumers want a typed projection.
- Add the table to DSQL via
transact(see thedsqlskill). - Confirm new events appear in
cdc_eventswith the newsource_tablevalue (SELECT DISTINCT source_table FROM cdc_events). - Author a
*_currentview per the template in append-only-pattern.md.
Workflow 2: Add a column to an existing source table
Same answer: zero sink-side DDL required for ingestion. The new column
appears in event_data automatically because SUPER is schema-flexible.
The matching *_current view needs a new
event_data."new_col"::TYPE AS new_col line if the column should be
projected.
Workflow 3: Diagnose missing rows in *_current
The append-only pattern fails closed: if a row is missing from
*_current, check (in order):
- Is the row in
cdc_events?SELECT * FROM cdc_events WHERE record_id = '...' ORDER BY commit_timestamp DESC LIMIT 5. If absent, the event never reached Redshift -> check Lambda CloudWatch logs and the DLQ. - Is the latest event a
'd'(delete tombstone)? The view filters those out. - Is the
commit_timestampparsing correct? See theCAST(:ts AS BIGINT) / 1000.0note in lambda-consumer.md. A wrong divisor moves the timestamp by 3 orders of magnitude and the ROW_NUMBER ordering picks the wrong event.
Workflow 4: Reset / replay
Truncate cdc_events, set the EventSourceMapping
StartingPosition: TRIM_HORIZON, redeploy. CDC retention on the Kinesis
stream defines the replay window (default 24 hours, max 365 days).
Error scenarios
execute_statementsucceeded but rows are missing. You skipped the async poll. See lambda-consumer.md section 3.Number of parameters in statement exceeds maximum allowed (200).ROWS_PER_CHUNKis set above200 / params_per_row. Lower it.- DSQL CDC stream stuck in
CREATING. The IAM trust policy almost certainly mismatches. Checkaws:SourceArnisArnLikeagainst<cluster-arn>/stream/*, not the bare cluster ARN. See cdc-stream-setup.md. - Lambda hits its timeout under load. Either lower
BatchSizeor raise the function timeout. The math: `ceil(BatchSize / ROWS_PER_CHUNK)- STATEMENT_POLL_TIMEOUT_S` plus boto3 RTT.
- DLQ is filling with batches. Pull a sample, decode the
kinesis.dataBase64, inspect the payload. Common causes: source schema change the Lambda chokes on, malformedts_ms, missing primary key.
Additional resources
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deployment | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |