Data Architecture and Pipelines Questions
Designing data storage, integration, and processing architectures. Topics include relational and NoSQL database design, indexing and query optimization, replication and sharding strategies, data warehousing and dimensional modeling, ETL and ELT patterns, batch and streaming ingestion, processing frameworks, feature stores, archival and retention strategies, and trade offs for scale and latency in large data systems.
MediumTechnical
83 practiced
Design a dimensional model for retail sales analytics: identify facts and dimensions, choose grain for the sales fact, define primary/foreign keys, and propose how to implement Slowly Changing Dimensions (SCD), specifically Type 2. Sketch a high-level schema for sales_fact, product_dim, customer_dim, and describe how promotions should be modeled.
Sample Answer
Requirements & grain:- Business asks for retail sales analytics at the transaction line level (report by day, store, product, promotion, customer). Choose fact grain = one sales transaction line: one row = one sold SKU on one receipt (transaction_id) at a point-in-time.Facts (sales_fact) and keys:- sales_fact (surrogate keys and FKs) - sales_sk (PK, surrogate) - transaction_id (natural id / receipt id) - transaction_line_number - transaction_datetime (redundant timestamp) - store_sk -> store_dim.store_sk - product_sk -> product_dim.product_sk (points to the version valid at sale time) - customer_sk -> customer_dim.customer_sk (SCD2 version) - promotion_sk -> promotion_dim.promotion_sk (nullable) - qty_sold, unit_price, discount_amt, extended_amt, cost_amt, tax_amt - created_date, load_dateDimensions & keys:- product_dim (SCD Type 2) - product_sk (surrogate PK) - product_id (business key) - name, category, brand, size, color, current_flag, effective_from, effective_to, version - other attributes (hierarchies) and load metadata - Primary key = product_sk; business FK product_id used for updates- customer_dim (SCD Type 2) - customer_sk (surrogate PK) - customer_id (business key) - first_name, last_name, email, loyalty_tier, address fields, current_flag, effective_from, effective_to, version- store_dim (slowly changing; often Type 1 or Type 2 depending on requirements) - store_sk, store_id, name, region, latitude, longitude, open_date, close_date, current_flag- promotion_dim (treat promotions as separate dimension; SCD Type 2 or time-bound snapshot) - promotion_sk, promotion_id, promo_name, promo_type, start_date, end_date, discount_type, discount_amount, details, current_flagSCD Type 2 implementation:- Use surrogate keys for dims. On change of tracked attribute: - Insert new row with new surrogate key, set effective_from = change_ts, effective_to = 9999-12-31, current_flag = true - Set previous row current_flag = false and effective_to = change_ts - 1 second- ETL pattern: detect changes using hash of tracked attributes; if changed -> expire old row and insert new. Maintain version number and change_reason for audit.How sales_fact references SCD2:- Join sales timestamp to the dimension row valid at that timestamp; populate fact.product_sk and fact.customer_sk during ETL by looking up the dimension row where business_key = product_id AND effective_from <= transaction_datetime < effective_to.Promotions modeling:- Model promotions as their own dimension and also a bridge table if many-to-many per transaction line: - promotion_dim (as above) - sales_promotion_bridge: sales_sk -> promotion_sk (if a sale line may have multiple promos)- Capture promotion attribution fields on fact (promotion_sk primary promo, promo_discount_amt) and link to bridge for secondary promos.- For time-varying promo definitions use SCD2 or keep immutable promotion rows per version.Notes & trade-offs:- Surrogate keys simplify joins and SCD behavior; keep natural IDs for reconciliation.- If history retention is large, partition facts by date and dimensions by effective_from.- Consider conformed dimensions (product, customer) across business lines for consistency.- For real-time needs, maintain a “current” view of each dimension (Type 1 view) and separate history tables for querying past state.
MediumTechnical
57 practiced
Describe how to implement CDC-based incremental ingestion into a data warehouse while ensuring correct ordering and idempotency. Include use of transaction IDs or LSNs, watermarking, deduplication strategies, replay handling for connectors (Debezium/Kafka Connect), and approaches for schema changes and backfills.
Sample Answer
Situation: For CDC-based incremental ingestion into a data warehouse you must guarantee two things: (1) correct ordering of changes and (2) idempotent results so replays/duplicates don’t corrupt state. Below is a pragmatic architecture and implementation pattern a Solutions Architect can propose.High-level approach- Source CDC (Debezium) → Kafka (topic per table, compacted where helpful) → Stream processor or CDC consumer (Spark/Flink/Beam or custom) → Warehouse (upsert/merge).Key building blocks and patterns1) Use of transaction IDs / LSNs- Preserve source transaction metadata (LSN/txId/timestamp) emitted by Debezium (source.lsn, source.txId, source.ts_ms).- Treat LSN/txId as monotonic ordering keys. When writing to the warehouse include last_seen_lsn column per row.- For multi-row transactional ordering (e.g., Postgres multi-statement transaction), process events grouped by txId and apply them in tx order to maintain atomicity where required.2) Watermarking and progress tracking- Maintain per-source watermark = highest committed LSN processed. Store it centrally (offset store / metadata table).- Use watermarks to: (a) decide safe-to-apply ordering windows, (b) avoid applying late-arriving events that violate ordering without remediation, (c) support incremental recovery and backfills.3) Idempotency / Deduplication strategies- Upsert semantics using primary key + last_lsn: - SQL pattern: MERGE INTO table t USING staged s ON t.pk = s.pk WHEN MATCHED AND t.last_lsn < s.lsn THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... - This ensures replays or older events do not overwrite newer state.- Dedup buffer for in-flight batches: in streaming job, dedupe by (pk, lsn) before writing.- Use Kafka compacted topics keyed by pk for an eventual compact view; consumer still relies on LSN for ordering.4) Replay handling for connectors (Debezium / Kafka Connect)- Debezium emits snapshot and change events. Configure snapshot.mode appropriately (initial, schema_only, never) and use snapshot.locking if you need consistent snapshot.- Use Kafka Connect offset storage + consumer group commits to allow replay; when replaying, consumer should reapply with idempotent MERGE logic (last_lsn check).- If reprocessing from some older offset, advance watermark only after successful commit to warehouse; if failure, rollback watermark to previous safe LSN.- For exactly-once semantics across Kafka → Warehouse, use transactional sinks where available (Kafka transactions + idempotent writes) or make sink idempotent as above.5) Schema evolution and changes- Use a schema registry (Avro/Protobuf/JSON Schema) to track schema evolutions produced by Debezium.- In warehouse: use flexible typed columns (VARIANT/JSON) or evolve table schema with ALTER TABLE as new fields appear. Prefer adding nullable columns and backfilling later.- For breaking schema changes (rename/drop), coordinate via mapping layer in consumer: map old field names to new, populate deprecated columns, and use transform SMTs in Debezium for simple renames.- Validate new schemas against compatibility rules in the registry; alert and require manual approval for incompatible changes.6) Backfills and replays- Two patterns: - Full snapshot backfill via Debezium snapshot (snapshot.mode=initial or export tool) into the same topics; consumer will dedupe with LSN checks. - Separate backfill pipeline: export historical data to batch file, write to a backfill topic with proper LSNs/time, run idempotent merges. Use higher-priority windowing to apply backfill safely without overwriting newer data (i.e., ensure backfill LSNs are earlier).- For late-arriving events: either hold them for a watermark-based reprocessing window or apply compensating transactions (emit reversal events) depending on SLAs.Operational considerations and trade-offs- Latency vs strict ordering: strict per-transaction ordering requires grouping by txId and may increase latency. For many analytics use-cases per-row LSN ordering is sufficient.- Storage of last_lsn per row increases table width but simplifies idempotency.- If you need stronger transactional guarantees end-to-end, use stream processing frameworks that support exactly-once semantics (Flink/Beam with two-phase commit) or leverage Kafka transactions.- Monitor: topic lags, watermark progress, schema registry changes, LSN holes; implement alerting and automated replays for gaps.Example SQL upsert (pseudo):MERGE INTO warehouse.table tUSING staging sON t.pk = s.pkWHEN MATCHED AND t.last_lsn < s.lsn THEN UPDATE SET col = s.col, last_lsn = s.lsnWHEN NOT MATCHED THEN INSERT (pk, col, last_lsn) VALUES (s.pk, s.col, s.lsn);Summary- Keep source LSN/txId, track watermarks, perform idempotent merges using last_lsn, dedupe in-stream, handle connector snapshots/replays by relying on idempotent sink logic, and use schema registry + controlled schema evolution and separate backfill pipelines. This combination provides correctness, safe replays, and operational clarity for CDC-based ingestion into a data warehouse.
EasyTechnical
90 practiced
Explain common index types (B-tree, hash, inverted, columnar) and when to prefer composite indexes versus single-column indexes. For an analytics workload that commonly filters by date range and customer_id, recommend an indexing/partitioning approach and explain how selectivity influences whether an index is beneficial.
Sample Answer
B-tree: ordered, balanced tree index for range scans and equality. Works well for ORDER BY and WHERE with ranges (e.g., date BETWEEN). Hash: constant-time equality lookups (good for =), but cannot do range scans or ordering. Inverted index: maps tokens/terms to document IDs — used for full‑text search and many-to-many attribute lookups. Columnar indexes/storage: stores columns separately, excellent for analytics OLAP workloads (vectorized scans, compression); combined with per-column indexes (bitmaps) it speeds up aggregation and large scans.Composite vs single-column:- Single-column index: simple, small, useful when queries filter by one column.- Composite index: multi-key ordered by the index column order. Use when queries commonly filter on the same combination. Important: leftmost-prefix rule — index (A,B) helps queries on A or A+B, but not on B alone for range/equality patterns.Recommendation for analytics workload filtering by date range and customer_id:- Partition the table by date (daily/monthly) so queries prune partitions quickly for date ranges and improve maintenance (archival).- Create a composite B-tree index with equality first then range: (customer_id, date). This is optimal when queries are WHERE customer_id = X AND date BETWEEN A AND B.- Alternatively, if most queries are large scans/aggregations across many customers for a date range, use columnar storage (e.g., Parquet in a data lake or a columnar DB) with partitioning by date and a secondary index on customer_id or a bitmap index.Selectivity:- High selectivity (few rows matched per key) makes an index very beneficial — index lookup + row fetch is cheaper.- Low selectivity (e.g., gender with ~2 values) makes indexes less useful; full table/partition scan may be faster.- Cost depends on cardinality, fraction of rows matched, and I/O pattern. Aim indexes at high-cardinality/equality predicates and use partitioning for high-cardinality range predicates like date.
HardSystem Design
59 practiced
For a real-time fraud detection system, design low-latency feature pipelines that guarantee feature freshness (e.g., <500ms) for online scoring. Cover ingestion, stateful enrichment, feature TTLs, materialization to low-latency stores, handling missing features, idempotency, and rollback strategies for model misbehavior in production.
Sample Answer
Requirements & constraints:- Hard real-time: feature freshness <500ms, high throughput, per-entity stateful aggregates, strong availability, eventual consistent with bounded staleness, observability and safe rollback.High-level architecture:- Ingestion: producers push events to partitioned Kafka topics (user_tx, account_events) with schema registry (Avro/Protobuf) and keys = entity_id.- Stream processing/stateful enrichment: Apache Flink (or Kafka Streams) with event-time processing, watermarks, keyed state for per-entity aggregates (counts, rolling windows). Use RocksDB backend + periodic snapshotting for fast recovery and exactly-once semantics via Kafka transactions/checkpoints.- Feature TTLs & eviction: each keyed state stores last-updated timestamp and TTL metadata; Flink timers evict or mark stale features when TTL passes so freshness guarantees are enforced.- Materialization: write computed features to a low-latency serving store (Redis Cluster or DynamoDB DAX) via idempotent upserts keyed by entity_id; include version and timestamp in value.- Online scoring: scorer service reads feature vector from store; if missing/stale, scorer can either (a) read directly from processing layer via RPC for urgent single-entity recompute, or (b) use fallback/defaults and score with lower-confidence path.Idempotency:- Use event deduplication at ingestion (Kafka unique IDs + compacted topic of processed offsets) and idempotent writes to the feature store (writes include version/timestamp; apply only if incoming version > stored).- Stream processors commit to Kafka transactions to ensure exactly-once sinks.Handling missing/stale features:- Mark features with freshness flag and confidence score.- Model handles missing inputs via learned defaults or surrogate model; scoring service can tag transaction for offline review/hold if critical features missing.- Telemetry: log rate of missing/stale and enforce SLOs.Rollback & model misbehavior:- Deploy model via staged rollout: shadow mode -> canary -> full. Shadow scoring runs in parallel and compares decisions.- Feature-level kill-switch: feature flags to disable suspect features instantly (via central config store). The scorer ignores flagged features and switches to fallback model.- ML store versioning: keep previous model versions and feature schema; enable instant rollback by toggling model endpoint or routing traffic.- Circuit breaker & throttles: if fraud rate anomaly or latency spike detected, demote model to safe-mode (baseline rules-only).- Replay & backfill: when fixing processor bugs, use event log (Kafka compacted topics) and Flink savepoints to replay and rebuild state; maintain immutable feature change logs for audit.Operational considerations:- Observability: per-feature freshness metrics, latency percentiles, error budgets, alerting on TTL violations.- Security & compliance: encryption-in-transit, KMS for secrets, RBAC for feature toggles.- Trade-offs: Redis for <1ms reads; DynamoDB for durability/scale; Flink gives low-latency rich state but higher ops cost.This design guarantees <500ms freshness by keeping compute in a distributed stream layer, using keyed state and timers, idempotent sinks, TTL enforcement, and rapid rollback controls (feature flags, canary/shadow, circuit breakers) to safely operate models in production.
EasyTechnical
58 practiced
Compare relational databases to NoSQL stores (document, key-value, wide-column, graph) across schema flexibility, consistency, query expressiveness, indexing, and scaling. For a product catalog with nested attributes and heavy reads, explain when a document store is preferable to a relational DB and what hybrid options you might propose.
Sample Answer
High-level comparison (relational vs NoSQL types) across the asked dimensions:- Schema flexibility - Relational: Rigid, normalized schemas; migrations required for structural changes. - Document (e.g., MongoDB): Schema-on-read; nested/optional fields allowed per document. - Key-value: Extremely flexible but opaque values; structure managed by application. - Wide-column (Cassandra): Flexible columns per row; good for sparse data. - Graph: Flexible node/edge properties; schema evolves with graph model.- Consistency - Relational: Strong ACID by default. - Document: Typically tunable; many provide primary-replica strong reads/writes or eventual consistency options. - Key-value / Wide-column: Often AP-principled; tunable consistency (quorum reads/writes). - Graph: Varies; many graph DBs favor transactional guarantees for small transactions.- Query expressiveness - Relational: Rich JOINs, aggregates, window functions, SQL. - Document: Powerful document queries and aggregation pipelines; joins limited or heavier. - Key-value: Simple lookup; minimal query capabilities. - Wide-column: Good for range scans and time-series patterns; limited ad-hoc querying. - Graph: Best for relationship traversals, shortest paths, pattern matching.- Indexing - Relational: Rich multi-column, functional, full-text indexes. - Document: Secondary indexes on fields (including nested), text and geospatial in many systems. - Key-value: Usually primary-key access; some systems add secondary indexes. - Wide-column: Secondary indexes limited; primary design via partition keys. - Graph: Index nodes/edges by properties; traversal performance relies on the graph model.- Scaling - Relational: Vertical scaling common; sharding possible but operationally complex. - Document / Key-value / Wide-column: Designed for horizontal scaling and sharding. - Graph: Scaling across large graphs is harder; specialized partitioning required.Product catalog with nested attributes + heavy reads — when choose a document store vs relational DB:- Prefer document store when: - Catalog items have many optional, nested attributes (variants, specs, localized fields) that vary per product — documents store nested JSON naturally without costly schema migrations. - Read patterns are heavy and mostly item-by-item or category listing, allowing efficient single-document reads and serving pre-joined data (no expensive JOINs). - You require fast horizontal scaling and simple replication for read-heavy traffic.- Prefer relational DB when: - Strong transactional integrity across many normalized entities is required (inventory, orders, consistent multi-table updates) or complex cross-entity joins and reports are frequent.Hybrid options to propose (practical solutions):- Polyglot persistence: Use document store (e.g., MongoDB) for product catalog (read-optimized JSON documents) and relational DB (Postgres) for transactional systems (orders, inventory). Keep a clear bounded context and sync via events.- CQRS + Materialized views: Primary relational/normalized model for writes; build denormalized document views for reads via change-data-capture (CDC) or event streams (Kafka). Read replicas serve heavy read traffic.- Cache layer + CDN: Serve catalog documents from an in-memory cache (Redis) or edge CDN to reduce DB load.- Search/indexing: Complement document store with a search engine (Elasticsearch) for faceted search and complex queries.Recommendation pattern: model product as a document for read paths, enforce critical invariants in relational stores or services, and use asynchronous sync (CDC/events) to keep systems consistent while optimizing read scalability and developer agility.
Unlock Full Question Bank
Get access to hundreds of Data Architecture and Pipelines interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.