Database Engineering & Data Systems Topics
Database design patterns, optimization, scaling strategies, storage technologies, data warehousing, and operational database management. Covers database selection criteria, query optimization, replication strategies, distributed databases, backup and recovery, and performance tuning at database layer. Distinct from Systems Architecture (which addresses service-level distribution) and Data Science (which addresses analytical approaches).
Database Design and Query Optimization
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.
Technology Selection & Deep Technical Knowledge
Deep understanding of specific technologies relevant to complex system design. Master databases (PostgreSQL, Cassandra, DynamoDB, Elasticsearch), message queues (Kafka, RabbitMQ), caching systems (Redis), search engines, and frameworks. Understand their strengths, weaknesses, trade-offs, operational characteristics, scaling patterns, and common pitfalls. Be able to justify technology choices based on specific system requirements.
Data Model Design and Access Patterns
Discuss how you'd design data models based on access patterns. Understand relational vs. NoSQL trade-offs. Know when to denormalize, how to handle distributed transactions, and strategies for scaling databases (sharding, partitioning). Discuss read vs. write optimization.
Data Partitioning and Sharding
Techniques and operational practices for horizontally partitioning data across multiple database instances or storage nodes to achieve scale, improve performance, and manage growth. Includes selection and design of partition and shard keys to evenly distribute load and avoid hotspots, with range based, hash based, and directory based approaches and consistent hashing mechanisms. Covers handling uneven distribution and data skew, hotspot detection and mitigation, and the impact of partitioning on query patterns such as joins and cross shard queries. Explains implications for transactions and consistency, including transactional boundaries that span partitions and approaches to distributed transactions and compensation. Describes resharding and online data migration strategies, rolling rebalances, and methods to minimize downtime and data movement. Emphasizes operational concerns including shard management, automation, monitoring and alerting, failure recovery, and performance tuning. Discusses trade offs between simplicity, latency, throughput, and operational complexity and highlights considerations for both transactional and analytical workloads, including routing, caching, and coordination patterns.
Database Selection and Trade Offs
How to evaluate and choose data storage systems and architectures based on workload characteristics and business constraints. Coverage includes differences between relational and nonrelational families such as document stores, key value stores, wide column stores, graph databases, time series databases, and search engines; mapping query patterns and latency requirements to storage options; trade offs between strong consistency and eventual consistency and their impact on availability and complexity; partition key design, replication strategies, and high availability considerations; operational concerns including backups, monitoring, vendor and cost trade offs, migration or hybrid strategies, and when to adopt polyglot persistence. Senior level discussion includes selecting specific managed services and reasoning about expected load patterns, failure modes, and operational burden.
Database Fundamentals and Data Modeling
Encompasses foundational database concepts and techniques for designing and querying data stores. Topics include relational database concepts, structured query language fundamentals for selecting and modifying data, schema design with tables and relationships, normalization principles, entity relationship modeling, indexing and basic performance considerations, transactions and data integrity, constraints and data types, and common operational tasks such as migrations, backups, and simple optimization strategies. Also discusses trade offs with nonrelational stores and when to choose different persistence models.
Database Design and Architecture
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.
Database Scalability and High Availability
Architectural approaches and operational practices for scaling and maintaining database availability. Topics include vertical versus horizontal scaling trade offs; replication topologies, leader and follower roles, read replicas and replica lag; read write splitting and connection pooling; sharding and partitioning strategies including range based, hash based, and consistent hashing approaches; handling hot partitions and data skew; federation and multi database federation patterns; cache layers and cache invalidation; rebalancing and resharding strategies; distributed concurrency control and transactional guarantees across shards; multi region deployment strategies, cross region failover and disaster recovery; monitoring, capacity planning, automation for failover and backups, and cost optimization at scale. Candidates should be able to pick scaling approaches based on read and write patterns and explain operational complexity and trade offs introduced by distributed data.
Relational Database Fundamentals and Design
Core concepts of relational databases and schema design including tables, relationships such as one to one one to many and many to many, primary keys and foreign keys, data integrity constraints, and the properties of atomicity consistency isolation and durability and why they matter. Understand differences between relational systems using structured query language and nonrelational databases, indexing strategies, normalization and denormalization trade offs, simple query optimization techniques, and when to choose a normalized relational design versus a document or key value store. Candidates should be able to perform basic entity identification, produce simple schema diagrams, explain persistence and durability considerations, and reason about basic performance and scaling trade offs.