Data Ingestion Strategies and Tools Questions

Covers patterns, approaches, and technologies for moving data from source systems into downstream storage and processing platforms. Candidates should understand pull based and push based ingestion models including periodic polling of application interfaces, event driven webhooks, log collection, file based batch uploads, database replication using change data capture, and streaming ingestion. Evaluate trade offs for latency, throughput, ordering, delivery semantics such as at least once and exactly once, backpressure and flow control, idempotency, fault tolerance, and cost. Be familiar with common ingestion technologies and platforms such as Apache Kafka, Amazon Kinesis, Google PubSub, and Apache NiFi as well as managed cloud ingestion and extract transform load services. Topics also include schema management and evolution, data formats such as JavaScript Object Notation and columnar file formats, data validation and cleansing at ingress, security and authentication for ingestion pipelines, monitoring and observability, and operational concerns for scaling and recovery.

EasyTechnical

69 practiced

Compare JSON, Avro, Protobuf, and Parquet for ingesting data into a data lake. Discuss schema enforcement, compactness, streaming vs batch suitability, support for schema evolution, and how each affects downstream consumers and query performance.

Sample Answer

High-level summary: JSON, Avro, Protobuf, and Parquet serve different needs in a data lake. Choose by trade-offs: human-readability, schema enforcement, compression/IO efficiency, streaming vs batch, and how easy downstream consumers and analytical engines can use the data.

Schema enforcement- JSON: schema-on-read; no enforced schema at write time -> flexible but risk of inconsistent records.- Avro/Protobuf: explicit schemas; schema is stored alongside or registered in a registry -> strong enforcement at producer level.- Parquet: columnar format with optional embedded schema; typically written by systems that enforce a schema (schema-on-write).

Compactness & IO- JSON: verbose, larger storage and network cost.- Avro/Protobuf: binary, compact; Protobuf slightly smaller/ faster but both efficient for row-based use.- Parquet: highly compact for analytical workloads due to columnar layout, column-level encoding, and better compression.

Streaming vs batch suitability- JSON: common for lightweight streaming (e.g., REST, Kafka messages) but inefficient at scale.- Avro/Protobuf: excellent for streaming—small, fast serialization/deserialization, compatible with Kafka, support for schema registry.- Parquet: designed for batch/analytical workloads (HDFS/S3), not ideal for high-frequency per-message streaming.

Schema evolution- JSON: flexible but no formal evolution; consumers must handle variations.- Avro: built-in backward/forward/compatible evolution semantics (default values, field aliases).- Protobuf: supports optional fields and numbering—good evolution but requires discipline with field numbers.- Parquet: supports evolution at file-level; more complex when mixing schemas across files—tools (e.g., Spark) merge schemas at read time.

Downstream consumers & query performance- JSON: easy for ad-hoc debugging and some ETL, but slow to query at scale; requires parsing overhead.- Avro/Protobuf: good for ETL pipelines and stream consumers; row-oriented so less efficient for large analytical scans.- Parquet: best for analytics — column pruning, predicate pushdown, vectorized reads -> much faster queries and lower IO for OLAP.

Recommendations- Use Avro/Protobuf for streaming ingestion into Kafka with schema registry to ensure producer/consumer compatibility.- Persist landing/raw zone as Avro/Parquet depending on access pattern: Avro/Protobuf for record-level processing, Parquet for analytical/warehouse layers.- Keep a clear schema registry and data contracts to protect downstream consumers; add automated validation and documentation.- For query-heavy datasets prioritize Parquet (or ORC) to minimize cost and maximize performance; use compaction jobs to manage small-file issues.

HardTechnical

87 practiced

A nightly Spark ingestion job processing 50TB/day suffers from GC pauses and shuffle skew. Describe concrete code and cluster configuration changes (memory tuning, partitioning, broadcast joins, avoiding wide joins, serialization), and show pseudocode for a repartitioning strategy to reduce skew and GC pressure.

Sample Answer

Approach: identify skewed keys, reduce shuffle size, and lower per-executor heap/GC pressure via serialization, off-heap shuffle, and tuned executor resources. Use map-side aggregation, salting for heavy keys, broadcast small tables, and avoid wide joins by pre-aggregating.

Cluster / Spark conf changes (concrete):- Use Kryo: spark.serializer=org.apache.spark.serializer.KryoSerializer; register classes for speed.- Shuffle / IO: spark.shuffle.manager=sort, spark.shuffle.service.enabled=true, spark.shuffle.compress=true, spark.shuffle.spill.compress=true- Off-heap and memory fractions: spark.memory.offHeap.enabled=true, spark.memory.offHeap.size=4g (if supported). Tune spark.memory.fraction=0.6, spark.memory.storageFraction=0.2.- GC tuning (YARN/containers): use G1GC for Java 11+: -XX:+UseG1GC -XX:MaxGCPauseMillis=200 -XX:+UnlockExperimentalVMOptions -XX:+UseStringDeduplication- Executor sizing: prefer more smaller executors to reduce GC pauses. Example: for nodes with 64GB RAM, set executor memory ~14-20GB, cores 3-4 (avoid >5 cores).- Reduce shuffle block sizes: spark.reducer.maxSizeInFlight=96m, spark.shuffle.file.buffer=32k- Enable adaptive execution: spark.sql.adaptive.enabled=true, spark.sql.adaptive.skewJoin.enabled=true

Code changes / patterns:- Broadcast small dimension tables:

python

from pyspark.sql import functions as F
small = spark.table("dim_small")
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", 50*1024*1024)  # 50MB
df = big.join(F.broadcast(small), "key")

- Pre-aggregate before join to avoid wide joins:

python

agg = big.groupBy("key").agg(F.sum("val").alias("sum_val"))
join = agg.join(dim, "key")

- Use map-side combiners (reduceByKey / aggregateByKey) in RDD APIs or groupByKey avoidance.

Skew mitigation & repartition pseudocode (salting + heavy-key handling):

python

# 1. sample to find heavy keys
sampled = big.sample(0.01).groupBy("join_key").count().orderBy(F.desc("count"))
heavy_keys = [r['join_key'] for r in sampled.take(100) if r['count'] > THRESHOLD]

# 2. split into heavy and light
heavy = big.filter(F.col("join_key").isin(heavy_keys))
light = big.filter(~F.col("join_key").isin(heavy_keys))

# 3. salt heavy keys: add random salt and expand dimension side similarly
NUM_SALTS = 50
heavy = heavy.withColumn("salt", (F.rand()*NUM_SALTS).cast("int"))
dim_heavy = dim.filter(F.col("key").isin(heavy_keys)).withColumn("salt", F.explode(F.array([F.lit(i) for i in range(NUM_SALTS)])))

# 4. repartition to distribute heavy load
light = light.repartition(200, "join_key")          # keyed shuffle for light data
heavy = heavy.repartition(200, "join_key","salt")   # salted distribution

# 5. perform joins and union results
joined_light = light.join(dim, light.join_key==dim.key, "left")
joined_heavy = heavy.join(dim_heavy, (heavy.join_key==dim_heavy.key) & (heavy.salt==dim_heavy.salt), "left")
result = joined_light.unionByName(joined_heavy)

Key reasoning:- Salting spreads heavy-key records across many partitions so no single reducer OOMs/Gc-stalls.- Pre-aggregation reduces data size before shuffles.- Kryo + small executors reduce object overhead and GC pause durations.- Adaptive execution + skew handling further reduces manual tuning.

Edge cases and notes:- Reassemble salted results if aggregation by key required (remove salt + re-aggregate).- Choose NUM_SALTS based on heavy key volume; too large increases overhead.- Monitor with Spark UI: task duration, shuffle read/write, GC time per executor.

EasyBehavioral

68 practiced

Tell me about a time you were on-call for a production data ingestion pipeline that failed. Using the STAR method, explain the situation, how you diagnosed the issue, the remediation steps you executed, and the long-term changes you implemented to prevent recurrence.

EasyTechnical

64 practiced

Define 'at-least-once', 'at-most-once', and 'exactly-once' delivery semantics in streaming ingestion. For each, provide a concrete example of producer/consumer behavior and typical techniques used to achieve that semantic in distributed systems.

Sample Answer

At-least-once, at-most-once, and exactly-once describe guarantees about how many times a message is delivered/processed in streaming ingestion.

At-least-once- Definition: Every message will be delivered one or more times; duplicates possible but no messages lost.- Producer/consumer behavior example: Producer retries on network failures; consumer may crash after processing but before committing offset, so message is processed again after restart.- Techniques: idempotent producers or idempotent consumers; retry logic; durable log (Kafka) + consumer offset commit after processing; deduplication downstream (unique keys, de-dup tables); transactional sinks less common.- Typical use: when loss is unacceptable and duplicate handling is feasible.

At-most-once- Definition: Messages are delivered zero or one time; no duplicates but some messages may be lost.- Example: Consumer commits offset upon receipt before processing; if crash occurs during processing, message is lost.- Techniques: commit-before-processing, fire-and-forget producers, no retries; low-latency choices where occasional loss is acceptable.- Typical use: low-risk telemetry where occasional loss is tolerable.

Exactly-once- Definition: Each message is processed exactly once end-to-end — no loss and no duplicates.- Example: Producer writes message once; consumer processes it once and writes results once even across retries and failures.- Techniques: end-to-end transactions or atomics: Kafka idempotent producers + Kafka transactions (producer txn + consumer read-committed), two-phase commit for sinks, atomic upserts in target stores, deduplication with persistent unique IDs, stream-processing frameworks with checkpointing (Flink’s two-phase checkpoint + stateful operators).- Trade-offs: higher complexity and latency; requires durable coordination and idempotency in sinks.

Practical note: most production pipelines aim for at-least-once with idempotent sinks or exactly-once where supported (Kafka transactions + transactional sink, Flink).

MediumTechnical

85 practiced

Given an events staging table (event_id, user_id, payload, received_at), write ANSI SQL that selects the latest row per event_id based on received_at to produce a deduplicated dataset suitable for ingestion into the analytics table. Use window functions and explain behavior if there are ties or nulls.

Sample Answer

Approach: use a window function to rank rows per event_id by received_at (most recent first) and keep the top row per partition. Use ORDER BY ... NULLS LAST to prefer non-null timestamps. If you need deterministic tie-breaking add a secondary unique column (e.g., a surrogate id or hash of payload).

SQL (ANSI):

sql

SELECT event_id, user_id, payload, received_at
FROM (
  SELECT
    event_id,
    user_id,
    payload,
    received_at,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY received_at DESC NULLS LAST, -- prefer newest non-null timestamps
               /* secondary deterministic tie-breaker: */
               COALESCE(surrogate_id, 0) DESC
    ) AS rn
  FROM staging_events
) t
WHERE rn = 1;

Notes and behavior:- ROW_NUMBER() returns exactly one row per event_id (deduplicated). If multiple rows have identical received_at values, the secondary ORDER BY (surrogate_id) ensures deterministic selection; without a secondary column ROW_NUMBER will still pick one row but which one is non-deterministic.- NULL received_at values: ORDER BY ... DESC NULLS LAST ensures rows with real timestamps are preferred. If all rows for an event_id have NULL received_at, the chosen row will be the one with highest surrogate_id (or arbitrary one if none).- If you want to keep all tied-most-recent rows (rather than one), use RANK() or DENSE_RANK() and filter WHERE rank = 1; that returns multiple rows when ties exist.- For pipeline reliability: ensure staging has a stable tie-breaker (monotonic surrogate id or ingestion sequence) to avoid nondeterminism across runs.

Unlock Full Question Bank

Get access to hundreds of Data Ingestion Strategies and Tools interview questions and detailed answers.

Join thousands of developers preparing for their dream job.