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.

MediumTechnical

33 practiced

Compare the implications of using a lakehouse (Delta Lake or Iceberg) for ELT workloads versus a traditional cloud data warehouse. From a BI perspective, what changes in query patterns, data freshness, governance, and data mutation capabilities would you expect?

Sample Answer

High-level summary: A lakehouse (Delta Lake / Iceberg) blends data lake scale and formats (Parquet, object storage) with transactional metadata (ACID, time-travel, schema evolution). Compared with a traditional cloud data warehouse (Snowflake/BigQuery/Redshift), expect different operational trade-offs that affect how BI teams build dashboards, query patterns, SLAs for freshness, governance workflows, and mutation semantics.

Query patterns- Lakehouse: Queries often read large parquet files via compute engines (Spark, Trino, Databricks SQL). To get good performance, BI should rely more on well-designed partitioning, Z-order/clustering, file compaction, and materialized views or aggregated marts. Expect occasional higher latency on ad-hoc, highly selective queries unless you use caching (Photon, Delta cache) or create aggregates/OLAP tables. Pushdown and predicate pruning matter a lot.- Warehouse: Optimized for low-latency ad-hoc and concurrency out of the box; less engineering needed for partitioning/compaction. BI can run many small, selective queries without as much tuning.

Data freshness- Lakehouse: Excellent for ELT and streaming/CDC flows—transactions commit to the metadata meaning near-real-time visibility is possible. However, freshness seen by BI depends on ingestion job cadence, file commit/compaction, and when compute clusters pick up new snapshots. Time-travel lets you reproduce historical states.- Warehouse: Fast ingestion via loading APIs and typically immediate visibility; simpler SLA for dashboard freshness. Warehouses often ingest micro-batches or streaming with managed latency.

Governance & security- Lakehouse: Modern implementations support fine-grained ACLs, catalog-based governance (Unity Catalog, Iceberg + Ranger), row/column masking, and audit logs—but these need setup. Data lineage and schema evolution are powerful (you can preserve versions). Governance is more decentralized: data engineering often manages raw layers, BI teams curate marts.- Warehouse: Centralized access controls and simpler RBAC; many BI tools integrate natively with warehouse security and metadata. Governance is often more opinionated and turnkey.

Data mutation capabilities- Lakehouse: Delta/Iceberg support UPDATE/DELETE/MERGE and time-travel, enabling CDC-style workloads and easier slowly changing dimensions. But these operations are implemented as file-level rewrites and can be expensive; frequent small updates require compaction and maintenance (VACUUM/optimize). Expect higher operational overhead.- Warehouse: DMLs are usually instant and optimized for row-level operations; less manual maintenance required.

Practical implications for a BI analyst- Build aggregated marts or materialized views in the lakehouse for interactive dashboards; avoid many small, selective queries against raw zone.- Coordinate with data engineers on partitioning, compaction schedules, and cache strategies to meet dashboard latency SLAs.- Leverage time-travel for reproducible reports and debugging; use snapshot/version tags for regulatory reports.- Expect to participate in governance (catalog, data contracts) to ensure trusted metrics.- Monitor cost/performance: queries on object storage can be cheaper per TB but may need larger compute for complex queries.

Example pattern:- Use ELT to land raw events in Delta, run nightly incremental transforms to create a denormalized BI table (partitioned by date, Z-ordered on customer_id), expose that table to Looker/Power BI, and maintain a small set of incremental materialized aggregates for high-concurrency dashboards.

In short: lakehouses give flexibility, scale, time-travel, and stream-native ELT, but require more operational practices (compaction, clustering, caching, governance setup). Warehouses give more predictable low-latency BI with less engineering, at potentially higher per-GB cost.

HardSystem Design

18 practiced

Architect an enterprise-grade ELT platform for a multi-business-unit company ingesting varied data types (events, transactional records, third-party CSVs). Outline ingestion, storage tiering (raw/staging/curated), transformation layers, governance/metadata, data catalog, and self-service reporting. Explain how the platform supports both ETL and ELT patterns where needed.

Sample Answer

Requirements & constraints:- Ingest high-volume events (streaming), transactional RDBMS, and ad-hoc 3rd-party CSVs across BU boundaries.- Low-latency analytics for dashboards, audited lineage, role-based access, cost control, support both ELT (push SQL in warehouse) and ETL (server-side transforms when needed).- Scalable, cloud-native, secure, and self-service for BI analysts.

High-level architecture:- Ingest → Landing (raw) → Staging → Curated → Consumption (analytics/BI)- Core components: Event pipeline (Kafka/Kinesis), Ingest API/CDC (Debezium/Fivetran), Object storage data lake (S3/ADLS), Cloud data warehouse (Snowflake/BigQuery/Redshift), Orchestration (Airflow/Prefect), Transformation engine (dbt + Spark), Metadata catalog & governance (Amundsen/Collibra + Glue Data Catalog), Access & BI (Looker/Tableau/Power BI), RBAC & audit (IAM, Cloud KMS, CloudTrail).

Flow:1. Ingestion - Streaming events → Kafka → raw partitioned Parquet in S3 (time, source, BU). - Transactional DBs → CDC → raw transaction files & append-only change logs. - CSVs → Ingest UI/API or managed connectors → raw zone, schema inference & checksum.2. Raw (immutable): store original files with audit metadata (ingest time, source, checksum, schema snapshot).3. Staging: lightweight normalization (parquet, typed columns), basic validations, deduplication; orchestrated jobs tag bad records to quarantine.4. Curated: business-ready models built with dbt (SQL-first ELT) for dimensional and subject-area marts; for heavy transforms use Spark/EMR and write back to warehouse or lakehouse.5. Consumption: marts exposed as semantic layer (LookML/Power BI semantic model), materialized tables/views for fast dashboards; BI connects read-only with row/column-level security.

Support for ETL vs ELT:- ELT-first: prefer ELT via warehouse SQL/dbt for agility and BI analyst self-service (transform after load). Use warehouse compute for most transformations, incremental models, and testing.- ETL when required: for PII masking, complex streaming joins, heavy machine-learning feature engineering, or to reduce warehouse egress/costs—run pre-processing in Spark or an ETL service and load curated outputs to warehouse.- Orchestration allows hybrid pipelines and enforces SLA.

Governance & metadata:- Central metadata/catalog: automatic harvesting of schemas, lineage (source → raw → staging → curated), data quality metrics, freshness, ownership, SLAs.- Data contracts per source, schema evolution policies, and schema registry for events.- RBAC and encryption, audit logs, masking policies, approval workflows for publishing curated datasets.- Data quality: Great Expectations or built-in dbt tests; failed tests create incident tickets.

Self-service reporting for BI analysts:- Curated semantic layer with clear naming, examples, business definitions, and upstream lineage links in catalog.- Shared templates, sandbox schema per analyst with quota limits.- Automated refresh schedules & alerting for stale datasets.- Training and on-call data steward to support onboarding.

Scalability & cost controls:- Use serverless warehouse (auto-scaling) and lifecycle policies on the lake (hot/cold tiers), partitioning and clustering, materialized views for hot queries, quotas for sandboxes.

Observability & operations:- Central monitoring dashboard for pipeline health, SLA breaches, data quality, cost by BU.- Readiness checks and canary ingestion for schema changes.

Why this helps a BI Analyst:- Fast ELT flow and dbt models let analysts iterate on business logic with SQL, traceable lineage enables trust, curated semantic layer speeds dashboard building, sandboxing supports experimentation without impacting production, and governance ensures data correctness and access control.

HardSystem Design

31 practiced

Design an orchestration strategy for a mixed ETL+ELT environment with dependencies across batch jobs and streaming ingestion. Which orchestration tools would you evaluate (Airflow, Dagster, Conductor, etc.), and how would you implement retry/backoff, SLA enforcement, cross-job dependency handling, and support for manual approvals?

Sample Answer

Requirements & constraints:- Mixed ETL (transform before load) and ELT (load then transform) pipelines.- Batch jobs (daily/hourly) + streaming ingestion (Kafka).- Clear dependencies across jobs, SLA alerts, retries with backoff, manual approvals for sensitive steps.- BI-focused: produce reliable tables/views for dashboards (Looker/Power BI/Tableau).

High-level architecture:- Orchestrator(s): use a primary DAG-based orchestrator (Airflow or Dagster) plus streaming connectors (Kafka Connect / ksqlDB / Flink) for near-real-time work.- Metadata & state: centralized metastore (e.g., Glue/BigQuery/Redshift spectrum + Hive metastore) and lineage store (OpenLineage).- Executor/run infra: Kubernetes for scalable task execution, object storage for artifacts, cloud scheduler for cron.

Tool evaluation (pros/cons):- Airflow: mature, wide operator ecosystem (DB, cloud, Kubernetes), good for complex batch DAGs and cross-job dependencies. Weakness: streaming-native support limited.- Dagster: better type/asset-aware modeling, stronger testing and asset-level dependencies, built-in observability; good for BI asset lineage.- Conductor: scalable microservice orchestration, good for heterogeneous services; heavier to adopt for data teams.- Complement streaming tools: Kafka Connect for ingestion, ksqlDB/Flink for streaming transforms; use Kafka → materialized tables or CDC.

Implementation patterns- Cross-job dependency: model assets/targets instead of tasks. Use sensorless triggers where possible (Airflow deferrable operators / Dagster sensors) to avoid busy polling. For ELT, tasks emit lineage events (OpenLineage) so downstream jobs subscribe to completion.- Retry & backoff: exponential backoff with jitter; configurable per task. Example: Airflow’s retries=3, retry_delay=timedelta(minutes=5), exponential_backoff=True. For streaming connectors use connector-level retries + DLQ to avoid blocking.- SLA enforcement & alerting: define SLAs per dataset and per job. Use orchestrator SLA callbacks (Airflow on_failure_callback/on_retry_callback) + monitoring (Prometheus + Alertmanager, or cloud-native alerts). If SLA breached, escalate via Slack/email and optionally pause downstream DAGs.- Manual approvals: model gating tasks that require human approval. Implement via: - Airflow: ShortCircuitOperator or ExternalTaskSensor awaiting approval flag in DB/feature store plus a UI button to set approved = true. - Dagster: use "runs" blocking step and the Dagit UI for materializations requiring approval. - For stricter audits, integrate with Jira/ServiceNow to create ticket and resume on ticket transition via webhook.- Idempotency & checkpointing: write atomic commits to staging tables; use CDC/watermarks for streaming to support re-runs without double-loads.- Backfill & recompute: asset versioning; provide DAGs to rebuild assets with date range parameters; isolate impacts on production dashboards.

Operational concerns & trade-offs:- Use Airflow if team needs many prebuilt operators and cloud compatibility; prefer Dagster if you want asset-aware development and better testing/lineage for BI datasets.- Keep streaming and batch responsibilities separated: use streaming systems to produce materialized incremental tables; orchestrator triggers downstream batch aggregations.- Ensure RBAC for manual approvals and audit logs for compliance.

Example Airflow retry config:

python

default_args = {
  "retries": 3,
  "retry_delay": timedelta(minutes=5),
  "retry_exponential_backoff": True,
  "max_retry_delay": timedelta(hours=1),
}

This design prioritizes observable, asset-centric orchestration, robust retry/backoff, SLA alerting, and human-in-the-loop approvals—delivering reliable data for BI dashboards.

MediumTechnical

19 practiced

Discuss the trade-offs of denormalizing data (star schema) in ELT for BI consumption versus keeping normalized schemas. When would you denormalize to speed queries versus normalize to save storage and reduce update complexity?

Sample Answer

Situation/context (short): As a BI analyst you choose models that balance fast dashboard queries, storage costs, data freshness and maintenance effort. Below is a practical trade-off guide.

Trade-offs — star-schema (denormalized) vs normalized- Query performance: Star schemas (fact table + wide dimension tables) reduce joins, simplify SQL and greatly speed dashboard queries—especially for BI tools that issue many ad-hoc aggregations. Normalized schemas require multi-way joins which slow interactive queries.- Storage & redundancy: Denormalization duplicates attributes (higher storage, potential inconsistencies). Normalized schemas minimize storage and avoid duplicated master data.- Update complexity: Denormalized data needs more careful update/refresh logic (rebuilds or change propagation). Normalized models make updates simpler and transactional integrity easier.- Freshness & ELT cost: Wide denormalized tables can be expensive to rebuild frequently; normalized can allow targeted updates. On cloud columnar warehouses, compression often mitigates storage cost and wide tables can still be efficient.- Governance & lineage: Normalized source preserves clear canonical records; denormalized marts require lineage and SCD handling to ensure trust.

When to denormalize (practical signals)- Dashboards need sub-second interactivity with many joins or complex aggregations.- BI team wants simpler SQL for users and faster development.- Data volume is manageable or cloud warehouse compression/partitioning keeps cost acceptable.- You can accept periodic batch refreshes or implement incremental materialized views.

When to keep normalized- Source of truth must be authoritative; updates are frequent and need minimal duplication.- Storage cost or regulatory constraints demand single canonical records.- You need fine-grained OLTP-style updates, or transformation complexity would be high to keep denormalized copies consistent.

Patterns & mitigations- Hybrid: keep normalized source-of-truth, expose denormalized materialized views / daily marts for BI. Use incremental ELT, CDC, or SCD2 for dimensions.- Use columnar warehouses (BigQuery/Snowflake) and clustering/partitioning to reduce rebuild cost.- Automate lineage tests and reconciliation jobs to catch drift from denormalized copies.- Consider aggregate tables or BI-layer semantic models (LookML, Power BI datasets) as a lighter-weight denormalization.

Recommendation: For BI dashboards prioritize denormalized star schemas when interactive performance and user simplicity matter; keep normalization for transactional systems and as the canonical source, and automate ETL/ELT patterns to manage the trade-offs.

MediumTechnical

17 practiced

Discuss how schema evolution and data contracts influence the choice between ETL and ELT. As a BI Analyst, how would you enforce schema contracts or monitor schema changes while preserving flexibility for fast experimentation by analysts?

Unlock Full Question Bank

Get access to hundreds of ETL vs. ELT Patterns and Processing Strategy interview questions and detailed answers.

Join thousands of developers preparing for their dream job.