Incremental file ingestion

The problem

The naive job lists the landing folder, compares with what it loaded last time (by file name or modification time), and loads the difference. It works on day one. Then the folder holds millions of files and listing takes longer than loading; a file is written slowly and picked up half-written; a job fails between loading and recording; two runs overlap. Each failure means duplicated or missing data.

The file source as a stream

Structured Streaming treats a folder as a stream of files. The checkpointcheckpoint: In streaming, the folder where Spark records which input it has processed and its state, so a restarted query continues where it stopped. Learn more → records which files each micro-batch processed, so each file is processed once, even across restarts. Run it continuously, or as a scheduled batch job with an availableNow trigger, which processes everything new and stops:

(spark.readStream
    .format("json")
    .schema(order_schema)                       # streams need a schema up front
    .option("maxFilesPerTrigger", 1000)         # bound each micro-batch
    .load("s3://landing/orders/")
  .writeStream
    .format("delta")
    .option("checkpointLocation", "s3://chk/orders_bronze/")
    .trigger(availableNow=True)                  # Spark 3.3+: process what is new, then stop
    .toTable("bronze.orders"))

• Exactly once comes from the checkpoint plus a transactional sink such as Delta or Iceberg.
• availableNow gives batch scheduling with streaming bookkeeping: no "what did I load last time" table to maintain. It also respects maxFilesPerTrigger, splitting a large backlog into several batches.
• cleanSource (archive or delete) moves or deletes processed files, which keeps the landing folder small.
• The catch: the source still lists the directory every batch. With millions of files that listing becomes the bottleneck.

Auto Loader and COPY INTO

On Databricks, two tools address the gaps. They are proprietary features of that platform, but the ideas show up in interviews generally.

Schema drift and bad records

Backfills and late files

A stream only processes files it has not seen. To reprocess history (a parsing bug, a new column), start a new checkpoint into a new or truncated target, or run a separate batch backfill over a date range and keep the stream for new data. Never delete the checkpoint of a running pipeline casually: it will reload everything into the same target and duplicate it.

Common mistakes

• ✗Tracking loaded files by modification time — Clock skewskew: When one key has far more rows than the others, so one task does most of the work while the rest wait. Learn more →, slow writers and copied files break it. Use a source that records processed files.
• ✗Picking up files while they are still being written — Writers should write to a temporary name or folder and rename or move when done.
• ✗Deleting the checkpoint to "fix" a stream — Everything is reprocessed into the same target: duplicates.
• ✗Inferring the schema on every batch run — The schema changes with whatever files happen to arrive.