What's the best open-source ELT tool?

Summary

• There is no single best open-source ELT tool, because ELT is three jobs covered by different projects: extract and load, transform, and orchestrate.
• For extract and load, Meltano is an MIT-licensed, Singer-based ELT engine and dlt is an Apache-2.0 Python library that loads data into well-structured datasets. Kafka Connect, Debezium and Apache NiFi cover event streams, change data capture and data flows.
• dbt Core handles the transform step with SQL select statements, data tests and generated documentation, and Apache Airflow orchestrates workflows that are defined in Python.
• On Databricks, dbt runs against compute or SQL warehouses through the dbt-databricks package, Auto Loader ingests new cloud storage files incrementally, and Lakeflow Jobs orchestrates the steps.

What's the best open-source ELT tool?

There is no single best open-source ELT tool, because ELT is several jobs. Extract and load move data from your sources into your data platform. Transform then runs inside that platform, and an orchestrator schedules the steps. Different open-source projects cover each job, so the practical question is which combination fits your sources, your destination and your team. Of the projects below, Meltano is the one whose docs describe it as an ELT engine; the others cover parts of the extract and load step or the steps around it.

Which open-source tool covers which ELT step?

Step	Tool	What its docs say	License
Extract and load	Meltano	The core Singer-based ELT engine; bring your own orchestration, storage and infrastructure	MIT
Extract and load	dlt	An open-source Python library that loads data from various, often messy data sources into well-structured datasets	Apache-2.0
Streaming extract and load	Kafka Connect, part of Apache Kafka	Continuously imports and exports data as event streams to integrate Kafka with existing systems such as relational databases	Apache-2.0
Change data capture	Debezium	An open source distributed platform for change data capture	Apache Software License 2.0
Data flows	Apache NiFi	A system to process and distribute data, with data provenance tracking and a browser-based interface	Apache License 2.0
Transform	dbt Core	You write SQL select statements, and dbt builds the models, runs data tests and generates documentation	Apache-2.0
Orchestrate	Apache Airflow	A platform to programmatically author, schedule and monitor workflows	Apache-2.0

How do I choose the right combination?

• Sources and destinations: Meltano supports Singer taps as extractors and Singer targets as loaders, which are executables that implement the Singer specification. Check that a tap exists for each source and a target for your destination.
• Where the transform runs: dbt works alongside your ingestion tools so you can transform data directly in your cloud data platform. It transforms data that is already in your platform, so pair it with an extract and load tool.
• Orchestration: Meltano Open leaves orchestration to you, and Meltano supports Apache Airflow out of the box. Airflow targets batch-oriented pipelines, and its README says it is not a streaming solution.
• How pipelines are defined: A Meltano project is a directory with a meltano.yml file, so you can use version control, code review and CI/CD. dbt models are primarily SQL select statements, and Airflow workflows are defined entirely in Python.
• Licensing: dbt Core is the Apache 2.0 licensed dbt-core project. dbt's current docs also describe dbt v1 as the original Python-based CLI, dbt v2 as the Rust-based generation, and dbt OSS as the Apache 2.0 distribution of the v2 engine.

How does Databricks approach open-source ELT?

You can run dbt against Databricks: the dbt-databricks package connects to Databricks compute or a SQL warehouse, and you can run dbt Core projects as Lakeflow Jobs tasks. Auto Loader incrementally and efficiently processes new data files as they arrive in cloud storage, through a Structured Streaming source called cloudFiles, with schema inference and evolution. Databricks recommends Auto Loader in Lakeflow Pipelines for incremental ingestion.
Lakeflow Jobs coordinates multiple tasks as one workflow, with time-based or event-based triggers. Airflow can orchestrate Lakeflow Jobs through the open source Databricks provider, which is not directly supported by Databricks. For popular enterprise sources, Lakeflow Connect offers fully managed connectors built on Lakeflow Pipelines.

FAQs

Is dbt enough for a complete ELT pipeline?

No. dbt covers the transform step. Its docs say it works alongside your ingestion, visualization and other data tools, so you still need an extract and load tool such as Meltano or dlt.

Do I need an orchestrator with Meltano?

Meltano Open is fully self-managed: you bring your own orchestration, storage and infrastructure. Meltano supports Apache Airflow out of the box as an orchestrator.

How does Apache Spark fit into ELT?

Spark SQL reads a variety of data sources through the DataFrame interface, including Parquet, ORC, JSON, CSV, XML, Hive tables and JDBC databases. A Spark job can load data and transform it in the same application.

Should I choose Meltano or dlt?

They are set up differently. A Meltano project is defined in a meltano.yml file, with Singer taps as extractors and Singer targets as loaders. dlt is a Python library that you install with pip install dlt and call from your own code. Pick the one that matches whether your team prefers project files or Python code.