Extract, Transform, Load and Pipeline Implementation Logic Questions

Design and implement extract transform load pipelines and the transformation logic that powers analytics and operational features. Topics include source extraction strategies, incremental and full loads, change data capture, transformation patterns, schema migration and management, data validation and quality checks, idempotent processing, error handling and dead letter strategies, testing pipelines and data, and strategies for versioning and deploying transformation code. Emphasize implementation details that ensure correctness and maintainability of pipeline logic.

HardSystem Design

74 practiced

Propose a schema and approach for storing and querying time-series feature aggregates that must support both fast writes from streaming and efficient ad-hoc analytics queries. Include partitioning, compaction, TTL, and query patterns.

Sample Answer

Requirements:- Ingest high-volume streaming writes (millions/sec), low-latency appends.- Serve ad-hoc analytics (aggregations, joins, time-windowed queries) with reasonable latency.- Support TTL/retention, compaction to reduce storage, and partitioning for scale.- Provide deterministic feature aggregates for model training and feature store use.

High-level design:- Use a write-optimized storage layer (append-only log + ingestion buffer) + columnar analytical store for queries. Example stack: Kafka (streaming) → ingestion service → OLAP store (ClickHouse/Druid/BigQuery/Delta Lake) with partitioned Parquet files and an index.

Schema:- Table: feature_aggregates - entity_id (string) — partitioning key candidate - feature_name (string) - window_start (timestamp) - window_end (timestamp) - aggregate_value (double / JSON for vector) - count, sum, min, max (optional) - source_ts (timestamp) — last update time - version / watermark (int) — for dedupe/late events

Partitioning:- Two-level partitioning: 1) Time-based coarse partitions (e.g., daily or hourly) on window_start for efficient TTL and compaction. 2) Within each time partition, hash partition by entity_id (or entity-shard) to distribute load and enable parallel writes/reads.- Maintain partition metadata with min/max time for pruning.

Writes & streaming flow:- Streaming events go to Kafka topics keyed by entity_id.- Lightweight stream processor (e.g., Flink/Faust) performs local windowed partial aggregations (pre-aggregation) and writes compacted deltas to a write-forward store (e.g., Kafka or write-ahead delta files).- A background loader consumes deltas and upserts into OLAP store in micro-batches (e.g., every minute) producing columnar files (Parquet) organized by partitions.

Compaction:- Periodic compaction job per partition: - Merge micro-batches and delta files into larger columnar files. - Deduplicate by (entity_id, feature_name, window_start) using version/watermark — keep latest. - Compress and compute secondary indexes (min/max) and bloom filters.- Compaction frequency balances write amplification vs query freshness.

TTL/Retention:- Implement retention at partition level: drop entire time partitions older than retention period.- Soft-delete: mark records expired first to allow rollback; final delete during compaction.- For entities with sparse data, run lifecycle jobs to remove small partitions to reduce metadata.

Query patterns and optimizations:- Point lookup: query by entity_id + time window — route to relevant time partitions, use hash partitioning to read only shard files, return fast.- Time-series range queries: filter by entity_id and window_start range; leverage columnar predicate pushdown and indexes.- Aggregates across entities: scan time partitions and use pre-aggregated rollups (hour/day) stored in separate tables to avoid full scans.- Ad-hoc joins with feature tables: use broadcast join for small dimension tables; for large joins, co-partition or repartition on join key.- Late-arriving data: use watermark/versioning; streaming pre-aggregates reduce reprocessing. For correctness, maintain changelog of deltas to recompute affected partitions when late events arrive.

Trade-offs:- Near-real-time freshness vs compaction cost: more frequent micro-batches lowers latency but increases small-file overhead.- Fine-grained partitions (hourly + per-entity) speed queries but increase metadata; balance with hashing.- Using a transactional store simplifies correctness (ACID upserts) but can add write latency—choose based on tolerance.

Operational concerns:- Monitoring: ingestion lag, compaction backlog, partition sizes.- Backups and export for model training: snapshot partitions to object storage and register dataset versions.- Security: encryption at rest, access controls for feature tables.

This design supports fast streaming writes, efficient ad-hoc analytics, and operational controls (TTL, compaction, indexing) to keep storage performant and queries fast.

EasyTechnical

84 practiced

Explain the role of orchestration tools such as Airflow or Dagster in ETL workflows. Describe three capabilities these systems provide that are important for production data pipelines and one limitation you must work around as a data scientist.

MediumTechnical

75 practiced

Describe strategies to handle late-arriving data for daily aggregated metrics (e.g., daily active users). Include reprocessing approaches, incremental correction updates, and how to communicate updates to dashboards and downstream consumers.

Sample Answer

Strategy overview:- Aim for correctness + timely reporting. Combine prevention (streaming watermarks, validation) with remediation (reprocessing/backfill and incremental corrections) and clear communication.

Approaches

1) Prevent/limit late data- Use event-time processing with watermarks in streaming (e.g., Beam/Flink) to emit early aggregates and allow a bounded lateness window (e.g., 1 day).- Validate producers (idempotent event IDs, producer-side buffering) and monitor lag.

2) Reprocessing / backfill- Full reprocess: run batch job over affected date range when late volume or logic changed. Use partitioned storage (date partitions) so reprocesses are efficient.- Incremental/partitioned reprocess: re-compute only impacted partitions (day granularity) and atomically swap partitions or update materialized tables.- Keep reproducible job configs and provenance (code + input snapshot).

3) Incremental correction updates- Emit correction deltas rather than full rewrites when possible. Compute corrected_value = new_aggregate - previous_written_value and apply idempotent upserts.- Use CDC or time-series “corrections” table: store (date, metric, delta, timestamp, reason).Example upsert (SQL):

sql

-- apply corrected daily_active_users delta
MERGE INTO daily_metrics tgt
USING (SELECT date '2025-11-01' as dt, 42 as delta) src
ON tgt.dt = src.dt
WHEN MATCHED THEN UPDATE SET value = tgt.value + src.delta, updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (dt,value,updated_at) VALUES (src.dt, src.delta, CURRENT_TIMESTAMP);

4) Consistency & idempotency- Make ingestion and correction jobs idempotent (use event IDs, upserts, or write-then-atomic-rename).- Keep “last_processed_offset” metadata to resume safely.

5) Communicating to dashboards & downstream consumers- Data freshness metadata: expose last_updated and completeness flags per date (e.g., “finalized” boolean).- Versioning: show metric version or lineage; allow consumers to opt into “stable” vs. “near-real-time” layers.- Notify stakeholders for material changes: automated Slack/email for >X% correction, and postmortem with root cause.- UX: annotate charts with “partial” or “updated” badges; support drill-through to correction logs.

When to choose which- Small, frequent late-arrivals: use bounded-lateness streaming + incremental deltas.- Large/backfilled historical fixes: full/partitioned reprocess.- Business-critical accurate metrics: longer lateness window + nightly reconciliation + explicit finalization.

Monitoring & SLAs- Alert on correction rate, reprocess frequency, and percent-change between initial vs final values.- Define SLAs for “near-real-time” vs “final” metric availability.

This combination balances timely insights for product/ops with correctness and transparent communication for analysts and stakeholders.

HardSystem Design

56 practiced

Design a CI/CD pipeline for transformation code (dbt models, SQL scripts, Python UDFs) that includes unit tests, previewing transformed outputs on sample datasets, approval gates, and rollback. Describe the stages, required artifacts, and how you would ensure data correctness before production deployment.

Sample Answer

Requirements:- Automate build/test/deploy for dbt models, SQL scripts, Python UDFs- Run unit tests, preview transformed outputs on sampled datasets- Approval gates for promos to staging/production- Safe rollback and verification of data correctness- Integrate with existing data warehouse (e.g., Snowflake/BigQuery), CI system (GitHub Actions/GitLab CI), and orchestration (Airflow/Prefect)

High-level stages (pipeline per PR and per branch):1. Pre-commit / Static checks (local + CI quick run) - Linting: SQLFluff, flake8/isort, dbt parser - Dependency / schema checks (dbt compile) Artifacts: compiled SQL, dependency graph

2. Unit tests (CI on PR) - dbt unit tests + schema tests - Pytest for Python UDF logic (mocking db connections) - Small-data integration: run models against in-repo sample datasets (CSV/Parquet) using ephemeral local warehouse (duckdb) or cloud dev schema Artifacts: test reports (JUnit), sample outputs, dbt docs site

3. Integration / Preview (CI/CD) - Deploy transformed models to isolated preview schema (naming with PR ID or feature branch) in target DW - Run full dbt run for affected models - Run data quality checks: dbt tests, custom SQL assertions, row count / distribution comparisons vs baseline - Generate data diff artifacts (e.g., Delta tables, or query-based diffs) and data profile reports (Great Expectations) Artifacts: preview schema, result tables, diffs, profiling reports, data lineage, dbt docs

4. Approval gate (manual + policy) - Notify stakeholders with preview links, samples, diffs, and test results - Require approvals via GitHub/GitLab or CI (e.g., protected branches) and optionally SLA-based automated approvals if DQ passes - Capture sign-off metadata (who, when, comments)

5. Staging deploy (post-approval) - Promote models to staging schema; run smoke tests and production-like validation on representative datasets - Run performance tests (query/compute cost, explain plans) Artifacts: staging tables, performance metrics, DQ pass/fail

6. Production deploy & post-deploy validation - Blue/green or shadow deploy pattern: write to new tables/schemas and switch alias/views once validations pass - Run final reconciliation: sample-based row-level diff, aggregation checks, and model-specific metrics - Monitor downstream consumers for anomalies; run canary queries and DB alerting Artifacts: production tables, audit logs, rollback plan

Rollback strategy:- Use atomic switches: views/aliases or versioned table names to swap datasets quickly- Maintain previous good snapshot (time travel or retained tables) for immediate rollback- Automated rollback trigger on failed post-deploy DQ or anomaly detection- Post-rollback tests to confirm restoration

Ensuring data correctness:- Shift-left tests: unit tests for logic + local sample integration- Strong DQ suite: dbt schema tests, custom SQL assertions, Great Expectations suites, and statistical checks (distribution shifts, nulls, cardinality)- Data contracts: automated checks comparing input schema/row counts to expectation- Sampling + row-level diffs for preview/staging using deterministic keys; metric monitoring for business KPIs- Observability: lineage, metrics, SLAs, dashboards, and alerts integrated into CI artifacts and monitoring (Datadog, Grafana)- Governance: approval workflows, audit trail, and access controls on deploy jobs

Trade-offs & notes:- Ephemeral preview schemas increase cost but greatly reduce risk- DuckDB/local runs speed PR validation but may not catch DW-specific behavior—always include staging runs on real warehouse- Use feature-branch isolation to make reviews reproducible and enable easy rollback/snapshots

This design balances automation and human oversight, enforces data correctness through layered testing and previews, and enables safe, fast rollbacks.

MediumTechnical

106 practiced

Write a SQL query that extracts incremental rows from a source table `orders` based on a `last_updated` timestamp. Given the `orders` table schema:

Assume you have a stored `high_water_mark` timestamp and want only rows where last_updated > high_water_mark. Provide the query and briefly explain how you would handle late-arriving updates that have older last_updated but were committed later.

Sample Answer

Use the high-water-mark (HWM) to pull only rows whose last_updated is greater than the stored HWM. A basic incremental query:

sql

-- incremental pull using stored high_water_mark (e.g. '2025-12-01 00:00:00')
SELECT order_id, user_id, amount, last_updated
FROM orders
WHERE last_updated > TIMESTAMP '2025-12-01 00:00:00';

Practical pattern and handling late-arriving updates:- Use a safe lookback window: include a small overlap when querying (e.g., HWM - 5 minutes) to capture late commits whose last_updated is slightly older than the HWM but were committed after the previous run.- De-duplicate on primary key using the latest last_updated (or commit_ts if available) before applying to the target.

Example with lookback and deduplication (ANSI SQL):

sql

WITH raw AS (
  SELECT *
  FROM orders
  WHERE last_updated > TIMESTAMP '2025-12-01 00:00:00' - INTERVAL '5' MINUTE
),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY last_updated DESC) AS rn
  FROM raw
)
SELECT order_id, user_id, amount, last_updated
FROM ranked
WHERE rn = 1;

Operational notes:- After successful apply, set HWM = MAX(last_updated) from the rows you processed (not earlier than previous HWM).- If the source exposes a commit timestamp or CDC log, prefer that for correctness instead of last_updated.- In Python ETL, wrap read-apply-update-HWM in a transaction and persist HWM atomically (or use a transactional metadata table) so retries don’t advance HWM incorrectly.

Unlock Full Question Bank

Get access to hundreds of Extract, Transform, Load and Pipeline Implementation Logic interview questions and detailed answers.

Join thousands of developers preparing for their dream job.