@Bauke Brenninkmeijer​ :

It sounds like you are facing a few performance and scalability challenges in your current process. Here are some thoughts and suggestions:

1. Partitioning: Partitioning the Delta table can definitely help with query performance, especially for count queries. When you partition a table, Spark can prune the partitions that don't need to be read, leading to significant query speed-up. You can partition the Delta table based on a column that is frequently used in queries (e.g., timestamp), or any other column that makes sense for your data.
2. Delta Lake optimization: Delta Lake provides several features that can help with performance and scalability, such as Delta caching, Z-Ordering, and auto-optimization. Delta caching allows you to cache frequently accessed tables in memory or on disk for faster query performance. Z-Ordering helps organize the data in a Delta table based on a particular column to improve query performance. Auto-optimization automatically applies various optimizations to a Delta table to improve query performance.
3. Use PySpark instead of Structured Streaming: It sounds like you are using Structured Streaming for your processing steps. While Structured Streaming is great for handling real-time data streams, it might not be the most efficient solution for batch processing of large amounts of data. You could try using PySpark instead, which provides more flexibility and control over your processing pipeline.
4. Consider using Databricks Runtime: If you're not already using it, consider using Databricks Runtime, which is a fully managed and optimized version of Apache Spark. Databricks Runtime includes many optimizations and features that can help with performance and scalability, such as Delta Lake integration, auto-scaling, and GPU support.
5. Splitting data: It's possible that splitting the data and then applying the UDF could improve performance, depending on the size and structure of your data. You could try using the repartition method to split the data into smaller partitions before applying the UDF.
6. Monitoring Spark UI: The Spark UI can provide valuable insights into the performance of your Spark jobs. You can use the Spark UI to monitor the execution plan, identify bottlenecks, and optimize your queries.

I hope these suggestions help you optimize your processing pipeline and improve performance and scalability.