Databases & Warehousing

Question 1

What is the primary purpose of an OLTP database?

Answer 1

To support high-concurrency, low-latency transactional workloads such as inserts, updates, and small, selective queries.

Question 2

What is the primary purpose of an OLAP database or data warehouse?

Answer 2

To support analytical workloads that scan and aggregate large volumes of data for reporting and decision-making.

Question 3

How do OLTP and OLAP systems typically differ in schema design?

Answer 3

OLTP systems are usually highly normalized to avoid redundancy; OLAP systems are often denormalized into star or snowflake schemas for fast reads.

Question 4

What is normalization in relational databases?

Answer 4

The process of structuring tables to reduce redundancy and dependency anomalies by dividing data into related tables.

Question 5

Why is normalization beneficial for OLTP systems?

Answer 5

It reduces duplication, simplifies updates, and helps maintain data integrity across many small transactions.

Question 6

Why is denormalization common in data warehouses?

Answer 6

It reduces the number of joins needed for analytical queries, trading some redundancy for simpler and faster reads.

Question 7

What is a relational database management system (RDBMS)?

Answer 7

A database system that stores data in tables with rows and columns and uses SQL and relational principles for querying and integrity.

Question 8

What are common examples of OLTP RDBMS engines?

Answer 8

MySQL, PostgreSQL, SQL Server, and Oracle Database.

Question 9

What is a distributed MPP data warehouse?

Answer 9

A massively parallel processing system where data is distributed across nodes and queries are executed in parallel for scalability.

Question 10

What are common examples of data warehouse engines?

Answer 10

Snowflake, Amazon Redshift, Google BigQuery, Azure Synapse, and distributed columnar systems like ClickHouse.

Question 11

What is a primary key in a relational table?

Answer 11

A column or set of columns that uniquely identifies each row and is used to enforce uniqueness and relationships.

Question 12

What is a foreign key?

Answer 12

A column in one table that references a primary key in another table, representing a relationship between the tables.

Question 13

What is an index in a database?

Answer 13

A data structure that accelerates lookups and joins on specific columns at the cost of extra storage and write overhead.

Question 14

Why should indexes be used carefully in write-heavy systems?

Answer 14

Indexes speed up reads but slow down inserts, updates, and deletes because they must be maintained with each write.

Question 15

What is a clustered index conceptually?

Answer 15

An index that defines the physical order of rows in the table, often on the primary key in many systems.

Question 16

What is a nonclustered index conceptually?

Answer 16

A separate structure that references table rows without changing their physical order, used to speed up queries on other columns.

Question 17

What is a transaction in database systems?

Answer 17

A unit of work that is executed with ACID properties: atomicity, consistency, isolation, and durability.

Question 18

What does isolation mean in the context of transactions?

Answer 18

The degree to which concurrent transactions are prevented from interfering with each other’s intermediate changes.

Question 19

What is eventual consistency?

Answer 19

A model where all replicas will converge to the same value over time, but reads may temporarily see stale data after writes.

Question 20

Why is eventual consistency common in distributed NoSQL systems?

Answer 20

It allows higher availability and partition tolerance by relaxing strict transactional guarantees.

Question 21

What are the main categories of NoSQL databases?

Answer 21

Key-value stores, document stores, column-family stores, and graph databases.

Question 22

When are key-value or document stores typically preferred over relational databases?

Answer 22

When flexible schemas, high write throughput, or low-latency simple access patterns are more important than complex joins and transactions.

Question 23

Why are relational warehouses usually columnar?

Answer 23

Columnar storage matches analytical access patterns, enabling efficient scans, compression, and aggregation on selected columns.

Question 24

What is a star schema in data warehousing?

Answer 24

A schema with a central fact table joined to multiple dimension tables, forming a star-like structure.

Question 25

What is a snowflake schema?

Answer 25

A star schema variant where dimensions are further normalized into additional related tables, forming a more complex structure.

Question 26

What is a fact table’s grain?

Answer 26

The level of detail represented by each row in the fact table, such as one row per order line or per daily metric per customer.

Question 27

Why is grain definition critical when designing fact tables?

Answer 27

It determines how data can be aggregated and prevents ambiguity when joining or combining facts.

Question 28

What is a slowly changing dimension (SCD)?

Answer 28

A dimension where attribute values change over time and must be tracked, typically handled with SCD Type 1 or Type 2 patterns.

Question 29

What is an SCD Type 2 pattern?

Answer 29

A pattern where changes are recorded by adding new rows with effective dates or version indicators to preserve history.

Question 30

What is a data mart?

Answer 30

A subject- or department-specific subset of the data warehouse designed for a particular analytical domain.

Question 31

Why do organizations create data marts?

Answer 31

To provide targeted, simplified schemas for specific teams and use cases while still sourcing from a central warehouse.

Question 32

What is a materialized view?

Answer 32

A precomputed, stored result of a query that can be refreshed periodically or incrementally to speed up repeated analytical queries.

Question 33

How do materialized views differ from regular views?

Answer 33

Regular views are just stored query definitions; materialized views persist the query results physically.

Question 34

What is a column statistics or histogram used for in query planning?

Answer 34

They provide information about data distribution, helping the optimizer choose efficient join orders and access paths.

Question 35

Why can poor statistics lead to bad query plans?

Answer 35

The optimizer may underestimate or overestimate row counts, leading to suboptimal join strategies and high execution time.

Question 36

What is partitioning in relational or warehouse tables?

Answer 36

Dividing a table into segments based on partition keys so queries can scan only relevant segments and improve performance.

Question 37

Why should partition keys for warehouse tables often be time-based?

Answer 37

Most analytical queries filter by time; time-based partitioning supports pruning and lifecycle management.

Question 38

What is sharding in distributed databases?

Answer 38

Splitting data across multiple nodes or clusters based on a shard key to scale out storage and throughput.

Question 39

Why is choosing a good shard key important?

Answer 39

A poor shard key can cause hotspots or uneven load, undermining the benefits of horizontal scaling.

Question 40

What is a query federation or virtual warehouse conceptually?

Answer 40

Executing queries that span multiple underlying data sources or compute clusters while presenting a unified logical schema.

Question 41

Why is separating compute clusters for different workloads beneficial in a warehouse environment?

Answer 41

It isolates performance and allows independent scaling of ETL, BI, and ad hoc workloads without resource contention.

Question 42

What is the role of a query engine in a modern warehouse stack?

Answer 42

To parse, optimize, and execute SQL queries against one or more storage layers, often abstracting away physical details.

Question 43

Why is concurrency an important consideration in warehouse design?

Answer 43

Many users and jobs may run queries simultaneously; the system must handle concurrency without excessive queuing or degradation.

Question 44

What is a good one-sentence mental model for databases vs warehouses?

Answer 44

Databases are optimized for transactional correctness and small, selective queries, while warehouses are optimized for large, read-heavy analytical workloads over well-modeled historical data.