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

50 practiced

Design a solution to capture programmatic lineage across an ecosystem with Airflow tasks, ad-hoc SQL in the BI tool, and Python scripts that write to the warehouse. Include how to ingest metadata, store lineage graph, and expose impact analysis for analysts. Mention open-source or commercial tools you would consider.

Sample Answer

Requirements and constraints- Capture end-to-end programmatic lineage across Airflow DAGs, ad-hoc BI SQL (Looker/Tableau/Power BI), and Python scripts writing to the warehouse.- Provide column- and table-level lineage, searchable graph, impact analysis (upstream/downstream), and lightweight analyst UX (what dashboards/reports are affected).- Non-functional: near-real-time ingestion, secure, queryable by analysts, integrates with existing tools.

High-level architecture1. Metadata collectors (producers) - Airflow: use OpenLineage or Airflow lineage hooks/operators to emit dataset, job, and column-level events on task start/finish (extract SQL from operators). - BI tool: collect query logs / usage API + parse SQL (or use BI-tool lineage features) to extract datasets and fields used; for tools with API (Looker), ingest Explores and LookML lineage. - Python scripts: instrument with OpenLineage client or add lightweight decorators that emit the source SQL/DF schema and target table. - Warehouse: capture table DDL/column changes via information_schema + audit logs (Snowflake/BigQuery/Redshift).

2. Ingestion pipeline - Stream events to a message bus (Kafka or managed Kinesis). - ETL service normalizes events to a common schema (OpenLineage / Marquez model), enriches with warehouse schema, and writes to metadata store.

3. Metadata store / lineage graph - Graph DB for traversal queries: Neo4j or JanusGraph (Scylla/Janus) for heavy graph queries. - Optionally keep a copy in a relational store (Postgres) for search/analytics and an object store for raw events. - Use OpenLineage / Marquez as metadata model and Apache Atlas if governance/ACLs required.

4. API & UI / Analyst-facing features - REST/GraphQL API exposing: upstream/downstream traversal, impacted dashboards/reports, column-level lineage, last run timestamps, owners. - Web UI: searchable catalog (tables, dashboards, jobs) with lineage graph visualization, "impact analysis" button that returns affected dashboards, queries, and owners. - Integrations: BI tool plugin/overlay to show lineage when viewing a dashboard; Slack/Email alerts for schema changes affecting critical dashboards.

5. Impact analysis workflow - Given a changed column/table, API runs graph traversal to list direct and transitive downstream nodes (jobs, datasets, dashboards), rank by recency and criticality (usage metrics). - Provide recommended action: notify owners, block deployments, run automated test queries. - For analysts: show example queries/dashboards that reference the changed field, last refreshed time, and a confidence score (exact column match vs name similarity).

Scalability, reliability & governance- Partition graph by dataset domain; use caching for frequent traversals.- Incremental updates via event stream to keep near-real-time.- AuthN/Z integrated with existing SSO; audit logs kept for compliance.- Data quality: add automated lineage tests in CI for Airflow jobs and unit tests for SQL.

Open-source / commercial tools to consider- Open-source: OpenLineage (producer spec), Marquez (metadata store), Amundsen (catalog + search), Apache Atlas (gov), Neo4j for graph queries, Airflow lineage hooks.- Commercial/managed: Monte Carlo/Bigeye (data observability), Collibra/Alation (enterprise catalog & governance), Databricks Unity Catalog (if on Databricks).

Why this approach- Using OpenLineage/Marquez standardizes producers across Airflow/Python/BI tools so ingestion is consistent.- Graph DB provides fast traversal for impact analysis; catalog + UI tailored to analysts shows exactly which dashboards to check, reducing manual discovery.- Modular design allows starting small (Airflow + warehouse) and progressively adding BI-tool ingestion and governance.

EasyTechnical

60 practiced

Security: describe how you would secure sensitive fields (PII) during ETL/ELT. Include encryption-at-rest and in-transit, role-based access controls in the warehouse, masking techniques for BI tools, and strategies for auditing access to sensitive data.

Sample Answer

I would secure PII during ETL/ELT with a layered, practical approach focused on least-privilege access, strong encryption, masking for BI, and comprehensive auditing.

Encryption- In-transit: enforce TLS 1.2+ for all connectors (DB->ETL/ELT, ELT->warehouse, BI->warehouse).- At-rest: use column-level encryption or cloud-managed envelope encryption (e.g., KMS + CMKs) for sensitive fields (SSNs, emails). Store keys in a managed KMS and enable automatic key rotation.

Access control in the warehouse- Implement RBAC: separate roles (etl_service, analyst_readonly, analyst_masked, admin) with least privilege.- Use column- and row-level permissions where supported (e.g., Snowflake masking policies, Redshift column privileges).- Separate production vs. dev schemas; ensure dev uses anonymized or synthetic PII.

Masking for BI tools- Use dynamic data masking and row-level security so dashboards show only allowed views. Example: full SSN only for compliance role, masked (***-**-1234) for executives, hashed/obfuscated for analysts.- Configure BI tool RLS (Tableau/Power BI/Looker) to respect warehouse policies and not cache raw PII unless explicitly permitted.

Auditing & monitoring- Enable warehouse query logging (user, timestamp, query text, returned columns), ETL job logs, and BI access logs.- Centralize logs into SIEM/monitoring (e.g., Splunk, Cloud Logging) and set alerts for anomalous access patterns (large exports, queries of many PII rows).- Retain audit trails for compliance and regular reviews; perform periodic access reviews and certify roles.

Operational best practices- Tokenization/anonymization for downstream analytics where exact PII isn’t needed.- Use parameterized queries to avoid accidental leakage; avoid exporting PII into spreadsheets.- Automate key rotation, periodic penetration tests, and training for analysts on handling PII.

This combination protects data in motion and at rest, ensures analysts can do their jobs with minimized exposure, and provides traceability for compliance.

MediumSystem Design

54 practiced

Design a near-real-time analytics pipeline for a checkout funnel KPI that must show counts with <30s latency to product managers. Sources: web events (Kafka), payments (dedicated API). Outline stream processing approach, how to materialize aggregates for BI tools, and how to maintain correctness in the face of late-arriving events and restarts.

Sample Answer

Requirements & constraints:- KPI: checkout funnel counts (views, add-to-cart, checkout-start, payment-success/fail)- End-to-product-manager latency <30s, eventual consistency allowed with corrections- Sources: web events (Kafka topics), payments via API (can publish to Kafka or call ingest)- Support BI tools (Looker/Tableau) and ad-hoc queries; correctness under late events & restarts

High-level architecture:1. Ingest: Kafka for web events; payments pushed into Kafka (sync service that writes payment events).2. Stream processing: Use a stream engine (Apache Flink or Kafka Streams) to consume topics, enrich events (user/session), and compute windowed aggregates (event counts per funnel step) with short tumbling windows (e.g., 10s) and session stitching.3. Event-time semantics & watermarks: Use event-time processing with watermarks (e.g., 1–2 minute allowed lateness) so counts reflect real order; emit provisional aggregates quickly and corrected updates when late events arrive.4. Materialized aggregates: Stream job writes/upserts aggregates into a serving store optimized for BI: - Real-time store: ClickHouse or Bigtable/BigQuery streaming inserts for low-latency slices (minute-level partitions), or a materialized view in Druid/Pinot for fast OLAP queries. - Batch canonical store: Periodic (hourly) compaction into the data warehouse (BigQuery/Redshift) for historical/backfill analysis.5. Serving to BI: Expose the real-time store via a semantic layer (Looker PDTs or BI views) that reads the low-latency store for dashboards; fallback to warehouse for deep historical queries.

Correctness & availability:- Exactly-once processing: Use Flink with checkpointing and Kafka transactional connectors or Kafka Streams with EOS to avoid double-counting across restarts.- Late events: Use event-time windows + allowed lateness; stream job produces two kinds of updates: - Fast provisional counts emitted as soon as watermark passes small threshold (sub-30s) - Retractions/deltas when late events arrive; stores support upserts and versioning so BI sees corrections- Idempotency: Enrich events with unique IDs and perform deduplication in-stream using a short TTL state store keyed by event id.- Backfill / restarts: Maintain durable checkpoints (Flink state backend on S3) and Kafka offsets; on schema changes or reprocessing, run a bounded batch job to recompute aggregates into the warehouse and reconcile serving store.

Scalability & operational notes:- Partition Kafka by user/session to scale processing and maintain locality.- Tune window size and watermark to balance freshness vs corrections (e.g., 10s windows, 30–90s max lateness).- Monitor SLA: end-to-end latency, watermark lag, out-of-order rate, and correction volume; alert on rising retractions.- Dashboards: indicate data freshness and a “corrected” badge when updates occur.

Trade-offs:- Lower watermark (faster) increases corrections; higher watermark reduces corrections but increases latency.- Using a specialized OLAP store (Druid/Pinot/ClickHouse) gives sub-second queries; BigQuery is simpler but has higher query latency/costs.

This design gives product managers sub-30s provisional counts with robust correctness via event-time processing, deduplication, upserts, and reconcile paths for backfills.

MediumTechnical

52 practiced

Describe three strategies to detect and load only changed data when the source does not provide a reliable last_updated column. Discuss pros/cons of (1) change-data-capture (CDC), (2) hash-based row comparison, and (3) full-table snapshots with differential comparison. For each, note cost, latency, resource use, and data completeness guarantees.

Sample Answer

When the source lacks a reliable last_updated, three common strategies are: CDC, hash-based row comparison, and full-table snapshots with differential comparison. Below I describe each and compare cost, latency, resource use, and completeness.

1) Change Data Capture (CDC)- What: Capture DB-level changes (transaction log, triggers, or vendor CDC) and stream inserts/updates/deletes to target.- Pros: Low latency (near real-time), minimal data scanned (only changed rows), strong completeness if implemented correctly (transactional ordering preserved).- Cons: Higher engineering and operational cost (requires DB access, tooling like Debezium/Kafka or vendor product), complexity (schema evolution, initial snapshot), potential performance impact on source if misconfigured.- Cost: Medium–High (infra + maintenance)- Latency: Low (seconds–minutes)- Resource use: Low on data transfer, moderate infra for CDC pipeline- Completeness guarantees: High if CDC supports transactional logs; requires careful tuning for edge cases.

2) Hash-based row comparison- What: Maintain a hash (e.g., MD5/SHA1) of business-key + relevant columns in source snapshot versus target; only load rows whose hash changed or new keys.- Pros: Simpler to implement than CDC, no DB log access, good accuracy for detecting changed content, lower transfer than full snapshot.- Cons: Requires full scan of source rows each run to compute hashes (costly for large tables), hashing CPU/IO overhead, must ensure deterministic hashing and stable key; careful with nullable/ordering issues.- Cost: Low–Medium (compute for hashing)- Latency: Medium (depends on table size and scan frequency)- Resource use: Moderate (reads across entire table each run)- Completeness guarantees: Medium–High for content changes; may miss changes if you hash wrong set of columns or schema changes occur.

3) Full-table snapshot + differential comparison- What: Extract entire table periodically, compare to previous snapshot (via joins or set-diff), and apply inserts/updates/deletes.- Pros: Very simple and robust (no assumptions), easy reconciliation, catches any change including metadata/sometimes hidden changes.- Cons: Highest cost and resource use (network, storage, compute), high latency if snapshots are infrequent, not suitable for large/high-change tables without partitioned snapshots.- Cost: High (storage and transfer)- Latency: High (depends on schedule; often hourly/daily)- Resource use: High (full reads and diffs)- Completeness guarantees: Very High (assuming snapshot completes successfully); slow to react.

Guidance for BI: choose CDC for near-real-time dashboards and high-change tables if you can invest in tooling. Use hash-based comparison for moderate-volume tables where CDC isn’t available — optimize by scanning partitions or incremental keys. Use full snapshots for small tables, low-change reference data, or when you need a simple, auditable reconciliation. Combine approaches (e.g., CDC for transactional tables, snapshots for lookup tables) and implement monitoring/reconciliation jobs to surface missed changes.

MediumTechnical

61 practiced

Design an error handling pattern for batch transformation jobs where some rows may be malformed (bad JSON, unexpected enum values). Describe quarantining, schema validation, retry windows, and how to present the problematic rows to data owners for correction. Include metrics to track and policies for automatic vs manual remediation.

Sample Answer

Situation: We run nightly batch transformations that feed BI dashboards, but some input rows are malformed (bad JSON, unexpected enums) causing pipeline failures or silent wrong results.

Pattern (high-level):1. Schema validation at ingest- Strong schema (JSON Schema/Avro) applied as first step. Validate types, required fields, enum domains.- Classify failures: hard errors (parse failure, missing key) vs soft errors (enum out-of-range, minor type mismatch).

2. Quarantine layer- Route failed rows to a quarantine table/storage with metadata: raw payload, error code, source, timestamp, job-id, attempt-count.- Keep quarantined data immutable and indexed for query.

3. Retry windows & automated remediation- Automatic retries for transient issues (e.g., temporary encoding glitches) with exponential backoff within a configured retry window (e.g., 24 hours, 3 attempts).- For known fixable patterns, run auto-fixes (trim whitespace, map deprecated enum values) via transform rules; log changes.

4. Presentation to data owners- Provide a curated “Quarantine Dashboard” (Looker/Tableau) showing sample rows, error types, frequency, and raw payloads with a one-click “claim” or “request fix” workflow that opens a ticket (Slack/ServiceNow) with context and example rows.- Allow data owners to download CSV of problematic rows and propose mapping corrections (e.g., map “N/A” → NULL).

5. Remediation policies: automatic vs manual- Auto-remediate: non-ambiguous fixes (format normalization, known legacy values) and transient parse errors within retry window.- Manual review: ambiguous enums, missing critical keys, or high-impact rows (revenue, user_id). After manual fix, re-ingest or mark resolved.

6. Metrics & SLAs- Track: quarantine_count, quarantine_rate (% of total rows), mean_time_to_resolve, retry_success_rate, top error types, rows_by_source.- Alerts: threshold-based (e.g., quarantine_rate > 0.5% triggers pager/email to data owner); SLA for resolution (e.g., critical sources 4 hours, others 48 hours).- Dashboards: trends by source and error type, resolution latency distribution.

Why this works:- Prevents pipeline failures and silent data corruption, preserves raw context for debugging, balances automation with human judgment, and provides transparency and accountability to data owners so BI can maintain trusted reports.

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.