ETL vs. ELT Patterns and Processing Strategy Questions
Understanding Extract-Transform-Load vs. Extract-Load-Transform approaches. Knowing when to transform before loading (ETL) vs. after (ELT). Trade-offs: data quality, flexibility, performance, and operational complexity. Choosing batch processing, streaming, or hybrid approaches based on requirements.
HardSystem Design
18 practiced
Design an approach to achieve exactly-once semantics for pipelines that land events to object storage and process them using Spark Structured Streaming with micro-batch joins. Discuss checkpointing, idempotent sinks, transactional file sinks (Delta/ICEBERG), deduplication, and how to reason about external side effects.
Sample Answer
Requirements:- Exactly-once end-to-end for events landed to object storage and consumed by Spark Structured Streaming with micro-batch joins.- Support scale, recoverability, and bounded latency.- Minimize duplicates and handle external side effects safely.High-level approach:1) Ingest: Events land to object storage (S3/GCS/ADLS) as immutable files with deterministic object keys (e.g., partition/date + producer-id + event-seq or UUID + etag). Include event metadata: unique event_id, ingestion_time, source_id, offset. Use an append-only manifest (or topic) to list new files.2) Spark Processing: Use Spark Structured Streaming in micro-batch mode reading the file source via file stream + input file metadata or by streaming from a message-log (Kafka) that references file locations. Enable checkpointing: set a stable checkpointLocation (on durable storage) so Spark can recover offsets, micro-batch state and streaming query progress.3) Stateful joins & deduplication:- Maintain event-state with watermarking and allowed-lateness to bound state size.- Deduplicate using event_id: use mapWithState / dropDuplicates on event_id within a deterministic watermark window. Persist state in Spark's checkpointed state store so replays won't reapply deduped events.- For micro-batch joins, ensure both sides use stable join keys and deterministic join order; use stateful streaming joins with timeout semantics, and checkpoint the join state.4) Transactional sinks (preferred): Write outputs into transactional table formats: Delta Lake or Apache Iceberg- Use Spark's native transactional write path (Delta's job/txn IDs or Iceberg's atomic commits). Each micro-batch writes a new atomic commit containing file additions and metadata.- Include the source micro-batch id / input offsets in metadata to make commits idempotent and traceable. On retry, the commit is either idempotent or detected as duplicate and skipped.5) Idempotent sinks (if transactional unavailable):- Implement idempotent upserts keyed by output record primary key and source event_id. Use "upsert where source_seq > existing_seq" semantics. For object-store files, write to a staging path then perform atomic rename/manifest update guarded by commit metadata (optimistic concurrency using conditional writes in metastore).6) Checkpointing details:- Spark checkpoint stores offsets, operator state, and transaction/commit markers.- Also persist a mapping of processed input files -> output commit id. On recovery, consult this mapping to avoid re-processing an input file already committed.7) External side effects (calls to downstream APIs, notifications):- Treat them as non-transactional; isolate via reliable delivery patterns: - Prefer writing an event to a durable transactional sink (Delta/Iceberg) first. Downstream side effects are triggered by a separate, idempotent consumer reading the committed table (change data capture / CDC) and performing side effects. The CDC process should be idempotent (use event_id dedup, use at-least-once retries with de-dup store). - If immediate external call required within batch, implement a two-phase commit: record intent in transactional table, publish a "to-be-delivered" record, then a separate exactly-once dispatcher reads and executes side effects and marks delivered. Dispatcher must dedupe by event_id.Trade-offs and reasoning:- Transactional table sinks (Delta/Iceberg) give strong guarantees with minimal application logic; they are preferred.- Deduplication window and watermark trade-off latency vs completeness.- Persistent mapping of input->commit and durable checkpointing are essential to prove idempotence and allow safe retries.- External side effects should be eventually consistent and driven off transactional state to avoid coupling.Monitoring and correctness:- Emit metrics: micro-batch ids, processed input files, commit ids, dedup counts, state size.- Periodic reconciliation job comparing source manifests to committed processing log to detect missed/duplicated files.This design yields exactly-once semantics for storage-to-table processing and practical exactly-once behavior for external effects by isolating side-effects into an idempotent, transactional-driven dispatch.
MediumTechnical
19 practiced
Describe a comprehensive testing strategy for ELT pipelines: unit tests for SQL transformations, integration tests for transforms against sample data, regression tests to detect drift, and how to integrate these tests into CI/CD. Include tools or frameworks you'd use.
Sample Answer
Approach (high level)- Treat ELT like software: small, fast unit tests on logic; integration tests that run transforms against representative sample data; regression/monitoring to detect data drift in production; enforce via CI/CD gating.Unit tests for SQL transforms- Use dbt for modular SQL models and dbt’s built-in schema/tests (unique, not_null, relationships) plus custom data tests for business logic.- For pure SQL logic, write small SQL-based unit tests that run against an ephemeral/local DB (Postgres/SQLite) using pytest + sqlalchemy. Example: run a transform query and assert rows/columns/aggregates.Integration tests against sample data- Use a CI-only ephemeral warehouse (Postgres/BigQuery sandbox, or dbt with a Snowflake dev schema). Load curated sample datasets (fixtures) that cover edge cases.- Run: seed sample data -> dbt run -> dbt test/custom assertions -> compare output to golden snapshots (small parquet/csv).- Tools: dbt seeds, pytest for orchestration, Dockerized dev warehouse for reproducibility.Regression tests & drift detection- Implement statistical and schema regression checks via Great Expectations or Soda: record baseline expectations (row counts, null rates, distributions, column histograms). Alert when drift exceeds thresholds.- Maintain snapshot tests for deterministic outputs; use hash-based dataset fingerprints to detect unexpected changes.- Add targeted regression tests for derived metrics (daily active users, conversion rates) with historical tolerance bands.CI/CD integration- Run quick unit tests on every PR (fast dbt run on limited models + pytest).- Run full integration tests on merge to main or nightly: deploy to ephemeral dev schema, run full dbt run + tests + snapshot comparisons.- Gate deployments: require test pass to promote models to prod schema or trigger Airflow/dagster pipeline.- Tools: GitHub Actions / GitLab CI / CircleCI for orchestration; dbt Cloud or self-hosted dbt + Git; Terraform for infra; use feature branches + ephemeral environments (dbt Cloud dev schemas, ephemeral BigQuery datasets).Best practices & notes- Keep test data small, deterministic, and versioned in repo (dbt seeds or fixtures).- Isolate tests: reset schemas between runs, use transactions or ephemeral databases.- Prioritize speed: run most tests in PRs, heavier regression suites nightly.- Instrument monitoring and lineage (OpenLineage) to triage failing regression alerts quickly.This strategy combines dbt for SQL modularity, pytest for orchestration/custom asserts, Great Expectations/Soda for data quality and drift, and CI (GitHub Actions/dbt Cloud) to enforce quality gates.
python
# tests/test_transform_unit.py
import sqlalchemy
import pandas as pd
def test_total_revenue():
engine = sqlalchemy.create_engine("sqlite:///test.db")
df = pd.read_sql("SELECT SUM(amount) as total FROM my_transform", engine)
assert df.total.iloc[0] == 12345HardTechnical
25 practiced
Design cross-region disaster recovery for ELT pipelines with active-active ingestion. Consider data duplication, eventual consistency, conflict resolution, and recovery time and point objectives for both raw landing and transformed analytics artifacts.
Sample Answer
Requirements & high-level approach:- Active-active ingestion in two (or more) regions with continuous availability, target RPOs: raw landing ≤ seconds–minutes, transformed artifacts ≤ minutes–hours depending on SLA; RTOs: raw landing < 5 minutes, transformed analytics < 30–60 minutes (tunable).- Preserve correctness (no silent data loss), bound duplication, support eventual consistency and deterministic conflict resolution.Architecture:- Ingest streaming and batch from producers into local regional ingestion layer (regional API/gateway → regional Kafka / Kinesis). Each event carries a globally-unique id (UUID), source timestamp, and optional monotonic sequence or vector clock.- Use geo-replicated streaming (Kafka with MirrorMaker 2 or Confluent Replicator, or cloud-native Global Topics) for cross-region replication with at-least-once delivery.- Raw landing store: region-local object store (S3/GCS) with cross-region replication enabled. Writes are idempotent: upload files/objects using content-addressed keys (hash or event-id + partition) so duplicates are deduplicable.- Metadata/catalog: globally replicated metadata store (e.g., DynamoDB global tables, Spanner) to hold ingestion manifests, offsets, materialization versions, and tombstones.Duplication & eventual consistency:- Allow duplicates during network partitions — deduplicate at the earliest possible point using event-id or content hash. Maintain a dedupe window (short-lived index) plus eventual reconciliation jobs against manifests for older windows.- Design pipelines as idempotent: transforms use upserts keyed on primary business key + event timestamp/version.Conflict resolution:- For commutative/mergeable data (counters, sets), use CRDTs so concurrent updates converge automatically.- For last-writer semantics use vector clocks or hybrid logical clocks (HLC) to pick deterministic winner; if business logic requires, implement application-level merge functions with audit logs and manual review flows for irreconcilable conflicts.- Record provenance: store source-region, source-offset, and conflict-resolution metadata so audits and replays are possible.Transformed artifacts & consistency:- Implement region-local materialization jobs (Spark/Flink) consuming from local topics that are kept in sync. Each materialization emits artifacts with a deterministic version (hash + commit timestamp) and writes to region-local data warehouse (Snowflake/Redshift/BigQuery) with replication of commits to a global catalog.- For consistency guarantee, use watermarking and late-arrival policies: expose “freshness” metrics and mark data as provisional until reconciliation. Provide two consumable views: best-effort low-latency view and reconciled authoritative view (recomputed or merged during reconciliation).RPO/RTO techniques:- Minimize RPO for raw landing: synchronous replication of minimal metadata and async replication of payloads; rely on object replication + retained change logs for replay.- To meet RTO: automations to failover consumers to remote replicated topics and switch materialization to the healthy region; pre-warmed compute pools and IaC scripts to rebuild stateful services quickly.- For transformed artifacts RTO: maintain incremental checkpoints and write-ahead logs (Kafka offsets, structured commit logs) so consumers can resume from last committed offsets; keep frequent snapshots for fast restore.Operational practices:- Continuous reconciliation: background jobs that compare manifests, row counts, checksums between regions; surface drift via alerts and auto-heal when safe.- Chaos and DR drills with injected partitions, region failover, and replay testing.- Observability: end-to-end lineage, freshness, data-loss and duplication metrics, and SLA dashboards.- Runbooks for automated and manual conflict resolution, rollback, and reprocess.Trade-offs:- Stronger determinism (CRDTs, HLC) increases complexity but reduces manual reconciliation.- Lower RPO/RTO increases cost (synchronous/replicated infra, pre-warmed capacity).- Provide configurable SLA tiers: “nearline” low-latency vs “authoritative” reconciled datasets.This design balances availability and correctness: idempotent ingest + global metadata + deterministic resolution and continuous reconciliation give bounded duplication and clear RPO/RTO guarantees for both raw landing and transformed analytics.
EasyTechnical
26 practiced
Explain idempotency in ingestion pipelines. Describe a simple idempotent design for a REST-based ingestion endpoint, including how to detect duplicates and how to handle retries or backoff from clients.
Sample Answer
Idempotency means that applying the same operation multiple times has the same effect as applying it once — crucial for ingestion pipelines so retries or duplicate client requests don’t create duplicate records or side effects.Simple idempotent REST ingestion design:- Client provides an Idempotency-Key (UUID) with each POST (header or body).- Server stores a small dedupe table: (Idempotency-Key, request-hash, response, status, created_at).- On request: - If key not seen: process, persist data in a way that enforces uniqueness (e.g., write with a deduplication key or DB unique constraint), store the produced response and status under the key, return response. - If key seen and processing completed: return stored response (HTTP 200/201) without re-applying side effects. - If key seen but processing in-progress: return 202 Accepted or wait briefly and return stored result when done.Duplicate detection techniques:- Idempotency-Key lookup (primary).- Request payload hash + dedupe store as secondary check.- Database unique constraints on natural unique keys (e.g., event_id) to enforce exactly-once semantics.Retry and backoff guidance:- Clients should use exponential backoff with jitter.- Server returns clear status codes: 409 for logical conflicts, 202 for in-progress, 200/201 with stored result for repeats.- Include Retry-After header when appropriate.- Expire idempotency entries after a retention window (e.g., 24–72h) to bound storage.Trade-offs and notes:- This gives “at-most-once” or effectively “once” behavior for a retention window; true distributed exactly-once requires stronger transactional systems (idempotent sinks, deduplication in downstream storage).- Ensure idempotency keys are unique per logical operation and authenticate/authorize them to prevent abuse.
MediumTechnical
23 practiced
Explain cost trade-offs between ETL and ELT when using pay-as-you-go warehouses (Snowflake, BigQuery): where costs typically manifest, which patterns increase costs, and list practical tactics (materialized views, clustering, compute sizing) to reduce warehouse spend without sacrificing SLAs.
Sample Answer
High-level trade-off- ETL (transform before load) moves CPU/IO into your compute (e.g., Spark on VMs) so costs are dominated by your cluster EC2/GKE spend and operational overhead; storage in the warehouse is smaller and query cost later is lower.- ELT (load raw, transform in-warehouse) shifts work into the pay-as-you-go warehouse. This simplifies pipelines and leverages warehouse scaling but increases warehouse compute and/or scanned bytes costs.Where costs typically manifest- Compute time: Snowflake charges per-second virtual warehouse compute; BigQuery bills for bytes processed by queries (and flat-rate slots as an alternative).- Storage: raw + transformed copies, table snapshots, long-lived staging tables.- Maintenance/refresh: automatic materialized view or clustering maintenance, continuous streaming ingestion buffers.- Data egress and network: cross-region transfers or external extracts.Patterns that increase costs- Repeated full-table scans (SELECT * on large raw tables).- Frequent small ad-hoc queries that repeatedly rescan large partitions.- Large wide joins and unpruned aggregations done at query time.- Over-retaining raw + curated copies without lifecycle policies.- High-frequency incremental refreshes (e.g., materialized views refreshed too often).- Poorly sized compute (too-large warehouses, too many BigQuery slots idle).Practical tactics to reduce spend (without harming SLAs)- Pushdown & hybrid ETL: do cheap filters/joins upstream (ETL) for high-cardinality, expensive ops; keep lightweight transforms in-warehouse.- Partitioning / clustering: - BigQuery: use ingestion/partitioned columns and clustering to reduce bytes scanned. - Snowflake: choose clustering keys when pruning benefits outweigh maintenance cost (micro-partitioning often sufficient).- Incremental processing: use CDC or change tables so transforms operate on deltas, not full tables.- Materialized views & precomputed aggregates: - Use them for high-frequency queries; schedule refreshes aligned with SLA windows to avoid constant maintenance. - Monitor maintenance cost — some MVs can cost more than they save.- Result / query caching: tune cache TTLs and design workflows to reuse cached results for BI dashboards.- Right-size compute & auto-scaling: - Snowflake: use multi-cluster for concurrency but prefer smaller warehouses with auto-suspend and short auto-resume. - BigQuery: consider on-demand for spiky needs, flat-rate slots for steady heavy usage.- Optimize queries: avoid SELECT *, push predicates early, reduce shuffle by pre-aggregating, use approximate algorithms where acceptable.- Data lifecycle & compaction: archive cold raw data to cheaper storage (GCS/S3) and keep curated, query-hot datasets in warehouse.- Monitoring and governance: track query cost per user/job, set budgets, alert on anomalies, enforce cost-aware patterns (templated queries, query quotas).- Use cheaper storage formats: columnar and compressed file formats for external tables; store large raw logs in object storage and query with federated/preview when needed.Example SLA-friendly pattern- Ingest raw events into cheap object storage + landing table. Run hourly incremental Spark job to dedupe/compact and push only new partitions to warehouse. Maintain hourly materialized aggregates refreshed every 15m for dashboards. Keep auto-suspend warehouses and use clustering on time/user columns to ensure query pruning.Measure and iterate: quantify bytes scanned / compute seconds before and after each change; optimize where ROI (cost saved vs engineering cost) is positive.
Unlock Full Question Bank
Get access to hundreds of ETL vs. ELT Patterns and Processing Strategy interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.