Skip to main content

Auto Loader ingestion

Since: v0.6.0

declare_ingest declares a complete ingestion flow in a Lakeflow pipeline. Auto Loader watches a Volume folder, and the GeoBrix reader you name reads each new or changed file. The rows land in a table. on_change decides what happens when a file is overwritten: keep every version (scd2, the default), keep only the latest content (replace), or append every delivery (append). Data that cannot be read is set aside in a bad-records table, and the rest is written (on_error).

Set up the pipeline first (channel, GeoBrix extra, Serverless notes): see Lakeflow SDP.

declare_ingest flow — Auto Loader (cloudFiles, binaryFile) watches the source_dir landing folder, where glob picks the files, and delivers an overwritten file again. Under scd2 and replace, each delivery becomes a row of the file-events table with its SHA-256 _source_hash. A foreach_batch_sink skips content already stored. It reads the newest delivery of each file with read_paths into the target rows table. There, scd2 appends each new version. Replace swaps a file’s rows with an atomic replaceWhere. Under scd2, AUTO CDC SCD type 2 builds the file-versions table from the file events, with history on _source_hash. The current view joins that table with the rows to show each file’s latest readable version. With on_error set to quarantine, each micro-batch is read once into a staging folder under scratch_dir. What cannot be read is appended to the bad-records table before the rows. Under append, the target is a plain streaming table. read_paths over the Auto Loader stream appends every delivery, with no hash and no bad-records table. Bad records are skipped with a warning

Quick start​

This is a complete pipeline source file. It keeps main.geo.boroughs in sync with the GeoJSON files in a landing folder:

# Lakeflow pipeline source file (serverless)
from databricks.labs.gbx.ds.changes import declare_ingest

declare_ingest(
"main.geo.boroughs", # rows table: catalog.schema.table
"/Volumes/main/geo/landing/boroughs", # folder Auto Loader watches
"geojson_gbx",
on_change="scd2", # default; or "replace" / "append"
on_error="quarantine", # default; or "fail"
)

Stage at least one file in the folder before the first pipeline update: the table schema is resolved from it when the pipeline is declared. After an update, main.geo.boroughs holds the rows, main.geo.boroughs_current shows the rows of each file's latest readable content, and main.geo.boroughs_file_versions records when each version of a file was current.

Reader options and parallelism go in the same call. Here a raster folder is cut into tile windows that are spread across tasks, and only the latest content of each file is kept:

# Lakeflow pipeline source file (serverless)
from databricks.labs.gbx.ds.changes import declare_ingest

declare_ingest(
"main.geo.sentinel2_tiles",
"/Volumes/main/geo/landing/sentinel2",
"gtiff_gbx",
options={"tileSize": "512"}, # reader options, as on the GeoTIFF reader page
on_change="replace", # keep only the latest content of each file
num_partitions=64, # spread the tile windows across 64 tasks
)
Key considerations
  • scd2 is the default and keeps every version. The rows table grows with each content change of a file. Use on_change="replace" when only the latest content matters.
  • Stage one file before the first update. The schema is read from schema_path (by default the first matching file in source_dir) when the pipeline is declared, at each update.
  • Unity Catalog Volume paths only. source_dir and schema_path are /Volumes/... paths; to ingest from cloud storage, create an external volume over it. See Paths.
  • Each format has a default file pattern, with lower- and upper-case extensions. GeoJSON is matched by .geojson only (pass glob="*.{geojson,json}" for .json files), a File Geodatabase is delivered as a .gdb.zip file, and pmtiles_gbx needs its source option. See Default file patterns.
  • Set num_partitions on Serverless. Without it a large raster or PMTiles archive is read by a single task. See Parallelism.
  • Use a channel that has the pipeline APIs. declare_ingest needs pyspark.pipelines with foreach_batch_sink and create_auto_cdc_flow (the CURRENT channel has them), and the GeoBrix extra for that channel's runtime. See Environment.
  • Overwrite files without preserving their modification time. A copy made with cp -p or rsync -t can give new content an older time than the version it replaces (see Query the latest content).
  • Bad data is set aside, not fatal. By default (on_error="quarantine") a file, a feature, a raster window or a run of LiDAR points that cannot be read goes to <t>_bad_records. The rest of the micro-batch is written. Each micro-batch is staged in a hidden folder under scratch_dir (default source_dir), so the pipeline must be able to write there. Landing folders are often read-only to the pipeline, so set scratch_dir to a Volume folder it can write. See Unreadable data.

declare_ingest parameters​

declare_ingest(target, source_dir, format, options=None, *,
on_change="scd2", glob=None, num_partitions=None, schema_path=None,
on_error="quarantine", scratch_dir=None)
ParameterDefaultDescription
targetrequiredRows table, a fully qualified catalog.schema.table name. The other objects are created in the same catalog and schema.
source_dirrequiredVolume folder Auto Loader watches (see Paths).
formatrequiredReader name from the formats table.
optionsNoneReader options, exactly as on that reader's page.
on_change"scd2"What to do when a file is overwritten: "scd2", "replace" or "append". See Choosing on_change.
globNoneFile-name filter for the folder, in Spark pathGlobFilter syntax, such as "zones_*.gpkg". By default, the format's default file pattern; a glob replaces that pattern.
num_partitionsNoneSpread the reads across this many tasks. See Parallelism.
schema_pathsource_dirFile or folder that defines the table schema; a folder's first file matching glob is used. It is read once when the pipeline is declared (at each update), not for every micro-batch.
on_error"quarantine"What to do with data that cannot be read: "quarantine" sets it aside in <t>_bad_records and writes the rest; "fail" fails the update. See Unreadable data.
scratch_dirsource_dirVolume folder (/Volumes/..., shared by every node) where on_error="quarantine" stages each micro-batch, in a hidden .gbx_scratch folder that is removed when the micro-batch ends. The pipeline must be able to write it. Set it when the landing folder is read-only to the pipeline.

Auto Loader delivers every file under source_dir on its own. The file pattern must therefore leave out the other files of a multi-file dataset: a shapefile's sidecars, or the files inside an unzipped .gdb folder. The format's default pattern does. A glob you set replaces it, so for shapefiles end it in .shp. See Default file patterns.

Choosing on_change​

Auto Loader runs with cloudFiles.allowOverwrites on in every mode, so a file that is overwritten in place is delivered again. on_change decides what the table does with that second delivery.

scd2 (default)replaceappend
Rows tableAppend-only: one copy of a file's rows for each distinct content versionLatest content of each file onlyEvery delivery of every file
Re-delivery, content unchangedWrites nothingWrites nothingDuplicates the rows
File modifiedNew rows are added; the earlier version's rows stayThat file's rows are replaced, one atomic replaceWhere per micro-batchNew rows are added; the earlier rows stay
History<t>_file_versions records when each version was currentNoneNone
Rows have _source_hashYesYesNo
Use whenYou need the latest content and a record of what it used to beOnly the latest content mattersYou want a log of every delivery

Here <t> is the last part of target. scd2 and replace compare a SHA-256 hash of the file's bytes (_source_hash) with what is already stored. A file that is re-delivered without a content change is therefore recognized and skipped. append does not hash anything.

Each mode declares these objects in the catalog and schema of target:

Objectscd2replaceappend
target: the rowsyesyesyes (streaming table)
<t>_file_events: one row per delivery with _source_path, _source_mtime, _source_hash, _source_sizeyesyesno
<t>_file_versions: one row per content version of a file, with __START_AT and __END_ATyesnono
<target>_current: a view of the rows for each file's latest versionyesnono
<t>_bad_records: one row per bad record, created when the first one is foundyesyesno

In scd2 and replace the rows table is written by the pipeline's batch sink. It is a regular Delta table, created on the first update and clustered by _source_path. It is not a dataset the pipeline manages.

Query the latest content and past content​

<target>_current returns the rows of each file's latest readable version: the newest version whose content has rows in the table. It is a plain view that scans the rows table, so filter it by _source_path to look up one file quickly. A delivery that could not be read is still recorded as the file's newest version in <t>_file_versions. It does not hide the content that was read before it (see Unreadable data). To read what was current at an earlier time, join the rows to the versions table on both _source_path and _source_hash. Keep the version whose interval contains the time:

-- latest content of every file
SELECT * FROM main.geo.boroughs_current;

-- what was current at a point in time
SELECT r.* FROM main.geo.boroughs r
JOIN main.geo.boroughs_file_versions v
ON r._source_path = v._source_path AND r._source_hash = v._source_hash
WHERE v.__START_AT <= TIMESTAMP'2026-10-17 12:00:00'
AND (v.__END_AT IS NULL OR v.__END_AT > TIMESTAMP'2026-10-17 12:00:00');

__START_AT and __END_AT are the file's modification times, so the time you ask for is when the file's content was written, not when the pipeline ingested it. __END_AT is NULL for the current version, and an interval includes its start and excludes its end. For a time when the file's content could not be read, the query returns no rows for that file.

"Current" means the version with the newest modification time. A copy that keeps or backdates the source's modification time (cp -p, rsync -t) can give an overwrite an older time than the version it replaces. That content is then recorded as history and does not become current. Copy without preserving times when you overwrite a file.

Unreadable data​

on_error decides what a micro-batch does with data its reader cannot read:

on_errorWhat happens
"quarantine" (default)Each file is read on its own, and what cannot be read is a bad record: it is written to <t>_bad_records, then the rest of the micro-batch is written to the rows table. The update succeeds.
"fail"The first error fails the update, with an error that names the file.

A bad record is the smallest part of a file its reader can set aside. It can be a whole file that cannot be opened, or whose columns differ from the table schema. It can be a feature whose geometry bytes are not readable, or whose text is not valid UTF-8. It can be a GeoJSONSeq line that is not JSON. It can also be a raster tile window, a run of LiDAR points, a PMTiles tile, or a NetCDF variable or slice. Rows read from a file before an error stay in the table. The Unreadable files reference lists what each format sets aside, and the damage no reader can detect.

A NULL or empty geometry is data, not a bad record. A feature without a location ("geometry": null in GeoJSON) or a POINT EMPTY is written like any other row. GDAL also returns NULL for a geometry it cannot decode (an unknown geometry type, unreadable coordinates, a damaged GeoPackage or shapefile record). Those rows are therefore written too. To watch for them, query the geometry column with IS NULL (it is geom_0 for GeoJSON) and ST_IsEmpty. Curves and surfaces (CircularString, CompoundCurve, CurvePolygon, MultiCurve, MultiSurface, PolyhedralSurface, TIN, Triangle) are data too.

Errors of the environment are not bad data either. Out of memory, a missing module (such as a LAZ decompression backend), and an I/O error of the Volume (retried first) fail the micro-batch. So does an image too large to read whole in exif_gbx qc mode. Spark then retries the task, or the update fails with that error. No bad record is written for them.

Other damage to a LAZ file can still stop the worker

A LAZ file is set aside before it is decoded when its chunk table or LASzip record is implausible or cannot be decoded. The same holds when (point formats with GPS time) its compressed points hold a run of 0xFF bytes. Other damage, inside the compressed points or in a chunk table that is wrong but plausible, still reaches the native LAZ decoder (lazrs). It can crash the decoder and end the worker process. A crash cannot be caught, so the file is not set aside: the task fails, Spark retries it, and then the update fails. Remove the file or replace it with a readable copy to continue.

Query the bad records​

<t>_bad_records is created in the catalog and schema of target when the first bad record is found:

-- what was set aside, per file and kind
SELECT _source_path, error_class, count(*) AS records, max(quarantined_at) AS last_seen
FROM main.geo.boroughs_bad_records
GROUP BY _source_path, error_class
ORDER BY last_seen DESC;

-- the features set aside from one file, with their other columns
SELECT record_index, error_class, error_message, raw
FROM main.geo.boroughs_bad_records
WHERE _source_path = '/Volumes/main/geo/landing/boroughs/boroughs.geojson'
AND record_index LIKE 'feature=%';
ColumnTypeMeaning
_source_pathSTRINGThe file
_source_mtimeTIMESTAMPIts modification time, as delivered
_source_hashSTRINGSHA-256 of the delivered file, as in <t>_file_events
_source_sizeBIGINTIts size in bytes
record_indexSTRINGWhere in the file: feature=17 (position in the file's read order), fid=1009 (a GeoPackage feature), line=11 (GeoJSONSeq), window=128,64,64,64 (column, row, width, height), points=2000-2999 or point=12 (LiDAR), tile=3/1/2 (z/x/y) or tiles=3/0/0..3/1/3 (PMTiles), variable=temp,time=1 (NetCDF). NULL for the whole file
error_classSTRINGThe reader's exception class (for example pyogrio.errors.DataSourceError), or the check that set the record aside (table below)
error_messageSTRINGThe error and the errors that caused it, the warnings and log lines the reader gave while reading, and how many rows of the file were read before the error
rawSTRINGWhat can be recovered, as JSON of at most 4096 characters (longer is cut and marked "truncated": true): a feature's other columns (text that is not UTF-8 is escaped, a binary value is shown as <binary, N bytes>), a GeoJSONSeq line, or a LiDAR point's x, y and z. Otherwise NULL
quarantined_atTIMESTAMPWhen the micro-batch set it aside
error_classSet aside because
UnreadableGeometryThe geometry bytes are not valid WKB (for example a polygon ring that is not closed)
InvalidTextA text value is not valid UTF-8
RecordsNotReadThe file declares features, points or lines its reader did not return (a dropped .dbf record, an unreadable GeoPackage page, a truncated LAS file, a GeoJSONSeq line that is not JSON)
PointOutOfBoundsLiDAR points outside the bounds the file's header declares, widened on every side by their largest extent (garbled point records, or garbage that damaged LAZ data decodes to); off with the lidar_gbx option checkHeaderBounds="false"
ShortTileA PMTiles tile is shorter than its directory entry declares (a truncated archive)
NotJpegA .jpg / .jpeg file has no JPEG start marker, or header segments that do not lead to a frame header (nothing is decoded, so a very large image is still a JPEG; a missing end marker only gives a warning: a truncated scan, or a Motion Photo's video after the image)
UnreadableFileAn empty file, or a LAS file whose point record length does not fit its data
NothingReadableNo row of the delivery could be read: every record of it was set aside. One per delivery (record_index is NULL), next to the records that say why

How bad records interact with the tables​

  • A file with bad records still has its version. Under scd2 the delivery is recorded in <t>_file_versions like any other. When part of a file's new content was set aside, the rows that were read are written under the new hash. That version is current. When all of it was set aside, the version has no rows, so <target>_current keeps showing the file's last readable content, and <t>_bad_records explains the empty version. Removing the bad file keeps it that way. The same holds for a file overwritten with content that has no rows (an emptied file): <target>_current keeps showing its previous rows, while replace removes them.
  • replace keeps the last readable rows too. A delivery that is partly readable replaces the file's earlier rows with the rows that were read. A delivery set aside entirely (a NothingReadable record) leaves the file's earlier rows in place.
  • The same bad bytes are not read twice. A re-delivery with the same content is skipped when it has rows in the table, or when it was set aside entirely. It would be set aside again. A re-delivery with new content is read.
  • Each micro-batch is staged in scratch_dir. The files are read once. The rows go to a hidden .gbx_scratch folder under scratch_dir (default source_dir), in a subfolder of its own for each target table. The bad records are collected there. Then the bad records are appended to <t>_bad_records, then the rows to the table, and the folder is removed. A bad record already in the table is not appended again. A micro-batch that fails between the two writes and is retried therefore neither loses nor repeats one. A staging folder left by a killed micro-batch is removed a day later, by the next micro-batch of the same table. Pipelines that share a scratch_dir never touch each other's folders. When the pipeline cannot write to scratch_dir, the micro-batch fails with an error that says so.
  • To read a file that was set aside again, for example after fixing a reader option, delete its NothingReadable rows from <t>_bad_records (DELETE FROM ... WHERE _source_path = '...' AND error_class = 'NothingReadable'), then touch the file or deliver it again. Until then an identical delivery is skipped. A delivery that was partly read has rows under its _source_hash, so the same bytes delivered again are skipped as stored. To read it again, delete its rows for that hash from the table (DELETE FROM ... WHERE _source_path = '...' AND _source_hash = '...') before you touch or deliver it, or deliver changed content.
  • append has no bad-records table. Its rows table is a plain streaming table, so bad records are skipped with a warning in the executor logs, and nothing is staged. Use scd2 or replace to keep them.

With on_error="fail"​

The update fails with the reader's error, which names the file. Nothing from that micro-batch is written to the rows table, and the flow's progress is not saved. The next update reads the same files again and fails the same way until the file is dealt with. To recover, remove the file or replace it with a readable copy, then start an update. A file deleted before it is read is skipped with a warning (see Files deleted before they are read). A replacement is delivered again and handled like any overwritten file (see Choosing on_change). The Lakeflow connector always reads this way.

What a pipeline update does​

  • The newest delivery of each file wins (scd2 and replace). When one micro-batch holds several deliveries of the same file, only the newest is read, because the bytes on disk are the newest content. Under scd2 the older deliveries still appear in <t>_file_versions, but have no rows in the table.
  • A file modified after it was hashed is skipped (scd2 and replace) until its newer delivery arrives, so a row's _source_hash describes the content it was read from. Before reading, each file must still have its delivered size and a modification time within one second of the delivered one. A file can still be stored under the earlier hash if it is overwritten in the instant between that check and the read. The same holds if it is rewritten with the same size within that second. Its next delivery is written as usual (and under scd2 recorded as the current version).
  • A file deleted before it is read is skipped with a warning (all modes), and Auto Loader counts it as processed. Deleting a file does not remove rows that were already written, with one rare exception. Under replace, a file deleted in the instant between that check and the read is still part of the micro-batch's replaceWhere. Its earlier rows are therefore removed, and no new rows are written for it.

See also​

Production examples

Two examples are complete Lakeflow SDP pipelines with an Auto Loader streaming-table bronze layer. They list their files with a plain binaryFile Auto Loader stream and read the listed files in silver with read_paths. Their files never change once landed, so they don't use declare_ingest, and they show the pipeline setup around it:

  • Wireless Coverage: an Auto Loader laz_inventory streaming table over staged USGS 3DEP LiDAR feeds DSM, DTM and CHM surfaces and H3 tower viewsheds.
  • Vapor-Eyes: five raw sources land on a Volume and become a bronze streaming-table inventory, then silver detection and attribution and gold map-ready tables and PMTiles.