Quality is not optional. It's our standard. Free QA tools for testers and developers.

Test Data for Change Data Capture Pipelines

Test Data for Change Data Capture Pipelines

Change Data Capture (CDC) pipelines move every insert, update, and delete from a source database to downstream consumers—message brokers, data lakes, analytics warehouses, or micro‑service caches. The pipeline’s correctness hinges on what changes look like, not just that they arrive. If the test data does not exercise the full spectrum of CDC edge cases, bugs slip into production and surface as duplicate rows, missing tombstones, or schema‑drift errors that are expensive to trace.

This guide walks through a practical, evidence‑driven approach to building and managing test data for CDC pipelines. It covers decision criteria, a repeatable workflow, a worked end‑to‑end scenario, validation checks, tool considerations (including a free generator you can try today), common pitfalls, and a concise checklist you can hand to the team.


1. Why CDC Test Data Is Different

Traditional ETL test dataCDC test data
Static snapshots of tablesOrdered streams of operations (INSERT/UPDATE/DELETE)
Focus on row counts & aggregatesFocus on event ordering, primary‑key stability, tombstone handling, schema evolution
One‑off load verificationContinuous replay, back‑fill, and exactly‑once semantics
Simple referential integrityCross‑table transaction boundaries, cascading deletes, partial updates

Because CDC emits a log of changes, the test data must be a log as well—not a set of tables you can truncate and reload. The data generator therefore needs to produce time‑ordered change events that respect transaction boundaries and the source database’s concurrency model.


2. Decision Criteria for a Test‑Data Strategy

Before writing scripts or buying a tool, agree on the following dimensions with the team. Use the table as a decision matrix; each row can be scored 1‑5 (1 = low priority, 5 = critical) to surface the most important requirements.

DimensionQuestions to AnswerTypical Weight
Source fidelityDo we need exact column types, collations, and default values?5
Transaction realismMust a single CDC event contain multiple tables from the same DB transaction?4
Schema evolutionWill columns be added, dropped, or type‑changed during the test window?4
Volume & velocityTarget events/sec, burst size, and total event count for stress runs.3
Determinism & replayabilitySame seed → identical event stream? Needed for CI regression.5
Data privacyCan we use production‑like PII, or must we synthesize?5
Toolchain integrationDoes the generator plug into CI/CD, Airflow, dbt, or custom harnesses?3
MaintainabilityHow much effort to update when source schema changes?4
Cost & licensingOpen‑source vs. commercial, cloud‑hosted vs. on‑prem.2

Outcome: The highest‑scoring rows become non‑negotiable requirements for the generator you choose or build.


3. End‑to‑End Workflow for CDC Test Data

Below is a repeatable workflow that works whether you hand‑craft SQL scripts, use a dedicated generator, or combine both.

  1. Catalog the source schema – Export DDL (including PK, FK, indexes, triggers). Store in version control.
  2. Define change‑event model – Decide on the CDC envelope (e.g., Debezium’s before/after structure, Kafka Connect’s schema+payload). Document required fields: op, ts_ms, transaction_id, lsn.
  3. Create a scenario library – Each scenario is a named set of transactions that exercise a specific CDC behavior (e.g., “single‑row update”, “multi‑table transaction”, “cascade delete”, “DDL add column”).
  4. Generate deterministic event streams – Use a seed‑based generator so the same scenario always yields the same JSON/Avro/Protobuf payloads.
  5. Persist events to a test topic or file store – This becomes the immutable input for the pipeline under test.
  6. Run the pipeline – Point the CDC consumer (Kafka Connect, Flink job, custom code) at the test topic.
  7. Validate output – Compare the sink state (warehouse tables, materialized views, cache) against an expected state derived from the scenario library.
  8. Capture metrics & diffs – Latency, throughput, duplicate count, missing tombstones, schema‑mismatch errors.
  9. Automate in CI – Gate merges on a “green” CDC test run.

Tip: Keep the scenario library small but orthogonal. Ten well‑chosen scenarios cover more ground than a hundred random rows.


4. Worked Example: MySQL → Kafka → Snowflake

4.1 Source Schema (simplified)

CREATE TABLE customers (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    email       VARCHAR(255) NOT NULL UNIQUE,
    full_name   VARCHAR(200),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


CREATE TABLE orders (
    id           BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id  BIGINT NOT NULL,
    amount_cents INT NOT NULL,
    status       ENUM('NEW','PAID','SHIPPED','CANCELLED') NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

4.2 CDC Envelope (Debezium‑style)

{
  "schema": { … },
  "payload": {
    "before": null,
    "after": { "id": 1, "email": "alice@example.com", "full_name": "Alice", "created_at": 1724012345000, "updated_at": 1724012345000 },
    "source": { "version": "2.5.0", "connector": "mysql", "name": "prod", "ts_ms": 1724012345123, "snapshot": false, "db": "shop", "sequence": null, "table": "customers", "server_id": 1, "gtid": null, "file": "binlog.000001", "pos": 1234, "row": 0, "thread": 12, "query": null },
    "op": "c",
    "ts_ms": 1724012345123,
    "transaction": { "id": 0.1, "total_order": 1, "data_collection_order": 1 }
  }
}

4.3 Scenario Library (YAML)

scenarios:
  - name: "single_customer_insert"
    seed: 42
    transactions:
      - tables:
          customers:
            - op: "c"
              after:
                email: "bob@example.com"
                full_name: "Bob Builder"
  - name: "multi_table_txn"
    seed: 43
    transactions:
      - tables:
          customers:
            - op: "c"
              after:
                email: "carol@example.com"
                full_name: "Carol"
          orders:
            - op: "c"
              after:
                customer_id: "{{ref:customers.id}}"
                amount_cents: 1999
                status: "NEW"
  - name: "cascade_delete"
    seed: 44
    transactions:
      - tables:
          customers:
            - op: "d"
              before:
                id: 10
          orders:
            - op: "d"
              before:
                id: 100
                customer_id: 10
  - name: "schema_add_column"
    seed: 45
    ddl:
      - "ALTER TABLE customers ADD COLUMN phone VARCHAR(20);"
    transactions:
      - tables:
          customers:
            - op: "u"
              before:
                id: 5
                email: "dave@example.com"
                full_name: "Dave"
                phone: null
              after:
                id: 5
                email: "dave@example.com"
                full_name: "Dave"
                phone: "+1-555-0199"

Notes

  • {{ref:customers.id}} is a placeholder the generator resolves to the PK generated in the same transaction.
  • The ddl block tells the generator to emit a Debezium schemaChange event before the subsequent DML.

4.4 Generating the Event Stream

Using the free QA3 Test Data Generator (available at /tools/test-data-generator), you can feed the YAML and get a Kafka‑ready Avro file:

qa3-test-data-gen \
  --scenario-file scenarios.yaml \
  --output-format avro \
  --output-path ./cdc-test-data/ \
  --seed 42

The tool:

  • respects transaction ordering (transaction.id monotonic)
  • emits schemaChange events for DDL
  • produces deterministic output for a given seed (CI‑friendly)
  • can optionally mask PII (e.g., replace emails with user{{n}}@example.com)

4.5 Running the Pipeline



# Start a local Kafka + Debezium connector (Docker Compose)


docker compose up -d kafka connect mysql


# Load the generated events into the source topic


kafka-console-producer --broker-list localhost:9092 \
  --topic mysql.shop.customers < ./cdc-test-data/customers.avro
kafka-console-producer --broker-list localhost:9092 \
  --topic mysql.shop.orders < ./cdc-test-data/orders.avro

The Debezium connector reads the binlog (or the test topic if you use the embedded mode) and writes to downstream topics that the Snowflake sink consumes.

4.6 Validation Checks

CheckImplementationPass Criteria
Row‑level paritySELECT COUNT(*) FROM snowflake.customers vs. expected count from scenarioExact match
PK stabilityVerify id values in sink equal those emitted in after payloadsNo gaps, no duplicates
Tombstone handlingQuery orders for customer_id = 10 after cascade deleteZero rows
Schema evolutionDESCRIBE snowflake.customers includes phone column, nullableColumn present, type VARCHAR(20)
Event orderingCompare ts_ms monotonicity per partitionNo out‑of‑order events
Exactly‑onceRe‑run the same event stream twice; sink state unchangedIdempotent

Automate these checks with a small Python/pytest suite that runs after each pipeline execution. Store the expected state as JSON snapshots (generated from the same scenario library) to keep the test deterministic.


5. Tool Considerations

CategoryOptionsStrengthsWeaknesses
Open‑source generatorsdbt seed, go-faker, SQLDataGeneratorFree, extensible, version‑controlledLimited CDC‑specific envelopes; manual transaction modeling
Commercial CDC test platformsDelphix, Tonic, DatprofBuilt‑in masking, schema‑drift detection, UILicense cost, often tied to specific CDC vendors
QA3 Test Data Generator (free)/tools/test-data-generator• YAML‑driven scenario library <br>• Deterministic seeds <br>• Emits Debezium, Kafka Connect, and generic JSON envelopes <br>• PII masking & synthetic data plugins <br>• CLI + Docker image for CI• No GUI (yet) <br>• Limited to relational sources (MySQL, Postgres, SQL Server)
Custom script + Kafka ConnectPython + confluent-kafkaFull control, can mimic any sourceHigh maintenance, easy to drift from real CDC format

When to pick QA3’s generator

  • You need a single source of truth for both schema and change events.
  • Your CI pipeline runs on Linux containers and you prefer a CLI‑only tool.
  • You want deterministic replay without a heavyweight test‑data management platform.

If you already own a commercial platform that integrates with your CDC vendor, evaluate whether the extra licensing cost buys you masking or compliance features you cannot replicate yourself.


6. Common Pitfalls & How to Avoid Them

PitfallSymptomRoot CauseMitigation
Static snapshots instead of streamsPipeline passes “load test” but fails on replayTest data generated as INSERT … SELECT on a clean DBGenerate ordered change events (see workflow step 4).
Missing transaction boundariesDownstream sees partial updates, duplicate PKsGenerator emits each table change as independent eventGroup multi‑table changes under a single transaction.id.
Ignoring DDLNew column appears in sink as NULL or causes deserialization errorNo schemaChange events in test streamInclude DDL blocks in scenario library; verify connector emits schema change.
Non‑deterministic seedsCI flaky – same scenario passes once, fails next runRandom data generators without fixed seedEnforce seed per scenario; store seed in version control.
PII leakageProduction‑like emails appear in test logsUsing production dump without maskingEnable masking in generator; or synthesize with faker libraries.
Over‑reliance on a single scenarioEdge cases (e.g., large BLOB updates) never exercisedScenario library too narrowAdd orthogonal scenarios for each CDC feature you depend on.
No validation of tombstonesDeleted rows reappear after back‑fillSink does not process op = "d" correctlyExplicit tombstone checks in validation suite.
Schema drift not testedALTER TABLE in prod breaks pipelineTest data never includes DDLAdd at least one DDL scenario per release cycle.

7. Checklist for a CDC Test‑Data Run

- [ ] Source DDL exported and version‑controlled
- [ ] CDC envelope definition documented (Debezium, Kafka Connect, custom)
- [ ] Scenario library covers:
    - [ ] Single‑row INSERT/UPDATE/DELETE
    - [ ] Multi‑table transaction
    - [ ] Cascade DELETE / SET NULL
    - [ ] DDL: ADD COLUMN, DROP COLUMN, TYPE CHANGE
    - [ ] Large payload / BLOB / JSON column
    - [ ] High‑frequency burst (e.g., 10k events/sec)
- [ ] Deterministic seed assigned to each scenario
- [ ] PII masking policy applied (email, phone, SSN)
- [ ] Generator produces Avro/JSON/Protobuf matching envelope
- [ ] Events written to immutable test topic or file store
- [ ] Pipeline configured to consume from test topic
- [ ] Validation suite runs:
    - [ ] Row count parity
    - [ ] PK stability
    - [ ] Tombstone verification
    - [ ] Schema evolution check
    - [ ] Ordering & exactly‑once verification
- [ ] CI job fails on any validation error
- [ ] Results (Optional:
- [ ] Performance metrics captured (latency, throughput)
- [ ] Test data archived for audit/replay

Print this checklist, pin it to the team board, and tick it off for every CDC‑related change (new connector version, schema migration, sink upgrade).


Read more

GraphQL Test Data Generation for Queries and Mutations

A practical guide to “GraphQL Test Data Generation for Queries and Mutations,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.

Postman Test Data Generation with CSV and JSON Files

A practical guide to “Postman Test Data Generation with CSV and JSON Files,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.

Generate API Test Data Directly from an OpenAPI Specification

A practical guide to “Generate API Test Data Directly from an OpenAPI Specification,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.