Data Processing and Transformation Questions

Focuses on algorithmic and engineering approaches to transform and clean data at scale. Includes deduplication strategies, parsing and normalizing unstructured or semi structured data, handling missing or inconsistent values, incremental and chunked processing for large datasets, batch versus streaming trade offs, state management, efficient memory and compute usage, idempotency and error handling, and techniques for scaling and parallelizing transformation pipelines. Interviewers may assess problem solving, choice of algorithms and data structures, and pragmatic design for reliability and performance.

HardTechnical

35 practiced

Design a checkpoint and recovery algorithm for a custom stateful operator that maintains tens of millions of keys. Discuss how you would snapshot state efficiently, perform incremental state transfers, and restore with minimal downtime and network overhead.

Sample Answer

Requirements & constraints:- Maintain tens of millions keys with low-latency processing and minimal downtime on failure.- Efficient snapshots (disk + network), support incremental transfers, fast restore.

Design overview:1. Consistent snapshot semantics- Use a lightweight barrier/epoch coordinator to mark a checkpoint epoch.- Operator flushes in-memory buffers and persists RocksDB memtable -> SSTs; ensure WAL is synced up to checkpoint epoch.- Capture a manifest: epoch id, applied WAL offsets, list of SST files with file-level checksums and key-range metadata.

2. Efficient snapshotting (local)- Rely on RocksDB snapshot + atomic flush: call Flush() then CreateCheckpoint() to hard-link SSTs into checkpoint dir (no data copy).- For copy-on-write FS (or object store), create manifests that reference existing objects to avoid duplicating data.- Include incremental snapshot support: only list SST files added/changed since last successful checkpoint using file fingerprint (checksum + file size + creation time).

3. Incremental state transfer- Transfer only new/changed SSTs and WAL tail segments referenced by manifest.- Use chunked transfer with deduplication: break SSTs into fixed-size chunks with rolling checksum (rsync-like) and only send missing chunks. Alternatively, use content-addressable storage (CAS) in object store; upload once, share via pointers.- Parallelize transfers per-file and per-chunk; apply backpressure and rate-limit to avoid network spikes.- Use compression (snappy/zstd) and optional delta-encoding for small WALs.

4. Restore with minimal downtime- Two-phase restore: a) Fast-path: Start operator in read-only or degraded mode using existing local state + apply WAL from checkpoint epoch to catch up (apply incrementally). b) Bulk restore: parallel download of SSTs into RocksDB checkpoint directory using CAS/hard-links; ingest SSTs via RocksDB IngestExternalFile to avoid full compaction.- Serve traffic as soon as key ranges needed are available; for missing ranges, forward to remote replicas or return temporary errors with client retries.- Apply WAL segments after SST ingestion to reach exact epoch.

5. Consistency & verification- Verify checksums for each SST/WAL chunk before ingest.- Use manifest-anchored epoch and causal ordering to guarantee deterministic recovery.

6. Scalability & trade-offs- Pros: No full-state copy, fast parallel transfers, minimal restore time via ingest & WAL-apply.- Cons: Requires CAS or object store support; complexity in chunk dedupe and manifest management.- Tunables: checkpoint frequency, chunk size, parallelism, throttling.

Operational notes:- Rotate and GC SSTs referenced by no active manifest.- Monitor checkpoint lag, network utilization, and recovery time objective (RTO) metrics.- Test failure scenarios: partial transfer, corrupted chunks, concurrent writes during checkpoint.

This design balances low network overhead (dedupe/CAS + incremental SSTs) and low downtime (ingest + WAL catch-up + parallelism) for tens of millions of keys.

EasyTechnical

32 practiced

List common strategies to handle missing or inconsistent values during transformation for analytics. For each strategy (imputation, deletion, sentinel values, forward/backward fill) explain when it is appropriate and pitfalls that could lead to biased analytics.

EasyTechnical

36 practiced

Write a SQL query (compatible with PostgreSQL) to deduplicate records in a table events(id bigint, user_id bigint, event_ts timestamp, payload jsonb) keeping for each user_id the latest event by event_ts and preferring rows where payload is not null. Explain assumptions and performance considerations.

Sample Answer

Approach: use a window function to rank rows per user_id preferring non-null payload and newest event_ts, then delete rows with rank > 1.

sql

WITH ranked AS (
  SELECT
    id,
    user_id,
    event_ts,
    payload,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY (payload IS NOT NULL) DESC, event_ts DESC, id DESC
    ) AS rn
  FROM events
)
DELETE FROM events
USING ranked
WHERE events.id = ranked.id
  AND ranked.rn > 1;

Key points / assumptions:- "Latest" means max(event_ts); if ties, use id to deterministically pick one.- Prefer rows where payload IS NOT NULL; boolean expression sorts those first.- id is primary key / unique.

Performance considerations:- Create an index to speed partitioning/ordering: CREATE INDEX ON events (user_id, event_ts DESC);- For very large tables, delete in batches to avoid long transactions and bloating: - Use LIMIT with a subquery of ids to delete repeatedly. - Or write duplicates to a temp table, TRUNCATE and re-insert if appropriate.- Run VACUUM and ANALYZE after mass deletes to reclaim space and update planner stats.- Test on a copy and ensure referential constraints/triggers behave as expected.

Edge cases:- All payloads null → picks latest by event_ts.- timezone-aware timestamps handled by PostgreSQL.- If keeping history instead of deleting, consider marking rows as current with an is_current flag.

HardTechnical

36 practiced

Describe a CI/CD strategy for versioning, testing, and deploying data transformation code and schemas so that schema changes do not break downstream analytics. Include unit tests, integration tests with sample data, schema registry checks, canary deployments, and migration rollback plans.

Sample Answer

Situation: We were responsible for shipping frequent data transformation updates for analytics consumers without breaking dashboards or ML models.

Task: Design a CI/CD strategy that ensures schema changes are safe, testable, and reversible.

Action:- Versioning: Enforce semantic versioning for transformation code and schema (major.minor.patch). Store schema artifacts in a Git-monorepo and publish schema versions to a central schema registry (with immutable IDs and compatibility rules).- Local/unit tests: Require unit tests for each transform (logic, edge cases) that run in CI with coverage gating. Use property tests for critical transforms to catch datatype/precision issues.- Integration tests with sample data: Maintain small, curated test datasets representing historical edge cases. CI runs pipeline in a containerized test harness (Spark/Flink job runner or Python pipeline) asserting output rows, column types, nullability, and key metrics.- Schema registry checks: CI validates proposed schema changes against registry compatibility settings (backward/forward/none). If strategy requires additive-only changes, CI blocks breaking diffs and surfaces a migration plan.- Contract tests: Generate consumer contracts from downstream models/dashboards; run consumer-side validation in CI to detect breaking changes before merge.- Canary deployments: Deploy transforms and schemas first to a shadow/canary environment that processes a small percentage of production traffic or a time-windowed slice. Monitor data quality metrics (row counts, schema drift, null rate, cardinality), alerts, and downstream dashboard smoke tests.- Rollback/migration plan: For breaking changes, require a migration PR that includes an explicit backfill plan, compensating transforms, and a rollback script. Use feature flags and schema evolution (write new version while continuing to serve old) to allow safe cutover. Canary failures automatically trigger automated rollback to previous code and schema version and run remediation job.- Observability and governance: Integrate data quality checks, lineage, and SLAs into CI/CD dashboards; require sign-off from data owners for major schema bumps.

Result: This approach minimizes surprises for analytics consumers by blocking incompatible changes early, catching logic bugs via unit/integration tests, validating compatibility via registry checks, and using canaries plus automated rollback to protect production consumers.

MediumTechnical

40 practiced

How would you implement an idempotent sink to a relational database for a distributed ingestion pipeline where producers may retry and network failures occur? Describe the data model, transactional patterns, and alternatives when the sink does not support transactions across partitions.

Sample Answer

Goal: ensure each logical record is persisted exactly once despite producer retries and transient failures.

Data model (relational):- Primary table (payload) with a natural or generated primary key id.- Dedup table or idempotency columns on payload: (producer_id VARCHAR, producer_seq VARCHAR, status ENUM, created_at, processed_at). Unique constraint on (producer_id, producer_seq) or a single global idempotency_key.- Optional tombstone/attempts table for audits.

Transactional pattern (when DB supports transactions):1. Producer includes an idempotency_key with each message.2. In a single transaction: - INSERT INTO payload USING idempotency_key ON CONFLICT DO NOTHING returning id. - If inserted, write any side-effects, mark status = 'done' in idempotency table. - Commit.3. If INSERT did nothing (conflict), SELECT existing row and return success to caller.This uses atomic uniqueness constraint to dedupe and keeps operations ACID within one partition.

When sink does not support transactions across partitions (e.g., sharded DB or multi-table without distributed TX):- Use an atomic upsert on a single partitioned table keyed by idempotency_key to act as the source-of-truth. - Step A: Upsert idempotency row with conditional state transition (e.g., status: 'processing' -> 'done') using lightweight compare-and-set (WHERE status IN ('new')). - Step B: Process payload and store result in shard, but include idempotency_key in payload so replays are detectable. - Compensate with eventual consistency: if payload write fails after idempotency was set, mark idempotency 'failed' and allow retries with backoff; include a reconciliation job that scans idempotency keys and ensures payload exists (repair).Alternatives and trade-offs:- Use external idempotency store (Redis with strong single-key writes) to coordinate before writing DB.- Sequence numbers per producer: store last_seen_seq per producer and accept messages with seq > last_seen, reject duplicates.- Exactly-once via two-phase commit or distributed transactions (complex, high latency).- At-least-once with idempotent writes (best pragmatic trade-off): unique constraints + retry-safe upserts + background reconciliation.

Edge cases:- Clock skew (avoid relying on timestamps for uniqueness).- Long-running processing: use lease TTL in idempotency row to avoid stuck 'processing' state.- High cardinality of producer_id: partition idempotency store or TTL cleanup.

This design balances correctness, performance, and operational complexity; prefer single-key atomic upserts plus reconciliation when full distributed transactions are unavailable.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.