Prepare for database fundamentals and analytical storage interviews with concise explanations, SQL and Python examples, practical trade-offs, and real-world design considerations.
Quick Reference
| Concept | Remember |
|---|---|
| OLTP | Operational transactions, low-latency reads/writes, concurrency |
| OLAP | Analytical scans, aggregations, and historical reporting |
| Columnar format | Read selected columns efficiently; often compresses well |
| Index | Faster lookups at the cost of storage and write maintenance |
| Partitioning | Split a dataset into manageable/prunable pieces |
| Sharding | Distribute data across nodes or servers |
| Parquet | Typed, compressed columnar file format for analytics |
1. What is the difference between OLTP and OLAP?
Suggested interview answer
OLTP (Online Transaction Processing) supports frequent, short operational transactions such as creating an order or updating an account. It prioritizes correctness, concurrency, and low-latency reads and writes. OLAP (Online Analytical Processing) supports analytical queries across large volumes of historical data, such as revenue trends or customer cohorts. OLTP systems commonly use normalized models; OLAP systems often use dimensional models, columnar storage, and large scans or aggregations.
Example
-- OLTP-style lookup: retrieve one order
SELECT order_id, status, total_amount
FROM orders
WHERE order_id = 10025;
-- OLAP-style aggregation: trend by month
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue
FROM fact_orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;2. Why are columnar formats effective for analytical workloads?
Suggested interview answer
Columnar storage keeps values from the same column together, allowing engines to read only the columns required by a query. It can improve compression because values in a column often share similar types and patterns. Analytical engines can also apply vectorized processing and skip row groups or data pages using statistics. Columnar formats are especially effective for scans and aggregations, but are not universally best for point updates or single-row transactional access.
Example
-- Only two columns are needed for this aggregation.
SELECT product_category, SUM(net_sales_amount)
FROM fact_sales
WHERE order_date >= DATE '2026-01-01'
GROUP BY product_category;3. How do indexes improve query performance, and what are their trade-offs?
Suggested interview answer
An index is an auxiliary data structure that helps the database find rows without scanning the entire table. B-tree indexes can accelerate equality and range lookups; other index types suit different operators or data types. Indexes consume storage and must be maintained on inserts, updates, and deletes. Too many or poorly chosen indexes can slow writes, increase maintenance, and still be ignored by the optimizer when a scan is cheaper.
Example
-- Example syntax in many relational databases
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);
SELECT order_id, order_date
FROM orders
WHERE customer_id = 42
AND order_date >= DATE '2026-01-01';4. What is the difference between partitioning and sharding?
Suggested interview answer
Partitioning divides a table or dataset into smaller logical or physical pieces, often by date, range, list, or hash. Partitions may remain inside one database instance and help with pruning, maintenance, and lifecycle management. Sharding distributes data across separate database nodes or servers, often to scale storage and throughput horizontally. Sharding adds routing, rebalancing, cross-shard query, and operational complexity. The terms can overlap in some systems, so explain the architecture being discussed.
Example
-- Example conceptual partition filter
SELECT COUNT(*)
FROM events
WHERE event_date >= DATE '2026-10-01'
AND event_date < DATE '2026-11-01';
-- A shard key might route a customer to a node:
-- shard_id = hash(customer_id) % number_of_shards5. Explain ACID properties with a practical example.
Suggested interview answer
ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means all transaction steps succeed or none do. Consistency means constraints and invariants remain valid. Isolation controls how concurrent transactions interact. Durability means committed changes survive failures according to the database's guarantees.
Example
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT;6. When would you choose a relational database over a document database?
Suggested interview answer
Choose a relational database when the application benefits from a well-defined schema, joins across related entities, constraints, and multi-row transactions. It is often a good fit for financial transactions, order management, and systems with strong referential integrity requirements. A document database can be attractive for flexible, nested, aggregate-oriented records or rapidly evolving schemas. The choice depends on access patterns, consistency needs, query complexity, scaling, and operational expertise—not just whether data looks like JSON.
Example
-- Relational model: orders reference customers
SELECT o.order_id, c.customer_name, o.total_amount
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.customer_id;7. Why would you use Parquet instead of CSV?
Suggested interview answer
Parquet is a columnar, typed, compressed file format that supports metadata and row-group statistics. It often reduces storage and I/O for analytical queries that read selected columns. CSV is plain text, human-readable, and widely compatible, but has no built-in column types or compression requirement and often needs parsing and type inference. CSV can still be preferable for simple interchange, manual inspection, or systems that require it.
Example
# Python example: write/read Parquet with pandas
import pandas as pd
df = pd.DataFrame({
"order_id": [101, 102],
"amount": [250.0, 175.5],
"country": ["IN", "DE"]
})
df.to_parquet("orders.parquet", index=False)
loaded = pd.read_parquet("orders.parquet")8. How would you choose a storage format for a large analytical pipeline?
Suggested interview answer
Start from the workload and operational requirements. For analytical tables in a data lake, Parquet or ORC are common columnar choices. For streaming ingestion or event interchange, Avro or JSON may be useful because they support record-oriented messages or flexible payloads. If the pipeline needs transactional updates, concurrent writers, schema evolution, and time travel, consider a table format such as Apache Iceberg, Delta Lake, or Apache Hudi on top of data files. Also evaluate file sizes, partitioning, compaction, compression, compatibility, and governance.
Example
# Example: choose a typed columnar format for an analytical batch
import pandas as pd
df = pd.read_csv("raw_orders.csv")
df["order_date"] = pd.to_datetime(df["order_date"], errors="raise")
df["amount"] = pd.to_numeric(df["amount"], errors="raise")
df.to_parquet(
"curated_orders.parquet",
index=False,
compression="snappy"
)Scenario-Based Practice Questions
1. A dashboard scans billions of rows but reads only three columns. What storage characteristics help?
Columnar storage can read only the required columns, while compression and row-group/page statistics may reduce I/O further. Partition pruning and predicate pushdown can help when filters align with the physical layout. Confirm with the query plan and scan metrics.
2. An index speeds up a report but slows ingestion. What would you investigate?
Review whether the index is selective and used by the query, whether its key order matches predicates, and whether redundant indexes can be removed. Every write may need to maintain indexes. Compare execution plans and write latency before and after changes.
3. A table is partitioned by date, but queries remain slow. Why?
Queries may not filter on the partition key, predicates may prevent pruning, partitions may be too small or too large, or the bottleneck may be joins, skew, or data movement. Inspect the actual plan and bytes or partitions scanned.
4. You need schema evolution and reliable updates in a data lake. Is Parquet alone enough?
Parquet is a file format, not a complete transactional table-management layer. Consider a table format such as Iceberg, Delta Lake, or Hudi if you need features such as atomic commits, concurrent writes, snapshots, schema evolution, or time travel.
Chapter Summary
- Choose database architecture based on transactional versus analytical access patterns.
- Columnar storage is efficient for many scans and aggregations because it reduces unnecessary I/O.
- Indexes can speed reads but increase storage and write-maintenance costs.
- Partitioning organizes data; sharding distributes data across nodes, with different operational trade-offs.
- ACID properties help protect transactional correctness.
- Choose relational or document storage based on relationships, constraints, query patterns, and consistency requirements.
- Parquet is often a good analytical file format; choose broader table formats when transactional lakehouse capabilities are needed.
Note: SQL and Python examples are illustrative. Syntax, indexing behavior, partitioning features, and transaction guarantees differ across database engines and platforms.
Comments
Post a Comment