Database and Data Platform Selection Questions

Evaluation and selection of database and data platform technologies to meet analytical and operational needs. Covers assessment of relational, non relational, columnar, and specialized systems such as time series and search engines; data warehouse platforms and cloud analytics platforms; query patterns and workload characteristics; consistency and transactional guarantees; partitioning and clustering strategies; storage formats and compression; performance and scalability trade offs; operational complexity and administration overhead; data ingestion and incremental loading patterns; pricing and cloud platform considerations; and how to choose the right solution based on data volume, concurrency, latency requirements, and total cost of ownership.

MediumTechnical

50 practiced

Propose a set of data quality monitoring metrics and alerting rules you would implement for nightly ETL pipelines feeding dashboards. Include sample SQL checks, thresholds, and remediation steps for common anomalies like row-count drops, schema changes, and distribution shifts.

Sample Answer

High-level approach:- Monitor pipeline health at three levels: ingestion (pipeline success + row counts), schema & freshness, and data quality/distribution. Alert on failures with severity and automated remediation where possible.

Key metrics, sample SQL checks, thresholds, and remediation:

1) Pipeline run & row-count- Metric: daily_row_count(table, date)- SQL:

sql

SELECT COUNT(*) AS rows
FROM events
WHERE event_date = CURRENT_DATE - INTERVAL '1' DAY;

- Threshold: alert if rows < 70% of 7-day rolling average OR absolute drop > 30k rows (choose business-specific limits).- Alert rule: P1 if rows < 50% of baseline or pipeline failed; P2 for 50–70% drop.- Remediation: check job logs, re-run ingestion, validate upstream source; if intentional business change, acknowledge and update baseline.

2) Schema changes (column added/dropped/type change)- SQL (compare to stored schema snapshot):

sql

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema='public' AND table_name='events';

- Threshold: any diff -> immediate alert (P1).- Alert rule: notify data engineering + BI; block dashboard refresh if required.- Remediation: inspect migration PRs, coordinate with engineers; update ETL/semantic layer; add compatibility view or backfill if drop/unexpected type.

3) Null / completeness checks (key fields)- SQL:

sql

SELECT
  SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS null_user,
  COUNT(*) AS total
FROM events WHERE event_date = CURRENT_DATE - INTERVAL '1' DAY;

- Threshold: alert if null_user/total > 1% for user_id (adjust by field).- Remediation: check upstream mapping, backfill from raw logs, mark dashboards with data caveat.

4) Distribution shift / metric drift (quantiles & PSI)- SQL (daily quantiles):

sql

WITH quant AS (
  SELECT
    percentile_cont(ARRAY[0.1,0.5,0.9]) WITHIN GROUP (ORDER BY amount) AS qs
  FROM payments WHERE payment_date = CURRENT_DATE - INTERVAL '1' DAY
)
SELECT qs;

- Calculate Population Stability Index (PSI) offline comparing last 7 days vs prior baseline; alert if PSI > 0.2.- Threshold: PSI > 0.2 (moderate), >0.5 (severe).- Remediation: investigate business change (pricing, promotion), validate data types, check duplicates/outliers and apply filters or corrections.

5) Uniqueness & duplication- SQL:

sql

SELECT COUNT(*) AS total, COUNT(DISTINCT event_id) AS unique_ids
FROM events WHERE event_date = CURRENT_DATE - INTERVAL '1' DAY;

- Alert if total - unique_ids > 0 or duplicate rate > 0.1%.- Remediation: dedupe in ETL, check replay logic.

Alerting practices and runbook:- Use incremental alerts: low-severity to on-call Slack + dashboard; high-severity to pager/email and stop downstream refreshes.- Include automated diagnostics in alert payload: last successful run, row_count trend, sample failing rows (first 10), schema diff link.- Maintain runbook per alert with: checks to run (logs, source health), commands to re-trigger pipeline, owner contacts, and postmortem steps.

Instrumentation & governance:- Store baselines (7/30/90-day) and schema snapshots in a metadata table.- Schedule nightly checks post-ETL; surface results as an internal health dashboard.- Periodically review thresholds with stakeholders; add business validations (e.g., conversion rate bounds).

This set balances quick detection (counts, schema) with deeper statistical checks (PSI/quantiles) and prescribes clear alert severity and remediation steps tailored for BI dashboards.

HardSystem Design

37 practiced

Architect an analytics stack to handle 10,000 concurrent dashboard queries per minute with sub-second response targets for top-priority dashboards. Describe how you would use caching, pre-aggregation, workload isolation, and autoscaling compute, plus cost trade-offs.

Sample Answer

Requirements & constraints:- 10k concurrent dashboard queries/min (~167 qps sustained, bursts higher). Sub-second SLA for top-priority dashboards (executive, real-time KPIs). Mixed exploratory queries for analysts tolerated at higher latency. Cost-conscious.

High-level approach:- Separate workloads, serve SLAs with caching + pre-aggregation, autoscale compute for non-latency-critical work, and use read-optimized stores for low-latency slices.

Architecture:1. Ingest / storage: - Raw events → streaming (Kafka) → OLAP warehouse (Snowflake/BigQuery/Redshift) for ad-hoc and historical. - Nearline store (streaming aggregator) writing to materialized views and a low-latency analytics store (Druid/ClickHouse/Pinot) for time-series/aggregation queries.

2. Pre-aggregation: - Build materialized views / rollups at necessary grain (minute/hour/customer/product) in both warehouse and low-latency store. - Incremental refresh via streaming jobs (Flink or ksqlDB) so materialized aggregates are near-real-time. - For Looker/Tableau, expose aggregated derived tables (PDTs) for top-priority dashboards.

3. Caching: - Multi-layer cache: - Edge/browser cache + CDN for static dashboard assets. - Application-level cache (Redis/Memcached) keyed by dashboard id + filter hash for rendered JSON / chart data; store pre-computed results for most common filter sets. - Query-result cache inside the low-latency store (Pinot/ClickHouse often has in-memory segments). - Cache invalidation: TTLs (short for high-priority, longer for stable metrics) and event-driven invalidation on upstream writes for strong freshness dashboards.

4. Workload isolation: - Separate clusters/tenants: - Real-time cluster (ClickHouse/Pinot) sized for sub-second dashboards. - Analytical cluster (warehouse) for long-running ad-hoc queries; use separate compute to avoid noisy neighbors. - Query routing layer that tags requests by dashboard priority and routes to appropriate cluster + cache.

5. Autoscaling compute: - Real-time cluster: scale replicas/ingestion nodes horizontally; maintain enough memory for hot segments. Use policy-based autoscaling (scale on CPU, queue length, 95th latency). - Warehouse: use per-query compute clusters (Snowflake virtual warehouses / BigQuery Slots) with autosuspend and auto-resume for cost control. - Use serverless API layer (k8s + HPA) for front-end and caching proxies.

Operational and SLA behavior:- Top-priority dashboards serve from cache or low-latency store → sub-second.- If cold cache miss: fallback query to pre-aggregated tables (still sub-second to low-second).- Ad-hoc queries run on warehouse with higher latency; place limits and queuing.

Cost trade-offs:- In-memory/low-latency stores + aggressive pre-aggregation and high cache hit rates increase cost (compute + memory) but are necessary for sub-second SLAs.- Reduce cost by: - Choosing coarser-grain aggregates for less-used filters. - Tiering dashboards: only a small set gets hot resources. - Using autoscaling & spot instances for non-critical compute. - Leveraging cloud-managed services (Snowflake/BigQuery) to shift ops cost to consumption-based billing.- Monitor cost per query and measure cache hit ratio; tune TTLs and aggregation granularity to balance freshness, latency, and cost.

Key metrics to track:- 95th/99th percentile latency per priority tier- Cache hit ratio and invalidation rate- Concurrency and queue lengths- Cost per 1k queries by tier

Example concrete stack:- Ingest: Kafka → Flink streaming → Druid/ClickHouse (real-time segments) + Snowflake (historical)- Cache: Redis for dashboard result cache- Orchestration: Kubernetes for APIs, Snowflake autoscaling for heavy analyticsThis yields deterministic sub-second responses for prioritized dashboards while controlling costs via tiering, caching, and autoscaling.

HardSystem Design

51 practiced

Design an analytics platform that supports a global product team with customers in multiple regions. Requirements: update aggregated metrics within 1 minute, support local dashboards with <2s latency, store 5 TB of cold data, and minimize cross-region egress costs. Sketch architecture components and justify choices around replication, local compute, and global aggregation.

Sample Answer

Requirements (clarify):- Fresh aggregated metrics <=1 minute globally.- Local dashboards per region with <2s query latency.- Cold storage for 5 TB.- Minimize cross-region egress.

High-level architecture:- Regional ingestion + OLAP cluster (one per region) - Ingest events via regional streams (Kafka / Kinesis) → real-time stream processor (Flink/ksqlDB) that computes rolling aggregates and writes to regional OLAP store (ClickHouse, BigQuery regional, or Snowflake reader/writer). - Regional cache / serving layer (Redis / Pinot) for dashboard queries to meet <2s latency.- Global aggregator - Periodic (30s–1m) push of region-level deltas (pre-aggregated metric bundles) to a central aggregator endpoint (small payloads) to compute global KPIs within 1 minute.- Cold storage - Regional object stores (S3/GCS) with lifecycle to move to a single cold region or multi-region archive (Glacier/Archive) — store raw events and full fact tables (5 TB).- Metadata/catalog + access - Centralized data catalog (e.g., Amundsen) and BI access layer (Looker/Tableau) connected to regional OLAP stores; dashboards routed to local region via DNS or gateway.

Replication & egress minimization:- Replicate only pre-aggregated deltas (small) rather than raw events across regions.- Keep full raw data regional; copy to cold central region asynchronously only when necessary (batch transfer during low-cost windows).- Use WAN-optimized protocols / cloud provider inter-region transfer discounts.

Local compute:- Serve dashboards from regional OLAP + cache to hit <2s.- Run heavier rollup jobs locally; push only rollup results.

Global aggregation:- Use central aggregator to merge region deltas with conflict-free timestamps and versioning; ensure idempotency.- Provide eventual consistency guarantees within 1 minute; surface data freshness indicator on dashboards.

Trade-offs:- Pros: low latency locally, low egress, scalable global metrics.- Cons: slightly more complex orchestration and need for strong schema/version management.

Monitoring & ops:- Track pipeline lag, egress volume, and cache hit rates; automated failover to global aggregator if regional cluster fails.

This design balances BI requirements for fast, local dashboards with cost-conscious global aggregation by moving compute to regions and minimizing raw-data transfer.

HardTechnical

48 practiced

Write an efficient SQL MERGE (or equivalent) to perform an SCD Type 2 update for a customers table with columns: customer_id, name, email, valid_from, valid_to, is_current. Provide the SQL and explain how you avoid long locks or full-table scans when updating 100M rows.

Sample Answer

Approach: load incremental changes into a small staging table, then apply SCD Type 2 using targeted, indexed operations and batching to avoid full-table scans/long locks. Key techniques: partitioning by customer_id or date, an index on (customer_id, is_current), small transactions (micro-batches), and using MERGE only against the staging set.

Example (ANSI-ish SQL — adapt syntax for your RDBMS):

-- staging table contains incoming rows: customer_id, name, email, change_tsCREATE TEMP TABLE stg_customers AS SELECT * FROM incoming_changes; -- small, indexed on customer_id

-- 1) MERGE: expire current rows when attributes changed, insert new history rowsMERGE INTO customers tgtUSING ( SELECT s.customer_id, s.name, s.email, s.change_ts FROM stg_customers s) srcON (tgt.customer_id = src.customer_id AND tgt.is_current = 1)WHEN MATCHED AND (tgt.name <> src.name OR tgt.email <> src.email) THEN UPDATE SET valid_to = src.change_ts, is_current = 0WHEN NOT MATCHED BY TARGET THEN INSERT (customer_id, name, email, valid_from, valid_to, is_current) VALUES (src.customer_id, src.name, src.email, src.change_ts, '9999-12-31', 1);

-- 2) Insert new current rows for changes (ensure duplicates avoided)INSERT INTO customers (customer_id, name, email, valid_from, valid_to, is_current)SELECT s.customer_id, s.name, s.email, s.change_ts, '9999-12-31', 1FROM stg_customers sLEFT JOIN customers c ON c.customer_id = s.customer_id AND c.is_current = 1WHERE c.customer_id IS NULL OR (c.name <> s.name OR c.email <> s.email);

Performance & locking strategies:- Work with a small staging set (only changed rows). Do NOT MERGE against full 100M table.- Ensure an index on customers(customer_id, is_current) so MERGE/updates hit few pages.- Use partitioning (by customer_id hash or date) so updates target single partition.- Run in micro-batches (e.g., 10k–100k staging rows) inside separate transactions to limit lock duration and log growth.- Use snapshot/READ COMMITTED SNAPSHOT isolation if available to reduce blocking.- Prefer set-based MERGE for matched expire/update and a separate INSERT for new current rows to avoid long-held locks from large MERGE transactions.- Monitor and tune: statistics, fillfactor, and avoid page splits; use async replication or change-data-capture for upstream ingestion.

Edge cases: concurrent updates for same customer — serialize by customer_id (e.g., application lock) or include change_ts / source ordering to deterministically order history.

HardTechnical

41 practiced

You are migrating a 10 TB Teradata on-prem data warehouse to Snowflake with a requirement of minimal downtime and verified analytics parity. Outline an end-to-end migration plan including data movement strategy, schema mapping, SCD handling, validation testing, cutover plan, and rollback considerations.

Sample Answer

Requirements & constraints- 10 TB Teradata on‑prem, minimal downtime, verified analytics parity, BI consumers (Tableau/PowerBI), preserve SCD semantics.

Plan overview (phases)1. Discovery & design (2–3 weeks)- Inventory tables, row counts, PKs, indexes, constraints, data types, SCD patterns, ETL jobs, downstream reports.- Define SLAs for downtime and parity acceptance tests.- Choose migration tools: Teradata TPT/FASTLOAD for bulk extract, CDC via Teradata QueryLog/Audit or a CDC tool (Attunity/Qlik Replicate, Fivetran, Striim) to capture deltas; cloud staging on S3 (or Azure Blob) + Snowflake COPY/PUT; orchestration via Airflow/DBT.

2. Schema mapping & transformation- Create mapping doc per table: Teradata types → Snowflake types (e.g., BYTEINT/SMALLINT→NUMBER, VARCHAR→VARCHAR with length, BLOB→VARIANT if JSON).- Map distribution/primary index logic to Snowflake clustering keys / search optimization: select clustering on high-cardinality date or business keys for large fact tables.- Recreate constraints as metadata (Snowflake is permissive) and implement data quality checks in ETL.

3. Bulk load (initial full copy, low priority downtime)- Export using parallel TPT to compressed files, land in S3.- Load into Snowflake staging tables via COPY INTO with proper file format and PARALLEL.- For large FACTS, use multipart and multi-cluster warehouses to parallelize.

4. SCD & CDC handling (minimal downtime)- Implement CDC to stream changes from Teradata to Snowflake. Options: - Log-based CDC (preferred) with Qlik Replicate / Attunity → write to Snowflake staging; or - If not available, use timestamp-based incremental extracts.- Recreate SCD Type 1/2 logic in target ETL: - Type 1: overwrite current rows via MERGE. - Type 2: MERGE to close existing record (set end_date) and INSERT new row with new surrogate key.- Maintain source system change sequence and apply in order; include conflict resolution rules.

5. Validation testing & analytics parity- Row-count reconciliation per table, partition/date ranges.- Hash-based row-level comparison: compute MD5/SHA256 of concatenated key+cols in source and target on sampled partitions.- Aggregate checks: sums, distinct counts, null counts, business KPIs over sliding windows.- End-user report parity: run canonical dashboard queries against both sources and compare visuals and numbers.- Automate tests (pytest/Great Expectations/DBT tests), generate diff reports and thresholds; involve business owners to sign off.

6. Cutover plan (minimal downtime)- Run full bulk load, start CDC to keep target near-real-time.- Run iterative reconciliation until delta backlog is tiny.- Schedule short freeze window (e.g., 15–60 min) for final catch-up: - Pause ETL that writes to Teradata. - Drain CDC stream, apply last transactions to Snowflake. - Run final parity checks (critical KPIs). - Switch BI connections to Snowflake (DNS/connection string change, update ODBC/JDBC and dashboards).- Monitor with smoke tests and key dashboards for 24–72 hours.

7. Rollback considerations- Keep Teradata read-only for a rollback window (e.g., 7 days).- If parity fails post-cutover, revert BI connections to Teradata connection strings; communicate rollback to stakeholders.- Do not truncate Teradata; preserve CDC logs and staging files.- Document fail criteria and automated rollback steps.

Operational & non-functional items- Performance tuning: right-size Snowflake warehouses; use clustering & result caching; consider materialized views for heavy joins.- Security & compliance: encrypt files in transit, use IAM roles for S3, set Snowflake RBAC, mask sensitive columns.- Cost control: monitor storage vs compute; use auto-suspend.- Training & runbook: update dashboards’ owners, provide queries and runbooks for validation, monitor SLAs.

Outcome & metrics- Success criteria: zero critical KPI discrepancies, < agreed downtime, user sign-off on sample dashboards, full rollback plan tested.

Unlock Full Question Bank

Get access to hundreds of Database and Data Platform Selection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.