Databricks Data Ingestion Patterns and Lakeflow Connect: Choosing an Ingestion Method (Data Engineer Associate)

Databricks ingestion follows three patterns. Batch loading reads a complete dataset and writes it in one run, usually replacing the target. Incremental loading processes only the data that arrived since the last run, so each run costs what the new data costs. Streaming ingestion processes data continuously as it arrives, for latency measured in seconds. The right pattern follows from the shape of the delivery and how fresh the data must be.

Incremental loading does not decide on its own how records are applied. If each delivery contains only new records, append them. If it contains new and changed records for a key (for example an hourly extract of customers created or changed in that hour), apply each file as an upsert, a MERGE on the key (or AUTO CDC in a declarative pipeline), so the table holds one current row per key. Replacing the table with a partial extract would delete every unchanged customer, and appending would create a second row for each updated one.

Incremental and streaming are the same engine in Databricks: a streaming read (Auto Loader, a Kafka source, STREAM read_files) remembers its progress, and the trigger decides whether it runs continuously or in scheduled bursts. The next two sections cover that choice and the checkpoint that makes it work.

Choose triggered execution when data can wait minutes or hours, and continuous execution only when it must land within seconds. A triggered run processes everything that has arrived since the last run and then stops, so you pay for compute only while it works; a continuous run keeps compute up all the time.

(spark.readStream.format("cloudFiles")
   .option("cloudFiles.format", "json")
   .option("cloudFiles.schemaLocation", "/Volumes/main/raw/_meta/orders_schema")
   .load("/Volumes/main/raw/orders/")
 .writeStream
   .option("checkpointLocation", "/Volumes/main/raw/_meta/orders_ckpt")
   .trigger(availableNow=True)
   .toTable("main.bronze.orders"))

A long processingTime trigger (say, one hour) only spaces out micro-batches; the cluster still stays up between them, so it barely saves money. availableNow is different: the query ends when it has caught up, and the job's compute shuts down. Pick the schedule from the freshness requirement: a once-a-day delivery needs one triggered update after it lands and before its consumers read it; a freshness target of N minutes needs a triggered update at least every N minutes, not a continuous pipeline. For a message bus, keep the interval well inside the source's retention period, so no record expires before a run reads it.

Two pipeline behaviours matter when you change things later:

• A normal update is incremental. If you change a streaming table's query (for example to fix a parsing bug), the new logic applies only to rows processed from then on; rows already in the table are not recomputed. Switching to continuous mode changes only how often updates run, not what counts as already processed.
• A full refresh reprocesses everything. It clears the streaming table and its streaming state and requeries all data still available in the source. Use it deliberately, for example to apply a fix to historical rows when every source file still exists. Never schedule full refreshes as the routine load: each run reprocesses all history, and rows whose source files have since been deleted are lost.

The checkpoint is what makes incremental and streaming ingestion exactly-once. For every committed micro-batch, a Structured Streaming query records its progress in the checkpoint location: for Auto Loader, the files it has discovered and processed; for Kafka or Kinesis, the offsets or sequence numbers it has read. Combined with Delta Lake's transactional writes, a stream that stops halfway (for example because its cluster was terminated) restarts from the last committed batch without loading a file twice or skipping one.

• A new or deleted checkpoint starts from scratch. Point a stream at an empty checkpoint path (easy to do when copying or moving a job) and Auto Loader treats every existing file as new. An append-only target then holds every historical record twice. Reset a checkpoint only on purpose, together with the target table. Creating a new checkpoint on every scheduled run turns incremental loading into repeated full loads with duplicates.
• The schema location is not the checkpoint. Auto Loader's cloudFiles.schemaLocation stores inferred schemas and their evolution, not ingestion progress. Nothing is written back to the source files, and the cluster event log plays no part in resuming a stream.
• Some options only count on the first start. cloudFiles.includeExistingFiles (default true) decides whether files already in the path when the stream first starts are loaded. It is evaluated only then; changing it on a stream with an existing checkpoint has no effect. To reload old files into a streaming table, run a full refresh (or start a new checkpoint with a cleared target).
• Deleting rows does not rewind progress. If you delete rows from a streaming table, the checkpoint still marks their files as processed, so a normal update will not reload them.

Streaming tables in Lakeflow Spark Declarative Pipelines manage their checkpoints for you, which is one reason they are the recommended way to ingest incrementally.

In SQL, the recommended way to load only new files is a streaming table over STREAM read_files(...). read_files is a table-valued function that reads files from a volume or cloud storage path; wrapped in STREAM inside a streaming table, it becomes an incremental source (Auto Loader underneath) with its own checkpoint, so each refresh appends only files it has not seen.

CREATE OR REFRESH STREAMING TABLE sales.bronze.orders
AS SELECT * FROM STREAM read_files('/Volumes/sales/raw/orders/', format => 'json');

Schedule the refresh (for example with SCHEDULE EVERY 1 HOUR or a job) and each run picks up new files once. The same read_files call used without STREAM behaves very differently:

This matters when the landing zone has a retention rule (files are deleted after some period) and the table must keep every row ever received: only the streaming table appends each file once and keeps its rows afterwards, provided nobody runs a full refresh.

COPY INTO is the older SQL path. It is a retriable, idempotent command that loads files from a cloud storage path or volume into an existing Delta table and records which files it has loaded, so a rerun skips them. It suits occasional loads of up to thousands of files, scheduled from SQL. Databricks now positions streaming tables as the scalable SQL replacement, and Auto Loader for directories of millions of files. COPY INTO syntax, Auto Loader options and schema evolution are covered in the lesson on incremental file loading with COPY INTO and Auto Loader.

Volume and arrival frequency usually decide between the file-loading methods. Both COPY INTO and Auto Loader load only new files, but they scale differently: Databricks recommends COPY INTO for directories of thousands of files and Auto Loader for millions of files or more and for files that arrive continuously.

When a COPY INTO job slows down because its directory keeps growing, more compute only treats the symptom, and a PATTERN filter does not help either: COPY INTO still lists the directory and checks candidates against its load history, so run time keeps growing with the directory. Moving to Auto Loader fixes the scaling problem. Then choose the trigger separately: if users need hourly data and cost matters, run Auto Loader as a triggered, hourly update rather than continuously.

For a small file on your own machine, use the workspace's create or modify table upload page: it accepts small CSV, TSV, JSON, Avro, Parquet and text files, previews the schema and writes a managed Delta table in a catalog and schema you choose. You need CREATE TABLE (with USE CATALOG and USE SCHEMA) on the destination and a running compute resource. It is ideal for small, one-off reference data and wrong for anything recurring: it is a manual step, does not leave you a governed raw copy of the file and does not turn documents such as PDFs into tables.

For larger files, or when the raw file must be kept, put the file in a Unity Catalog volume and load it from there:

• A volume's upload button in Catalog Explorer is convenient but has a per-file size limit. For larger files, use the Databricks CLI or SDK, which write to /Volumes/<catalog>/<schema>/<volume>/ paths and are limited only by what the cloud storage supports.
• Then create the table from the volume path with read_files, COPY INTO or Auto Loader. The raw file stays in the governed volume, which satisfies an audit-copy requirement.
• Splitting a large file by hand to fit a browser upload is error-prone and still leaves no governed raw copy.

databricks fs cp ./vendor_extract.parquet dbfs:/Volumes/main/landing/vendor/vendor_extract.parquet

Do not use the DBFS root or the old FileStore folder as a landing zone. The DBFS root is a legacy, workspace-level location that Unity Catalog does not govern, so files there sit outside Unity Catalog grants, audit and lineage.

Lakeflow Connect is Databricks' set of ingestion connectors, in two families. Standard connectors are the building blocks you configure yourself: Auto Loader for files and Structured Streaming or declarative pipelines for message buses. Managed connectors are fully managed ingestion from enterprise applications, databases and file services, where Databricks handles authentication, incremental reads, schema evolution and retries.

Streaming sources

Message buses are read with Structured Streaming (spark.readStream.format("kafka"), "kinesis", and so on) or declaratively as a streaming table in a pipeline. They are not read with JDBC, COPY INTO, Auto Loader (which reads files, not streams) or Lakehouse Federation. A declarative pipeline is the more managed choice: it handles checkpoints, retries and orchestration, and lets you declare data quality rules as expectations on the streaming table. Raw Structured Streaming gives full control, but you code validation (for example in foreachBatch) and manage checkpoints and retries yourself.

SFTP

The SFTP connector extends Auto Loader: you create a Unity Catalog connection holding the SFTP credentials and point cloudFiles at an sftp:// path, getting incremental, exactly-once loads directly from the server with no copy step. COPY INTO and spark.read do not support SFTP sources.

A managed connector is built from a few Unity Catalog-governed pieces, and the exam expects you to know what each one does:

• Connection: a Unity Catalog securable that stores the source's authentication details (OAuth tokens, user and password). It is not a volume, a storage credential (those are for cloud object storage) or a workspace secret scope.
• Ingestion pipeline: a serverless Lakeflow Spark Declarative Pipeline that reads the source incrementally and writes one streaming table per ingested object into the destination catalog and schema. The pipeline owns these tables, so do not edit them by hand.
• Ingestion gateway and staging (databases only): the gateway runs on classic compute close to the database, extracts snapshots and changes, and writes them to a staging volume that the ingestion pipeline applies.

SaaS and file-service connectors (Salesforce, Workday reports, ServiceNow, SharePoint, Google Drive) remove all API code: no pagination, token refresh or incremental logic to maintain, and new or changed records (or documents, for file services) arrive on the schedule you set. A file-service connector is how documents such as PDFs and Word files in SharePoint reach Unity Catalog; Auto Loader cannot read a SharePoint site URL.

Database connectors come in two kinds, and the difference is tested:

Separately, a managed connector's history tracking setting decides what the destination keeps. SCD type 1 overwrites each row with its latest values (current state only). SCD type 2 keeps every version with validity columns, which is what a change-history or audit requirement needs. A full change history including hard deletes therefore needs both a CDC-based connector and SCD type 2; either one alone falls short.

Compared with nightly JDBC full reloads, a CDC connector moves only changed rows, so data can be minutes old instead of a day, and deletes propagate. CSV extracts of changed rows lose deletes and add a custom export job.

Databricks' guidance is to start with the most managed layer that meets the requirements and move down only when that layer cannot meet one: a managed connector first, then Lakeflow Spark Declarative Pipelines, then raw Structured Streaming. Each step down adds code and operations you own. A preference for flexibility is not a reason to move down when the managed connector meets every stated requirement.

The type of data narrows the choice first: structured tables in a database or SaaS application go to a database or SaaS managed connector; files landing in cloud object storage go to Auto Loader (a standard connector); unstructured documents such as PDFs and Word files kept in a file service like SharePoint go to a file-service managed connector, which lands the files for downstream parsing rather than flattening them into rows.

Transformation needs are also not a reason to replace a connector. A managed connector lands raw (bronze) data; cleansing and filtering rules belong in a downstream pipeline that builds a silver table from the connector's output.

When no managed connector covers the source, the options are:

Governance can decide between two tools that both support a source. If policy requires that source credentials be held and granted in Unity Catalog and that ingestion cost appear in Databricks system tables, a Lakeflow Connect connector meets it (a Unity Catalog connection; serverless pipeline usage in system.billing.usage) and a partner tool does not.

Lakehouse Federation vs ingestion

Lakehouse Federation registers an external database (PostgreSQL, MySQL, SQL Server, Snowflake and others) in Unity Catalog through a connection and a foreign catalog, and pushes queries down to it. Nothing is copied, so data is always current and there is no pipeline, but every query runs on the source, and the foreign catalog shows only the current state. It targets databases and warehouses, not SaaS APIs, event streams or SFTP servers.

A materialized view over a foreign catalog still queries the source on each refresh and holds only the latest result, so it fixes neither the load nor the history problem.

Every ingestion path should read and write through Unity Catalog objects, so that access is granted to groups, audited and traced in lineage. The relevant securables are:

• Connections for external systems (SaaS apps, databases, SFTP). Creating one needs CREATE CONNECTION on the metastore. Building an ingestion pipeline on an existing connection needs USE CONNECTION (or ALL PRIVILEGES) on it, plus USE CATALOG on the destination catalog and USE SCHEMA and CREATE TABLE on the destination schema. (The prerequisites list other combinations too, for example CREATE SCHEMA on the catalog instead of an existing schema.) Ownership or CREATE CONNECTION grants more than that; MODIFY changes data in existing tables but does not let you create the pipeline's tables.
• Storage credentials and external locations for cloud object storage. A storage credential wraps the cloud identity; an external location pairs it with a path. Granting READ FILES on an external location lets a group read that path directly with its cloud URI.
• Volumes for files. An external volume registers an existing path inside an external location, so files stay where they are; READ VOLUME on a volume created at a prefix such as landing/ limits a group to that prefix, and code reads /Volumes/<catalog>/<schema>/<volume>/ paths with no cloud URI, which makes it portable across workspaces. A managed volume stores files in Unity Catalog's managed storage, so existing files would have to be copied in.

Legacy patterns sit outside this model. A DBFS mount backed by a cluster instance profile (or service principal) gives everyone on the cluster the same access, with no Unity Catalog grants, audit or lineage, and Unity Catalog tables and volumes cannot be defined on mount paths. Reading the S3 path directly with the same instance profile changes nothing. The fix is a storage credential plus an external location (or a volume) for the bucket, then pointing Auto Loader at the governed path.

Workspace secret scopes still hold secrets for custom notebook code, but they are a workspace feature outside Unity Catalog grants; managed connectors store credentials in connections instead.