Data Transformation and Loading Questions

Focuses on the extract transform load and extract load transform approaches for ingesting transforming and loading data. Candidates should understand three core stages: extract which is acquiring data from sources such as application programming interfaces databases logs and message queues; transform which is cleaning validating reshaping aggregating and enriching data to meet downstream requirements; and load which is writing processed data to targets such as analytic databases data warehouses data lakes or reporting systems. Topics include the differences between extract transform load and extract load transform, incremental loads versus full refresh, scheduling and orchestration best practices, tooling and frameworks used for transformation and orchestration, idempotency and deduplication strategies, error handling and retry semantics, data quality checks end to end validation recovery and integration with business intelligence and analytics consumers. Interview focus is on concrete transformation logic pipeline orchestration and validation strategies and on choosing the right pattern and tooling for given constraints.

HardSystem Design

48 practiced

Design an approach to implement data lineage and impact analysis for ETL pipelines that span Airflow DAGs, Spark jobs, and SQL transformations across on-prem and cloud. Include how you'd capture metadata, store it, query upstream/downstream impact, and build a tool for engineers to answer questions like 'which datasets will be affected if I change column X in table Y?'.

Sample Answer

Requirements & constraints:- Capture end-to-end lineage for Airflow DAGs, Spark jobs, and SQL (on-prem + cloud).- Support column-level lineage and impact queries like “If I change column X in table Y, what is affected?”- Low run-time overhead, searchable, versioned, and queryable by engineers.

High-level architecture:Airflow/Spark/SQL instrumenters → Lineage collector (OpenLineage-compatible) → Metadata store (graph DB + column-store) → Query & impact API → UI/CLI + alerts

Components and responsibilities:1. Instrumentation- Airflow: add OpenLineage hook/operator that emits run/job events (inputs, outputs, start/end, job_id, params). Use existing OpenLineage contrib for Airflow.- Spark: attach listener (SparkListener or via OpenLineage Spark integration) to capture DataFrame read/write sources, schema and column projection, query plan, and job identifiers.- SQL: for managed warehouses (Snowflake/BigQuery/Redshift) use query logs + parse SQL; for ad-hoc SQL, wrap execution in a lightweight proxy or use JDBC interceptor to capture SQL text and parameters.- Persist schema snapshots (table/column types) and sample hashes per run.

2. Collector- Stream events into Kafka/Kinesis; normalize to a lineage event model (dataset, fields, operation, run_id, upstream_ids, tool, timestamp).- Enrich events: resolve dataset canonical names, capture env (prod/dev), compute schema diffs, extract column-level mappings (via SQL AST, Spark logical plan, or OpenLineage facet).

3. Metadata storage- Graph database (neo4j/JanusGraph) for relationship queries: dataset nodes, column nodes, job nodes, run nodes, edges like PRODUCES/CONSUMES/TRANSFORMS. Enables fast upstream/downstream traversal.- Complement with a columnar store (Elasticsearch or analytically: Redshift/BigQuery) for faceted search, schema history, and heavier aggregations (e.g., most impacted jobs).- Store raw events & SQL in object store (S3) for audit and reprocessing.

4. Query & impact analysis- API that accepts (dataset.table, column) and traverses graph up/down to N hops with filters (env, timeframe). Use cached materialized views for common heavy queries (e.g., full downstream graph).- For column-level impact, follow edges from column node to transformation nodes then to output columns. If column mapping not explicit (opaque UDFs), mark as unknown and trigger static analysis/annotation request.- Support “what-if”: overlay schema change (drop/rename/nullable change) and simulate impact (jobs that read column, dashboards depending on derived columns).

5. Developer tooling & UX- Web UI: interactive DAG/graph explorer, ability to click column -> show upstream SQL snippets, affected downstream datasets, owners, SLA, last run status.- CLI/PR bot: when a migration or schema-change PR mentions table/column, call API to comment which data products will be affected and suggested reviewers.- Notifications: integrate with Slack/Teams for high-impact changes.

Key implementation details:- Column-level extraction: parse SQL with an AST (sqlfluff/Apache Calcite) and map select expressions to source columns. For Spark, use logical plan extraction to derive mapping.- Versioning: schema snapshots per run; graph edges include run_id and timestamp to support temporal queries.- Provenance of transformations: store transformation metadata (code hash, git ref) to trace back to exact logic.- Security & governance: RBAC on metadata, encrypt storage, mask sensitive SQL/text.

Scalability & trade-offs:- Graph DB scales for traversal; heavy write workloads can be sharded by dataset namespace. Use long-term cold storage for raw events to keep graph lean.- Best-effort vs exact lineage: exact column mapping for UDFs may be impossible; adopt annotation/contract approach for black-box jobs.- Latency vs completeness: near-real-time ingestion via streaming; allow backfills to fix missed/retro events.

Failure modes & mitigations:- Missing instrumentation: provide a monitoring dashboard for “uncovered” jobs and enforcement via CI gate (reject merges that run uninstrumented jobs).- SQL parsing failures: fall back to table-level lineage and surface “unknown column mappings” for manual annotation.

This approach balances practical engineering (OpenLineage reuse, event streaming) with strong graph-based impact analysis, enabling engineers to answer “which datasets will be affected if I change column X in table Y?” with column-level precision where possible and clear fallbacks when not.

HardTechnical

52 practiced

Compare using dbt (SQL-first transformations) versus distributed Spark jobs for complex transformations such as large multi-way joins, feature engineering for ML, and custom UDFs. Discuss trade-offs in developer productivity, testability, performance, cost, and operational complexity and give scenarios where each is a better fit.

Sample Answer

Situation: As a data engineer choosing between dbt (SQL-first, warehouse-centric) and distributed Spark jobs, I evaluate trade-offs across developer productivity, testability, performance, cost, and operational complexity to pick the right tool for each workload.

Developer productivity- dbt: High for analysts/SQL-savvy engineers—declarative models, templating, refable lineage, built-in macros. Fast onboarding and iterative development.- Spark: Higher ramp for engineers (Scala/PySpark), but powerful when complex control flow or programmatic pipelines are needed.

Testability & maintainability- dbt: Strong—model-level tests, schema/assertions, ephemeral models, CI-friendly. Easy to reason about SQL lineage.- Spark: Unit tests possible but require more infra (local runner, fixtures). Harder to trace lineage unless you add metadata tooling.

Performance & scalability- dbt (warehouse compute): Excellent for set-based SQL on columnar warehouses (big joins, aggregations) if data fits warehouse scale and compute slots. Some warehouses pushdown optimizations and materialized views.- Spark: Superior for very large datasets, complex multi-way joins that exceed single-node memory, heavy UDFs, bespoke algorithms, or when you need distributed iterative processing (graph, ML pipelines).

Cost- dbt: Predictable—pay for warehouse compute/storage and dbt Cloud. For occasional transformations, using warehouse compute can be cheaper.- Spark: Potentially cheaper at massive scale if using spot instances or cluster autoscaling, but costs rise with long-running clusters and engineering overhead.

Operational complexity- dbt: Lower ops—leverages managed warehouses, simpler deployments, scheduling via orchestration tools.- Spark: Higher—cluster provisioning, tuning, dependency packaging, monitoring, JVM/Python serialization issues.

When to pick dbt- ELT workflows where raw data lands in a cloud warehouse, transformations are SQL-friendly, schema & lineage visibility matters, and team is SQL-first.- Regular analytics marts, BI aggregations, slowly changing dimensions, and feature stores where transformations are set-based.

When to pick Spark- Data exceeds warehouse capacity, requires distributed joins/reshuffles, complex feature engineering with heavy UDFs or iterative ML pipelines, custom windowed or streaming processing, or when integrating with HDFS/S3 at extreme scale.

Hybrid approach- Use dbt for most ELT/BI models and canonicalized features; delegate heavy distributed joins, expensive featurization, or bespoke ML preprocessing to Spark. Surface Spark outputs back into the warehouse for downstream use and lineage via metadata.

Trade-offs summary- dbt: faster dev, better testability, simpler ops, limited by warehouse scale and expressiveness.- Spark: flexible, scalable, handles complex compute, higher engineering and operational cost.

Recommendation: Define SLAs (latency, freshness), data volume thresholds, and team skills. Prefer dbt where possible; adopt Spark for workloads that truly require distributed compute or advanced custom processing.

HardTechnical

50 practiced

Define and measure data freshness (the latency from source event creation to availability in analytics). Design an SLA framework to track freshness across your pipelines, outline instrumentation to compute these metrics, and suggest concrete operational steps to improve freshness when SLAs are missed.

Sample Answer

Situation / goal: As a data engineering lead I was asked to define, measure and enforce freshness SLAs so analysts and ML models could trust latency guarantees for time-sensitive features.

Definition:- Data freshness = event_time → analytics_ready_time. We measure two deltas: 1. Ingestion latency: event_time to raw_store_arrival_time 2. Processing latency: raw_store_arrival_time to analytics_ready_time- Overall freshness = event_time to analytics_ready_time (end-to-end).

SLA framework:- Per dataset/topic define SLA tiers (Gold/Silver/Bronze) with numeric targets and SLO windows: - Gold: 99th percentile end-to-end ≤ 5 minutes (measured per 1h window) - Silver: 95th percentile ≤ 1 hour - Bronze: 99th percentile ≤ 24 hours- Error budget: allow X% breaches per week/month before escalation.- Ownership: each pipeline has an owner, upstream producers and downstream consumers registered.

Instrumentation to compute metrics:- Emit lightweight per-batch / per-event metadata: event_time, ingestion_time, processing_completion_time, pipeline_id, partition_key.- Central metrics pipeline: collect these timestamps to a time-series DB (Prometheus/Grafana for aggregates) + a clickhouse/BigQuery table for detailed queries.- Compute latency histograms and percentiles (P50/P95/P99) per pipeline, per partition, per hour.- Track counts of late records, skewed partitions, backlog size, consumer lag (Kafka offsets), processing throughput, and compute moving-window SLA compliance.- Dashboards: per-pipeline SLA status, trend lines, top offenders, and alerting thresholds on breach rate or rising P95/P99.- Correlate with system metrics: CPU, GC, network IO, cloud storage egress, job failures.

Operational steps when SLA missed:1. Automatic classification: transient spike vs sustained. Use policy: if single small window breach -> auto-retry/priority replay; if > error budget -> human escalation.2. Fast triage runbook: - Check producer health and event_time distribution (clock skew). - Check ingestion layer: broker lag, partition hotness, network errors. - Check processing: job failures, backpressure, resource saturation, GC, stragglers. - Check downstream sinks: write latency, schema errors.3. Immediate mitigations: - Increase parallelism/scale workers, allocate emergency CPU/memory, trigger autoscaling or submit a high-priority job. - Bypass heavy transformations: route a “fast-lane” minimal transformation to serve Gold SLA while full pipeline recovers. - Replay missing windows from raw store (tiered retention) with parallelized jobs.4. Communication & postmortem: - Notify consumers with estimated time-to-recovery and mitigations via status page. - Post-incident RCA, update runbooks, add automated detection (e.g., detect growing headroom violations), add tests or backpressure handling to prevent recurrence.

Trade-offs and improvements:- Instrumentation adds cost and slight latency; keep telemetry lightweight and sample if necessary.- Prioritize SLA enforcement for critical datasets; use feature flags to enable fast-lane.- Invest in idempotent replay, partitioning strategies, consumer backpressure and autoscaling to sustainably meet SLAs.

This framework creates clear expectations, measurable signals, automated mitigations, and owner-driven escalation so we can reduce latency and restore trust when breaches occur.

EasyTechnical

45 practiced

Define idempotency in the context of data pipelines. Provide a concrete example pattern that achieves idempotent writes to object storage (for example S3) when tasks may retry and explain how you deal with eventual consistency and partial writes.

Sample Answer

Idempotency means the same operation can be applied multiple times with the same effect as applying it once. In data pipelines that means retries (or duplicate runs) must not create duplicate or corrupt objects in object storage.

Concrete pattern (idempotent writes to S3):- Deterministic final key: derive the object key from upstream stable inputs (e.g., dataset/table, partition, run-id or input-checksum) so the same logical output maps to the same path.- Write-to-temp-then-commit: 1. Upload the file to a temporary key (e.g., s3://bucket/path/_tmp/<key>.<attempt> or multipart parts). Multipart uploads are invisible until CompleteMultipartUpload. 2. Verify checksum/ETag locally. 3. Atomically move/commit by copying temp -> final key and then deleting temp, or upload directly to final key only after success. Also write a small success marker/manifest (e.g., <final>.done containing checksum + size). 4. On start, check for final key + .done; if present and checksum matches, skip work (idempotent).- Retries: if a retry happens, the pipeline sees the manifest and either skips or re-uploads temp and then overwrites final with identical content (safe because deterministic content). Use If-None-Match/ETag comparison if you want to avoid overwrite when identical.

Dealing with eventual consistency and partial writes:- Rely on multipart semantics: incomplete multipart uploads are not visible until completed so partial parts don’t appear as final objects.- Use the manifest/marker pattern to represent “commit”. Only consider an object valid when the manifest exists and its checksum matches the object. This avoids race/visibility issues if listing is eventually consistent.- Implement retries with exponential backoff and verify by GET + checksum before assuming success.- Periodically garbage-collect stale temp/multipart uploads (abort multipart uploads older than threshold) and reconcile by scanning for final keys without valid manifests.

This pattern ensures safe retries, avoids duplicates, and guards against partial/uncertain visibility.

EasyTechnical

56 practiced

What is schema evolution in a Parquet-based data lake? Describe concrete approaches to safely add or remove fields over time, including use of schema registries, Avro/Parquet schemas, mergeSchema options, and why table formats like Delta Lake, Iceberg or Hudi help manage evolution and time travel.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.