https://community.databricks.com/t5/technical-blog/dbr-18-streaming-workload-simplification-with-improved-aqe-and/ba-p/147752 <P><SPAN>Since 2022, Databricks Engineering has been on a mission to simplify streaming workloads through </SPAN><STRONG>Project Lightspeed</STRONG><SPAN>. We’ve democratized stateful processing with features like </SPAN><A href="https://www.google.com/search?q=https://docs.databricks.com/aws/en/stateful-applications/%23what-is-transformwithstate" target="_blank" rel="noopener"><SPAN>transformWithState</SPAN></A><SPAN> and the </SPAN><A href="https://docs.databricks.com/aws/en/structured-streaming/read-state" target="_blank" rel="noopener"><SPAN>State Reader API</SPAN></A><SPAN>.</SPAN></P> <P><SPAN>Now, with </SPAN><A href="https://docs.databricks.com/aws/en/release-notes/runtime/18.0" target="_self"><STRONG>Databricks Runtime 18</STRONG></A><SPAN>, we are addressing one of the most persistent headaches in streaming optimization: </SPAN><STRONG>Shuffle tuning.</STRONG></P> <P><SPAN>We are excited to announce that </SPAN><STRONG>Adaptive Query Execution (AQE)</STRONG><SPAN> and </SPAN><STRONG>Auto Optimized Shuffle (AOS)</STRONG><SPAN> are now fully supported for </SPAN><STRONG>stateless Structured Streaming queries</STRONG><SPAN>. This update brings the "set it and forget it" simplicity of batch processing to your streaming pipelines.</SPAN></P> <H3><STRONG>Shuffle and When it Occurs</STRONG></H3> <P><SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/shuffle" target="_self">Shuffle</A> is the redistribution (repartitioning) of data across executors and partitions, usually inserted as an Exchange between stages; it moves rows over the network and is one of the costliest operators. If you are unsure whether your streaming job includes a shuffle stage, </SPAN><A href="https://docs.databricks.com/aws/en/optimizations/spark-ui-guide/long-spark-stage" target="_blank" rel="noopener"><SPAN>the Spark UI</SPAN></A><SPAN> can help you determine whether it is involved in the Spark plan, as not all streaming workloads require shuffle. A Spark query plan may include a shuffle stage for various reasons, including:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Joins, aggregations, and window operations: These introduce exchanges and therefore shuffles.&nbsp;</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Global sorting: orderBy typically requires a shuffle for global ordering.&nbsp;</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Explicit repartitioning: <FONT face="courier new,courier">repartition(...)</FONT> or output <FONT face="courier new,courier">partitionBy(...)</FONT> forces data redistribution before the write.&nbsp;</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Non-broadcast joins: If the smaller side isn’t broadcast, the plan uses shuffle-based joins; AQE may switch to broadcast at runtime when thresholds allow.</SPAN></LI> </UL> <H3><STRONG>The Problem: The "200 Partition" Trap</STRONG></H3> <P><SPAN>If you’ve optimized streaming pipelines before DBR 18, you know the struggle.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>The "Auto" Illusion:</STRONG><SPAN> Setting </SPAN><FONT face="courier new,courier"><SPAN>spark.sql.shuffle.partitions="auto"</SPAN></FONT><SPAN> in a streaming query often did nothing, silently falling back to the default </SPAN><STRONG>200 partitions</STRONG><SPAN>. This left clusters underutilized on large workloads or created too many small files on tiny ones.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Manual Tuning:</STRONG><SPAN> You had to hard-code partition counts (e.g., 4000) based on peak traffic, leading to inefficiency during quiet periods.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>The </STRONG><FONT face="courier new,courier"><STRONG>foreachBatch</STRONG></FONT><STRONG> Hack:</STRONG><SPAN> To get around this, many developers resorted to complex </SPAN><FONT face="courier new,courier"><SPAN>foreachBatch</SPAN></FONT><SPAN> workarounds to force a static batch plan, thereby triggering AQE. While effective, this added unnecessary complexity and maintenance overhead.</SPAN></LI> </UL> <H3><STRONG>The Solution: Adaptive Streaming in DBR 18</STRONG></H3> <P><SPAN>In DBR 18, the engine is finally smart enough to handle this for you. For stateless queries (like filters, projections, and stream-static joins), Spark now leverages the same adaptive intelligence used in batch jobs.</SPAN></P> <H4><STRONG>Auto Optimized Shuffle (AOS) "Just Works"</STRONG></H4> <P><SPAN>When you set </SPAN><FONT face="courier new,courier"><SPAN>spark.sql.shuffle.partitions</SPAN></FONT><SPAN> to </SPAN><SPAN>"<FONT face="courier new,courier">auto</FONT>"</SPAN><SPAN>, it actually behaves as "auto".</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>How it works:</STRONG><SPAN> AOS automatically determines the initial pre-shuffle partition count based on the estimated size of the micro-batch.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>The Impact:</STRONG><SPAN> No more hard-coded magic numbers. Whether your micro-batch is 100MB or 100GB, AOS sizes the shuffle correctly.</SPAN></LI> </UL> <H4><STRONG>AQE Enters the Chat</STRONG></H4> <P><SPAN>Once the shuffle happens, Adaptive Query Execution (AQE) steps in to optimize the plan on the fly.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Coalescing:</STRONG><SPAN> AQE combines small, empty partitions into properly sized ones, preventing the "small file problem" downstream.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Skew Handling:</STRONG><SPAN> It detects skewed data partitions and splits them dynamically to prevent straggler tasks from holding up the stream.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Join Optimization:</STRONG><SPAN> It can switch join strategies (e.g., <FONT face="courier new,courier">SortMerge</FONT> to <FONT face="courier new,courier">Broadcast</FONT>) at runtime based on actual data volume.</SPAN></LI> </UL> <H3><STRONG>Why This Matters</STRONG></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Simpler Code:</STRONG><SPAN> You can delete those foreachBatch wrappers and manual config overrides. Your streaming code focuses on business logic, not infrastructure tuning.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Better Performance:</STRONG><SPAN> Your streams instantly adapt to changes in data volume. A spike in traffic results in more partitions; a lull leads to fewer.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Cost Efficiency:</STRONG><SPAN> No more over-provisioned clusters waiting on a few skewed tasks. AQE ensures resources are used efficiently.</SPAN></LI> </UL> <H3><STRONG>Getting Started</STRONG></H3> <P><SPAN>Upgrading is simple. Move your workspace to </SPAN><STRONG>DBR 18+</STRONG><SPAN>, and these optimizations are enabled by default for stateless queries.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>AQE:</STRONG><SPAN> Enabled by default (<FONT face="courier new,courier">spark.sql.adaptive.streaming.stateless.enabled = true</FONT>).</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>AOS:</STRONG><SPAN> Just set <FONT face="courier new,courier">spark.sql.shuffle.partitions = "auto"</FONT>.</SPAN></LI> </UL> <P><SPAN>Note: These improvements currently apply to stateless operators. Complex stateful repartitioning requires different heuristics and remains a focus for future Project Lightspeed efforts.</SPAN></P> <H3><STRONG>Conclusion</STRONG></H3> <P><SPAN>With DBR 18, we are one step closer to a world where you simply write business logic, and the engine handles the rest. By bringing AQE and AOS to stateless streaming, we’re removing the "tuning tax" from data engineers, letting you focus on building value rather than managing partitions.</SPAN></P> <P><SPAN>Stay tuned for more updates from Project Lightspeed!</SPAN></P> <H3><STRONG>FAQ: AQE and AOS in Streaming</STRONG></H3> <P><SPAN><STRONG>Q:</STRONG> Does this update apply to stateful streaming operations like aggregations or </SPAN><FONT face="courier new,courier"><SPAN>mapGroupsWithState</SPAN></FONT><SPAN>? <STRONG>A:</STRONG> No. Currently, these AQE and AOS improvements apply specifically to </SPAN><I><SPAN>stateless</SPAN></I><SPAN> queries (e.g., filters, projections, stream-static joins). Stateful repartitioning requires strict state store compatibility and is not yet supported by these specific features.</SPAN></P> <P><SPAN><STRONG>Q:</STRONG> Do I need to change my code to enable AQE for streaming? <STRONG>A:</STRONG> In DBR 18+, AQE for stateless streaming is enabled by default via </SPAN><FONT face="courier new,courier"><SPAN>spark.sql.adaptive.streaming.stateless.enabled</SPAN></FONT><SPAN>. You do not need to change code, though you should ensure </SPAN><FONT face="courier new,courier"><SPAN>spark.sql.shuffle.partitions</SPAN></FONT><SPAN> is set to </SPAN><FONT face="courier new,courier"><SPAN>"auto"</SPAN></FONT><SPAN> to leverage AOS.</SPAN></P> <P><SPAN><STRONG>Q:</STRONG> How does AOS determine the partition count for a micro-batch? <STRONG>A:</STRONG> AOS estimates the size of the incoming micro-batch data and calculates an appropriate number of pre-shuffle partitions, ensuring the cluster isn't over-provisioned for small batches or choked by large ones.</SPAN></P> <P><SPAN><STRONG>Q:</STRONG> Will this fix "small file" problems in my downstream Delta tables? <STRONG>A:</STRONG> Yes, indirectly. By using AQE to coalesce small partitions during the write phase, the engine writes fewer, larger files to storage, reducing the need for aggressive downstream </SPAN><FONT face="courier new,courier"><SPAN>OPTIMIZE</SPAN></FONT><SPAN> or auto-compaction jobs.</SPAN></P> <P><SPAN><STRONG>Q:</STRONG> Can I remove my existing </SPAN><FONT face="courier new,courier"><SPAN>foreachBatch</SPAN></FONT><SPAN> logic? <STRONG>A:</STRONG> If you were using </SPAN><FONT face="courier new,courier"><SPAN>foreachBatch</SPAN></FONT><SPAN> solely to trigger batch-style AQE optimizations for a stateless write, you can likely revert to a standard streaming write in DBR 18. If the </SPAN><FONT face="courier new,courier"><SPAN>foreachBatch</SPAN></FONT><SPAN> contains other custom logic (such as merging to external systems), you must keep it.</SPAN></P> Thu, 19 Feb 2026 17:58:34 GMT craig_lukasik 2026-02-19T17:58:34Z https://community.databricks.com/t5/technical-blog/dbr-18-streaming-workload-simplification-with-improved-aqe-and/ba-p/147752 <P><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="0">Databricks Runtime 18 is finally ending the era of manual shuffle tuning for stateless streaming workloads. For years, data engineers struggled with the "200 Partition" trap, forcing complex<SPAN>&nbsp;</SPAN></SPAN><CODE class="appsElementsGenerativeaiAstInlineCode">foreachBatch</CODE><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="2"><SPAN>&nbsp;</SPAN>workarounds and hard-coded partition counts to manage data fluctuations. Now, thanks to<SPAN>&nbsp;</SPAN></SPAN><STRONG><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="3">Project Lightspeed</SPAN></STRONG><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="4">,<SPAN>&nbsp;</SPAN></SPAN><STRONG><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="5">Adaptive Query Execution (AQE)</SPAN></STRONG><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="6"><SPAN>&nbsp;</SPAN>and<SPAN>&nbsp;</SPAN></SPAN><STRONG><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="7">Auto Optimized Shuffle (AOS)</SPAN></STRONG><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="8"><SPAN>&nbsp;</SPAN>are fully supported for<SPAN>&nbsp;</SPAN></SPAN><I><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="9">stateless</SPAN></I><SPAN class="appsElementsGenerativeaiAstAnimated" data-ast-node-id="10"><SPAN>&nbsp;</SPAN>Structured Streaming queries, automatically optimizing partition counts, handling data skew, and coalescing small files on the fly. Upgrade to DBR 18+ to instantly adapt your streams to any data volume, simplify your code, and bring the reliable "set it and forget it" performance of batch processing to your streaming pipelines.</SPAN></P> Thu, 19 Feb 2026 17:58:34 GMT https://community.databricks.com/t5/technical-blog/dbr-18-streaming-workload-simplification-with-improved-aqe-and/ba-p/147752 craig_lukasik 2026-02-19T17:58:34Z