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

You have a slow query: SELECT * FROM orders WHERE customer_id = ? AND status = 'PENDING' ORDER BY created_at DESC LIMIT 50. The orders table has 200M rows. Recommend index(es) and explain why they speed up this query and any trade-offs.

HardTechnical

0 practiced

Describe how to design end-to-end encryption at rest and in transit for database storage, including key management, rotating keys without downtime, and protecting backups and replicas.

HardTechnical

0 practiced

Outline the transactional outbox pattern to reliably emit events when updating the database in the same transactional scope. Include schema for outbox table, polling versus CDC integration approaches, and how to ensure idempotent consumers.

EasyTechnical

0 practiced

Given a table transactions(transaction_id, user_id, amount, occurred_at), write a SQL query using window functions to return each user's most recent transaction date and the total amount in the last 30 days. Specify which SQL dialect you assume.

HardTechnical

0 practiced

You manage a heavy reporting query in a columnar data warehouse. Describe steps to adapt the query and tables: distribution/partition keys, columnar compression, sort keys, and how to avoid data skew when joining large tables.

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.