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).
Indexing Strategy and Selection
Covers index design principles and practical selection of indexes to accelerate queries while managing storage and write cost. Topics include index types such as B tree hash and bitmap indexes and full text and functional indexes; single column composite and covering indexes; clustered versus nonclustered index architectures and partial or filtered indexes. Candidates should reason about index selectivity and cardinality and how statistics and histograms influence optimizer choices. Also assess index maintenance overhead fragmentation and rebuild strategies and the trade off between faster reads and slower inserts updates and deletes. Practical skills include reading execution plans to identify missing or inefficient indexes proposing index consolidation or covering index designs testing and benchmarking index changes and understanding interactions between indexing partitioning and denormalization.
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.
SQL Scenarios
Advanced SQL query design and optimization scenarios, including complex joins, subqueries, window functions, common table expressions (CTEs), set operations, indexing strategies, explain plans, and performance considerations across relational databases.
Managed Databases and Data Services
Covers choosing and operating managed database offerings and complementary cloud data services. Candidates should understand managed relational database services such as Amazon Relational Database Service for MySQL PostgreSQL MariaDB Microsoft SQL Server and Oracle, and NoSQL document and key value stores such as Amazon DynamoDB Azure Cosmos Database Google Cloud Firestore and Datastore. Expect to explain when to choose relational versus NoSQL based on data shape query complexity transactional guarantees including atomicity consistency isolation and durability read and write patterns latency and scalability requirements. Understand scaling techniques including vertical scaling read replicas for read scaling horizontal scaling via partitioning or sharding and multi region replication and failover strategies. Be familiar with backup and restore approaches including snapshots point in time recovery cross region replication and disaster recovery planning. Know consistency models and trade offs such as strong eventual and causal consistency, and understand provisioned capacity versus serverless autoscaling models and their cost and operational implications. Candidates should also be able to discuss performance tuning topics such as indexing query optimization caching connection pooling storage and input output optimization monitoring and alerting, as well as security and compliance considerations including encryption access control and network isolation. Finally be prepared to recommend a database solution given workload characteristics such as data size read to write ratio latency targets and operational constraints.
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.
Consistency Models and Transactions
Comprehensive knowledge of data consistency models and transactional guarantees in databases and distributed systems. This includes understanding transaction properties such as Atomicity, Consistency, Isolation, and Durability (ACID) and alternative design philosophies such as Basically Available, Soft state, Eventually consistent (BASE). Candidates should be able to choose appropriate isolation levels including read uncommitted, read committed, repeatable read, serializable, and snapshot isolation and explain performance versus correctness tradeoffs and common anomalies such as dirty reads, non repeatable reads, phantom reads, lost updates, and write skew. Understand consistency models including strong consistency, strict serializability, serializability, snapshot isolation, causal consistency, eventual consistency, monotonic reads, and read your writes, and when each model is acceptable based on latency, availability, and business correctness requirements. Discuss replication strategies and their impact on guarantees, including synchronous versus asynchronous replication, multi region replication, replication lag, and replica divergence. Evaluate distributed transaction and coordination approaches such as two phase commit and consensus based protocols and weigh their performance and failure modes. Propose conflict detection and resolution strategies such as last write wins, version vectors and vector clocks, conflict free replicated data types, application level reconciliation, idempotent operations, retries, and saga or compensation patterns for long running workflows. Consider practical engineering concerns including consistency service level objectives, monitoring and alerting for staleness and replication lag, testing strategies for consistency, implications for caching and sharding, and the tradeoffs between developer complexity and user facing correctness.
Storage and Database Infrastructure
Storage concepts: SSDs vs. HDDs, RAID configurations, storage protocols. Database troubleshooting basics, replication concepts, backup and recovery strategies, understanding query performance and index behavior, and storage at scale.
Data Consistency and Recovery
Covers the spectrum of data consistency models used in distributed systems and the operational practices for detecting and recovering from inconsistency. Topics include strong consistency guarantees provided by atomicity, consistency, isolation, and durability style transactions and synchronous replication, and weaker models such as eventual consistency and causal consistency along with their read guarantees like read your writes and monotonic reads. Explain the trade offs between consistency, availability, and latency and how those trade offs influence architecture decisions, user experience, and cost. Discuss replication strategies including synchronous replication, asynchronous replication, and read replicas, and how replication modes affect staleness and failure behavior. Include coordination and consensus mechanisms for achieving stronger guarantees, for example leader based replication and consensus protocols, and distributed transaction approaches such as two phase commit. Cover operational concerns: how consistency choices change testing, deployment, monitoring, and incident response. Describe detection and recovery techniques for inconsistency such as validation checks, reconciliation and anti entropy processes, tombstones and conflict resolution strategies, use of vector clocks or conflict free replicated data types to resolve concurrent updates, point in time recovery and backups, and procedures for partial repairs, rollbacks, and replays. At senior levels also address how consistency decisions shape runbooks, alerting, and post incident analysis.
Data Infrastructure Technology Selection
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.