Ingestion with Auto Loader and Lakeflow Connect

Get the data in once, and only once

Ingestion looks simple: files land, you read them. Then a source sends the same file twice, adds a column on a Friday, or stops for three days and sends everything on Monday. A good ingestion design survives all three without a person at the keyboard.

In this lesson we pick the right tool for each source type, explain how Auto Loader tracks files, and set out the rules for schema changes and landing zones. We do not cover transformation logic. That belongs in silver, and lesson 3 covers it.

Choose the tool by source type

Databricks offers two managed routes into the lakehouse.

Lakeflow Connect is the managed ingestion service. The documentation groups connectors by source type: database connectors using change data capture (for example MySQL, PostgreSQL and SQL Server), query-based database connectors, SaaS connectors (for example Salesforce, HubSpot, Jira and Workday), file connectors (for example Google Drive and SharePoint), streaming connectors, and community or custom connectors. Pipelines are governed by Unity Catalog and run on serverless compute, and after the first full load they read only changes. The documentation says connectors are in various release states and points to a release types page, so check the status of the exact connector you plan to use before you commit to a production date.

Use the managed connector when one exists for your source. You avoid writing and operating your own extraction code, which is the part of a pipeline that breaks most often.

How Auto Loader works

Auto Loader is a Structured Streaming source named cloudFiles. Given an input location, it processes new files as they arrive. Three mechanisms matter.

Checkpoint. Auto Loader stores metadata about the files it has discovered in a RocksDB key-value store in the checkpoint location. That is what gives you exactly-once processing: after a failure, the stream resumes from the stored state and does not re-read files it has handled. Never delete or share a checkpoint casually. A new checkpoint means a full re-read.

File detection mode. Directory listing is the default. File notification mode with file events avoids listing a large directory and reduces cloud costs. The documentation says Databricks recommends file notification mode using file events for most workloads. For a landing directory with a modest number of files, the default is fine to start with.

Schema handling. Auto Loader can infer the schema, detect schema drift, notify you when the schema changes, and rescue data that would otherwise be dropped. In Lakeflow pipelines you do not set a schema location or a checkpoint location yourself, because the pipeline manages them.

# Schematic Auto Loader read and write. Option names can change between runtime versions,
# so check the Auto Loader options reference before use.
df = (
    spark.readStream
         .format("cloudFiles")
         .option("cloudFiles.format", "json")
         .option("cloudFiles.schemaLocation", "/Volumes/dev_sales/landing/_schemas/orders")
         .load("/Volumes/dev_sales/landing/orders/")        # the landing volume
)
 
(
    df.writeStream
      .option("checkpointLocation", "/Volumes/dev_sales/landing/_checkpoints/orders")
      .trigger(availableNow=True)                           # process what is there, then stop
      .toTable("dev_sales.bronze.orders_raw")
)

The availableNow trigger is a good default for scheduled ingestion: it processes everything that has arrived and then stops, so the job costs compute only while it works. For low-latency needs, run the stream continuously and accept the higher cost.

Landing zones as contracts

Treat the place where files arrive as a contract with the supplier.

• One volume or folder per source and entity. Mixed formats in one folder make schema inference guess.
• Immutable files. A supplier that overwrites a file breaks exactly-once logic. Agree on new file names for every delivery, for example with a timestamp.
• Write-only for the supplier, read-only for engineers. The ingestion identity reads the landing zone. Suppliers write into it with a scoped identity and cannot read other suppliers' files.
• Private network paths. Storage accounts take private endpoints, and public access stays off, as in our delivery standards.
• A retention rule. Decide how long raw files stay. Bronze may hold the data, but files in landing are still personal data if the content is.

Schema changes without panic

Sources change. Decide in advance what each kind of change does.

The principle from lesson 1 helps here: bronze stays loose, silver enforces. A source that changes a type then breaks a silver expectation that tells you so, and not your ingestion.

Metadata you should always add

Add load metadata at the bronze layer: the source file path, the load timestamp and a batch or run identifier. It costs almost nothing, and it is the first thing you need when someone asks "where did this row come from?".

Common mistakes

• Writing your own incremental logic where a managed connector exists.
• Sharing one checkpoint between two streams.
• Overwriting landed files and wondering why data is missing.
• Letting the schema silently change with no alert.
• Full refresh as a habit. It hides a design problem and costs compute every run.

Summary

Use Lakeflow Connect for databases and SaaS sources, Auto Loader for files. Treat the landing zone as a contract with immutable files and scoped identities. Keep bronze loose, add load metadata and decide in advance what each schema change does.

Next lesson: declarative pipelines, where ingestion and transformation meet.