Data Infrastructure Technology Selection Questions

Deep understanding of specific technologies relevant to complex system design. Master databases (PostgreSQL, Cassandra, DynamoDB, Elasticsearch), message queues (Kafka, RabbitMQ), caching systems (Redis), search engines, and frameworks. Understand their strengths, weaknesses, trade-offs, operational characteristics, scaling patterns, and common pitfalls. Be able to justify technology choices based on specific system requirements.

MediumSystem Design

77 practiced

Design a storage and search solution for a product catalog with 50M products needing faceted filters, sort by popularity, and near-real-time updates. Compare using Postgres + materialized views, Elasticsearch, or a hybrid approach. Explain indexing, consistency, and refresh strategies.

Sample Answer

Requirements clarification:- 50M products, faceted filters (category, brand, price ranges, attributes), sort by popularity, near‑real‑time (seconds → low tens of seconds) updates, high read throughput, moderate write rate (product updates, inventory, popularity signals).

High-level options and trade-offs:1) Postgres + materialized views- Pros: Strong consistency, familiar SQL, ACID. MV can precompute facets/sorts.- Cons: With 50M rows, per-request faceting expensive unless many MVs; refreshes lock/expensive; MV refresh latency often minutes; not ideal for full-text search or complex scoring.

2) Elasticsearch (ES)- Pros: Designed for faceted aggregation, fast sorted queries, relevance ranking, near-real-time (refresh interval ~1s), horizontal scaling for reads.- Cons: Eventual consistency between source DB and ES; need careful mapping, analyzers; reindexing heavy operations; weaker transactional guarantees.

3) Hybrid (Recommended)- Use Postgres (or DynamoDB) as source of truth for product data and writes. Stream changes (CDC via Debezium/Kinesis) to a processing layer that: - Applies business logic, updates popularity counters, and writes to ES. - Persists authoritative state in Postgres.- Query path: Read from ES for catalog search & faceting; fall back to Postgres for authoritative single-record reads.

Indexing strategy:- In ES, index product documents with denormalized attributes needed for filters (keyword fields for facets, numeric for ranges, geo if needed). Use multi-fields for text (keyword + analyzed).- Use doc-values for aggregations and nested fields for variant attributes.- Maintain a popularity field (numeric score) updated via incremental scripts or streams.

Consistency & refresh strategies:- ES refresh interval tuned to 1s for near‑real‑time; batch heavy updates into small windows to reduce segment churn.- Use write-ordering via change streams with sequential offsets; ensure idempotent upserts in ES (by product id).- Handle transient inconsistency: include "last_updated" timestamps; for critical reads, read a product from Postgres if ES timestamp older than threshold.- Reconciliation job: nightly full diff or use versioned snapshots to detect missed updates and reindex small subsets.

Operational considerations:- Monitoring (indexing lag, refresh time, failed documents).- Shard sizing to keep segments manageable; use rollover and force-merge policies.- Backups and ability to reindex from Postgres.

Summary:For 50M products needing rich faceting and sub‑second-ish freshness, a hybrid approach (Postgres as source-of-truth + ES for search/aggregations) balances consistency, feature set, and performance. Tune ES refresh/merge, use CDC for robust sync, and implement reconciliation to ensure eventual consistency.

EasyTechnical

39 practiced

Describe primary key selection and use of foreign keys in a relational database. Then explain how to map those relational concepts when modeling data in a NoSQL system like DynamoDB or Cassandra, highlighting trade-offs in queryability and join avoidance.

Sample Answer

Primary keys & foreign keys (relational):- Primary key: unique, non-null identifier for a row (surrogate int/UUID or natural key). Enforces entity identity and fast lookups.- Foreign key: column(s) referencing another table’s primary key to enforce referential integrity and model relationships (1:many, many:many). Relational design favors normalization to avoid redundancy and enable ACID consistency.

Mapping to NoSQL (DynamoDB / Cassandra):- No rigid foreign keys: these systems optimize for fast key-based access and denormalization. Model by designing access patterns first.- DynamoDB: choose a partition key (and optional sort key) that supports your queries. Represent relationships by: - Composite item patterns: store parent and children in same partition (PK=User#<id>, SK=Order#<orderId>) so queries return related items without a join. - Single-table design: multiple entity types in one table distinguished by type prefix; enables multi-entity reads by single key/range. - Secondary Indexes (GSI/LSI) for alternate query patterns, at cost of extra storage and eventual consistency.- Cassandra: use partition key + clustering columns to co-locate related rows. Use wide rows for one-to-many. Materialized views or application-managed denormalization for alternative queries.

Trade-offs:- Queryability: NoSQL requires designing for specific queries; flexible ad-hoc joins are poor. You gain predictable, low-latency reads for designed patterns.- Join avoidance: denormalization and grouping eliminate runtime joins but duplicate data, increasing storage and update complexity — updates must be propagated (eventually consistent).- Consistency and complexity: relational FK enforces integrity; NoSQL shifts integrity to application logic or use of transactions (limited). Choose between strong consistency (more coordination) or higher throughput with eventual consistency.

Example (DynamoDB): store Orders and OrderItems under PK=ORDER#<orderId>, SK=METADATA / ITEM#<itemId> to fetch an order and its items in one partition read.

MediumTechnical

41 practiced

Explain how Kafka achieves durability and ordering guarantees per partition. Describe consumer groups, offsets, and how different delivery semantics (at-most-once, at-least-once, exactly-once) are achieved or approximated in Kafka-based systems.

Sample Answer

Durability and ordering:- Kafka persists messages to replicated logs on disk. Each topic partition is an ordered, immutable sequence of records. A partition’s leader accepts writes, appends them to its log, and replicates to followers. Durability is controlled by replication factor and acks: a.producer acks=all (or -1) plus min.insync.replicas ensures a record is persisted to multiple brokers before the write is acknowledged. Ordering within a partition is guaranteed because writes are appended in a single log and read in that same sequence.

Consumer groups & offsets:- A consumer group is a set of consumers that jointly consume a topic; each partition is assigned to at most one consumer in the group, preserving per-partition ordering for that consumer. Consumers track their progress via offsets (the numeric position in a partition). Offsets can be committed automatically (auto.commit) or manually to Kafka (or an external store). Committed offsets represent the durable claim of “I processed up to offset X.”

Delivery semantics:- At-most-once: consumer commits offsets before processing (or producer uses acks=0). If failure occurs after commit but before processing, message is lost—no retries.- At-least-once: common default. Consumer processes message then commits offset (synchronous/manual). On crash before commit, the message is re-delivered and reprocessed, possibly causing duplicates.- Exactly-once (effectively): Kafka’s EOS uses idempotent producers + transactions. Producer sets enable.idempotence=true to avoid duplicates on retries; for multi-partition/topic atomicity, use transactions (initTransactions/beginTransaction/commitTransaction). On the consumer side, use read-process-write within a transaction and use consumer offsets commit via sendOffsetsToTransaction so offsets and output writes are atomically committed—this yields end-to-end exactly-once semantics for processing pipelines that use Kafka transactions. Note: EOS incurs throughput/latency trade-offs and requires careful configuration (transactional.id, broker versions, proper commit handling).

Edge considerations:- Ordering is only per-partition; cross-partition global ordering requires single partition or additional coordination.- Exactly-once across external systems is hard; use idempotent sinks or external two-phase commits if needed.

EasyTechnical

47 practiced

What is an index? Explain common index types in PostgreSQL (B-tree, Hash, GIN, GiST) and give at least one concrete scenario where each is the right choice. Mention cost implications for writes and maintenance.

Sample Answer

An index is a data structure that speeds up lookups by mapping key values to row locations so the database can avoid full-table scans. Indexes trade read performance for storage and write/maintenance cost.

B-tree- Description: Balanced tree, default for equality/range queries on ordered datatypes.- Good for: WHERE col = ? and WHERE col BETWEEN ? AND ?; ORDER BY; primary keys.- Concrete scenario: Indexing created_at for fast range queries on recent rows.- Costs: Moderate storage; every INSERT/UPDATE/DELETE must update tree pages — adds write latency and occasional page splits; requires VACUUM/REINDEX rarely.

Hash- Description: Hash table optimized for equality lookups.- Good for: WHERE col = ? only.- Concrete scenario: Very frequent point lookups on a unique token column.- Costs: Historically less flexible (no range queries), needs more careful sizing; writes update hash buckets — similar maintenance; PostgreSQL’s hash indexes are crash-safe now but less commonly used.

GIN (Generalized Inverted Index)- Description: Inverted index for composite/containment types (arrays, jsonb, full-text).- Good for: Existence/contains queries like jsonb @>, full-text search tsvector, array element searches.- Concrete scenario: Searching posts by tags stored as an array or jsonb document containment.- Costs: Larger index size; slower inserts/updates because many index entries per row; support for fast multi-key queries.

GiST (Generalized Search Tree)- Description: Flexible tree for range/nearest-neighbor/spatial data using custom strategies.- Good for: Geospatial (PostGIS), proximity queries, full-text ranking, range types.- Concrete scenario: Spatial queries finding points within a bounding box (PostGIS uses GiST/GiST-based R-tree variants).- Costs: Moderate-to-large index size; inserts may cause page splits and rebalancing; maintenance (VACUUM/REINDEX) needed for heavy churn.

General maintenance/cost notes:- Every index increases write cost (INSERT/UPDATE/DELETE) proportionally to index complexity and number of entries per row.- Monitor index usage (pg_stat_user_indexes idx_scan) and drop unused indexes.- Regular VACUUM/ANALYZE and occasional REINDEX for bloat; consider BRIN for very large append-only tables with correlated physical order as a low-cost alternative.

HardSystem Design

50 practiced

You must store append-only audit logs for compliance for 10 years with write-heavy ingestion (10k writes/sec) and occasional range queries by time or user. Design a storage architecture that balances cost, queryability, and durability (considering object storage, cold vs hot tiers, and indexing strategies).

Sample Answer

Requirements & constraints:- Functional: append-only audit logs for 10 years; write-heavy ingestion ~10k writes/sec; occasional range queries by time or by user; immutable records.- Non-functional: cost-sensitive (long retention), high durability (compliance), read latency can be relaxed for cold data, index/queryability required.

High-level architecture:- Front-end ingestion service (API/gRPC) → write buffer/stream (Apache Kafka / Kinesis) → short-term hot store (write-optimized DB) + batched archival to object storage (S3/compatible) → index service (time/user indexes stored in cheap DB) → query API.

Components & responsibilities:1. Ingestion: - API behind autoscaling LB; validate and append metadata (id, timestamp, user, checksum). - Publish to Kafka for durability and smoothing bursts.2. Hot store (minutes → days): - Use a write-optimized DB (e.g., DynamoDB or Cassandra) partitioned by time-window + user hash to support recent range queries and replay. - Keep TTL to limit cost; acts as staging for reads that need low latency.3. Archive storage: - Batch from Kafka to immutable files (compressed, e.g., Parquet/NDJSON) and write to S3 in partitioned paths: /year=YYYY/month=MM/day=DD/hour=HH/. - Use server-side encryption, object locking/Governance mode for immutability. - Lifecycle policies: move to infrequent access then Glacier Deep Archive after configurable days.4. Indexing: - Maintain compact indexes (per-hour or per-day) in a fast, cheap key-value store (DynamoDB/ElastiCache+RDS for metadata) mapping time ranges and user → list of object keys + byte offsets. - Optionally store bloom filters per object file to quickly rule out files for a user.5. Query path: - Query API consults index to find candidate objects; for recent data read from hot store; for archived data perform ranged GETs from S3 (using byte-range if index has offsets) and stream results; allow async job mode for large scans.

Scalability & performance:- Kafka handles 10k/s burst with partitioning; consumers scale horizontally.- Hot DB shards by time-user to avoid hot partitions.- Archival batching (e.g., 1–5 MB files) balances PUT cost vs query granularity.

Durability & compliance:- S3 with versioning, MFA delete/object lock; cross-region replication for DR.- Store checksums in metadata; end-to-end CRC verification on ingest and archive.- Audit trail of writes in immutable ledger (e.g., append-only DB or signed manifests).

Cost trade-offs:- Hot store capacity tuned for short window (days) to limit cost.- S3 Glacier for 10-year retention reduces storage cost; indices kept small (only pointers) to keep query cost low.- More aggressive indexing (per-record) increases cost but reduces query latency — choose per-hour/day+bloom filters for typical occasional queries.

Operational considerations:- Backfill/replay from Kafka for recovery.- Monitoring: ingestion lag, S3 PUT errors, index health.- Security: encryption at rest/in transit, IAM, separation of roles for deletion/lock.

Alternatives & trade-offs:- Use solely a time-series DB: easier queries but higher long-term cost.- Store full index in search engine (Elasticsearch): faster queries but expensive for 10-year retention.This design balances low long-term cost (S3 Glacier), durable immutability, and acceptable queryability using compact indexes and hot-store for recent reads.

Unlock Full Question Bank

Get access to hundreds of Data Infrastructure Technology Selection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.