It is not possible to achieve truly stateless microbatch aggregations within a Delta Live Tables (DLT) pipeline using standard declarative aggregation operations, because DLT streams are inherently designed to maintain state when using aggregations, groupBy, and window functions, to allow safe incremental/streaming computations.

Why DLT Aggregations Are Stateful

• When grouping and aggregating on columns (such as file_name), Spark Structured Streaming (the foundation for DLT) must keep track of all seen groups in its state store so it can update results if new rows for the same group arrive in future microbatches.

• The output mode defaults to complete for stream aggregations, triggering a full table rewrite and keeping track of previous aggregations, which is not stateless.

Watermark Limitations

• Watermarks only help trim state for time-based aggregations and late data, but, as you noted, they can cause recently arrived data to be delayed, waiting for the watermark to "advance" before emitting results for new groups.

• Using a short watermark mitigates state growth but does not truly make operations stateless, and introduces potential latency/ingestion delays for recently added files.

foreachBatch Drawback

• foreachBatch can implement stateless logic (processing only data in the current batch), but it is imperative rather than declarative, breaking DLT's managed pipeline model. It cannot be wrapped in a basic @Dlt.table, so orchestration and lineage benefits are lost.

Alternative Approaches

There is no direct stateless aggregation within DLT's declarative API. The best alternatives are:

• Use Delta Merge: Store raw ingested row counts in a staging temporary location (ideally a Delta table). Then, perform an upsert (“merge”) from this staging table into the silver table using foreachBatch. This way, the aggregate result is always up to date, and each batch only touches new files, but this must be orchestrated outside of true @Dlt.table context.

• External Aggregation Logic: Execute Spark jobs outside DLT (using notebooks or jobs) to periodically compute row counts and write them to the target table, preserving statelessness.

• DLT with Expectation: If the only metric is ingest rate, consider using DLT's built-in data quality and expectation features to track row counts or failures, although this does not create a custom silver table.

Key Takeaways

• Declarative aggregations in DLT (groupBy/agg) are always stateful because Spark must manage and update previously seen groups.

• For purely stateless batch-level aggregation, imperative approaches (like foreachBatch or external processing) are required, at the expense of breaking DLT's pipeline model.

• Watermarking and windowing help control state scope but do not eliminate state or latency artifacts for newly arrived data.

• There is no supported DLT-native stateless grouping/aggregation on dynamically arriving unique keys (such as file_name) without maintaining state across microbatches.