Database Design and Architecture Questions

Designing and architecting databases for production and cloud environments with attention to data modeling, schema design, and access pattern optimization. Topics include normalization and denormalization trade offs, schema versus query driven modeling, entity and relationship design for transactional and analytical workloads, indexing and query optimization techniques, partitioning and sharding design decisions, schema evolution and migration strategies, materialized views and caching strategies, selection of storage layers for different data shapes, and practical operational runbooks for provisioning, monitoring, alerting, backups, disaster recovery, and capacity planning. Candidates should justify schema and architecture choices with respect to latency, throughput, development and operational complexity, maintainability, and cost.

MediumTechnical

0 practiced

SQL (PostgreSQL): Given a transactions table (id, user_id, amount, occurred_at), write a query that computes a running daily total for each user ordered by occurred_at and outputs: user_id, occurred_at::date, daily_running_total. Show a solution using window functions and explain how partitioning and indexes can be used to optimize it.

HardTechnical

0 practiced

You need to rebalance shards from 10 to 100 nodes because of growth and hotspots. Design a rebalancing algorithm and migration plan for an existing key-value store that minimizes downtime and ensures consistency during migrations. Explain how you would detect hotspots, schedule migrations, and roll back if necessary.

HardTechnical

0 practiced

Analyze leaderless (Dynamo-style) vs leader-based replication models. For a write-heavy service with strict durability but varying latency tolerance, discuss trade-offs in availability, conflict resolution, repair mechanisms (anti-entropy, read-repair), and operational complexity.

EasyTechnical

0 practiced

You are advising a client building an e-commerce platform. They need low-latency product search, strong transactional checkout, and hourly analytics. Propose which database technologies and patterns you would use for each requirement (OLTP, search, analytics) and justify your choices in terms of latency, throughput, maintainability, and cost.

MediumTechnical

0 practiced

Design a shard key and partitioning strategy for an orders table that receives millions of records per day. Requirements: support transactional updates, efficient range queries by date, reporting per customer, and avoid shard hotspots. Explain your choice and how you'd handle re-sharding in production.

Unlock Full Question Bank

Get access to hundreds of Database Design and Architecture interview questions and detailed answers.

Join thousands of developers preparing for their dream job.