Database Design and Query Optimization Questions
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.
MediumTechnical
74 practiced
Row-level security (RLS) and column-level encryption protect data but can impact performance. As a BI Analyst, explain how RLS policies and encryption-by-column affect query plans and indexes, and propose design strategies to minimize performance impact while maintaining required security.
Sample Answer
Short answer: RLS adds predicates to queries per user; column-level encryption changes how values are stored/compared. Both can prevent the optimizer from using existing indexes or statistics, causing scans, higher CPU, and longer dashboard load times. Below I explain how they affect query plans and practical design choices a BI analyst can push for to reduce performance impact while preserving security.How RLS affects query plans and indexes- RLS is implemented as additional WHERE predicates (often functions or joins to an access table). If those predicates are non-SARGable (e.g., functions, UDFs, or joins on non-indexed columns), the optimizer can’t use indexes and falls back to scans.- RLS can inhibit index usage when predicate values are runtime-determined (session context), preventing plan reuse and accurate cardinality estimates.- Large numbers of tiny row filters increase plan complexity and may bloat execution time for interactive dashboards.How column-level encryption affects query plans and indexes- Encrypted columns are often non-searchable or require deterministic encryption to allow equality lookups. Randomized encryption prevents meaningful indexing and range scans.- Even with deterministic encryption, indexes work only for exact match queries and require matching encryption at query time; range queries and ORDER BY on encrypted columns typically become expensive.- Encryption changes data distribution and statistics, degrading cardinality estimates.Design strategies to minimize impact (role-focused, practical)- Push security patterns into the data platform, not dashboards: ask engineering/DBA to implement RLS using simple, index-friendly predicates (e.g., user_id = @current_user or join to an indexed access table) and avoid UDFs inside WHERE.- Use session/context-based security features (SET ROLE, application_user context) that the DBMS recognizes for predicate pushdown and stable plan generation.- For encrypted sensitive columns: - Use deterministic encryption for fields that need equality filters (IDs, SSNs), and leave range/filtering columns either tokenized or stored in a separate protected plaintext column with stricter access. - Consider format-preserving encryption or order-preserving only when absolutely necessary, knowing the risk.- Create pre-filtered, secured materialized views or aggregate tables per business domain (or per tenant) so dashboards query optimized, small datasets with security enforced at refresh time.- Maintain supporting indexes: index the columns used by RLS predicates and join keys, and update statistics frequently so the optimizer has accurate information.- Cache and extract: use ETL to create secured reporting schemas where sensitive detail is masked/encrypted appropriately; BI tools can connect to these optimized schemas instead of raw tables.- Use tokenization and a secure vault for lookups when you must map encrypted values back at presentation time rather than in heavy queries.- Monitor and test: measure query plans before/after security changes; force plan baselines or plan guides if necessary to stabilize performance.Trade-offs to articulate to stakeholders- Some approaches (deterministic encryption, order-preserving) weaken cryptographic guarantees. Pre-aggregated or materialized secure views increase storage and ETL complexity. Balance security, regulatory needs, and interactivity; recommend acceptable latency targets and collaborate with DBAs to implement indexable, optimizer-friendly security controls.
EasyTechnical
51 practiced
Explain the differences between OLTP and OLAP systems and why a BI Analyst would choose one over the other for reporting. In your answer describe: typical data models (normalized vs dimensional), storage characteristics (row vs columnar), indexing approaches, typical query patterns, example workloads (e.g., high-concurrency transactions vs large aggregations), and performance considerations for dashboards that aggregate months of sales data.
Sample Answer
OLTP vs OLAP — short answer: OLTP systems are optimized for fast, concurrent transactional processing (day-to-day operations). OLAP systems are optimized for analytical queries, large scans, and aggregations used in reporting and BI.Typical data models- OLTP: normalized relational schema (3NF) to minimize redundancy and ensure transactional integrity (orders, customers, line_items).- OLAP: dimensional/star or snowflake schemas (fact tables + dimension tables) designed for intuitive reporting and fast aggregations.Storage characteristics- OLTP: row-oriented storage (good for reading/writing whole rows in transactions).- OLAP: often columnar storage (stores each column together → very efficient for scanning, compression, and aggregations).Indexing approaches- OLTP: many B-tree indexes, primary keys, foreign keys, optimized for point lookups and small-range scans.- OLAP: fewer traditional indexes; rely on columnar compression, bitmap indexes, materialized views, and pre-aggregations (aggregated tables, cubes) to speed analytic queries.Typical query patterns & example workloads- OLTP: short, frequent, low-latency queries—INSERT/UPDATE/DELETE and SELECT by key; high-concurrency (e.g., thousands of users placing orders).- OLAP: long-running, read-heavy queries that scan large datasets and compute aggregates, group-bys, joins across dimensions (e.g., monthly sales trends, cohort analysis).Why a BI analyst chooses OLAP for reporting- Faster aggregations over months/years due to columnar reads and compression.- Easier semantics: dimensional model maps directly to business concepts (date, product, region).- Supports pre-aggregations/materialized views to make dashboards snappy.Performance considerations for dashboards aggregating months of sales- Use columnar OLAP storage or a data warehouse (Snowflake, BigQuery, Redshift) to reduce IO for aggregation.- Build date-partitioned fact tables and appropriate clustering/partitioning to avoid full scans.- Pre-compute common aggregates or use materialized views/cubes for heavy dashboards.- Cache results in BI tool or use query result caching to give sub-second dashboard responsiveness.- Balance freshness vs performance (near-real-time vs hourly/ nightly ETL).In summary: OLTP for transactional correctness and concurrency; OLAP/data warehouses for BI — choose OLAP for month-over-month sales dashboards because its storage, indexing, and modeling are designed for large aggregations and fast analytical performance.
HardSystem Design
43 practiced
Design a monitoring and alerting system for query performance and ETL jobs supporting BI dashboards. Specify what metrics to collect (latency percentiles, error rates, backlogs), alert thresholds, dashboards to show, and automated remediation or runbook steps for common failures like slow queries or job failures.
Sample Answer
Requirements & goals:- Ensure BI dashboards meet SLOs: dashboards load <2s for 95th percentile, data freshness <15 minutes for near-real-time, ETL job success ≥99.5% daily.- Detect slow queries, increased error rates, ETL backlogs, schema drift.High-level design:- Instrumentation: DB query logging (query text, user, duration, rows, plan id), ETL job telemetry (status, start/end, duration, processed rows, source offsets), message/queue backlog metrics, and data freshness timestamps per table.- Central store: time-series DB (Prometheus/Influx) + event store (Elasticsearch/Kafka) + metadata catalog (Glue/Atlas) for lineage.Metrics to collect:- Query latency percentiles (p50/p95/p99) by dashboard/view/query, count, and frequency of full-table scans.- Query error rate (5xx/timeout/permission) per service.- ETL job success/failure rate, duration distribution, processed rows, input lag/backlog (e.g., Kafka consumer lag).- Data freshness per dataset (minutes since last successful update).- Concurrency/CPU/memory on DB and ETL runners.Alert thresholds (examples):- Query p95 > 2s for primary dashboard → P2 alert.- Query p99 > 5s or full-table-scan rate spike (>3x baseline) → P1.- ETL failures >1 in 24h or consecutive failures ≥3 → P1.- Data freshness > SLA (15m) → P1 until resolved.- Backlog growth rate >20%/hour or consumer lag >100k messages → P1.Dashboards to show:- Executive: overall SLOs, %fresh datasets, ETL success rate.- Ops: heatmap of query latency by dashboard & time, top slow/expensive queries, ETL pipeline status with run history and lag trends, resource utilization.- Drilldowns: per-query traces, query plans, lineage from dataset to source.Automated remediation & runbook steps:- Automated: - Retry transient ETL failures with exponential backoff (3 attempts) and circuit-breaker to avoid cascading. - Auto-scale ETL workers when backlog > threshold. - Query governor: kill runaway queries > X minutes and notify. - Cache invalidation & warm-up for heavy dashboard views.- Runbook for slow queries: 1. Identify top slow queries from dashboard, capture query text and plan. 2. Check recent schema or data volume changes, and table stats. 3. If full-table-scan: add/verify indexes or rewrite query to use partitions; consider materialized view. 4. If resource contention: check DB CPU/I/O, consider read-replicas or query throttling; schedule heavy jobs off-peak. 5. Deploy fixes on staging, monitor p95 improvements, then promote.- Runbook for ETL failures: 1. Inspect job logs, error type (auth, schema, source outage). 2. If transient (network/timeouts): rerun job; if persistent (schema drift), alert data owner and pause dependent dashboards. 3. Restore from checkpoint or reprocess window; validate row counts and checksums. 4. Postmortem and add test/alert to catch similar drift.SLO, alert tuning, and periodic reviews:- Define dashboards owners, runbook owners, and weekly review of alert noise. Use alert burn rate & on-call rotations. Track MTTR and iterate thresholds to balance sensitivity vs noise.
MediumTechnical
43 practiced
Discuss the trade-offs between using CTEs and temporary tables for complex multi-step ETL/SQL logic in a BI pipeline. Include performance considerations, materialization behavior, statistics availability, and maintainability. Mention DB-specific behaviors you know (e.g., older Postgres versions materialize CTEs).
Sample Answer
Situation: In BI work you often build multi-step transformations — choosing CTEs vs temp tables affects performance, plan quality, and maintainability. Below is a practical comparison and recommendations.Materialization / optimizer behavior- CTEs: Most engines treat CTEs as inline/views (optimizer can push predicates and reorder) — which is good for plan flexibility and avoiding extra I/O. Exception: older Postgres (pre-12) always materialized CTEs (opaque barriers), preventing pushdown and sometimes causing very bad plans. Modern Postgres (>=12) will inline by default but allows FORCE/MATERIALIZED hints.- Temp tables: are physically materialized when created (persisted to disk/temporary storage). That guarantees a stable snapshot and avoids recomputing upstream logic.Performance- CTEs (inlined): often faster if the optimizer can fuse operations and avoid writing intermediate results. Good when intermediate sets are small or when predicates/joins can be pushed down.- CTEs (materialized or in engines that materialize): can be expensive because they force full computation and storage, then reuse; this hurts if you only need a subset later.- Temp tables: cost to write/read and extra I/O, but can dramatically improve performance when: - intermediate result is reused multiple times, - you need to build indexes on the intermediate set, - join order/plan stability is important.Also consider concurrency and temp storage limits — many temp tables can stress temp storage and increase lock/IO contention.Statistics & cardinality- Temp tables: collect statistics (ANALYZE or automatic in some DBs), so optimizer gets accurate row counts and can choose good join algorithms and orders.- CTEs: inlined CTEs benefit from full query statistics; materialized CTEs may not have independent stats, so optimizer uses conservative estimates and can misplan.Maintainability & debugging- CTEs: excellent for readability — put step names inline, easy to refactor and review. Good for short/one-off transformations and embedding logic in a single view/query.- Temp tables: better for long multi-step ETL pipelines, iterative debugging (you can SELECT from temp table intermediate results), add indexes, and checkpointing between steps. But more boilerplate (CREATE/DROP, cleanup) and you must manage lifecycle.DB-specific notes- Postgres: pre-12 CTEs are materialized (barrier). Postgres 12+ inlines by default; you can use MATERIALIZED/FORCE to control behavior.- SQL Server: CTEs are inlined; temp tables (#temp) are materialized and support indexes/statistics.- BigQuery: CTEs typically inlined; repeated subqueries can be recomputed unless you explicitly materialize (CREATE TABLE AS or use scripting).- Snowflake: CTEs are optimized/inlined; if you need materialization for reuse or indexing, create transient/table.Recommendations for a BI analyst- Use CTEs for clarity and when transformations are simple or not reused.- Use temp tables when intermediate results are large, reused, need indexing, or you want stable plans and easier debugging/checkpoints.- On Postgres, be explicit: if you rely on inlining, ensure version >=12 or avoid writing CTEs that force materialization; if you need materialization, create temp tables or use MATERIALIZED pragmas.- Always monitor explain plans and cardinalities; if planner chooses a bad plan, try materializing the problematic step (temp table or explicit materialized CTE) and collect stats.This balance—readability vs control—guides most BI ETL choices: prefer CTEs first, switch to temp tables when performance or plan stability and intermediate statistics matter.
MediumTechnical
50 practiced
You have a query that groups by country and product and orders by total_sales descending with LIMIT 20. Propose index strategies or pre-aggregation to make this query fast at scale, and explain how an index can or cannot be used for GROUP BY and ORDER BY in this scenario.
Sample Answer
Situation: You need fast results for a query that groups by country and product, computes total_sales per group, orders by total_sales DESC and returns top 20 — this is a common BI "top-N" aggregation for dashboards.How indexes help (and limits)- A B-tree index can help ORDER BY if the index’s key order matches the ORDER BY and no aggregation is needed. But when you GROUP BY and SUM many rows, the database must still read and aggregate all matching rows for each group — a simple index on (country, product) helps locate rows per group faster but doesn’t avoid scanning all raw rows that contribute to each group.- A composite index (country, product, sales) is a “covering” index: the engine can read index pages instead of full rows, which reduces I/O. But you still need to aggregate SUM(sales) over groups — the index does not store pre-aggregated totals.- An index with sales DESC won’t produce the GROUP BY totals in order because ordering applies to raw rows, not aggregated sums. So ORDER BY total_sales DESC cannot be satisfied directly from an index on sales.Practical index strategies- Composite covering index: CREATE INDEX idx_sales_cp ON sales_table (country, product, sale_date, sales_amount); - Helps filter by country/product/date ranges and avoids lookups to the base table.- Partition by country or sale_date: reduces scanned data for time-based dashboards (e.g., monthly top products per country).- Partial indexes: if queries usually restrict to recent data, index only recent partitions.Pre-aggregation approaches (recommended at scale)- Materialized view / summary table that maintains total_sales by country, product (optionally by date bucket): - Refresh strategy: incremental/fast refresh (on insert) or scheduled refresh (nightly/near-real-time) depending on freshness requirements.- Maintain an aggregate table via ETL (incremental upserts): write small transactions that update totals as new sales arrive. This makes top-20 queries trivial: SELECT ... FROM agg_table ORDER BY total_sales DESC LIMIT 20 with an index on (total_sales DESC) or reverse-sorted PK.- Use OLAP/columnar stores (ClickHouse, Snowflake, BigQuery) or cubes (Presto/Trino with materialized aggregates) that optimize group/aggregate and top-N.- Rollups or cube tables if you need multiple group granularities.Recommended pattern for BI dashboards- Create and maintain a materialized summary (country, product, date_bucket) with appropriate refresh cadence. Index that summary on (total_sales DESC) or keep it clustered by total_sales so top-N reads are fast.- Use partitioning to reduce refresh scope and keep queries narrow.- For interactive filtering, combine summary tables with selective raw-table lookups for drill-downs.Why this is best: pre-aggregation moves the expensive GROUP BY/SUM off the interactive path; indexes then make the small summary fast for ORDER BY/LIMIT. Indexes alone don’t eliminate the need to aggregate raw events at scale.
CREATE MATERIALIZED VIEW mv_country_product_sales AS
SELECT country, product, SUM(sales_amount) AS total_sales
FROM sales_table
GROUP BY country, product;Unlock Full Question Bank
Get access to hundreds of Database Design and Query Optimization interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.