Skip to content

jaingxyz/aws-data-skills / skills/dsql-to-analytics-pipeline

dsql-to-analytics-pipeline

Verified

Build 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 dsql skill.
  • You are doing a one-shot bulk export from DSQL (not ongoing CDC). Use a COPY ... TO or 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, the dsql.amazonaws.com trust 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-stream polling, the dsql.amazonaws.com trust principal with aws:SourceArn matching <cluster-arn>/stream/* (NOT the bare cluster ARN), the c-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 plansdsql (same plugin)
Validate SQL is DSQL-compatible before running itdsql -> dsql_lint MCP tool
Land CDC into S3 Tables (Iceberg) via Firehosestreaming-into-data-lake (data-analytics plugin) for Firehose / Iceberg-specific footguns
Query a Redshift Serverless external schema over S3The Redshift external-schema skill (lakehouse-style) in your data-analytics plugin
Generic Kinesis / Lambda / SQS referenceThe serverless skill in aws-core

Quick start

  1. Stand up the source. Use the dsql skill to create the cluster and validate the schema with dsql_lint.
  2. Create the CDC stream (cdc-stream-setup.md). Kinesis stream + IAM role first, then aws dsql create-stream, then poll until ACTIVE. There is no CFN type yet; script it.
  3. Create the analytics sink schema (append-only-pattern.md). cdc_events table + one *_current view per source table. Resist the urge to model in-place upserts.
  4. Deploy the consumer Lambda + EventSourceMapping (lambda-consumer.md, event-source-mapping.yaml). Set BisectBatchOnFunctionError, MaximumRetryAttempts, MaximumRecordAgeInSeconds, and a DLQ.
  5. Wire IAM for the sink (sink-redshift.md for Redshift; sink-s3-iceberg.md for Iceberg). Remember: redshift-data:* only accepts Resource: "*".
  6. Drive load and verify. A trickle of inserts at the source should land as new rows in cdc_events; the *_current view 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.

  1. Add the table to DSQL via transact (see the dsql skill).
  2. Confirm new events appear in cdc_events with the new source_table value (SELECT DISTINCT source_table FROM cdc_events).
  3. Author a *_current view 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):

  1. 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.
  2. Is the latest event a 'd' (delete tombstone)? The view filters those out.
  3. Is the commit_timestamp parsing correct? See the CAST(:ts AS BIGINT) / 1000.0 note 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_statement succeeded 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_CHUNK is set above 200 / params_per_row. Lower it.
  • DSQL CDC stream stuck in CREATING. The IAM trust policy almost certainly mismatches. Check aws:SourceArn is ArnLike against <cluster-arn>/stream/*, not the bare cluster ARN. See cdc-stream-setup.md.
  • Lambda hits its timeout under load. Either lower BatchSize or 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.data Base64, inspect the payload. Common causes: source schema change the Lambda chokes on, malformed ts_ms, missing primary key.

Additional resources

writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 322kUpdated Claude CodeCodexVerifiedInstall
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: 198kUpdated Claude CodeCodexVerifiedInstall
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: 119kUpdated Claude CodeCodexVerifiedInstall
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: 118kUpdated Claude CodeCodexVerifiedInstall