Design a CDC pipeline that handles schema evolution gracefully

WHAT THIS TESTS: This question tests whether you understand that CDC streams are living contracts that will inevitably drift, and whether you can design an analytics architecture that isolates upstream changes from downstream consumers. The interviewer cares about your ability to prevent silent data loss, corrupted joins, and broken dashboards when a source database adds columns, renames fields, or widens types. They want to see systems thinking around compatibility guarantees, not just point solutions.

A GOOD ANSWER COVERS: First, enforce a schema registry with versioning and compatibility rules. Use Confluent Schema Registry or AWS Glue Schema Registry to version Avro, Protobuf, or JSON schemas, setting policies like BACKWARD or FULL so producers cannot push breaking changes unnoticed. Second, choose serialization formats that natively support schema evolution. Avro and Parquet handle added nullable columns gracefully, while Delta Lake or Iceberg provide table-level schema evolution and time travel. Third, decouple ingestion from serving. Land CDC events into a raw bronze layer with flexible typing, then apply schema enforcement only when casting to structured silver tables; this absorbs drift without breaking historical data. Fourth, automate detection and CI/CD gating. Run compatibility checks in the deployment pipeline and alert on schema drift before changes reach production. Fifth, plan for rollback. Maintain a versioned contract so you can revert to a previous schema state if a change introduces corruption.

COMMON WRONG ANSWERS: A major red flag is suggesting you pause the pipeline and manually run ALTER TABLE statements across all downstream databases. This creates downtime and does not scale past a few tables. Another mistake is ignoring type changes entirely, assuming string casting will always work; this leads to silent truncation or join failures. Hardcoding column indexes instead of names is also dangerous because CDC events may shift ordinals when columns are added. Finally, proposing to reject all schema changes by default shows inflexibility; modern platforms expect controlled evolution, not frozen schemas.

LIKELY FOLLOW-UPS: The interviewer may ask how you would handle a dropped column without losing historical data, which tests your understanding of soft deletes or schema-on-read patterns. They might probe how to coordinate schema changes across microservices, looking for answers about contract testing and consumer-driven compatibility. Another follow-up is performance: how does a schema registry impact serialization throughput, or how do you backfill a new column added mid-stream?

ONE CONCRETE EXAMPLE: Imagine an e-commerce CDC pipeline capturing orders from PostgreSQL. A product team adds a nullable discount_code column to the orders table. Because the pipeline uses the Confluent Schema Registry with BACKWARD compatibility, the new schema version is accepted. The raw Kafka topic ingests the new field without breaking existing consumers. A downstream Spark job reading Delta Lake tables automatically merges the new column into the bronze layer with NULL defaults for historical rows. The analytics team discovers the field the next day and updates their dbt models to expose it, while legacy dashboards remain unaffected because they select specific columns rather than using SELECT star.

Source: branchboston.com