Data Transformation and Preparation Questions

Focuses on the technical skills and judgement required to connect to data sources, clean and shape data, and prepare datasets for analysis and visualization. Includes identifying necessary transformations such as calculations, aggregations, filtering, joins, and type conversions; deciding whether to perform transformations in the business intelligence tool or in the data warehouse or database layer; designing efficient data models and extract transform load workflows; ensuring data quality, lineage, and freshness; applying performance optimization techniques such as incremental refresh and pushdown processing; and familiarity with tools and features such as Power BI Power Query, Tableau data preparation capabilities, and structured query language for database level transformations. Also covers documentation, reproducibility, and testing of data preparation pipelines.

MediumSystem Design

96 practiced

Sketch an architecture for a feature store that supports offline batch features for 100M users and low-latency online lookup for inference. Describe choices for offline storage, online storage, ingestion paths, consistency model (eventual vs strong), and how to support backfills and point-in-time correctness.

Sample Answer

Requirements & constraints (clarify): support batch offline features for training at scale (100M users), low-latency (sub-10ms) online lookup for inference, support backfills and point-in-time-correct joins, high availability.

High-level architecture:- Ingestion: events → Kafka (or Kinesis). Two paths: 1. Streaming path for near-real-time feature updates: stream processors (Flink/Beam) compute incremental aggregates and write to online store. 2. Batch path for full recompute/backfills: daily/weekly Spark/Dataproc jobs reading raw events from data lake.- Offline store: immutable, append-only data lake (S3/GS) storing Parquet/Delta/ICEBERG tables partitioned by event_date and feature key. Use a data warehouse (BigQuery/Snowflake) or Spark over lake for training materialization.- Online store: low-latency key-value DB: DynamoDB/Redis (clustered) or RocksDB-backed serving layer (e.g., Feast + Redis/Dynamo). Use partitioning and autoscaling; store serialized feature vectors and metadata (version, event_time).- Serving API: feature server (gRPC/HTTP) that reads from online store and returns feature vectors with metadata and TTL checks.- Metadata & registry: feature catalog with schemas, transformation code refs, feature versioning, owner, and freshness SLAs.

Consistency model:- Offline (training): strong point-in-time correctness via snapshot semantics — compute training feature tables by joining event-time-snapshotted features from the data lake. Use transaction-like job runs producing immutable, versioned tables.- Online (serving): eventual consistency acceptable for many features (near-real-time). For features needing strict consistency, route updates through synchronous writes or use a strongly consistent store (DynamoDB with consistent reads) with higher latency cost. Document per-feature consistency SLOs in catalog.

Point-in-time correctness & backfills:- Always store event_time / feature_timestamp with each feature write.- For offline materialization, perform time-travel joins: join user events with feature snapshots as-of event_time (using windowed aggregation or windowed state in Spark/SQL over Parquet/Delta/ICEBERG) to prevent leakage.- Maintain historical feature tables (versioned snapshots) so training uses the snapshot corresponding to model training time.- Backfills: run batch recompute job that reads raw events and re-materializes feature tables into a new version. Update online store in a controlled rollout (can use blue/green or phased migration) or keep both versions and switch model to new feature version.- For streaming incremental updates, include watermarking and out-of-order handling logic in stream processors; if late events arrive that change historical aggregates, trigger backfill for affected ranges.

Scaling & operational considerations:- Key design: composite key (user_id + feature_group) and sharded partitions for 100M keys; use consistent hashing.- Compression / compact serialization (Protobuf/Avro) and TTL policies for ephemeral features.- Monitoring: freshness, error rates, feature drift, read latency, cache hit rates.- Testing: shadow reads, canary feature pushes, unit test of transforms, and offline validation that online values match recomputed batch within acceptable error.

Trade-offs:- Delta/ICEBERG on object storage gives cheap storage + time-travel capabilities for point-in-time joins, but online reads require a separate low-latency store.- Strong consistency online adds latency/cost; iterate per-feature based on SLOs.

This design provides scalable offline compute for 100M users, low-latency online lookups, explicit handling of point-in-time correctness, and operational paths for backfills and consistency management.

HardTechnical

82 practiced

Describe how to implement windowed aggregations for feature generation in a streaming pipeline that must tolerate events arriving out-of-order by up to 2 hours. Explain watermarking, allowed lateness, state eviction, and strategies for re-computing features used downstream for training labels.

Sample Answer

Clarify requirements: events may be out-of-order up to 2 hours; we must compute event-time windowed aggregations for features, keep correctness, and support recomputing features that affect downstream training labels.

Approach summary:- Use event-time windows with watermarking to bound late-arrival state.- Configure allowed lateness (watermark) = 2 hours; accept late events within that bound and update aggregates.- Use state eviction (state TTL) tied to watermark + safety margin to free state after no more updates expected.- Persist feature outputs as upsertable records (feature store / Kafka compacted topic / materialized table) and emit correction events when late updates change values.- For downstream training labels, support incremental recompute using change-log + lineage, or schedule backfills for affected windows.

Concrete Spark Structured Streaming pattern (PySpark pseudo-code):

python

from pyspark.sql.functions import window, col
stream = (spark.readStream.format("kafka")... 
          .selectExpr("CAST(value AS STRING)", "timestamp"))

# parse and get event_time column
agg = (stream
       .withWatermark("event_time", "2 hours")             # watermark = 2h
       .groupBy(window("event_time", "15 minutes"), "key")
       .agg({"value":"sum"})                               # example agg
)

# write as upsert to a feature store (e.g., Kafka compacted or DB)
agg.writeStream \
   .outputMode("update") \
   .format("kafka")... \
   .option("checkpointLocation", "/cp/agg") \
   .start()

Key concepts & reasoning:- Watermarking: tells engine events older than (max-seen-event-time - 2h) are late and can be dropped from event-time aggregation. It allows bounded state and correctness for most late arrivals.- Allowed lateness: withWatermark defines allowed lateness. Within this bound, windows remain mutable and engine will update outputs; beyond it, late events are ignored by window operator unless you handle them separately.- State eviction: Spark cleans window state when watermark passes window_end + allowed_lateness. Also set state TTL for per-key state (spark.sql.streaming.statefulOperator.stateIdleTimeout) to evict idle keys.- Emitting corrections: because downstream consumers (feature store, model training) rely on feature values, stream outputs should include metadata: window_id, event_time_max, watermarked_flag, and a tombstone/upsert pattern so stores can apply corrections. Use Kafka compacted topics or transactional databases to ensure idempotent upserts.- Recomputing features for labels: - Real-time correction: when a feature update arrives, emit a change event; downstream systems that compute training labels subscribe and update affected label rows. - Batch incremental recompute: maintain lineage (which event keys/windows contribute to each label). When late updates affect feature values beyond a threshold, trigger targeted reprocessing for affected time ranges (recompute windows and labels). - Full backfill: for large inconsistency windows or model retraining, run a bounded reprocessing job over historical data up to watermark window to ensure consistency.

Operational considerations & trade-offs:- Latency vs correctness: larger watermark reduces corrections but increases latency for final "stable" features. 2h bound forces accepting 2h staleness for finality.- State size: partition by key, use aggregation windowing granularity to control cardinality; evict per-key state after inactivity.- Storage: use append+upsert storage with compacting (Kafka compacted or Delta Lake MERGE) to retain latest feature value and changes for auditing.- Monitoring: track late-event counts, watermark progress, state-store size, and emitted correction rates; alert if late events spike.- Testing: simulate out-of-order arrival, force late events beyond 2h to validate behavior, and verify idempotent upserts.

This design ensures bounded correctness with controllable latency, safe state eviction, and mechanisms (change-log, lineage, targeted backfills) to recompute or correct downstream labels when late events alter computed features.

HardSystem Design

94 practiced

Outline an idempotent ETL design for ingesting events from Kafka into a feature table used for training and serving. Requirements: deduplicate retries, handle out-of-order events, provide at-least-once or exactly-once semantics where possible, and support efficient compaction. Explain the streaming framework choice and give pseudocode for deduplication logic.

Sample Answer

Requirements clarification:- Ingest Kafka event stream into a feature table for training/serving- Deduplicate retries, tolerate out-of-order events, provide at-least-once or exactly-once when possible- Support efficient compaction (merge/upsert) for feature store

High-level design:- Framework: Spark Structured Streaming with Delta Lake (or Flink + Iceberg). I choose Spark Structured Streaming because it provides transactional sinks (Delta), event-time watermarking, exactly-once semantics end-to-end with Kafka source offsets and checkpointing, and integrates well with ML pipelines (batch retrain + serving).- Pipeline stages: 1. Read from Kafka with event.time and event.id (unique event key) and metadata (partition, offset). 2. Parse and assign event_time; use watermarking to bound lateness (e.g., 30m). 3. Stateful deduplication keyed by entity_id or event_key using event_id + event_time; keep minimal state (last_processed_event_id or event_time window) and eviction policy. 4. Compute/upsert features per entity. 5. Write to Delta table using atomic MERGE (compaction) with foreachBatch or Delta's transactional writes to get exactly-once.

Idempotency & semantics:- Deduplication ensures idempotent processing of retries.- Exactly-once: achievable if using Spark structured streaming with Kafka source (read offsets) + Delta transactional sink + checkpointing. This yields end-to-end exactly-once for deterministic transforms.- If sink can't be transactional, use at-least-once + idempotent upserts keyed by entity + event_id.

Handling out-of-order:- Use event-time processing with watermarking and late-arrival policy. For very late events beyond watermark, route to a "late events" store and optionally reprocess by replaying raw events + compacting historical features.

Compaction:- Use Delta Lake MERGE to compact updates: write incremental updates then compact/optimize periodically (OPTIMIZE + ZORDER) to keep serving latency low.

Pseudocode (Spark Structured Streaming, Python-like):

python

from pyspark.sql import functions as F
from delta.tables import DeltaTable

# Read Kafka
df = (spark.readStream
      .format("kafka")
      .option("subscribe", "events")
      .option("startingOffsets","earliest")
      .load()
     )

events = (df.selectExpr("CAST(value AS STRING) as json")
            .select(F.from_json("json", schema).alias("e"))
            .select("e.event_id","e.entity_id","e.event_time","e.payload")
            .withColumn("event_time", F.to_timestamp("event_time"))
           )

# Deduplicate using event_id with watermark and stateful dropDuplicates
deduped = (events
           .withWatermark("event_time", "30 minutes")
           .dropDuplicates(["entity_id","event_id"])  # stateful, keeps minimal state until watermark
          )

# Compute features (example aggregation)
features = (deduped
            .groupBy("entity_id")
            .agg(F.last("payload.value").alias("latest_value"),
                 F.max("event_time").alias("last_seen"))
           )

# Write using foreachBatch to perform MERGE into Delta table (idempotent upsert)
def upsert_to_delta(microbatch_df, batch_id):
    delta_table = DeltaTable.forPath(spark, "/mnt/delta/features")
    (delta_table.alias("t")
       .merge(microbatch_df.alias("s"), "t.entity_id = s.entity_id")
       .whenMatchedUpdateAll()
       .whenNotMatchedInsertAll()
       .execute())

(features.writeStream
         .foreachBatch(upsert_to_delta)
         .option("checkpointLocation","/mnt/checkpoints/features")
         .start())

Key considerations:- Keep dedupe state small: dedupe by (entity_id,event_id) and rely on watermark TTL to evict.- For high cardinality, consider bloom filters or external exact-store (Redis) with TTL for dedupe tokens.- Monitor lateness, watermark drops, checkpointing health.- Periodically compact Delta table and validate merges to avoid write amplification.

This design yields idempotent ingest, handles out-of-order events via event-time + watermark, and achieves exactly-once when using Spark+Delta with checkpointing; otherwise it provides at-least-once with idempotent upserts.

EasyTechnical

94 practiced

Given an events table: events(event_id BIGINT, user_id INT, event_type VARCHAR, occurred_at TIMESTAMP), write an ANSI SQL query that computes daily active users (unique user_id per day) for the last 30 days. Also explain how you'd handle late-arriving events that should be attributed to prior days.

Sample Answer

Approach: convert timestamps to date, count distinct users per day for the last 30 days. Use safe DATE cast so it's ANSI-compatible; in Postgres DATE(occurred_at) or CAST(occurred_at AS DATE) both work. Filter by occurred_at to limit scan.

sql

SELECT
  day,
  COUNT(DISTINCT user_id) AS daily_active_users
FROM (
  SELECT DISTINCT CAST(occurred_at AS DATE) AS day, user_id
  FROM events
  WHERE occurred_at >= CAST(CURRENT_DATE - INTERVAL '29' DAY AS TIMESTAMP)
    AND occurred_at < CAST(CURRENT_DATE + INTERVAL '1' DAY AS TIMESTAMP)
) t
GROUP BY day
ORDER BY day;

Key points:- SELECT DISTINCT (day, user_id) ensures each user counted once per day.- Filter uses last 30 calendar days (including today). Adjust intervals for business needs.

Handling late-arriving events:- Implement a watermark policy: accept late events up to N days (e.g., 2–7 days). Recompute aggregates for days within the watermark window when late data arrives (incremental backfill).- Maintain an events ingestion timestamp (ingested_at). Use it to detect late arrivals: if CAST(occurred_at AS DATE) < CURRENT_DATE - watermark_days and ingested_at > some threshold, flag for backfill but don’t retroactively update production metrics until a scheduled backfill.- Automate incremental backfills (ETL job or streaming retractions) to update daily aggregates and emit monotonic deltas to downstream consumers.- Track data completeness and emit staleness indicators so models or dashboards know whether counts are final.

MediumTechnical

93 practiced

Describe how you would implement CI/CD for data transformation pipelines used in model training. Include unit tests for transformation functions, integration tests using synthetic datasets, promotion from staging to production, and how you'd version artifacts, SQL scripts, and schemas.

Sample Answer

Requirements & constraints:- Reproducible, testable transformations used in model training- Safe promotion from staging -> production with traceability- Versioned code, SQL, schemas, and artifacts- Orchestrated (Airflow) and cloud-friendly

High-level CI/CD flow:1. Dev -> feature branch: run linters, unit tests for transformation functions, static checks for SQL (sqlfluff), and type checks.2. Merge to main: CI builds Docker image, runs integration tests against synthetic datasets, validates schemas and data quality, and builds artifacts (parquet/csv, preprocessed features, model training data).3. Staging deploy: CD deploys DAGs to Airflow staging, registers artifacts in artifact store, runs end-to-end smoke tests.4. Promote: After approval, tag release and promote artifacts/configs to production environment; Airflow picks up vetted DAGs.

Testing strategy:- Unit tests: Pytest for pure transformation functions with parametrized cases; mock external I/O. Example: test normalize_age handles nulls, out-of-range values.- Integration tests: Use small synthetic datasets (pytest fixtures or factory) to run full transformation DAG locally or in a containerized Airflow worker. Validate outputs against golden outputs.- Data quality & contracts: Use Great Expectations to assert schema, column types, ranges, cardinality; run as part of CI.- Regression tests: Compare feature distributions to baseline (KS test) to detect drift from code changes.

Versioning and artifacts:- Code & SQL: Git for source control; store SQL migrations and transformation SQL in repository. Enforce PR reviews and use semantic version tags.- Schemas: Maintain canonical Avro/Parquet/JSON schema files in repo; use a schema registry (e.g., Confluent Schema Registry or cloud equivalent) and CI checks to ensure compatibility (backwards/forwards).- Artifacts (features/models): Use artifact store (S3/GCS) with immutable paths like s3://bucket/project/feature_store/{version}/...; record metadata in MLflow or a metadata DB (run_id, git_sha, schema_version, tests_passed).- Models: Use MLflow or model registry; link model to data artifact versions and training DAG run id.

Promotion and governance:- Promotion via tagging and approval pipeline (GitHub Actions/GitLab CI manual approval). On promote, copy artifacts to prod path, update schema registry, and deploy Airflow DAGs pointing to production datasets.- Rollback: Keep previous artifact versions and DAGs; ability to switch Airflow variables to previous artifact version quickly.- Observability: Monitor CI test coverage, data quality metrics, and pipeline runs; alert on failed expectations or schema mismatches.

Why this works:- Unit tests catch logic bugs quickly; integration tests ensure end-to-end correctness with realistic synthetic data without using sensitive production data.- Versioned artifacts + registry provide traceability and reproducibility for model training and audits.- Manual approval for promotion balances safety with agility; automated checks reduce human error.

Unlock Full Question Bank

Get access to hundreds of Data Transformation and Preparation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.