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.

MediumTechnical

0 practiced

A nightly ETL job that builds a large report is regularly blocked by other processes. Describe how to diagnose locking and blocking issues that affect reporting queries, and list concrete changes you would make to ETL jobs or database configuration to reduce blocking and ensure reliable nightly reports.

MediumTechnical

0 practiced

Explain the differences between offset-based pagination and keyset (seek) pagination for UI dashboards. Provide SQL examples for both, discuss performance characteristics as offsets grow, and describe how to implement keyset pagination when ordering by created_at DESC with possible duplicates in created_at.

EasyTechnical

0 practiced

Explain primary keys, unique constraints, and foreign keys. For a BI environment with a very large fact table, discuss whether you would enforce foreign keys at the database level and when you might avoid them. Include implications for ETL, referential integrity, and query performance.

HardSystem Design

0 practiced

Design a multi-tenant analytics schema for a SaaS product where tenants range from tiny to very large. Discuss whether to use a shared schema with tenant_id in tables, separate schemas per tenant, or separate databases per tenant. Include considerations for security, performance isolation, index strategies, and reporting across tenants.

EasyTechnical

0 practiced

Describe the difference between clustered and non-clustered indexes (conceptually and practically). Explain how choosing a clustered index affects physical storage, range scans, and write performance, and recommend a clustered key for a time-series fact table used in BI dashboards.

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.