ETL vs ELT for Data Engineering interviews

Why interviewers ask this

Picture the Tuesday morning loop at Snowflake, Databricks, or any data-platform team at Stripe: the hiring manager opens with a softball — "walk me through the difference between ETL and ELT, and which would you pick for our raw event stream?" It feels conversational, but the answer is doing three jobs at once. It probes whether you grasp the architectural split between compute and storage, whether you've built pipelines on modern cloud warehouses, and whether you can argue trade-offs without reciting a vendor brochure.

The split changed how data teams are staffed. Ten years ago you needed a dedicated ETL engineer who lived inside Informatica or DataStage. Today the same job is done by an analytics engineer writing dbt models in SQL against a warehouse that costs pennies per query. The economics flipped, and the interview question is really "do you understand the flip?"

Classical ETL

ETL stands for Extract → Transform → Load. The transform step happens on a dedicated middleware tier — historically Informatica PowerCenter, IBM DataStage, Talend Open Studio, or Microsoft SSIS — running on its own JVM or .NET cluster, separate from the target warehouse. Data was pulled from source systems, reshaped in RAM on the ETL server, and only clean, business-ready rows landed in Oracle, Teradata, or DB2.

Source systems  ──►  ETL engine (in-memory)  ──►  Target DWH
                          ▲
                          │
                  transformations happen here

The constraint that drove this design is dead now but worth naming: legacy warehouses charged dearly for compute. A complex join across a 200-million-row fact on a Teradata appliance in 2008 could cost more than the engineering salary of the person writing it. So teams pushed transforms out to cheaper Java middleware and kept the warehouse lean. The trade-off was operational pain — two systems, two schedulers, two metadata catalogs, and any schema drift broke the engine before data reached the warehouse.

A small but useful tell at interviews: candidates who've actually run Informatica will mention the PowerCenter repository and the pain of migrating mappings between environments. Candidates who learned the term yesterday won't.

Modern ELT

ELT flips the middle two letters: Extract → Load → Transform. Raw data lands in a cloud warehouse or lakehouse first, in a raw schema, with minimal touching — maybe type casting and column renaming. Then SQL transforms (usually via dbt) build cleaned, joined, business-ready tables inside the warehouse, using the warehouse's own compute.

Source systems  ──►  Data Lake / DWH (raw)  ──►  Transformations in-warehouse
                                                      ▲
                                                      │
                                              dbt, Spark SQL, ClickHouse SQL

The modern stack splits into three roles. Loaders — Fivetran, Airbyte, Stitch, Debezium — handle extraction with managed connectors that auto-adapt to schema changes. Warehouses or lakehouses — Snowflake, BigQuery, Databricks, ClickHouse, Redshift — provide elastic compute on cheap object storage. Transformation layers — dbt for batch SQL, Spark SQL for huge joins, Flink for streaming — produce the marts that BI tools query. The whole stack is glued by git, CI, and a metadata catalog.

The cultural shift matters too. In ETL shops, business logic lived inside proprietary mappings that only the vendor's UI could read. In ELT shops, business logic is SQL files in a git repo, reviewed in pull requests, tested with dbt test, and documented in YAML. That's why ELT migrations come bundled with the phrase "analytics engineering" — the discipline is a sibling of software engineering now, not a specialty buried in IT.

Why ELT won

The honest answer is cloud economics. Snowflake and BigQuery commoditized warehouse compute. A query that cost $40 of Teradata time in 2010 costs roughly $0.10 of Snowflake credit in 2026 on the same data volume. Once compute became cheap and elastic, there was no reason to keep a separate transformation engine alive. You were paying for two clusters when one would do.

Four forces compound the economic argument. First, schema-on-read beats schema-on-write for fast-moving sources — when a SaaS source adds a column, ELT absorbs it as a JSON blob and lets analytics engineers handle the contract change in dbt; ETL pipelines break at the engine and need a redeploy. Second, dbt standardized transformation as version-controlled SQL with tests, docs, and lineage; no equivalent open standard exists in the ETL world. Third, time-to-value is faster — land a new source in raw on day one, ship a first mart on day three. Fourth, reusability: one raw layer feeds many marts, while ETL transforms were tightly coupled to one target.

ETL still exists, and not just as legacy. On-prem regulated environments — large banks under data-residency rules, healthcare systems with HIPAA constraints, telcos with lawful-intercept obligations — often keep ETL because the cost equation hasn't flipped for them. If storage is cheap but every CPU minute on the warehouse is audited, pushing transforms to a cheaper tier still makes sense.

The medallion architecture

If the interviewer wants depth, the medallion pattern is where you go. Coined by Databricks but adopted widely, it organizes the warehouse into three layers — bronze, silver, gold — each with a clear contract.

Source ──► Bronze (raw)  ──►  Silver (cleansed)  ──►  Gold (curated)  ──►  BI / ML

Bronze holds raw data with the minimum possible transformation. Type casting, partitioning by load date, maybe deduplication on a primary key, but no business logic. If a downstream model is wrong, bronze is the rerun source — you do not re-extract from the production database. The discipline rule: bronze should be reproducible without touching the source.

Silver is the cleansed, deduplicated, joined layer. Currencies normalized, timestamps in UTC, soft deletes resolved, slowly-changing dimensions tracked. Silver is what analysts join against for ad-hoc questions. The contract here is uniqueness, completeness, and referential integrity — typically enforced via dbt tests.

Gold holds business-ready marts: star schemas, wide reporting tables, ML feature stores. Each gold table maps to one or two specific use cases and is optimized for them — pre-aggregated, partitioned by the access pattern, sometimes materialized. BI dashboards and ML training jobs read from gold; nothing else does.

In Databricks Lakehouse the layering is native — three Delta databases, often bronze, silver, gold. In Snowflake or Postgres, the same pattern is implemented with schemas (raw_, staging_, marts_) and dbt model directories. The naming is cosmetic; the contract is what matters.

Reverse ETL

The newest twist on the conversation is reverse ETL — moving curated data out of the warehouse and back into operational tools. Tools like Hightouch, Census, and Polytomic read from your gold layer and sync to Salesforce, HubSpot, Braze, Iterable, or ad platforms like Google Ads and Meta Ads Manager.

DWH (gold)  ──►  Reverse ETL tool  ──►  Salesforce / HubSpot / Ad platforms

The use cases are operational: a Customer 360 view built in dbt syncs into the CRM so sales reps see the same definition of "high-value account" as the analytics team. ML scores (churn risk, propensity to upgrade) push into the marketing automation tool so the campaign manager can target on them. Segment definitions flow into ad platforms for lookalike audiences. Without reverse ETL, you end up with one definition of a segment in the warehouse and a different, drifted definition in every operational system — the "data swamp" anti-pattern.

The interview-worthy detail: reverse ETL is still ELT in spirit. The transformation happened in the warehouse; reverse ETL is just the last-mile delivery. Treating it as a separate paradigm tends to confuse the conversation.

Common pitfalls

When teams migrate from ETL to ELT, the most common failure is keeping the old middleware "just for now." What was meant as a transition becomes a permanent fork: half the data flows through Informatica, half through Fivetran. The team now maintains both stacks and two on-call rotations. The fix is a hard cutover date per source — every source moves once and never half-moves.

The second pitfall is skipping the bronze layer to ship a gold table faster. A PM wants a dashboard by Friday, so an engineer pulls from the source directly into a gold mart. Three months later a column changes upstream and the entire mart has to be rebuilt from scratch because there's no raw replay layer. Bronze is insurance, not bureaucracy — its job is to survive the day your upstream source changes shape.

A subtler trap is landing raw data into a production schema. Bronze should be isolated — its own schema, its own database role, its own access policy. Otherwise an analyst running ad-hoc SQL joins against half-transformed data and produces numbers that don't match the official dashboard.

Then there is the unversioned transformation. Teams adopt Snowflake and dbt but write SQL transforms in the warehouse worksheet UI, save them as views, and never check them into git. Two months in, no one knows which view depends on which or whether staging matches production. The cure is uncompromising: every transform lives in a dbt model in a git repo, deployed via CI.

Another widespread mistake is testing only the gold layer. Tests belong on every layer: bronze gets sanity checks (row count not zero, schema as expected), silver gets uniqueness and completeness assertions, gold gets business rules (revenue non-negative, segment revenue sums to total). Layered tests tell you immediately where the break happened; gold-only tests turn every failure into a forensic exercise.

Finally, ignoring data lineage in a medallion architecture undoes most of its benefits. The whole point of three layers is traceability — knowing the gross_revenue column in marts.kpi_daily flows from silver.orders.amount which came from bronze.stripe_charges.amount_captured. Without a lineage tool (dbt docs, DataHub, Unity Catalog), three layers become three layers of confusion.

Related reading

• Airbyte vs Fivetran for Data Engineering interviews
• What is dbt — Data Build Tool
• dbt fundamentals for DE interviews
• BigQuery for Data Engineering interviews
• Databricks for Data Engineering interviews
• CDC and Debezium for DE interviews
• SQL for Data Engineer interviews

If you want to drill DE interview questions like this every day, NAILDD is launching with 1,500+ problems across SQL, Python, and data-platform design — exactly the patterns hiring managers at Snowflake, Databricks, Stripe, and Airbnb use.

FAQ

Is Spark an ETL or an ELT tool?

It depends on where you run it. Spark on a standalone staging cluster that reshapes data before loading into a warehouse is ETL — the transformation tier is separate from the destination. Spark inside Databricks Lakehouse is ELT — the same Spark engine is the warehouse compute, transforming data that already landed in Delta tables. Same tool, different role. The follow-up to ask in the interview: "Is the Spark cluster the storage layer or upstream of it?"

Is ELT slower than ETL on huge datasets?

Not on modern cloud warehouses. Snowflake's micro-partitions and BigQuery's Dremel execution are tuned for massive analytical scans in a way classical Java middleware can't match. For terabyte-scale joins, in-warehouse compute is usually faster than RAM-based ETL on a fixed cluster, because the warehouse can spin up dozens of nodes for the duration of the query. The exception is complex row-by-row logic that doesn't vectorize well — those cases sometimes favor Spark or Flink upstream.

When does Informatica still make sense in 2026?

When you have a large existing investment, a regulated environment, and no business case to migrate. Specifically: on-prem enterprise data centers with thousands of existing mappings, sectors with strict data-residency rules that block cloud warehouses, and teams whose ETL specialists are the only people who can read the legacy logic. Greenfield projects almost never start with Informatica — the default in 2026 is Fivetran or Airbyte plus dbt plus Snowflake/BigQuery/Databricks.

What's the relationship between ELT and the data lake?

Originally, "data lake" meant cheap object storage (S3, GCS) with Parquet or Avro files queried by Spark or Presto. Modern lakehouse architectures — Databricks Delta, Apache Iceberg, Apache Hudi — merge cheap storage with warehouse-style ACID transactions and SQL. ELT works natively on a lakehouse: bronze in raw Parquet, silver and gold in Delta or Iceberg tables.

Do I need a metadata catalog?

Once your warehouse passes roughly 200 models or 5 source systems, yes. Tools like DataHub, OpenMetadata, Unity Catalog, or Atlan give you column-level lineage, ownership, and freshness — without them, every "why did this number change?" question becomes a manual git archaeology session.

Is this official guidance?

No. This is field-level interview prep based on published material from dbt Labs, Databricks, and Snowflake. Verify specific vendor claims against current documentation before quoting them in a system design interview.