topic Streaming Considerations for Daily Batch Ingestion in Data Engineering

Streaming Considerations for Daily Batch Ingestion

nye-d — Thu, 03 Sep 2026 01:15:21 GMT

Hey everyone,

First time posting looking to complete my research for a Databricks implementation.

My current data pipeline consists of Azure Data Factory Pipelines that load data from ADLS into a SQL database. This happens once a day on schedule since the source team provides data to our ADLS. Decryption, processing, cleansing, encryption, file moving is all handled through notebooks that sink parquet files in ADLS and ultimately copy activities load to a SQL database. All of this is very procedural, sequential and quite frankly, digestible and straightforward when you see the flows and read the code.

I'm having trouble wrapping my head around the declarative nature of Spark Declarative Pipelines and why would I want to use Autoloader when it limits the processing power in between reading files and writing to tables.

I'll list some of my doubts and any insight on any of them is more than welcome.

Is using autoloader's main advantage, reducing the overhead of manual file discovery and checkpointing for reads and writes?
Can I still handle file decryption if I'm using autoloader?
I've tested the writeStream for my sink to tables in Unity Catalog. Does the trigger AvailableNow effectively convert the stream into a batch load, suitable for my daily data intake?
Is ForEachBatch the only way to handle procedural code in both notebooks using autoloader and in SDPs? What I mean by this is performing checks on a spark dataframe built from each file to be ingested, between the readStream and the writeStream when using Autoloader, to reject rows or files that do not comply with custom rules. In SDPs, performing updates to log tables seem to be only viable via forEachBatch.

I guess in summary I want to understand if what I need is a paradigm shift on the data processing for our Databricks implementation, leaning towards the declarative nature for table processing and handling file processing procedurally.

Re: Streaming Considerations for Daily Batch Ingestion

balajij8 — Thu, 03 Sep 2026 03:15:54 GMT

@nye-d Auto Loader provides additional benefits such as file discovery at scale, flexibility to handle schema evolution/inference and automatic state management. You can seamlessly process both streaming and batch processing based on the cases.

You can keep a decryption/encryption job as a first job in the workflow to decrypt files into a staging location and use Auto Loader/SDP to cleanse and transform the data from it based on the cases.

Trigger - available Now True effectively converts stream to batch. It processes all available data as a micro batch and then stops. Its suitable for your case. In SDP, you do not specify triggers in code unlike Auto Loader as SDP handles it automatically. When you run a pipeline update, it processes all new data since the last checkpoint and then completes the load.

For each batch is one way of handling the processing. There are other options such as setting things via SQL in SDP expectations and SDP handles the batch management.

CREATE OR REFRESH STREAMING TABLE silver_files ( CONSTRAINT valid_files_revenue EXPECT (revenue > 0) ON VIOLATION DROP ROW, CONSTRAINT valid_files_date EXPECT (files_date IS NOT NULL) )

You can keep the transformations in the pyspark table function

@dp.table(name="silver_files") def silver_orders(): df = spark.readStream.table("bronze_files") df = df.filter(col("revenue") > 0) df = df.withColumn("validated", pyspark_udf(col("data"))) return df.filter(col("validated") == True)

You can use For Each Batch when you are writing to multiple sinks with different logic, updating external systems or log tables and complex stateful operations that can't be expressed declaratively. You can prefer SDP if you have direct cases (data quality expectations and filters).

You can use a hybrid approach with a procedural file encryption/decryption notebook as the first job and use SDP in declarative manner for Data Quality, SCD, Transformations as the next job. You can replace SDP with Auto Loader for complex cases.

Re: Streaming Considerations for Daily Batch Ingestion

Satyasai — Thu, 03 Sep 2026 04:23:03 GMT

in Addtion to @balajij8

Is using autoloader's main advantage, reducing the overhead of manual file discovery and checkpointing for reads and writes?

Yes,

What Auto Loader adds:

Directory Listing vs. File Notification: For high volumes, Auto Loader configures native Azure Event Grid / Queue services to detect newly arrived files instantly without directory scanning.

Automatic Schema Evolution & Drift: If a source system adds a new column or changes a data type, Auto Loader detects it, captures unknown columns into a _rescued_data column, or automatically updates the schema without throwing hard file-reading exceptions.

Idempotency Out-of-the-Box: Checkpointing ensures that even if a job fails mid-file or mid-batch, re-running the job guarantees exactly-once processing without duplicate ingestion or manual file movement.

Can I still handle file decryption if I'm using autoloader?
Yes, provided decryption happens during or before the streaming read.
I've tested the writeStream for my sink to tables in Unity Catalog. Does the trigger AvailableNow effectively convert the stream into a batch load, suitable for my daily data intake?

Yes, absolutely.

trigger(availableNow=True) is the recommended Databricks pattern for scheduled daily or periodic ingestion.

Is ForEachBatch the only way to handle procedural code in both notebooks using autoloader and in SDPs? What I mean by this is performing checks on a spark dataframe built from each file to be ingested, between the readStream and the writeStream when using Autoloader, to reject rows or files that do not comply with custom rules. In SDPs, performing updates to log tables seem to be only viable via forEachBatch.

In Spark Declarative Pipelines (SDP / DLT): You do not need foreachBatch for quality checks or logging. SDP completely abstracts this using declarative primitives:

Summary

Legacy Procedural Pattern (ADF)

ADLS Files ──> Directory Scan ──> Decrypt ──> Custom Checks ──> Loop ──> SQL DB

│

(Manual File Movement)

Modern Databricks Pattern (SDP / Auto Loader)

ADLS Files ──> Auto Loader (trigger=availableNow) ──> Bronze Delta Table

│

(Declarative SDP)

Silver Delta (Expectations)

│

(SCD Type 1 / 2)

Gold Layer

Re: Streaming Considerations for Daily Batch Ingestion

data_pulse — Thu, 03 Sep 2026 09:44:41 GMT

@nye-d

You don’t necessarily need a full paradigm shift, Databricks works best when you separate procedural orchestration from incremental data processing. Here are some insights on the doubts raised.

1) Auto Loader Solution
The biggest value is removing the need to manually mange :

File Discovery (Directory based / File Event based)
Already Processing Files Tracking
Checkpoints
Schema evolution / rescued data
scalable incremental ingestion

Note:
If the UC external location has file events = Enabled, managed file events are available. On new DBR versions, Auto Loader can use them automatically. On older runtimes you may still need to enable them explicitly. This avoids repeatedly listing large ADLS directories and makes incremental discovery more efficient.

2) Decryption Handling
Auto Loader discovers and reads the files, it does not automatically decrypt arbitrary encrypted files. The common mechanisms are :
Bespoke Handling
Encrypted Files in Storage Location → Databricks Job / notebook decrypts → Decrypted landing → Auto Loader → Bronze.

In-built Binary File
Read encrypted files using binaryFile, decrypt the content either with existing / custom code and write the result.

spark.readStream .format("cloudFiles") .option("cloudFiles.format", "binaryFile") .option("pathGlobFilter", "*.pgp") .load(source_path)

The resulting dataframe includes fields as path, modificationTime, length, content. So invoke the decryption on content.

3) Trigger Available Now
Yes, it makes the Auto loader behaves like an incremental batch job while internally it retains structed streaming semantics.
Eg:

( df.writeStream .option("checkpointLocation", checkpoint_path) .trigger(availableNow=True) .toTable("catalog.bronze.table") )

Above trigger processes all available data, potentially across multiple micro-batches, and then stops automatically. It also respects rate controls such as maxFilesPerTrigger / maxBytesPerTrigger. Checkpoint is still required for tracking.
So the flow is: Daily scheduled job → process only new files → checkpoint progress → finish → compute shuts down.
Advantage: Stream doesn't run 24x7, it can either be scheduled to run at specific time or can invoke FileArrivalTrigger based schedule which triggers when ever file is arrived.

4) ForEachBatch Use Case
Foreach batch not required for normal transformations, validation, filtering, joins, row filtering. They all can be done directly on streaming dataframe.

df_stream = ( df_stream .filter("customer_id IS NOT NULL") .filter("amount >= 0") .withColumn("load_ts", current_timestamp()) )

Can also filter before reading with Auto Loader as :
.option("pathGlobFilter", "orders_*.parquet")

For malformed/schema-drift, Auto Loader provides _rescued_data and _corrupt_record, which can be used to split valid and rejected records.
In Lakeflow SDP, data quality expectations can also warn/ drop invalid records/ route bad records to quarantine/ fail the pipeline.

For Each batch is required when writing to sink logic needs batch level control i.e custom jdbc writes/ custom APIs/ Logging / Complex Transformation Logic.
Databricks now also provides foreach_batch_sink() in Lakeflow pipelines for these cases, can refer this for usage.

Caveat:
Structured Streaming foreachBatch provides atleast once semantics, so custom writes should be idempotent, typically using batch_id, keys or merge logic to protect against duplicates. The databricks docs provide few more insights on structured streaming.

Summary
Move away from manually orchestrating the files, let Auto Loader handle incremental, stateful ingestion on its own. Save foreachBatch / separate job tasks for cases needing real imperative logic like writing to an external system, sending a notification, or triggering something outside the pipeline.