Data Pipelines and Feature Platforms Questions

Designing and operating data pipelines and feature platforms involves engineering reliable, scalable systems that convert raw data into production ready features and deliver those features to both training and inference environments. Candidates should be able to discuss batch and streaming ingestion architectures, distributed processing approaches using systems such as Apache Spark and streaming engines, and orchestration patterns using workflow engines. Core topics include schema management and evolution, data validation and data quality monitoring, handling event time semantics and operational challenges such as late arriving data and data skew, stateful stream processing, windowing and watermarking, and strategies for idempotent and fault tolerant processing. The role of feature stores and feature platforms includes feature definition management, feature versioning, point in time correctness, consistency between training and serving, online low latency feature retrieval, offline materialization and backfilling, and trade offs between real time and offline computation. Feature engineering strategies, detection and mitigation of distribution shift, dataset versioning, metadata and discoverability, governance and compliance, and lineage and reproducibility are important areas. For senior and staff level candidates, design considerations expand to multi tenant platform architecture, platform application programming interfaces and onboarding, access control, resource management and cost optimization, scaling and partitioning strategies, caching and hot key mitigation, monitoring and observability including service level objectives, testing and continuous integration and continuous delivery for data pipelines, and operational practices for supporting hundreds of models across teams.

EasyTechnical

22 practiced

Given a Postgres table events(event_id text, user_id int, event_time timestamp, payload jsonb) that can have duplicate event_id rows due to upstream retries, write a SQL query that returns a deduplicated result keeping only the latest event_time per event_id. Also describe how you'd delete duplicates in place efficiently without long table locks.

Sample Answer

To return a deduplicated result keeping only the row with the latest event_time per event_id, you can use either DISTINCT ON (Postgres-specific) or a window function. Example using window functions (portable & explicit):

sql

-- Deduplicated view: keep latest event_time per event_id
SELECT event_id, user_id, event_time, payload
FROM (
  SELECT
    event_id,
    user_id,
    event_time,
    payload,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY event_time DESC, ctid
    ) AS rn
  FROM events
) t
WHERE rn = 1;

Key points:- ROW_NUMBER() partitions by event_id and orders by event_time descending so rn=1 is the latest event.- ctid tie-breaks when event_time is equal.

Efficient in-place deletion of duplicates without long locks1. Delete in small batches using ctid (avoids full-table locks and keeps transactions short):

sql

-- Repeat this until it returns 0 rows deleted
WITH to_delete AS (
  SELECT ctid FROM (
    SELECT ctid, ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY event_time DESC, ctid
    ) rn
    FROM events
  ) s
  WHERE rn > 1
  LIMIT 10000            -- batch size tuned to your load
)
DELETE FROM events
WHERE ctid IN (SELECT ctid FROM to_delete);

Run this loop (in an application script or psql) until no rows are deleted.

2. After duplicates removed, create a unique index concurrently to prevent future duplicates:

sql

CREATE UNIQUE INDEX CONCURRENTLY idx_events_event_id ON events (event_id);

(Concurrent build minimizes locking; it will fail if duplicates still exist—hence the batch delete first.)

Best practices / reasoning- Using ctid lets you target physical rows exactly and keep deletes small/fast.- Batch deletes reduce lock contention, WAL pressure, and long-running VACUUMs.- Creating the unique index CONCURRENTLY enforces idempotency for future inserts without blocking writes.- Tune batch size based on peak load and monitoring (increase if too slow, decrease if causing replication lag).

HardSystem Design

25 practiced

Propose a dataset and feature lineage/versioning system that guarantees reproducibility of training runs after upstream source changes. Include storage choices (object store vs specialized stores), immutable manifests, content-addressed identifiers (hashes), and how model registries should link to dataset versions. Explain performance and cost trade-offs.

Sample Answer

Requirements & constraints:- Guarantee bit-for-bit reproducibility of any past training run despite upstream data/schema changes.- Support large datasets (TB+), frequent incremental updates, feature stores, and low-latency reads for training/serving.- Keep metadata immutable and traceable; enable audits and rollback.- Reasonable cost for storage and query performance.

High-level design:1. Immutable dataset snapshots + content-addressed artifacts.2. Manifest-based lineage graph that ties raw sources → transformations → features → dataset versions.3. Object store (e.g., S3/GCS) for heavy payloads; specialized metadata store (e.g., PostgreSQL/Spanner or a graph DB) for manifests/indexes; optional fast feature store (Redis/Feast) for serving.

Key components:- Source ingestor: reads upstream sources, chunks data, computes content hashes (e.g., SHA-256) per chunk/file/Parquet row-group.- Object store: stores chunked data blobs keyed by content-addressed IDs (CAIDs = hash). Blobs are immutable.- Transformation engine: runs ETL/feature pipelines; outputs artifact blobs and an immutable manifest describing inputs (list of CAIDs + schema), transformation code version (commit SHA), runtime environment (container image digest), and parameters.Example manifest (JSON):{ "dataset_version": "v1.3", "created_by": "pipeline#42", "created_at": "...", "inputs": [{"path":"s3://raw/part-0001.parquet","caid":"sha256:abc..."}], "transform_code": "git+https://repo@commit:deadbeef", "params": {"window":30}, "output_blobs": [{"path":"s3://artifacts/ds-v1.3/part-0001.parquet","caid":"sha256:fff..."}]}- Metadata/Lineage store: stores manifests, indexes by dataset name, CAIDs, transformation id, and graph edges for quick provenance queries. Immutable manifests are append-only.- Feature registry/feature store: materialized features linked to dataset manifests (store reference to dataset_version and specific output CAIDs). Serving keys map to CAIDs for lookups.- Model registry: models stored with metadata that links to: - exact dataset manifest id(s) - feature manifest ids / CAIDs - training code commit & container digest - random seed, hyperparams, and environment snapshotThis linkage ensures you can re-run training by fetching the exact blobs and environment.

Reproducibility workflow:- To reproduce a training run, the system fetches the model registry entry → reads dataset manifest → resolves CAIDs → downloads exact blobs from object store → rehydrates transformation environment using the recorded container digest → runs the same training entrypoint with recorded params and seeds.

Performance & cost trade-offs:- Object store (cheap, durable) pros: low cost for TBs, native immutability (object keys), easy content-addressed storage. Cons: higher latency for many small reads; repeated rehydration of large blobs can be slow and egress-costly.- Specialized stores (Delta Lake, Iceberg, Zarr, Pachyderm): pros: built-in versioning, partition indexing, time-travel queries, faster selective reads. Cons: higher operational complexity and cost; may still rely on object stores for actual payloads.- Chunking strategy: store larger compressed Parquet row-groups to reduce metadata overhead and faster sequential reads; but coarse chunks force re-reading more data for small changes. Finer chunking increases metadata size and small-object costs.- Caching: use SSD-backed cache (e.g., EBS/EFS or local cache) for hot blobs to reduce read latency and costs for repeated reproductions.- Delta vs full snapshots: store deltas (change sets referencing CAIDs) to save storage cost; reconstructing a past snapshot requires applying deltas (CPU cost). Full immutable snapshots are fastest to reproduce but cost more storage.- Indexing: maintain CAID→manifest and dataset_version→list(CAIDs) indexes in metadata DB for fast provenance queries; cost negligible vs scanning manifests.

Operational notes and best practices:- Always record transformation code and environment as immutable digests (git commit + container image digest).- Ensure manifests are signed and append-only; consider embedding CAID tree (Merkle tree) for efficient integrity checks.- Garbage collection: safe delete only when no manifest or model references a CAID (reference counting) to avoid breaking historical reproducibility.- Access patterns: optimize for reproducible reads (bulk download API that streams CAID-ordered blobs), and for training allow local staging cluster storage to avoid repeated egress.

This design balances durability and cost (object store for payloads) with strong metadata guarantees (immutable manifests, content-addressed identifiers) and explicit model→dataset links so any training run can be deterministically re-executed.

MediumSystem Design

30 practiced

You need to generate an offline training dataset with point-in-time correct features for ML using a cloud data warehouse (e.g., BigQuery or Snowflake). Describe an efficient implementation considering cost and performance: joins strategy, partition pruning, clustering, and incremental materialization for future runs.

Sample Answer

Requirements & constraints:- Produce point-in-time correct feature table for ML training (no label leakage), large historical data, repeatable incremental runs, cost-conscious (warehouse compute + storage).- Support joining many entity-event tables, fast queries, and ability to re-run for new dates.

High-level architecture:- Orchestrator (Airflow) triggers SQL jobs in BigQuery/Snowflake.- Persistent feature store tables: raw event tables (source), pre-aggregates, materialized feature tables (per-entity per-cutoff-date).- Use time-partitioned & clustered tables, plus incremental materialization.

Implementation details:

1) Data layout & partitioning- Store raw events partitioned by event_timestamp (DAY). In BigQuery use ingestion_time/DATE partitioning; in Snowflake use DATE column clustering/partitioning (micro-partitions naturally).- Cluster on join keys (entity_id) and high-cardinality filter fields (event_type) to enable pruning.

2) Join strategy for point-in-time correctness- For each feature, compute using a windowed aggregation up to cutoff time: WHERE event_timestamp <= cutoff_time. Implement as a lateral/ARRAY_AGG or window functions with RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW partitioned by entity and ordered by event_timestamp.- Perform joins using broadcast small dimension tables; for large event tables, use equality joins on entity_id plus predicate event_timestamp <= cutoff_time to enforce point-in-time.- Prefer "lookup-then-aggregate" pattern: pre-aggregate heavy-time-series per entity per day, then join to entity-level cutoffs — reduces data scanned.

3) Partition pruning & clustering- Always include partition filter on event_timestamp (e.g., event_date BETWEEN start_date AND cutoff_date) to prune partitions.- In BigQuery, write queries using _PARTITIONDATE or partitioned column; in Snowflake push predicates to micro-partitions by filtering on date columns.- Cluster feature/materialized tables on entity_id and cutoff_date to accelerate queries like WHERE cutoff_date = X AND entity_id IN (...)

4) Incremental materialization- Maintain a feature table partitioned by cutoff_date. For initial full backfill compute all partitions; for incremental runs compute only new cutoff_date partitions or dates with updated source data.- Use watermark logic: track max_processed_event_time per source and only re-compute partitions whose source changed (CDC or ingestion timestamp).- For idempotency: write to a staging table for the partition, validate row counts, then atomic INSERT/REPLACE INTO target partition (BigQuery: MERGE/WRITE_TRUNCATE partition; Snowflake: COPY INTO or MERGE).

5) Cost & performance optimizations- Pre-aggregate raw events into daily/weekly feature primitives to avoid scanning raw granular logs.- Use selective materialization: only materialize commonly used features; compute rare features on-the-fly.- Use clustered tables and predicate pushdown to minimize scanned bytes.- Schedule heavy backfills during off-peak prices; leverage cached results and result reuse.

Trade-offs & monitoring:- Pre-aggregation lowers query cost but adds storage and complexity for freshness.- Maintain lineage and tests: unit tests for point-in-time joins, data quality checks, and monitoring for partition sizes and query bytes scanned.

This design ensures point-in-time correctness by enforcing timestamp predicates at aggregation time, minimizes compute via partitioning/clustering and pre-aggregation, and supports efficient incremental re-materialization for future runs.

EasyTechnical

31 practiced

List and explain eight basic data validation checks and quality metrics you would implement at the start of a feature pipeline to catch regressions early. For each check briefly mention why it matters and a possible alert threshold example.

MediumTechnical

24 practiced

You ingest events from an at-least-once delivery source that sometimes replays events. Describe design and implementation patterns to make downstream feature materialization idempotent and resilient to duplicates and out-of-order deliveries, including deduplication, upserts with version keys, and transactional sinks.

Sample Answer

Situation: You ingest events from an at-least-once source that sometimes replays and can be out-of-order. As a Data Scientist building feature materialization, you must ensure features are correct, stable, and repeatable despite duplicates or late events.

Design patterns and implementation:

- Canonical event identity & metadata - Require each event to include a stable key (entity_id), event_id (UUID or monotonic sequence), and event_time. Include producer sequence/version when possible.

- Deduplication layer - Use a streaming engine (Kafka Streams/Flink/Spark Structured Streaming) to do stateful dedupe: keep a compacted store of recent event_ids per entity (or a bloom filter + TTL) and drop exact duplicates before feature logic. - For large cardinalities use Kafka compacted topics keyed by event_id or entity+version.

- Upserts with version keys - Materialize features as upserts keyed by entity_id with a version (event_time or sequence). On each event, compare incoming version to stored version; only apply if incoming > stored. This makes operations idempotent and handles out-of-order arrivals. - Store both feature value and last_applied_version in the feature store (e.g., Delta Lake, Hudi, BigQuery/Firestore).

- Handling out-of-order & late data - Process using event-time semantics and watermarks; allow a configurable lateness window. For very late events, either route to backfill jobs or apply if version comparison permits.

- Transactional sinks / exactly-once delivery - Use sinks that support idempotent upserts or transactions: Kafka transactions + transactional sink connector, or ACID file layers (Delta/Hudi/Iceberg) with atomic commits. This avoids partial writes on retries. - Alternatively implement an append-then-compact pattern: write raw events to a durable topic/table, then run idempotent batch jobs to rebuild feature state atomically.

- Observability & recovery - Persist processing offsets/checkpoints and last-applied versions. Emit metrics for duplicates, late events, and rejections; provide a repair/backfill pipeline to reconcile gaps.

Example flow:1. Raw events → compacted Kafka topic keyed by event_id for dedupe.2. Stream job reads by event_time, maintains state last_version[entity_id].3. If event.version > last_version → compute feature, write upsert to Delta table using transaction/atomic commit.4. Else drop or log for audit.

Why this works:- Dedup drops exact replays cheaply.- Versioned upserts guarantee idempotency and resolve out-of-order based on explicit ordering.- Transactional sinks ensure materialization is atomic and resilient to retries.

Edge cases:- Sequence gaps when producers crash (use sequence + actor_id, detect and repair).- Unbounded state for dedupe (use TTL, probabilistic structures).- Business rule changes require reprocessing/backfill.

This combination keeps feature materialization correct, repeatable, and maintainable in at-least-once environments.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.