Database Design and Query Optimization Questions

Principles of database schema design and performance optimization including relational and non relational trade offs, normalization and denormalization, indexing strategies and index types, clustered and non clustered indexes, query execution plans, common table expressions for readable complex queries, detecting missing or redundant indexes, sharding and partitioning strategies, and consistency and availability trade offs. Candidates should demonstrate knowledge of optimizing reads and writes, diagnosing slow queries, and selecting the appropriate database model for scale and consistency requirements.

HardTechnical

0 practiced

You inherit a database with a slow monthly ETL that fails intermittently. Draft a prioritized investigation plan that includes query-level diagnosis (EXPLAIN ANALYZE), OS-level metrics (iostat/cpu), and DB-level views (active sessions, locks). For each step, explain what signs indicate the likely underlying problem (IO, CPU, locking, network).

HardTechnical

0 practiced

Multiple dashboards depend on a core table you need to change (rename column, split column). Propose a strategy to perform schema changes with backward compatibility for dashboards and queries. Discuss use of views, dual-write, feature flags, and rollout steps.

MediumTechnical

0 practiced

A scheduled report shows stale data. As a data analyst, list possible causes (ETL failures, caching, replication lag, permissions) and provide a prioritized troubleshooting checklist. Include queries or checks you would run to determine whether the source tables, materialized views, or dashboard cache is stale.

HardTechnical

0 practiced

A fact table shows extreme partition skew: a single partition contains 70% of rows due to one very active user segment. Propose practical solutions to mitigate skew for both storage and query performance (including re-sharding, sub-partitioning, partial indexes, or adaptive partition keys). Discuss pros and cons.

MediumTechnical

0 practiced

Explain how statistics and optimizer cardinality estimation affect query plans. Describe concrete steps a data analyst or DBA can take when statistics are stale or inaccurate to improve planning and performance, including how to identify problematic tables.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.