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).
Cloud Data Warehouse Design and Optimization
Covers design and optimization of analytical systems and data warehouses on cloud platforms. Topics include schema design patterns for analytics such as star schema and snowflake schema, purposeful denormalization for query performance, column oriented storage characteristics, distribution and sort key selection, partitioning and clustering strategies, incremental loading patterns, handling slowly changing dimensions, time series data modeling, cost and performance trade offs in cloud managed warehouses, and platform specific features that affect query performance and storage layout. Candidates should be able to discuss end to end design considerations for large scale analytic workloads and trade offs between latency, cost, and maintainability.
Data Modeling for Query Performance
Focuses on schema and data modeling choices that enable efficient querying at scale. Topics include normalization and denormalization trade offs, analytical schemas such as star schema and snowflake schema, the roles of fact tables and dimension tables, modeling for common query patterns and aggregations, and how model choices impact indexing, join costs, and storage. Candidates should be able to justify schema decisions based on query workload, discuss partitioning and sharding implications for model design, and propose modeling adjustments that improve query latency and maintainability.
Complex Data Integration and Joins
Handling intricate join scenarios: multi-condition joins, conditional joins with complex logic, joining on date ranges or overlapping time periods, complex left joins with multiple filtering conditions, self-joins for hierarchical or relationship data, handling non-standard relationships between tables. Understanding implications of different join types on row counts, NULL values, and duplicate handling. Designing queries that correctly integrate data from multiple sources while maintaining data integrity and avoiding duplicate counting or missing data.
SQL Fundamentals and Query Writing
Comprehensive query writing skills from basic to intermediate level. Topics include SELECT and WHERE, joining tables with inner and outer joins, grouping with GROUP BY and filtering groups with HAVING, common aggregation functions such as COUNT SUM AVG MIN and MAX, ORDER BY and DISTINCT, subqueries and common table expressions, basic window functions such as ROW_NUMBER and RANK, union operations, and principles of readable and maintainable query composition. Also covers basic query execution awareness and common performance pitfalls and how to write correct, efficient queries for combining and summarizing relational data.
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.
Data Modeling and Architecture
Design and modeling principles for transactional and analytical data systems. Topics include entity relationship modeling, normalization and denormalization trade offs, dimensional modeling with fact and dimension tables and star and snowflake schemata, indexing strategies, partitioning and sharding, and schema design for performance and maintainability. Cover data pipelines and integration patterns including extract transform load and extract load transform approaches, data warehousing and data lake concepts, ETL orchestration, and how sources feed into reporting and business intelligence systems. Also include considerations for data quality, governance, and the differences between online transaction processing and online analytical processing workloads.
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.
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.
Advanced SQL Window Functions
Mastery of Structured Query Language window functions and advanced aggregation techniques for analytical queries. Core function families include ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, and NTILE; offset functions such as LAG and LEAD; value functions such as FIRST_VALUE, LAST_VALUE, and NTH_VALUE; and aggregate window expressions such as SUM OVER and AVG OVER. Candidates should understand the OVER clause with PARTITION BY and ORDER BY, frame specifications using ROWS BETWEEN and RANGE BETWEEN, tie handling, null behavior, and how frame definitions affect results. Common application patterns include top N per group, deduplication using row numbering, running totals and cumulative aggregates, moving averages, percent rank and distribution calculations, event sequencing and period over period comparisons, gap and island analysis, cohort and retention analysis, and trend and growth calculations. The topic also covers structuring complex queries with Common Table Expressions including recursive Common Table Expressions to break multi step analytical pipelines and to handle hierarchical or iterative problems, and choosing between window functions, GROUP BY, joins, and subqueries for correctness and readability. Performance and correctness considerations are essential, including join and sort costs, index usage, memory and sort spill behavior, execution planning and query optimization techniques, and trade offs across different database dialects and large data volumes. Interview assessments typically ask candidates to write and explain queries that use these functions, reason about frame semantics for edge cases such as ties, nulls, and partition boundaries, and to rewrite or optimize expensive queries.