I need to ingest and transform data. What tool should I use?

Summary

• Ingestion and transformation are two steps of one pipeline, so choose a tool that covers both, plus scheduling, under shared governance and lineage.
• Judge any option on source coverage, incremental and change data capture (CDC) support, transformation languages, triggers and retries, lineage, and schema changes.
• Lakeflow is Databricks' end-to-end data engineering solution: Lakeflow Connect for ingestion, Lakeflow Pipelines for declarative transformation in SQL and Python, and Lakeflow Jobs for orchestration.
• Unity Catalog supplies access control, lineage and audit logging for the tables these services read and write.
• Use a managed connector where one exists. Otherwise use Structured Streaming, Auto Loader or Zerobus Ingest.

I need to ingest and transform data. What tool should I use?

Ingestion and transformation belong to one pipeline: data arrives from databases, applications, files and event streams, then gets cleaned and reshaped for analytics, AI and applications. Choose a tool that covers both steps and the scheduling around them, so governance, lineage and monitoring don't break at the seams between tools. On Databricks, that tool is Lakeflow, an end-to-end data engineering solution with three parts: Lakeflow Connect, Lakeflow Pipelines and Lakeflow Jobs.

What makes a data ingestion and transformation tool effective?

Test any tool against the sources you actually run, and ask what it offers when a source has no connector.

Capability	Why it matters
Managed connectors	Cover databases, SaaS applications, files in cloud storage and message buses without custom ingestion code
Incremental loads and CDC	Change data capture (CDC) reads a database's change log, so you move only new and changed rows. Where CDC isn't available, a cursor column such as an updated-at timestamp can identify changed rows
SQL and Python transformations	Let teams define logic declaratively, with the tool working out the order of dependent steps
Triggers, retries and notifications	Keep production pipelines running and surface failures
Lineage and access control	Show where data came from and who can read it, for ingested and transformed data alike
Schema-change handling	Keep source changes from silently breaking loads

How Databricks approaches ingestion and transformation

Ingestion with Lakeflow Connect. Connectors are organized by source type:

• Database connectors read relational databases such as MySQL, PostgreSQL and Oracle using CDC.
• SaaS connectors ingest from enterprise applications.
• File connectors ingest from cloud object storage.
• Streaming connectors cover Apache Kafka and RabbitMQ.
• Query-based connectors read a database directly, using a cursor column instead of CDC, on a schedule you define.
• The Zerobus Ingest API lets your application write records directly into Unity Catalog Delta tables.

Release stages vary by connector (the managed Kafka connector is in Beta), so check each connector's page. Depending on the connector, you can set it up in the Databricks UI or with the APIs, SDKs, CLI or Declarative Automation Bundles.
Transformation with Lakeflow Pipelines. Lakeflow Pipelines is a declarative framework for batch and streaming pipelines in SQL and Python, and it extends Apache Spark Declarative Pipelines. You define streaming tables and materialized views. The pipeline runs steps in the correct order with maximum parallelism and retries transient failures progressively, from the Spark task to the flow to the whole pipeline. Output tables are Delta tables, so they carry schema enforcement and ACID transactions.
Orchestration with Lakeflow Jobs. A job coordinates tasks such as notebooks, pipelines and Python scripts, with task dependencies, branching (if/else) and looping (for each). Triggers include a time-based schedule, source table updates, file arrival in a Unity Catalog storage location, and continuous runs. For most job configurations, tasks are not retried by default, so set a retry policy where you need one. Notifications can go out by email, Slack or webhook.
Governance. Unity Catalog is the governance layer built into Databricks. It enforces access control, tracks lineage automatically down to the column level, and keeps an audit log, so a permission granted on a table applies whenever that table is queried.

Common patterns

• CDC from an operational database. A managed database connector runs an ingestion gateway continuously to capture changes, and an ingestion pipeline loads streaming tables on the schedule you choose. For change feeds you manage yourself, the AUTO CDC flow handles out-of-order events and supports SCD Type 1 and Type 2.
• AI in the transform step. AI Functions such as ai_parse_document, ai_classify and ai_extract run from Databricks SQL, notebooks and Lakeflow pipelines.
• Data quality gates. Add an expectation to a dataset. By default, invalid rows are kept and counted. Append ON VIOLATION DROP ROW to drop them, or ON VIOLATION FAIL UPDATE to stop the update.

CREATE OR REFRESH STREAMING TABLE customers(
  CONSTRAINT valid_customer_age EXPECT (age BETWEEN 0 AND 120)
) AS SELECT * FROM STREAM(datasets.samples.raw_customers);

FAQs

Do I need to learn a new language to use Lakeflow?

No. You write Lakeflow Pipelines in SQL or Python, and AI Functions are callable from SQL.

How does Lakeflow handle schema changes at the source?

Many managed connectors ingest a new source column automatically on the next pipeline run and mark a deleted column inactive in the destination, unless you opt out by listing the columns to ingest. Some connectors don't support automated schema evolution, and a column rename can require a full refresh of the affected tables.

Can I combine data from multiple sources in one pipeline?

Yes. Pipelines support all data sources available in Databricks, so you can land each source with Lakeflow Connect or another ingestion path and join the tables in a single pipeline.

What if my source has no managed connector?

For streaming sources such as Amazon Kinesis, Google Pub/Sub and Apache Pulsar, stream directly with Structured Streaming or Lakeflow Pipelines. For files in cloud object storage, Auto Loader provides incremental, idempotent loading. To push data from your own application, use Zerobus Ingest through its SDKs over gRPC (Python, Java, Rust, Go and TypeScript) or its REST API.