System Design interview question · Senior and staff trade-offs · staff level
You must move a 600-million-row orders table from MySQL to DynamoDB, where data is modeled around access patterns instead of joins. How do you migrate with no downtime and prove the new model answers every query?
Free to read in full. This is 1 of the 200 questions in the System Design and Distributed Systems interview pack, with 268 diagrams. Get the pack, ₹999 · $20
Why it is asked
Moving between data models is harder than copying rows. Teams that copy MySQL tables one-to-one into DynamoDB find that half their queries have no key.
The answer
I would start from the access patterns, design the keys to serve each one, then migrate with change data capture and per-query shadow reads, and keep a way back until every query matches.
1. List the queries with their rates. For example: get an order by ID; a customer's orders, newest first; a restaurant's open orders today. Joins and ad hoc filters (one-off queries on any field) are not on the list for a reason. 2. Design keys from that list. Base table: partition key (the value DynamoDB hashes to place and find an item) ORDER#id. A secondary index with partition key CUSTOMER#id and sort key (the value items are ordered by inside one partition) by created time serves the customer list. A sparse index (one that holds only items that have the indexed attribute, here open orders), keyed by restaurant and date, serves the kitchen view. Data that MySQL joined (items, address) is copied into the order item, because DynamoDB has no joins. 3. Move the data: start CDC (reading MySQL's binary log) first, then backfill history in throttled batches. At 20,000 rows per second, 600 million / 20,000 = 30,000 seconds, about 8.3 hours; at 5,000, about 33.3 hours. Newer versions win over older rows. 4. Prove each query: shadow-read every access pattern from both stores and compare, until differences are zero or explained. 5. Move reads, then writes, behind flags, streaming changes back to MySQL so rollback is a flag flip.
What does not fit: reports that need joins or scans go to the data warehouse. A new query that no key serves needs a new index and a backfill, the real cost of this model.
What the interviewer listens for
- Start from access patterns and their rates
- Keys and indexes per query; copy into the order what MySQL joined (denormalize)
- CDC before throttled backfill; newer versions win
- Shadow-read and diff per access pattern
- Joins and scans move to the warehouse
Follow-ups the interviewer may ask
- How do you keep the copied address in orders correct? Hint: it should be a snapshot of the address used for that order, so it never needs updating.
- What if one restaurant chain writes thousands of orders per second? Hint: one partition key takes about 1,000 writes per second, so split a hot key (RESTAURANT#ID#0..9) and read all parts; 100,000 orders for one customer is fine, because the sort key and a limit read only the newest page.
Common mistakes
- Copying the MySQL tables one to one into DynamoDB and expecting the same queries to work.
- Checking row counts only, instead of comparing the answers to each real query.
Written by Roni Das, who builds distributed systems and AI systems for a living.
Learn it properly
This is taught step by step in the course lesson Data Migration.
One question is not enough for the interview.
This is 1 of the 200 questions in the System Design and Distributed Systems pack
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
More free questions from this pack
- A page calls 100 backend servers in parallel and waits for all of them. Each server is slow only 1% of the time. How often is the page slow, and what can you do about it? (mid)
- Design a URL shortener like bit.ly. What are the requirements and the API, what happens when a link is created and when it is clicked, and should the redirect be a 301 or a 302? (junior)
- Your call to the payment provider times out. Did the customer pay? How do you design the payment state machine and reconciliation so no money is lost or charged twice? (senior)
- Walk me through a UPI person-to-person payment. What do the payer's app, the PSP, the NPCI switch and the two banks each do? (mid)
Free questions from the other packs
- What do temperature and top-p control, and what values would you use for data extraction versus brainstorming? (AI Engineering)
- What is hybrid search, and how do you merge keyword and vector results with Reciprocal Rank Fusion? (AI Engineering)
- Users ask "list all X" questions and RAG returns only five items. Why, and what do you do? (AI Engineering)
- How do you check that an LLM judge agrees with human reviewers? (AI Engineering)
- Cut the cost of an LLM feature serving 10 million requests a day by 60% without hurting quality. Walk through your plan. (AI Engineering)
- Why are LLMs bad at counting letters or reversing a word? (GenAI and LLM)
- Write out scaled dot-product attention with the shape of every tensor. (GenAI and LLM)
- How does speculative sampling keep exactly the target model's distribution? (GenAI and LLM)
- What is GRPO, and how do verifiable rewards train reasoning models? (GenAI and LLM)
- Design a system that answers questions from 300-page contracts accurately. (GenAI and LLM)
- Describe the basic agent loop. (Agentic AI)
- What are pass@k and pass^k, and why do they matter for agents? (Agentic AI)
- How does prompt injection become dangerous once the agent has tools? (Agentic AI)
- Design the security architecture for an agent that can read and send company email. (Agentic AI)
- Design a customer support agent for an e-commerce company. (Agentic AI)
- What happens, step by step, when an LLM server handles one request? (Inference Engineering)
- Derive the KV cache size formula and work it out for Llama 3 70B. (Inference Engineering)
- What is PagedAttention? (Inference Engineering)
- When does speculative decoding stop helping or make things worse? (Inference Engineering)
- Design a service to serve Llama 3 70B at 10 QPS with latency targets. (Inference Engineering)
- A model scores 95% accuracy offline but users say it is useless. What do you check first? (ML Engineering)
- How do you train on a dataset that does not fit in memory? (ML Engineering)
- A long distributed training job dies at hour 20. How do you make training fault tolerant? (ML Engineering)
- How does request batching on a GPU trade latency for throughput? (ML Engineering)
- An online model metric dropped 8% overnight. How do you debug it? (ML Engineering)
- You need to show delivery times for 20 cities. Would you use histograms, box plots, violin plots or ECDFs, and why? (Data Science)
- A test is 99% accurate and you test positive. What is the chance you have the disease? (Data Science)
- What share of first-time buyers make a second purchase within 30 days? Write the SQL. (Data Science)
- How would you forecast daily orders for the next four weeks, and how do you know the forecast is any good? (Data Science)
- A coupon A/B test shows a small average lift. How do you find which users the coupon actually works for, and who should get it next time? (Data Science)
- Compute the cosine similarity of a = (3, 4) and b = (4, 3) by hand. What does the result mean? (Maths for ML)
- A batch of 32 MNIST images goes into a dense layer with 128 units. What are the shapes, and what is the rule for when two matrices can be multiplied? (Maths for ML)
- A disease affects 1% of people. A test catches 90% of sick people (sensitivity) and wrongly flags 9% of healthy people (false positive rate). You test positive. What is the chance you are sick? (Maths for ML)
- Why do we not use mean squared error for classification with a sigmoid output? Compare the gradients numerically. (Maths for ML)
- Compute scaled dot-product attention by hand for two tokens with d_k = 2. (Maths for ML)
- A customer says: "We want AI for our support team." What do you do in the first meeting? (Forward Deployed Engineer)
- The customer's admin offers you a global admin account "to save time". What do you ask for instead, and why? (Forward Deployed Engineer)
- Your connector writes tickets into the customer's system. A request timed out, the connector retried, and now there are duplicate tickets. How do you make the writes safe to retry? (Forward Deployed Engineer)
- The customer wants RAG over 10 million documents, all behind their firewall. Size it for me on the whiteboard. (Forward Deployed Engineer)
- One deployment of your AI platform must serve 50 business units of the same enterprise. How do you design multi-tenancy? (Forward Deployed Engineer)