Processing · Chapter 13

Ingestion

Getting data in is where most of the operational pain of a data platform lives, because it is the only part of the system that depends on someone else's reliability.

Pick the simplest mechanism that meets the requirement #

There are more ways to ingest data into Databricks than any one platform should use, so we work down this list and stop at the first option that satisfies the requirement.

No ingestion at all, where the data can stay where it is. Lakehouse Federation queries an external database or warehouse in place, and OpenSharing reads a table another organisation publishes without copying it. A dataset queried occasionally and never joined at scale often does not need to be copied on a schedule.

Managed connectors for the sources they support, typically SaaS applications and common databases. When a managed connector exists and covers the objects you need, it is almost always the right answer, because the failure modes are Databricks' problem rather than yours.

Auto Loader for files arriving in object storage. It handles incremental discovery of new files without you tracking state, scales to very large directories, and supports schema inference and evolution. This is the workhorse for file-based ingestion and we reach for it before writing anything custom.

Change data capture from operational databases, either through a managed connector or through a CDC tool writing to storage that Auto Loader picks up. The decision that matters is whether you need a replica of current state or a log of changes, and the answer is almost always the log, because you can derive state from a log and not the reverse.

Streaming from a message broker where the latency requirement genuinely demands it. Structured Streaming against Kafka or a cloud equivalent is well trodden, and the operational cost is real, so we ask what decision depends on sub-minute freshness before accepting it.

Custom ingestion code last, and only where the source is genuinely unusual. Every line of custom ingestion is a line you maintain forever.

Auto Loader in practice #

A few configuration decisions account for most of the difference between an Auto Loader pipeline that runs for years and one that needs babysitting.

Use file notification mode rather than directory listing for large or busy directories, since listing cost grows with directory size while notifications do not.

Set the schema location deliberately and keep it with the pipeline, because it is state that determines how the stream behaves on restart.

Decide the schema evolution mode explicitly. addNewColumns is the right default in bronze, and it fails the stream on a new column so that the next run picks it up, which surprises people expecting silent adaptation.

Configure the rescued data column and then actually look at it, because a rescued data column nobody reads is the same as dropping data.

(spark.readStream
  .format("cloudFiles")
  .option("cloudFiles.format", "json")
  .option("cloudFiles.useNotifications", "true")
  .option("cloudFiles.schemaLocation", f"{checkpoint}/schema")
  .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
  .option("cloudFiles.rescuedDataColumn", "_rescued")
  .load(source)
  .withColumn("_ingested_at", current_timestamp())
  .withColumn("_source_file", col("_metadata.file_path")))

Beyond the basics #

The options below are the ones we find teams have never turned on, and several change the cost or reliability of a pipeline substantially.

Trigger.AvailableNow gives you batch economics with streaming bookkeeping. A stream started with .trigger(availableNow=True) processes everything currently available in a series of micro-batches and then stops, so you keep exactly-once semantics and checkpointed offsets without a cluster running overnight for a feed that updates twice a day. This is our default for scheduled ingestion, and the setting that most often removes an always-on cluster from a bill.

Bound each micro-batch on purpose. cloudFiles.maxFilesPerTrigger and cloudFiles.maxBytesPerTrigger cap what a single batch attempts. The case that matters is the first run after a backlog, where an unbounded batch tries to read four months of files at once, spills, and fails repeatedly. A bound turns a stuck pipeline into a slow one.

Notification mode still needs a backfill sweep. Cloud notification delivery is reliable rather than guaranteed, and a dropped event means a file that is never processed and never reported as missing. Setting cloudFiles.backfillInterval to a day makes Auto Loader periodically list the directory and pick up whatever the notifications missed, and we treat that as mandatory on notification-mode streams.

Managed file events remove the cloud plumbing. Rather than each Auto Loader stream creating and owning notification queues in your cloud account, file events can be enabled once on a Unity Catalog external location and read from there, which cuts the per-pipeline cloud resources and permissions once you have more than a handful of streams.

Schema hints beat full schemas. Supplying cloudFiles.schemaHints for the few columns whose inferred type you do not trust, such as an identifier that looks numeric until the day it does not, is far more maintainable than declaring the whole schema by hand and keeping it current.

Tidy up the source directory deliberately. cloudFiles.cleanSource can archive or delete files after successful processing, with a retention window. Left alone, a landing directory accumulates for years and listing performance degrades with it.

COPY INTO for one-shot and manual loads. For a backfill or an occasional operator-driven load, COPY INTO is idempotent at file granularity and carries no checkpoint you then have to look after. We use it for corrections and initial history loads, and Auto Loader for anything that recurs.

Idempotent writes inside foreachBatch. When custom logic forces you into foreachBatch, the write is no longer covered by the stream's exactly-once guarantee, because the batch function can rerun after a partial write. Setting txnAppId and txnVersion lets Delta recognise and skip a duplicate batch, and its absence is the most common source of silent duplication in hand-written ingestion.

Change feeds want AUTO CDC rather than hand-written merges. When the source delivers inserts, updates, and deletes, the apply-changes capability in Lakeflow pipelines orders by a sequence column and produces either a current-state table or a slowly changing dimension of type 2 without you writing the merge, including the out-of-order delivery that hand-written merges reliably get wrong.

Large streaming state needs the right store. Aggregations and stream-to-stream joins keep state in the checkpoint, and once that state is large, the RocksDB state store with changelog checkpointing keeps batch durations flat instead of climbing over weeks.

Unstructured data has a home too. PDFs, images, and audio land in Unity Catalog volumes, and Auto Loader reads them with cloudFiles.format set to binaryFile, which keeps governance and lineage in the same place as everything else rather than in a bucket with its own access policy.

The cloud-native services are individually capable, and Kinesis, Event Hubs, and Pub/Sub are excellent at what they do. The difference is the number of moving parts needed to get one dataset from a source into a governed, incrementally maintained table, and the fact that batch and streaming ingestion here are the same code against the same tables rather than two stacks with two operational models.

Idempotency is not optional #

Every ingestion path will at some point deliver the same data twice, because a source retried, an operator reran a job, or a failure happened between writing files and committing an offset. The pipeline needs to be correct when that happens rather than merely unlikely to encounter it.

In practice this means merging on a deterministic key rather than appending in silver, keeping the checkpoint alongside the target table so that they are recovered together, and treating manual reprocessing as a first-class operation with a documented procedure.

Contracts with source systems #

The technical mechanism is the easy half. What determines whether the platform is trustworthy is whether anyone upstream tells you before a field changes meaning.

We push for a written expectation covering schema change notice, delivery frequency and lateness bounds, the definition of a full refresh, and who to call. Where that is not achievable, which is often, we compensate with defensive validation in silver and alerting on distribution shifts rather than only on failures, so that a silent semantic change surfaces as an anomaly rather than as a wrong number in a board pack. What happens after the data lands is covered in Pipelines and The Medallion Architecture.

Table of Contents