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

45 practiced

For a columnar data warehouse with heavy aggregations, explain how compression codecs, encoding schemes and sort order (clustering keys) affect disk usage and query performance. Provide a step-by-step tuning approach (analyze cardinality, choose encoding, adjust sort keys, validate with sample queries).

Sample Answer

Compression codecs, column encodings and sort order are tightly coupled in columnar warehouses: the right combo minimizes disk I/O (smaller files, fewer bytes scanned) and speeds aggregations (better cache/CPU utilization), while wrong choices increase CPU or prevent efficient range/bitmap scans.

Why it matters- Codecs (ZSTD, LZ4, Snappy) trade CPU vs compression ratio: ZSTD compresses more (lower IO) with higher CPU; LZ4 is fast but larger.- Encodings exploit column value patterns: dictionary for low-cardinality strings, run-length (RLE) for repeated values, delta/varint for monotonic numerics (timestamps, surrogate keys), bit-packing for booleans/bitsets.- Sort order (clustering keys) groups similar values physically so encodings (RLE/dictionary/delta) compress much better and range scans touch fewer blocks.

Step-by-step tuning approach1. Analyze cardinality & access patterns- Collect per-column stats: cardinality, null ratio, value distribution, monotonicity, and query predicates/aggregations.- Example: user_id high-cardinality; country low-cardinality; event_time monotonic.

2. Choose encoding per column- Low-cardinality strings (country): dictionary encoding → small dictionary + compact indices.- Repeated values (status flags): RLE or bit-pack.- Monotonic timestamps or increasing IDs: delta + varint.- High-entropy text: leave unencoded or compress with codec only.

3. Pick compression codec at file or segment granularity- For scan-heavy, large clusters where IO is the bottleneck: choose ZSTD (level tuned) to reduce bytes scanned.- For latency-sensitive, CPU-constrained environments: choose LZ4/Snappy.- Consider per-table or per-partition codec if supported.

4. Adjust sort/clustering keys- Cluster by columns used in range filters or group-bys (e.g., event_time, country).- Keep high-cardinality group-by last in key order; put low-cardinality or monotonic fields first to maximize RLE/delta benefits.- Repartition/compact data to apply new sort order and compact encodings.

5. Validate with sample queries- Build representative queries (aggregations, filter patterns).- Measure: bytes scanned, IO wait, CPU time, latency, compression ratio, read amplification.- Compare before/after (e.g., bytes_scanned reduced 6x, latency 40% lower).- Check worst-case queries (full table scans) and update/ingest cost (reclustering overhead).

Trade-offs and operational notes- Better compression reduces disk and network cost but increases CPU during reads; balance depending on load.- Re-clustering/compaction is expensive; schedule during low activity or use incremental compaction.- Monitor metrics: compression ratio per column, predicate pushdown hit rate, block elimination stats.

Example heuristics- country → dictionary + ZSTD (high compression, low CPU per row)- event_time → delta + ZSTD; cluster by event_time- user_id (group-by heavy) → leave as varint + cluster secondarily

This approach aligns storage efficiency with query patterns: analyze, encode, cluster, and validate iteratively until compression ratio, bytes-scanned, and query latency meet SLAs.

HardTechnical

48 practiced

A financial customer requires RPO < 1s and RTO < 5s for critical ledger data. Evaluate database technologies and design patterns (synchronous replication, quorum writes, consensus protocols like Paxos/Raft, synchronous cross-region replication) that can meet these SLAs and discuss the operational complexity and residual risks of each approach.

Sample Answer

Situation: A financial customer requires Recovery Point Objective (RPO) < 1s and Recovery Time Objective (RTO) < 5s for critical ledger data. Below I evaluate feasible database technologies and design patterns, their ability to meet SLAs, operational complexity, and residual risks.

Summary of viable approaches- Synchronous replication (primary→replica commit blocks until replica ack): Meets RPO≈0 and supports RTO <5s if failover automation is fast. Best within a single datacenter or low-latency metro region (<5–10ms RTT). Operational complexity: moderate (configure sync replicas, monitors, failover). Risks: higher write latency; risk of availability loss if replica slow/unreachable (can block writes).- Quorum writes on multi-node clusters (e.g., majority-write in distributed DBs): Provides strong durability and consistency (RPO≈0). Works cross-az/regional if latency tolerable. Complexity: higher — cluster membership, tuning write consistency levels, leader election. Risks: split-brain handling, increased write latency, careful capacity planning needed to keep quorum nodes healthy.- Consensus protocols (Raft/Paxos) backed databases (CockroachDB, etcd, FoundationDB): Provide linearizable commits and durable logs—satisfy RPO and enable deterministic failover for RTO <5s if health detection and leader failover are tuned. Complexity: high — operational expertise to tune election timeouts, snapshotting, compaction, and scaling. Risks: same as quorum; long GC/snapshots can delay recovery; cross-region latency degrades throughput.- Synchronous cross-region replication: Achieves geo-durability with RPO≈0 but only if cross-region RTT is low. Complexity: very high — network engineering, cost, regulatory considerations. Risks: high write latency, outage domain expands (network flaps between regions block commits).- Hybrid: Local sync replication for availability/perf + async geo-replica for disaster recovery. This achieves RPO <1s locally; async DR provides additional copy with small RPO risk. Complexity: moderate-high but balanced. Residual risk: in a full-region loss, async tail might lose up to seconds of data unless combined with fast cross-region sync.

Operational considerations to meet RTO <5s- Automated failover with health checks and pre-tested runbooks; avoid manual steps.- Fast, deterministic leader election (tuned Raft timeouts) and connection draining.- Warm standby and connection routing (VIP, DNS TTL low, or service mesh) to reduce client reconnection time.- Regular DR drills, chaos testing, and monitoring of commit latency, replication lag, and tail durability.

Recommendation (Solutions-Architect perspective)- If customer latency budget allows only intra-metro sync: use a strongly-consistent DB with sync replicas (Raft-backed) and automated failover (e.g., FoundationDB, CockroachDB) plus local HA across AZs. This meets RPO<1s and RTO<5s with moderate operational effort.- If geo-durability needed and cross-region RTT low: use quorum/consensus over the regions but accept higher write latency or use hybrid: local sync + async cross-region with near-real-time streaming (CDC) and plan RTO workflows for full-region failover.- Invest in operational practices: runbooks, monitoring, capacity headroom, and frequent recovery exercises.

Residual risks summary- Increased write latency leading to UX/throughput impacts- Availability impact if sync replicas become slow/unavailable- Complex operational surface (elections, compaction, network)- Risk of data loss if async-only DR used for region failure

Decision factors: acceptable client write latency, topology (latency between sites), compliance/regulatory constraints, budget for higher-cost sync cross-region links, and internal ops maturity.

HardSystem Design

42 practiced

Design a multi-tenant analytics platform that allows tenants to run complex ad-hoc queries without impacting other tenants. Cover options for workload management, per-tenant resource pools, sandboxing/dedicated clusters, query prioritization, admission control, and cost attribution for tenant billing.

Sample Answer

Requirements:- Multi-tenant analytics that supports complex, long-running ad-hoc SQL queries- Strong isolation so one tenant’s heavy queries don’t impact others- Fairness, priority levels, admission control, and per-tenant cost attribution for billing- Elasticity, predictable SLAs, and operational simplicity

High-level architecture:API/Gateway → Auth & Tenant Service → Query Planner / Proxy → Workload Manager → Compute Layer (shared pools + dedicated clusters) → Storage (object store + metadata/catalog) → Telemetry & Billing

Core components & responsibilities:1. Tenant Service: tenant metadata, SLAs, priority levels, billing config.2. Query Proxy/Planner: parses incoming SQL, estimates cost (rows, CPU, memory) via statistics; tags queries with tenant, priority, estimated resources.3. Workload Manager: - Admission Control: uses estimated cost + current system utilization to accept, queue, reject, or route query. - Scheduler: maps queries to resource pools (per-tenant soft pools, global spare pool, or dedicated cluster). - Prioritization: multi-level queues (real-time/high/normal/low) with weighted fair share and token buckets for rate limits.4. Resource Pools: - Soft per-tenant pools: guaranteed minimal vCPU/RAM and IOPS; overflow allowed into global pool subject to throttling. - Hard dedicated clusters/sandboxes: option for premium tenants to get isolated clusters (K8s or managed SQL engines) for zero-noise isolation.5. Compute Layer: containerized worker nodes auto-scaled; use preemptible workers for low-priority jobs.6. Storage: separation of compute and storage (object store for data, columnar format), metadata catalog for stats.7. Telemetry & Billing: track CPU-seconds, memory-seconds, I/O, storage, and cluster-hours per tenant; attribute cost via metering pipeline.

Admission control & prioritization policy:- Use cost-estimation + backpressure: reject if estimated need > tenant quota or system critical capacity.- Weighted Fair Queueing across tenants with priority boost for SLA/premium tenants.- Preemption: low-priority queries can be suspended/evicted when capacity needed (checkpointing if supported).- Time-based throttles and concurrency limits per tenant.- “Sandbox” path: queries routed to dedicated clusters if tenant configured or if estimated impact > threshold.

Sandboxing vs shared pools trade-offs:- Dedicated clusters: strongest isolation, predictable latency, higher cost. Use for high-value tenants.- Per-tenant soft pools + global spare: cost-efficient, works for many tenants; requires robust admission control and preemption to protect SLAs.

Cost attribution & billing:- Meter CPU-seconds, memory-seconds, bytes read/written, and cluster reservation hours.- Tag metrics by tenant + job id; store in time-series DB and produce daily usage aggregates.- Billing models: pay-as-you-go (resource usage), subscription (reserved capacity), or hybrid (base reservation + overage).- Show transparent invoices: itemize compute, storage, egress, and dedicated cluster charges.

Scalability & resiliency considerations:- Use autoscaling for workers and storage separation for independent scaling.- Cache query plans and materialized views to reduce repeated costs.- Monitor fairness and noisy-neighbor signals, tune admission thresholds, and allow live configuration.

Security & governance:- Row/column-level access control in catalog, encryption at rest/in transit, audit logs of queries.

Key trade-offs:- Accuracy of cost estimation vs admission accuracy: invest in stats and ML models to improve predictions.- Isolation vs cost: dedicated clusters increase cost but simplify SLO guarantees.- Preemption complexity vs improved fairness: implement only if tenants accept possible job restarts.

This design gives predictable isolation options (soft pools or dedicated clusters), strong admission control and prioritization to protect tenants, and precise metering for fair billing.

HardSystem Design

49 practiced

Outline a governance and data lifecycle strategy for a petabyte-scale data lake: define hot/warm/cold tiers, automated lifecycle policies, retention and legal hold, access controls and RBAC, encryption, data catalog/lineage, data quality and profiling checks, and cost-optimization tactics. Explain how policies are enforced technically.

Sample Answer

Requirements & constraints:- Petabyte-scale data lake with low-latency analytics on recent data, cost-efficient archival, regulatory retention and legal-hold capability, strict access controls, lineage & data quality, and multi-tenant customer isolation.

High-level tiers:- Hot: last 7–30 days; storage: SSD-backed object store or ADB/Delta/iceberg on fast object tier; query via cached compute (Presto/Spark/Databricks SQL endpoints).- Warm: 30–180 days; standard object storage with partition pruning and moderate compute (on-demand clusters).- Cold: 180 days–several years; infrequent-access object tier (infrequent/archival buckets).- Deep-Archive: regulatory retention beyond X years; offline/Glacier-type with restore windows.

Automated lifecycle policies:- Use object lifecycle rules (e.g., S3 Lifecycle / GCS Object Lifecycle / Azure Blob lifecycle) to transition objects by prefix/partition and tags (env, sensitivity, ingestion_date).- Implement retention metadata (retention_until, legal_hold_flag) in object tags and catalog; lifecycle jobs evaluate tags and do transitions/deletes only when retention expires and no legal hold.

Retention & legal hold:- Policy: retention_period per dataset; legal hold sets immutable flag.- Technical: use object lock (WORM) + retention mode; catalog-level lock prevents deletion via IAM checks. Provide API for legal hold set/release with audit trail.

Access controls & RBAC:- Principle of least privilege: dataset-level roles (reader, analyst, owner, steward).- Implement IAM + resource policies (object prefixes, tags) and attribute-based access control (ABAC) using user/group attributes.- Enforce row/column level masking with query engine (dynamic data masking) and column-level encryption keys via KMS.- Use temporary credentials (STS) for analytics jobs and assume-role for cross-account access. Log all access to audit logs.

Encryption:- At-rest: server-side encryption with CMKs (KMS/HSM); use separate keys per environment/dataset sensitivity.- In-transit: TLS for all endpoints.- Envelope encryption for very sensitive columns; key rotation policy with re-encryption workflow.

Data catalog & lineage:- Central catalog (e.g., AWS Glue/Data Catalog, Apache Atlas) storing schema, tags, sensitivity, owners, retention, and physical locations.- Automated lineage captured via ingestion frameworks (Spark/Databricks lineage hooks, Kafka Connect), and ETL orchestration (Airflow) that emit lineage events into catalog.- Expose search, ownership, SLA, and automated alerting for stale datasets.

Data quality & profiling:- At ingestion: schema validation, null/uniqueness/PK checks, basic stats. Use frameworks (Deequ, Great Expectations) to run profiling jobs.- Continuous monitoring: scheduled tests, drift detection, and data contracts that block downstream pipelines if quality fails.- Quality results stored in catalog; steward notification and rollback or quarantine flows.

Cost-optimization tactics:- Partitioning, compaction, and file size optimization for query efficiency.- Use columnar formats (Parquet/ORC) and compression.- Tiered storage lifecycles; auto-suspend idle clusters, use serverless query engines for ad-hoc.- Use spot/preemptible instances for ETL; periodic cold-storage purge per retention.- Monitor cost per dataset and tag with chargeback.

Policy enforcement (technical):- Combine declarative policies in catalog + enforcement plane: - Pre-commit hooks in ingestion pipelines validate schema/tags and call policy engine. - IAM + resource policies block unauthorized API calls. - Lifecycle engine (serverless functions/cron jobs) reads catalog, evaluates retention/legal hold tags, and calls storage APIs (transition/delete) while respecting object-lock. - Query-time enforcement via query engine plugins for row-level security and masking. - Audit/monitoring via centralized logging (CloudTrail/Stackdriver) and SIEM; automated alerts and remediation playbooks.

Operational controls & governance:- Data steward governance board, periodic audits, automated certification workflows in catalog.- Playbooks for legal hold, breach, and key compromise. SLA & DR tested via rehearsals.

This design maps to cloud-native primitives (S3 Lifecycle/Object Lock, KMS, IAM, Glue/Atlas, Athena/Presto/Databricks, Lambda/Airflow) and enforces governance through a combination of metadata-driven automation, access controls, encryption, and auditability.

HardTechnical

78 practiced

For a collaborative editing product, analyze the trade-offs between using CRDTs (eventual consistency, automatic merge) vs strong consistency implemented via distributed transactions or locking. Consider latency, conflict resolution complexity, offline editing support, undo semantics and developer complexity; recommend an approach and justify it.

Sample Answer

Requirements & context: collaborative editing product needs real-time low-latency collaboration, offline edits, intuitive undo/redo, reasonable developer effort, and predictable correctness for business-critical content.

High-level trade-offs- Latency: CRDTs shine — local edits are immediately visible (O(1) local), remote merges are asynchronous. Strong consistency (distributed transactions/locks) forces coordination on each conflicting operation, adding round-trips (Paxos/Raft) and higher tail latency.- Conflict resolution complexity: CRDTs push complexity into data-type design (commutative, idempotent ops); once correct, merges are automatic. Strong consistency centralizes logic: application sees a single canonical state, so conflict logic is simpler but must be enforced via consensus protocols.- Offline support: CRDTs naturally support offline edits and later sync without user-visible merge failures. Strong-consistency systems typically block or require complex buffering + rebase/merge on reconnect.- Undo semantics: CRDTs make intuitive undo tricky—causal undo requires tombstones or operation-reversal CRDTs and careful causality tracking. Strong-consistency systems can implement linear undo more easily because there’s a single authoritative timeline.- Developer complexity & operational cost: CRDTs require investing in correct CRDT designs, testing convergence and edge cases; but they reduce backend coordination load. Strong-consistency needs implementing consensus, dealing with leader failover, and can be simpler at app-level but more operationally heavy.

RecommendationFor a real-time collaborative editor prioritizing low latency and robust offline support (e.g., docs, notes, design tools), choose CRDTs for the collaboration layer, but:- Use CRDTs tailored to primitives (sequence CRDT like RGA/WOOT or Logoot-Undo for text; JSON CRDT for rich docs).- Layer a strong-consistency service for critical metadata and access control (permissions, document ownership) and for server-side authoritative snapshots (periodic compaction/checkpointing) to simplify undo and GC (tombstone reclamation).- Provide an operational strategy: rigorous formal tests (convergence, commutativity), visible merge UI for ambiguous cases, and server-side reconciliation to support deterministic undo/redo and retention policies.

Justification: This hybrid gives best UX (instant local edits, offline), bounded complexity (scope CRDTs to collaboration ops), and operational safety (critical invariants via strong consistency).

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.