Megaphone

Free platform access and implementation support for your scoped 30-day POC.

Core Concepts

ETL Platform Guide: Types, Architecture and How to Choose

D

DataFuseAI Team

November 09, 2026 · 17 mins

ETL Platform Guide: Types, Architecture and How to Choose

Here is a failure an ETL platform should make impossible. A nightly load stops halfway, the scheduler retries it, and the morning report counts half of yesterday's orders twice. Nothing was wrong with the extract or the transform. The load step appended the same rows again.

An ETL platform is the software that runs extract, transform and load as a managed, repeatable job. It pulls data from sources, changes it with business rules in a dedicated engine, often through staging tables, and writes it to a target store [1]. The platform part is everything around those three steps: scheduling, retries, monitoring and access control. This guide covers the seven parts, the main types and how to choose, with a rerun test described in full in the methodology.

Who this is for: data engineers and analytics leads choosing, replacing or auditing the system that runs their pipelines. For the wider category that also covers matching, validation and governance, see our guide to what a data integration platform is.

Key takeaways

  • An ETL platform runs extract, transform and load on a schedule, and adds retries, monitoring and access control around them.
  • Seven parts decide how it behaves: connectors, extraction state, staging, the transform engine, the load step, orchestration, and monitoring with access and audit.
  • The load step's write mode decides what a retry does. In our illustrative test, appends committed in chunks left 1,500 rows where 1,000 belonged.
  • Small test runs can mostly measure overhead. In DataFuseAI's own benchmark, overhead fell from 70.8% of run time at 60,000 rows to 4.5% at 60 million [9].
  • Choose by where transformation must run, how incremental state is kept, and what a rerun does, before comparing connector lists.

Product documentation checked 7 November 2026.

What is an ETL platform?

An ETL platform is software that extracts data from sources, transforms it in its own engine and loads the result into a target, then runs that job on a schedule and recovers it when it fails. Microsoft's architecture guide describes ETL as consolidating "data from diverse sources into a unified data store", with transformation done "using a specialized engine" [1].

The three steps, in brief:

  • Extract reads data from source systems, such as databases, files and APIs. It can read everything each time, or only what changed since the last run.
  • Transform applies business rules. Microsoft lists filtering, sorting, aggregating, joining, cleaning, deduplicating and validating as typical operations [1].
  • Load writes the result to one or more target data stores [1], such as a warehouse or database, where reports and applications read it.

The rest of this guide is about what surrounds those steps, because that is where platforms differ.

ELT keeps the same steps and moves the transformation into the target. Microsoft says ELT "differs from ETL solely in where the transformation takes place" [1]. Many platforms now run both. Our ETL vs ELT guide covers when each fits, starting with compliance [12].

An ETL platform is also more than an orchestrator. Apache Airflow, for example, describes itself as "a batch workflow orchestration platform" [2]. It schedules and monitors tasks, while the transformation runs in whatever those tasks call. Where a standalone tool stops and a wider operations platform begins is covered in our post on ETL tools versus data operations platforms [16].

What are the seven parts of an ETL platform?

Any ETL platform, bought or built, can be described in seven parts. Five sit on the data path: connectors, extraction state, staging, the transform engine and the load step. Two wrap every run: orchestration with retries, and monitoring with access control and audit. The seven-part view is a DataFuseAI editorial framework, not an industry standard. Its use is to make sure no part goes unexamined.

Diagram of the seven parts of an ETL platform. Data flows left to right from sources through connectors, extraction, staging, transform and load into a target. Orchestration and retries sit above the flow, and monitoring, access and audit sit below it. The load step is highlighted.
Figure 1. The seven parts of an ETL platform. DataFuseAI editorial framework; staging and transform-engine terms follow Microsoft Learn.
Part What it does What to check
1 Connectors Hold credentials and drivers, and page through APIs Every source you need is listed, including custom databases or a generic API connector
2 Extraction state Records what was already read, so the next run reads only new data Where the state lives, and what a reset or rewind does. AWS Glue calls this a job bookmark, and it is off by default [3]
3 Staging Holds raw data while the run works on it [1] Whether a failed run leaves staging tables behind, and who clears them
4 Transform engine Applies the business rules Which engine runs them: in memory, Spark, or SQL in the warehouse [4] [5]
5 Load Writes the result to the target Which write modes exist (append, overwrite, upsert) and what each does on a retry
6 Orchestration and retries Schedules runs, orders steps and retries failures Whether one day can be rerun without touching the others [8]
7 Monitoring, access and audit Keeps run history, alerts, permissions and a change record Whether row counts are stored per run, and who can change or start a job

Source: DataFuseAI editorial framework; vendor behaviour as cited, checked 7 November 2026.

For the kinds of transformation part 4 handles (reshape, combine, filter, compute), see our guide to types of data transformations [17].

What happens when an ETL run fails halfway?

What happens depends on the load step's write mode. A plain append can leave duplicates after a retry. Overwriting a whole partition, or upserting on a unique key, can be run again safely. Apache Airflow's guidance says tasks "should produce the same outcome on every re-run", and warns that an INSERT on a rerun "might lead to duplicate rows" [8].

To show the difference, we loaded one day of 1,000 illustrative order rows into a SQLite table in four ways. In scenario A, the first attempt fails after 500 rows and the job retries the whole batch. In scenario B, the load succeeds and someone runs the same day again, as a backfill would. The correct result is 1,000 rows with an amount total of 500,500.

Load mode Rows after the retry (A) Amount total after the retry (A) Rows after a re-run (B) What readers see between the failure and the retry
Append, committed every 250 rows 1,500 625,750 2,000 500 of the day's rows
Append, whole batch in one transaction 1,000 500,500 2,000 The table as it was before the run
Overwrite the day, in one transaction 1,000 500,500 1,000 The table as it was before the run
Upsert on order ID, committed every 250 rows 1,000 500,500 1,000 500 of the day's rows

Source: illustrative sample data, computed in SQLite with Python on 7 November 2026. Not customer data.

Three findings follow from the test.

A single transaction fixes failed attempts, not reruns. Appending in one transaction survived the failure, because nothing was committed. It still doubled the day when the job was run again on purpose.

Overwrite and upsert survive both, with different risks. Overwriting a day is safe only when the batch holds the whole day; a partial batch deletes the rest. Upsert needs a key that is unique in the batch. PostgreSQL's upsert raises a cardinality violation if one statement would update the same row twice [7].

Some modes expose a half-finished day. Chunked appends and chunked upserts left 500 of the 1,000 rows visible until the retry finished. A report that ran in that window would have shown half a day.

Incremental extraction adds its own rerun rules. AWS Glue can rewind a job bookmark to reprocess earlier data, but it "does not clean the target files" when you do [3]. In dbt, an incremental model without a unique_key appends on most adapters, "without regard for whether the rows represent duplicates" [4]. Microsoft's guide notes that parallel work is typically designed "around data partition boundaries (date, tenant, shard key)" to enable idempotent retries [1].

Where does an ETL run spend its time?

In DataFuseAI's own benchmark, run on one 2-core, 16 GB node from PostgreSQL to PostgreSQL, overhead took most of a small run and target writes took most of a large one. In that benchmark, overhead took 70.8% of run time at 60,000 rows and 4.5% at 60 million. Writes grew from 12.3% to 57.1% over the same range [9]. That benchmark is first-party and has not been independently verified.

Rows (TPC-H scale) Read Transform Write Overhead
60,000 6.1% 10.8% 12.3% 70.8%
600,000 7.0% 14.5% 23.1% 55.4%
6 million 6.0% 31.0% 44.1% 18.9%
60 million 4.3% 35.1% 57.1% 4.5%

Source: DataFuseAI case study, 3 April 2026 [9]. Share of run time per phase, as published; the 60 million row sums to 101.0% in the source. First-party, not independently verified.

The runs used PostgreSQL as both source and destination, on one Databricks node with 2 cores and 16 GB of memory, across 31 pipeline runs [9]. At 60 million rows, destination writes were the bottleneck in 4 of the 7 pipelines and transformation in 3 [9].

Two lessons are worth testing on any platform. A test at a few thousand rows may mostly measure start-up time, so it can say little about production. And at scale, the target database's write capacity can matter as much as the ETL engine, which makes the load mode a performance choice as well as a correctness one. DataFuseAI's ETL proof-of-concept guide suggests testing at current volume and expected volume in 18 months [18].

What types of ETL platform are there?

ETL platforms come in six broad types: workflow orchestrators, managed cloud services, SQL transformation frameworks, enterprise integration suites, visual pipeline platforms and custom scripts. They differ most in where the transformation runs and who operates the infrastructure. One example of each is listed below, from its own documentation. The examples are illustrations, not rankings.

Type Example Where transformation runs Who runs the infrastructure Ask first
Workflow orchestrator Apache Airflow [2] In whatever each task calls: SQL, Spark or scripts Your team, or a managed service Is every task safe to re-run? [8]
Managed cloud ETL service AWS Glue, a serverless service whose ETL jobs run on Apache Spark [5]; Glue for Ray closed to new customers on 30 April 2026 [19] The service's engine The cloud provider Are job bookmarks on for each job? [3]
SQL transformation framework dbt incremental models, built as tables in the warehouse [4] Inside the warehouse Your warehouse Is unique_key set and free of nulls? [4]
Enterprise integration suite IBM DataStage, which supports ETL and ELT [6] Its parallel engine, or the target for ELT [6] Optionally, a managed control plane with an engine run where the data is [6] Where does the engine run, and what leaves your network?
Visual pipeline platform DataFuseAI (the publisher of this guide), with native, Databricks or Spark engines [10] The engine chosen in each engine profile [10] Managed, private-hosted or on-premise offline, by deployment option [15] Which write modes do targets support?
Custom scripts Python or SQL scripts run by cron Wherever the script runs Your team Who is alerted on failure, and does a rerun duplicate rows?

Source: each vendor's own documentation as cited, checked 7 November 2026. "Ask first" is DataFuseAI's editorial guidance.

For lists of specific products, see our guide to open-source data integration platforms, which checks each project's licence and activity.

How do you choose an ETL platform?

Choose an ETL platform by how it handles your constraints, not by its connector count. Start with where transformation must run and whether data may leave your network. Then check how it extracts incrementally and what each load mode does on a retry. Finally, test it at production volume. The six questions below are DataFuseAI's editorial checklist.

  1. Where must the transformation run? If rules require data to be cleaned or masked before it reaches the target, ETL is the safer fit; if the warehouse can do the work, ELT may be enough [1] [12].
  2. How does it extract incrementally? Ask where the state is stored, what a reset does, and whether the default is a full reload, as it is for AWS Glue [3].
  3. What does each write mode do on a retry and a rerun? Run the four-mode test above on a copy of one real table.
  4. Can you rerun one day without touching the others? Airflow recommends reading and writing "in a specific partition" for this reason [8].
  5. Where do the engine and control plane run? A managed control plane with a local engine is a different security review from a fully hosted service [6]. Our guide to control plane vs compute plane covers the trade-offs [20].
  6. Who is told when a run fails, and what is kept? Look for run history with row counts, alerts, and a record of who changed which job.

Then run a proof of concept with your own data at real volume, using the ETL proof-of-concept framework [18].

Mistakes to avoid

  • Trusting a retry setting without checking the write mode. A retry is only safe when the load can run twice; plain appends can duplicate rows [8].
  • Testing at a few thousand rows. At small volumes, overhead dominated DataFuseAI's benchmark, so the result may say little about production [9].
  • Rewinding incremental state without clearing the target. AWS Glue reprocesses from the rewound point but leaves earlier output in place [3].
  • Upserting on a key that isn't unique in the batch. PostgreSQL rejects the statement with a cardinality violation, and other engines may behave differently [7].
  • Choosing by connector count alone. A long list doesn't tell you where transformation runs or what a rerun does.

Where DataFuseAI fits (and where it doesn't)

DataFuseAI, the publisher of this guide, is a visual pipeline platform for scheduled batch work. Its engines page lists a native engine (marked Recommended), a Databricks engine and an Apache Livy engine for Spark, chosen per engine profile [10]. Its architecture page says jobs run pipelines "on a cron schedule", with execution history rolled into dashboards and logs [11].

Fits:

  • Scheduled batch pipelines that need run history and monitoring in one place; the architecture is "optimised for repeatable, stable, monitored batch workflows" [11].
  • Teams that want to reuse Databricks clusters or Spark through Livy for heavy transforms [10].
  • Sources its connectors page lists under database, cloud, file and transfer categories (the page advertises "50+ native integrations"), or APIs reached through its generic REST API connector [13].

May not fit:

  • Continuous event processing. The architecture page favours "scheduled runs with recorded history rather than continuous event processing" [11].
  • Teams that need ELT in production today. DataFuseAI's own ETL vs ELT post calls its ELT capability "in beta and not GA" [12].
  • SaaS sources such as a CRM with a pre-built connector. The connectors page lists databases, cloud services, files and a generic REST API connector, and no SaaS app connectors [13].

The architecture page doesn't describe target write modes or retry behaviour, so ask for both in an evaluation [11].

DataFuseAI's free 30-day POC covers one recurring workflow with two or three data sources. Applications are reviewed to confirm the workflow is suitable, and any external cloud or compute costs stay with you [14].

Frequently Asked Questions

Is Apache Airflow an ETL platform?

Not on its own. Airflow describes itself as "a batch workflow orchestration platform" for developing, scheduling and monitoring workflows [2]. It decides when tasks run and in what order. The extract, transform and load logic lives in the tasks, which call SQL, Spark or scripts. A full ETL setup pairs it with something that does the transformation, called from its tasks.

Is ETL still used now that ELT exists?

Yes. ELT moves transformation into the target, and Microsoft notes it "only works well when the target system is powerful enough to transform the data efficiently" [1]. ETL still fits when data must be cleaned or masked before it lands, or when the target can't do heavy transforms. Several platforms, including IBM DataStage, support both patterns [6].

Can an ETL platform handle real-time data?

It depends on the platform. Airflow says plainly that it "is not a streaming solution" [2]. DataFuseAI's architecture page favours scheduled runs over continuous event processing [11]. If you need events processed as they arrive, look at a streaming system, and use the ETL platform for the scheduled loads around it.

Do I need an ETL platform if I already use dbt?

Possibly not for transformation. dbt builds incremental models as tables inside your warehouse and transforms only the rows you filter for on each run [4]. You still need something to extract data from sources, load it into the warehouse and schedule the runs. That might be a managed loader, an orchestrator, or a platform that covers all three.

Methodology

What we read. We read Microsoft Learn's ETL guide, Apache Airflow's documentation and best practices, AWS Glue's documentation, dbt's incremental model guide, PostgreSQL 18's INSERT reference and IBM's DataStage product page. We also read DataFuseAI's own product pages and posts. All of it was read on 7 November 2026 between about 08:12 and 08:20 UTC. We re-checked Microsoft's ETL guide and read AWS's Glue for Ray notice at about 08:28 UTC.

The rerun test. We loaded 1,000 invented order rows (order IDs 1 to 1,000, amounts 1 to 1,000) into an in-memory SQLite table in four write modes. Each mode ran two scenarios: a first attempt that failed after 500 rows followed by a full retry, and a successful load followed by a full re-run. The figures come from that script, rerun_demo.py. It is illustrative and shows the logic, not the behaviour of any named product. SQLite's upsert does not raise PostgreSQL's cardinality error, so that point is cited, not tested.

The benchmark figures are quoted from DataFuseAI's case study, not recomputed or re-run. Its known issues (a missing run at the largest tier, a phase row that sums to 101.0%, and headline multipliers that don't follow from its own percentages) are why this guide quotes only the phase shares and bottleneck counts.

References

  1. Microsoft Learn, Azure Architecture Center, "Extract, transform, and load (ETL)", updated 30 Oct 2025. https://learn.microsoft.com/azure/architecture/data-guide/relational-data/etl
  2. Apache Airflow documentation, "What is Airflow?", read 7 Nov 2026. https://airflow.apache.org/docs/stable/
  3. AWS Glue Developer Guide, "Tracking processed data using job bookmarks", read 7 Nov 2026. https://docs.aws.amazon.com/glue/latest/dg/monitor-continuations.html
  4. dbt documentation, "Configure incremental models", last updated 13 Jul 2026. https://docs.getdbt.com/docs/build/incremental-models
  5. AWS Glue Developer Guide, "What is AWS Glue?", read 7 Nov 2026. https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html
  6. IBM, "IBM DataStage" product page, read 7 Nov 2026. https://www.ibm.com/ca-en/products/datastage
  7. PostgreSQL 18 documentation, "INSERT", read 7 Nov 2026. https://www.postgresql.org/docs/18/sql-insert.html
  8. Apache Airflow 3.4.0 documentation, "Best Practices", read 7 Nov 2026. https://airflow.apache.org/docs/apache-airflow/stable/best-practices.html
  9. DataFuseAI, "Case Study: How DataFuseAI Processed 60 Million Rows Without a Single Line of Code", 3 Apr 2026. First-party benchmark. https://www.datafuseai.com/blogs/case-study-how-datafuseai-processed-60-million-rows-without-a-single-line-of-code
  10. DataFuseAI, "Processing Engines" product page, read 7 Nov 2026. https://www.datafuseai.com/platform/engines
  11. DataFuseAI, "Platform Architecture" product page, read 7 Nov 2026. https://www.datafuseai.com/platform/architecture
  12. DataFuseAI, "ETL vs ELT Pipeline Architecture: How to Choose", 31 Mar 2026. https://www.datafuseai.com/blogs/etl-vs-elt-pipeline-architecture
  13. DataFuseAI, "Data Connectors" product page, read 7 Nov 2026. https://www.datafuseai.com/platform/data-connectors
  14. DataFuseAI, "Free 30-Day Data Integration POC" page, read 7 Nov 2026. https://www.datafuseai.com/free-poc
  15. DataFuseAI, "Deployment Options" product page, read 7 Nov 2026. https://www.datafuseai.com/platform/deployment-options
  16. DataFuseAI, "What Is a Data Operations Platform? ETL Tool vs Platform", 1 Mar 2026. https://www.datafuseai.com/blogs/what-is-data-operations-platform
  17. DataFuseAI, "Types of Data Transformations in ETL Pipelines", 3 Aug 2026. https://www.datafuseai.com/blogs/types-of-data-transformations
  18. DataFuseAI, "ETL Proof of Concept: A Five-Phase Evaluation Framework", 6 Mar 2026. https://www.datafuseai.com/blogs/etl-proof-of-concept
  19. AWS Glue Developer Guide, "AWS Glue for Ray end of support", read 7 Nov 2026. https://docs.aws.amazon.com/glue/latest/dg/awsglue-ray-jobs-availability-change.html
  20. DataFuseAI, "Control Plane vs. Compute Plane in Data Pipelines", 25 Aug 2026. https://www.datafuseai.com/blogs/control-plane-vs-compute-plane

Ready to transform your data workflows?

Explore all the capabilities of DataFuseAI, absolutely free of cost.