Skip to content

jaingxyz/aws-data-skills / skills/cdc-streaming-pipeline

cdc-streaming-pipeline

Verified

Build a real-time change-data-capture pipeline from a transactional database (Aurora DSQL, Aurora Postgres, RDS) to Redshift Serverless, S3, or any other sink, via Kinesis Data Streams (or MSK). Covers producer setup, consumer Lambda correctness, the append-only event log + reconstruction pattern, schema-drift handling with SUPER + JSON_PARSE, and the operational gotchas (Redshift Data API parameter caps, async statement polling, poison-record handling, retry-and-bisect). Use when the user asks for "CDC to Redshift", "stream change data capture", "Aurora CDC", "DSQL CDC", "Postgres replication to S3", or "build a Lambda consumer for Kinesis CDC". Aurora DSQL public-preview specifics are flagged; the rest is source-agnostic.

reference/lambda-consumer.py

View on GitHub
"""
Reference CDC consumer Lambda.

Reads CDC events from a Kinesis Data Stream and appends them to a Redshift
Serverless `cdc_events` log table via the Redshift Data API. Downstream
*_current views materialize current state from this append-only log.

Design choices, in order of importance:

  1. Append-only writes. Safe under unordered or duplicate Kinesis delivery.
     Reconstruction picks the latest event per primary key by commit
     timestamp at view time.

  2. Single sink table for all source tables. SUPER (event_data) absorbs
     schema drift; new source tables work with zero code changes.

  3. Aurora DSQL preview compat: only "c" and "d" ops. Both INSERT and
     UPDATE arrive as "c". The reconstruction view treats the latest "c"
     per record_id as current state regardless. For non-DSQL sources
     (Debezium, DMS) "u" is also accepted; the same INSERT path handles it.

  4. Parameterized SQL only. All CDC values pass through Redshift Data API
     named parameters. No string concatenation. Robust against unusual
     values in source data and the standard defense against SQL injection.

  5. Chunking. Redshift Data API caps a single execute_statement at 200
     parameters. With 5 parameters per row, batches of 40 rows max.

  6. Synchronous status check. execute_statement is async. Poll
     describe_statement until each chunk reaches FINISHED. FAILED or
     ABORTED raises a real Lambda error so the Kinesis EventSourceMapping
     retries the batch instead of silently checkpointing past lost data.

  7. Poison-record handling. Malformed payloads (missing op, missing PK,
     missing timestamp, undecodable data) are counted as `skipped` and
     logged at WARNING. They do not advance to the SQL submission step
     and do not raise (raising would block the shard on truly bad data).

Environment variables:
  REDSHIFT_WORKGROUP        Serverless workgroup name (e.g. "default-workgroup")
  REDSHIFT_DATABASE         Database name (e.g. "dev")
  ROWS_PER_CHUNK            Optional override for rows-per-statement (default 40)
  STATEMENT_POLL_TIMEOUT_S  Optional cap on per-chunk poll wait (default 25s)
"""

import base64
import binascii
import json
import logging
import os
import time

import boto3

logger = logging.getLogger()
logger.setLevel(logging.INFO)

redshift = boto3.client("redshift-data")

WORKGROUP = os.environ["REDSHIFT_WORKGROUP"]
DATABASE = os.environ["REDSHIFT_DATABASE"]

PARAMS_PER_ROW = 5
MAX_PARAMS_PER_STATEMENT = 200
ROWS_PER_CHUNK = int(
    os.environ.get("ROWS_PER_CHUNK", MAX_PARAMS_PER_STATEMENT // PARAMS_PER_ROW)
)
STATEMENT_POLL_TIMEOUT_S = int(os.environ.get("STATEMENT_POLL_TIMEOUT_S", 25))


def _row_for_op(payload: dict):
    """Extract (op, row_dict) from a CDC payload, or None if unprocessable.

    Accepts c | u | d. DSQL preview will only ever produce c | d; non-DSQL
    sources may emit u. The downstream INSERT path is identical for c and u
    because both represent "row exists with this state at commit_timestamp".
    """
    op = payload.get("op")
    if op in ("c", "u"):
        row = payload.get("after")
    elif op == "d":
        row = payload.get("before")
    else:
        logger.warning("Skipping unknown op: %s", op)
        return None
    if not row:
        # A "d" event with no `before` block has no PK to tombstone with;
        # a "c"/"u" event with no `after` is malformed. Treat both as poison.
        logger.warning("Skipping op=%s with empty row payload", op)
        return None
    return (op, row)


def _build_parameterized_insert(rows: list[dict]) -> tuple[str, list[dict]]:
    """Build a multi-row INSERT with Redshift Data API named parameters.

    Returns (sql_string, parameters_list).
    """
    value_clauses = []
    parameters = []

    for i, r in enumerate(rows):
        # CAST(:ts AS BIGINT) is required: the Redshift Data API type-infers
        # numeric parameters as INTEGER, but DSQL CDC commit timestamps are
        # millisecond-since-epoch (13 digits) and overflow INT4. Dividing
        # by 1000.0 (not 1000) preserves sub-second precision in
        # commit_timestamp; integer division would truncate to whole seconds.
        value_clauses.append(
            f"(:t{i}, :op{i}, :id{i}, JSON_PARSE(:d{i}), "
            f"TIMESTAMP 'epoch' + CAST(:ts{i} AS BIGINT) / 1000.0 * INTERVAL '1 second')"
        )
        parameters.extend(
            [
                {"name": f"t{i}", "value": r["table"]},
                {"name": f"op{i}", "value": r["op"]},
                {"name": f"id{i}", "value": str(r["record_id"])},
                {"name": f"d{i}", "value": json.dumps(r["row"])},
                {"name": f"ts{i}", "value": str(r["commit_ts_ms"])},
            ]
        )

    sql = (
        "INSERT INTO cdc_events "
        "(source_table, operation, record_id, event_data, commit_timestamp) "
        f"VALUES {', '.join(value_clauses)}"
    )
    return sql, parameters


def _chunked(items: list, size: int):
    """Yield successive `size`-sized chunks from `items`."""
    for i in range(0, len(items), size):
        yield items[i : i + size]


def _await_statement(statement_id: str) -> None:
    """Poll describe_statement until FINISHED; raise on FAILED, ABORTED, or timeout."""
    deadline = time.monotonic() + STATEMENT_POLL_TIMEOUT_S
    delay = 0.2
    while True:
        resp = redshift.describe_statement(Id=statement_id)
        status = resp.get("Status")
        if status == "FINISHED":
            return
        if status in ("FAILED", "ABORTED"):
            raise RuntimeError(
                f"Redshift statement {statement_id} ended in {status}: "
                f"{resp.get('Error', '<no error message>')}"
            )
        if time.monotonic() > deadline:
            raise RuntimeError(
                f"Redshift statement {statement_id} did not finish within "
                f"{STATEMENT_POLL_TIMEOUT_S}s (last status={status})"
            )
        time.sleep(delay)
        delay = min(delay * 2, 1.0)


def lambda_handler(event, context):
    rows = []
    skipped = 0

    for record in event.get("Records", []):
        try:
            raw = base64.b64decode(record["kinesis"]["data"])
            payload = json.loads(raw)
        except (KeyError, TypeError, ValueError, binascii.Error) as e:
            logger.error("Failed to decode record: %s", e)
            skipped += 1
            continue

        result = _row_for_op(payload)
        if not result:
            skipped += 1
            continue
        op, row = result

        record_id = row.get("id")
        ts_ms = payload.get("ts_ms")
        if record_id is None or ts_ms is None:
            # Skip rows without a PK or commit timestamp rather than
            # inserting record_id='' or commit_timestamp=1970-01-01.
            logger.warning(
                "Skipping op=%s payload with missing id=%r or ts_ms=%r",
                op,
                record_id,
                ts_ms,
            )
            skipped += 1
            continue

        source = payload.get("source", {})
        rows.append(
            {
                "table": source.get("table", "unknown"),
                "op": op,
                "record_id": record_id,
                "row": row,
                "commit_ts_ms": ts_ms,
            }
        )

    if not rows:
        logger.info("No processable records (skipped=%d)", skipped)
        return {"processed": 0, "skipped": skipped}

    statement_ids = []
    chunks = list(_chunked(rows, ROWS_PER_CHUNK))
    for chunk_index, chunk in enumerate(chunks):
        sql, parameters = _build_parameterized_insert(chunk)
        try:
            response = redshift.execute_statement(
                WorkgroupName=WORKGROUP,
                Database=DATABASE,
                Sql=sql,
                Parameters=parameters,
            )
            statement_id = response["Id"]
            statement_ids.append(statement_id)
            _await_statement(statement_id)
        except Exception:
            # Re-raise so Kinesis event source mapping retries the batch
            # (with BisectBatchOnFunctionError isolating the bad records).
            # `submitted_chunks` lets the operator see in CloudWatch logs
            # how many chunks already landed so they can reason about
            # potential duplicates on retry. The append-only design
            # absorbs the duplication safely.
            logger.exception(
                "Chunk %d/%d failed (already-submitted statement_ids=%s)",
                chunk_index + 1,
                len(chunks),
                statement_ids,
            )
            raise

    logger.info(
        "Wrote %d rows in %d chunks to Redshift (skipped=%d)",
        len(rows),
        len(statement_ids),
        skipped,
    )
    return {
        "processed": len(rows),
        "skipped": skipped,
        "chunks": len(statement_ids),
        "statement_ids": statement_ids,
    }
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