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.

HardSystem Design

57 practiced

Design a multi-region streaming ingestion and stateful processing pipeline that guarantees per-user ordering globally and supports low-latency reads for personalized features. Discuss replication strategies, leader election, trade-offs between strong and eventual consistency, and how you would route reads/writes across regions.

Sample Answer

Requirements:- Per-user global ordering for writes (events must be processed in the same sequence worldwide).- Low-latency reads per region for personalized features (model features/serving).- High availability across regions and tolerance to region failure.- Scalable ingest (millions of users), stream processing with user-level state.

High-level architecture:- Global edge ingestion → regional Kafka (or vanilla cloud Pub/Sub) clusters → distributed stateful stream processors (Flink/KSQL/Beam) per region → regional low-latency feature stores (Redis/CockroachDB/Scylla) with async replication → cross-region consensus/metadata service.

Per-user ordering and write routing:- Partition by user-id using a stable hash; ensure all writes for a user go to the same “home” partition. To keep global ordering, choose one of: 1) Global partition ownership (single leader region per user): designate a home region per user (hash→region) and route client writes to that region. That region is authoritative; it assigns sequence numbers. 2) Multi-master with conflict resolution: accept writes anywhere but attach Lamport/vector clocks and run deterministic merge—complex, only if occasional reorder allowed.

I recommend (1) home-leader per user for strict per-user ordering: a global routing layer (DNS+edge gateway) forwards writes to the user’s home region. On home-region failure, leader election promotes a new home.

Replication & leader election:- Use a consensus system (Raft/ZooKeeper/Etcd/Consul) to track partition-to-region mapping and to elect a region leader for a user partition. Each user partition is replicated to N regions for durability; the leader applies writes and replicates logs synchronously to followers in quorum (for strong commit) or semi-sync (to leader fast, followers async) depending on latency needs.- For stream logs, use Kafka mirroring with MirrorMaker 2 or tiered storage: leader writes to local Kafka; MirrorMaker replicates topic partitions to other regions. Leader election should be coordinated by the metadata service using Raft so promoted leader knows the last committed offset.

Consistency trade-offs:- Strong consistency (synchronous replication to quorum): guarantees global order and correctness but increases write latency (cross-region RTT). Use for critical user state (payments, inventory, counters).- Eventual consistency (async replication): low write latency and fast reads locally but risk stale reads and temporary reordering across regions; acceptable for non-critical features (recommendations, counters where eventual convergence OK).- Hybrid: keep authoritative user state (ordering-critical) strongly-consistent in home region; replicate deltas asynchronously to other regions for low-latency reads, and use read-repair or stale-while-revalidate patterns.

Stateful processing & feature store:- Run stateful processors in the home region to maintain user state and compute features deterministically from ordered events. Persist snapshots and changelogs to replicated log storage.- For low-latency reads, maintain a regional feature cache/store populated either via: - Synchronous read-through replication for small hot set (higher write latency). - Asynchronous eventually-consistent replication with TTL and background reconciliation for large/less-critical features.- Expose a versioned read API: clients can request the “latest strong” (route to home region) or “low-latency” (local cache) depending on SLAs. For model training, always consume the replicated committed log to ensure consistent examples.

Read/write routing:- Writes: route to home region (via global routing service). For mobile/edge clients, use SDKs to look up home-region quickly (cache).- Reads: default to local region feature store for low latency. If a strong-consistency read required, proxy to home region or read from quorum.- Provide flow-control/backpressure at ingest to prevent follower lag.

Failure modes & mitigation:- Home-region failure: metadata service elects new leader; MirrorMaker/replicated logs ensure followers have up-to-date state. During failover, either buffer writes or allow writes to new leader with risk of ordering gaps if followers not caught up—prefer preventing split-brain via quorum checks.- Network partitions: prefer availability model depending on feature criticality; mark stale reads with version/timestamp so models can handle staleness.

Operational considerations:- Monitor replication lag, follower offsets, and read staleness metrics; alert on lag thresholds.- Automate leader rebalancing (based on load) and partition reassignment.- For Data Science: ensure deterministic, versioned feature materialization (feature timestamps, lineage), and provide both strongly-consistent and low-latency feature endpoints for training vs serving.

Trade-offs summary:- Strong consistency: correct ordering and no stale reads, higher write latency and complexity.- Eventual consistency: better latency and availability, complexity in conflict handling and model correctness.- Hybrid (home-leader + async regional caches) gives best practical balance for per-user ordering and low-latency reads.

HardSystem Design

59 practiced

Design a secure data access model for analytics and model development that enforces row-level and column-level security, supports ephemeral credentials for interactive notebooks, integrates with centralized IAM and a data catalog, and provides auditing for compliance. Discuss performance trade-offs and developer ergonomics.

Sample Answer

Requirements:- Row-level and column-level security (RLS/CLS) enforced consistently across interactive notebooks, batch jobs, dashboards, and model training.- Ephemeral credentials for notebooks (short-lived, least-privilege).- Integration with centralized IAM (roles/groups) and data catalog (metadata, sensitivity tags).- Auditing for access, queries, data exfiltration signals.- Good developer ergonomics and acceptable performance at scale.

High-level architecture:1. Central IAM + AuthN/AuthZ: OAuth2/OIDC IdP (e.g., Okta/Azure AD) for user identity, groups, and ABAC attributes (department, project, purpose).2. Policy Engine: Centralized PDP/PAP (e.g., Open Policy Agent) that evaluates RLS/CLS policies expressed declaratively and linked to catalog tags.3. Data Access Proxy: A lightweight gateway that brokers requests to data stores (data lake, warehouse). It enforces RLS/CLS by rewriting queries (predicate injection) or using virtual views.4. Ephemeral Credential Service: Short-lived service tokens issued via IdP-backed exchange (STS) for notebook kernels; scopes limited by developer role, active project, and approved datasets.5. Data Catalog & Governance: Catalog stores dataset schemas, column sensitivity labels, recommended masks, and owner approvals. Policies reference catalog metadata.6. Auditing & Monitoring: Immutable audit logs (who, when, dataset, query, rows returned), query plan sampling, DLP hooks for sensitive outputs, SIEM integration.

Data flow (example notebook):- User authenticates via IdP -> requests ephemeral token for dataset X -> Policy Engine checks user attributes and catalog tags -> Proxy issues scoped ephemeral creds -> Notebook connects to proxy endpoint -> Proxy enforces RLS by injecting WHERE user_allowed_filter(user_id) and masks sensitive columns or returns tokenized values -> Query executed against warehouse -> Results returned; full query and metadata logged.

Enforcement techniques:- Predicate injection for RLS (low friction, works with SQL engines).- Column masking via query-time transformation or views; for stronger guarantees, use encryption/tokenization for high-sensitivity columns.- Push-down vs gateway enforcement: prefer push-down (rewrite) to leverage engine optimizations; use materialized views for heavy, repeated filters.

Performance trade-offs:- Runtime rewriting adds latency and prevents some engine optimizations; mitigate with prepared plan caching, predicate-aware materialized views, and partition pruning.- Heavy masking/encryption increases CPU; reserve tokenization/encryption for truly sensitive PII.- Fine-grained auditing and DLP scans increase storage and processing; sample high-risk queries to reduce cost.

Developer ergonomics:- Provide client libraries and notebook extensions that transparently request ephemeral creds and mount policy-aware dataframes (e.g., pandas.DataFrame proxy with metadata).- Self-service catalog workflow to request access; auto-approve via attributes for low-risk datasets to avoid blocking experimentation.- Clear error messages when policies block access and a one-click request workflow to reduce friction.

Compliance & auditing:- Immutable logs with cryptographic integrity, retention policies per regulation.- Automated reports: access recency, anomalous exports, high-volume queries, model training data lineage linking to model registry.- Periodic attestation workflows for dataset owners.

Trade-offs summary:- Balance security vs productivity by tiering datasets (open, internal, restricted, protected) with stronger controls for higher tiers.- Use runtime enforcement for flexibility; implement stronger static controls (encryption, dedicated secure enclaves) for highest-sensitivity data.This design enforces consistent RLS/CLS, integrates IAM and catalog, issues ephemeral creds for notebooks, and provides auditability while offering mitigations to keep developer latency and friction acceptable.

MediumTechnical

55 practiced

Design a feature pipeline and storage layout to support both (a) daily retraining of models and (b) online inference with 100ms or better latency. Discuss how you would implement offline and online stores, feature freshness guarantees, consistent joins at inference time, feature versioning, and backfill strategies.

Sample Answer

Requirements & constraints:- Daily retrain (batch) pipeline; online inference <=100ms; consistent features for training and serving; ability to backfill and version features.

High-level architecture:- Offline store: columnar OLAP (e.g., BigQuery / Redshift / Delta Lake) holding time-partitioned feature tables and raw events for full-history queries and backfills.- Online store: low-latency KV store (e.g., Redis, DynamoDB) keyed by entity_id with precomputed feature vectors and metadata (timestamp, version).- Feature registry: central metadata service recording feature definitions, transformations, feature IDs, versions, and lineage.

Offline pipeline (daily):- Batch ETL computes features for the full set of entities for that day; writes to offline store partitioned by event_date; emits an atomic manifest with feature_version and max_event_time.- Snapshot export: materialize per-entity feature vector for training and store in offline training table with feature_version tags.

Online pipeline:- Incremental stream processors (Kafka + Flink/Beam) compute near-real-time updates; upserts to online store with last_updated timestamp and feature_version.- Serving API reads from online store; if a feature is missing or stale, fall back to a cached lookup from the offline snapshot or compute on read (with tight timeout) — but prefer write-path freshness.

Feature freshness guarantees:- Each online record stores last_updated. Define SLOs: e.g., features must be updated within X minutes of event. Use monitoring to emit alerts when staleness exceeds threshold.- Training uses offline manifest to ensure models are trained only on data up to a consistent cut-off time matching production serving staleness assumptions.

Consistent joins at inference:- Use feature vector atomicity: online store stores a single serialized vector per entity updated atomically so reads get a consistent set.- For multi-table joins in offline training, use time-travel or partition snapshots at a fixed event_time to recreate the exact view used for training.- When combining online and on-demand computed features, include a request-scoped cut-off timestamp and reject/harmonize features newer than that timestamp.

Feature versioning:- Immutable feature definitions: any change creates new feature_version. Registry maps model -> required feature_versions.- Online store keeps feature_version per field so serving can validate compatibility. During rollout, write both old and new versions side-by-side (dual-write) until validation passes, then switch models.

Backfill strategies:- Cold backfill: run batch job to recompute historical features across partitions; write to offline store and optionally to online store for frequently-scored entities.- Warm backfill for online store: prioritized set of hot entity_ids updated first via parallel jobs; use rate-limited writers to avoid overload.- Maintain backfill logs and manifests to ensure idempotency and ability to resume.

Operational concerns:- Monitor latency, hit-rate, staleness, and version mismatches.- Snapshots and canary rollout: test new feature versions against a holdout before full production.- Security and access controls in feature registry and stores.

This design balances low-latency serving, reproducible training, consistent joins, and safe evolution via versioning and controlled backfills.

MediumTechnical

52 practiced

Explain how Change Data Capture (CDC) combined with event sourcing can be used to reconstruct historical feature values at any point-in-time for model backtesting. Discuss storage formats, performance implications, and the trade-offs between storing raw events vs periodically materialized snapshots.

Sample Answer

Answer:

At a high level, CDC captures all source-table changes as an append-only stream of events (inserts/updates/deletes) and event sourcing records those events as the single source of truth. For model backtesting you replay the CDC stream up to a chosen timestamp to reconstruct feature values "as of" that point-in-time.

Storage formats and layout:- Raw event log: append-only Kafka topics or cloud object store (Parquet/ORC) with columns: entity_id, event_ts, change_type, before, after, source_lsn. Partition by entity sharding and event_ts for efficient reads.- Materialized snapshots: periodic state dumps (Delta Lake/Parquet or a key-value store like RocksDB/Cassandra) keyed by entity and snapshot_ts.- Index/manifest: store latest LSN per file to ensure consistency.

Performance implications:- Pure replay of raw events is the most storage-efficient and auditable but can be slow for long histories (O(events) per entity to rebuild).- Snapshots drastically reduce reconstruction time: find latest snapshot before t and replay only subsequent events (O(events_since_snapshot)). Snapshot cadence trades storage for latency.- Query patterns matter: vectorized columnar formats (Parquet/ORC/Delta) speed bulk backtests; low-latency single-entity time travel benefits from key-value stores.

Trade-offs — raw events vs periodic snapshots:- Raw events pros: complete audit trail, minimal write amplification, easy lineage and debugging. Cons: expensive to replay for long histories and higher CPU at query time.- Snapshots pros: fast "as-of" reads, cheaper repeated backtests, lower compute during replay. Cons: increased storage, complexity of snapshot consistency (must snapshot at transactional LSN), potential staleness window.- Hybrid (recommended): retain raw CDC for auditability + regular incremental snapshots (e.g., daily/hourly or triggered by event volume) with manifests. For heavy backtesting, maintain feature tables in Delta/BigQuery with time-travel or change-versioning to allow efficient point-in-time queries.

Practical tips:- Ensure event ordering and exactly-once semantics (dedupe via LSN/offset).- Store both before/after values for tricky deltas.- Choose snapshot cadence based on event churn and backtest frequency; automate snapshot creation when event counts since last snapshot exceed threshold.- Use columnar formats and partition pruning for bulk backtests; use materialized feature stores (Feast, Hopsworks) for production-serving plus replayable provenance.

This approach gives reproducible, auditable historical features while balancing storage, compute, and latency for backtesting needs.

EasyTechnical

40 practiced

Compare Parquet, ORC, and Avro as storage formats for large-scale analytics and ML feature tables. As a data scientist choosing a storage format for feature engineering and model training, discuss pros and cons in terms of columnar vs row layout, compression, predicate pushdown/column pruning, schema evolution, and typical read/write performance characteristics.

Sample Answer

Columnar vs row layout:- Parquet & ORC: columnar — excellent for analytics and feature selection because you read only needed columns (faster scans, lower I/O). Ideal for wide feature tables and aggregations.- Avro: row-oriented — good for write-heavy, record-at-a-time workloads (streaming, logging) but less efficient when training reads only a subset of features.

Compression:- Parquet & ORC: block/column compression (snappy, ZSTD, gzip) with better compression ratios because similar typed values are contiguous. ORC often achieves slightly better compression due to lightweight encodings.- Avro: compresses whole records; compression benefits when records are similar but generally less compact for analytical columns.

Predicate pushdown / column pruning:- Parquet & ORC: strong support — statistics (min/max, null counts) per column stripe enable predicate pushdown and skip large data ranges.- Avro: limited — must read full records so predicate pushdown is weak or requires external indexing.

Schema evolution:- Avro: designed for schema evolution (forward/backward compatible) and common in streaming pipelines.- Parquet & ORC: support schema evolution but with more caveats (nullable additions are easier; removing/renaming fields requires care or rewrite).

Read/write performance:- Parquet: widespread, good read performance for analytics; writes can be slower due to column encoding and flushing (optimized in bulk jobs).- ORC: often faster reads and better compression in Hadoop ecosystems; strong for complex predicate filtering.- Avro: fast writes and low-latency single-record reads/writes, but poor for columnar scan-heavy training.

Practical recommendation for a data scientist:- Use Parquet (or ORC if you’re in Hive/Hadoop-heavy stack) for feature tables and ML training: fast columnar reads, good compression, and predicate pushdown reduce training data load. Use Avro for ingestion/streaming or when strict schema evolution is required; convert to Parquet for downstream analytics. Also consider partitioning, column selection, and compaction to optimize training pipelines.

Unlock Full Question Bank

Get access to hundreds of Data Architecture and Pipelines interview questions and detailed answers.

Join thousands of developers preparing for their dream job.