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 data | CDC test data |
|---|---|
| Static snapshots of tables | Ordered streams of operations (INSERT/UPDATE/DELETE) |
| Focus on row counts & aggregates | Focus on event ordering, primary‑key stability, tombstone handling, schema evolution |
| One‑off load verification | Continuous replay, back‑fill, and exactly‑once semantics |
| Simple referential integrity | Cross‑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.
| Dimension | Questions to Answer | Typical Weight |
|---|---|---|
| Source fidelity | Do we need exact column types, collations, and default values? | 5 |
| Transaction realism | Must a single CDC event contain multiple tables from the same DB transaction? | 4 |
| Schema evolution | Will columns be added, dropped, or type‑changed during the test window? | 4 |
| Volume & velocity | Target events/sec, burst size, and total event count for stress runs. | 3 |
| Determinism & replayability | Same seed → identical event stream? Needed for CI regression. | 5 |
| Data privacy | Can we use production‑like PII, or must we synthesize? | 5 |
| Toolchain integration | Does the generator plug into CI/CD, Airflow, dbt, or custom harnesses? | 3 |
| Maintainability | How much effort to update when source schema changes? | 4 |
| Cost & licensing | Open‑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.
- Catalog the source schema – Export DDL (including PK, FK, indexes, triggers). Store in version control.
- Define change‑event model – Decide on the CDC envelope (e.g., Debezium’s
before/afterstructure, Kafka Connect’sschema+payload). Document required fields:op,ts_ms,transaction_id,lsn. - 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”).
- Generate deterministic event streams – Use a seed‑based generator so the same scenario always yields the same JSON/Avro/Protobuf payloads.
- Persist events to a test topic or file store – This becomes the immutable input for the pipeline under test.
- Run the pipeline – Point the CDC consumer (Kafka Connect, Flink job, custom code) at the test topic.
- Validate output – Compare the sink state (warehouse tables, materialized views, cache) against an expected state derived from the scenario library.
- Capture metrics & diffs – Latency, throughput, duplicate count, missing tombstones, schema‑mismatch errors.
- 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
ddlblock tells the generator to emit a DebeziumschemaChangeevent 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.idmonotonic) - emits
schemaChangeevents 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
| Check | Implementation | Pass Criteria |
|---|---|---|
| Row‑level parity | SELECT COUNT(*) FROM snowflake.customers vs. expected count from scenario | Exact match |
| PK stability | Verify id values in sink equal those emitted in after payloads | No gaps, no duplicates |
| Tombstone handling | Query orders for customer_id = 10 after cascade delete | Zero rows |
| Schema evolution | DESCRIBE snowflake.customers includes phone column, nullable | Column present, type VARCHAR(20) |
| Event ordering | Compare ts_ms monotonicity per partition | No out‑of‑order events |
| Exactly‑once | Re‑run the same event stream twice; sink state unchanged | Idempotent |
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
| Category | Options | Strengths | Weaknesses |
|---|---|---|---|
| Open‑source generators | dbt seed, go-faker, SQLDataGenerator | Free, extensible, version‑controlled | Limited CDC‑specific envelopes; manual transaction modeling |
| Commercial CDC test platforms | Delphix, Tonic, Datprof | Built‑in masking, schema‑drift detection, UI | License 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 Connect | Python + confluent-kafka | Full control, can mimic any source | High 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
| Pitfall | Symptom | Root Cause | Mitigation |
|---|---|---|---|
| Static snapshots instead of streams | Pipeline passes “load test” but fails on replay | Test data generated as INSERT … SELECT on a clean DB | Generate ordered change events (see workflow step 4). |
| Missing transaction boundaries | Downstream sees partial updates, duplicate PKs | Generator emits each table change as independent event | Group multi‑table changes under a single transaction.id. |
| Ignoring DDL | New column appears in sink as NULL or causes deserialization error | No schemaChange events in test stream | Include DDL blocks in scenario library; verify connector emits schema change. |
| Non‑deterministic seeds | CI flaky – same scenario passes once, fails next run | Random data generators without fixed seed | Enforce seed per scenario; store seed in version control. |
| PII leakage | Production‑like emails appear in test logs | Using production dump without masking | Enable masking in generator; or synthesize with faker libraries. |
| Over‑reliance on a single scenario | Edge cases (e.g., large BLOB updates) never exercised | Scenario library too narrow | Add orthogonal scenarios for each CDC feature you depend on. |
| No validation of tombstones | Deleted rows reappear after back‑fill | Sink does not process op = "d" correctly | Explicit tombstone checks in validation suite. |
| Schema drift not tested | ALTER TABLE in prod breaks pipeline | Test data never includes DDL | Add 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.