Design relational-to-NoSQL migration pipeline

Quick Overview

This question evaluates a candidate's competency in designing a relational-to-NoSQL migration pipeline, covering distributed systems and data engineering concepts such as consistent snapshotting, change-data-capture, per-key ordering, idempotency, exactly-once behavior, schema evolution, partitioning, and operational reliability.

Design relational-to-NoSQL migration pipeline

Company: Google

Role: Software Engineer

Category: System Design

Difficulty: hard

Interview Round: Technical Screen

Design a system to migrate data from a relational database to a NoSQL key-value store. Implement a one-time consistent snapshot that converts each row into a {primary_key: value} JSON, then continue with change data capture (CDC) via the write-ahead log to capture updates. Explain snapshot consistency, ordering with CDC, idempotency, exactly-once semantics across replays, schema evolution, backfill catch-up, and failure recovery. Include queueing, partitioning, and monitoring.

Overview: This question evaluates a candidate's competency in designing a relational-to-NoSQL migration pipeline, covering distributed systems and data engineering concepts such as consistent snapshotting, change-data-capture, per-key ordering, idempotency, exactly-once behavior, schema evolution, partitioning, and operational reliability.

|Home/System Design/Google
Google logo
Google
Sep 6, 2025
hardSoftware EngineerTechnical ScreenSystem Design
13
0

System Design: Migrate From Relational DB to a NoSQL Key-Value Store with Snapshot + CDC

Context

You need to migrate data from an OLTP relational database to a NoSQL key-value store for read-optimized workloads. The target representation is a JSON document per primary key, stored as {primary_key: value} in the key-value store. The system must first take a one-time consistent snapshot of the entire dataset, then continuously apply changes captured from the database's write-ahead log (CDC).

Assume:

  • Source DB: a relational database with MVCC and logical change streams (e.g., PostgreSQL logical replication or MySQL binlog).
  • Transport: a durable, partitioned queue supporting ordered delivery per key (e.g., Kafka/Pub/Sub).
  • Sink: a scalable key-value store (e.g., DynamoDB, Bigtable, Cassandra, Redis), supporting conditional updates or a way to emulate them.

Requirements

Design and explain a system that:

  1. Performs a one-time, consistent snapshot, converting each row into {primary_key: value} JSON.
  2. Continues with CDC via the write-ahead log to capture inserts, updates, and deletes.
  3. Ensures:
    • Snapshot consistency
    • Ordering with CDC (especially per-key ordering)
    • Idempotency
    • Exactly-once semantics across replays (or effective-once)
    • Schema evolution handling
    • Backfill catch-up from snapshot to CDC
    • Failure recovery
  4. Includes details on queueing, partitioning, and monitoring.

Deliverables:

  • High-level architecture
  • Key data model choices (key format, value JSON, metadata)
  • Algorithms for snapshot, CDC ingestion, and sink application
  • Explanations and guardrails for the above correctness and operational concerns

Submit Your Answer to Earn 20XP

Sign in to leave a comment

Loading comments...

Browse More Questions

More System Design•More Google•More Software Engineer•Google Software Engineer•Google System Design•Software Engineer System Design