Implement CDC from an OLTP database to a data warehouse

WHAT THIS TESTS: Your ability to analyze trade-offs between data replication methods, specifically focusing on the impact to a critical production system. The interviewer wants to see if you prioritize source system stability over implementation ease. This isn't just a data definition question; it's a systems design question about production safeguards.

A GOOD ANSWER COVERS: Four key points. First, state the primary goal is protecting the OLTP database's performance. Second, propose two primary methods for comparison: log-based CDC and trigger-based CDC. Third, detail the pros and cons. Log-based CDC (e.g., using Debezium with Kafka) has minimal source impact and captures all changes, but is complex to set up. Trigger-based CDC is simpler to implement but adds overhead to every write transaction on the source, increasing latency and lock contention. Fourth, provide a clear recommendation: log-based CDC is the standard for modern, high-throughput systems despite its initial complexity.

COMMON WRONG ANSWERS: A major red flag is suggesting a query-based (timestamp) approach without immediately highlighting its severe flaws: it cannot capture deletes, it puts heavy load on the source with frequent polling, and it relies on perfectly maintained updated_at columns which often drift. Another weak answer is recommending triggers without acknowledging the performance penalty. For example, adding a trigger can increase write latency by 5-15% per transaction, which is unacceptable for a high-volume OLTP system. Finally, answers that lack specific tool names (Debezium, Maxwell, GoldenGate) suggest theoretical, not practical, knowledge.

LIKELY FOLLOW-UPS: How would you handle schema evolution? (Answer: Tools like Debezium propagate DDL changes, but you need a compatible sink and a schema registry). What if the transaction log is purged before your reader consumes it? (Answer: This is a critical failure mode. You need robust monitoring and alerting on consumer lag, and potentially increase log retention periods on the source DB). How do you ensure exactly-once delivery? (Answer: This is complex, often requiring idempotent consumers or transactional writes to the sink).

ONE CONCRETE EXAMPLE: To replicate a users table from a production PostgreSQL database to Snowflake, we would use Debezium's PostgreSQL connector to read from the WAL (Write-Ahead Log). The connector publishes change events (one for each INSERT, UPDATE, DELETE) to a Kafka topic. A Kafka Connect sink connector for Snowflake then consumes from this topic and applies the changes to the target table. This provides near real-time replication (latency under 5 seconds) with negligible impact on the production Postgres instance.