@Michael Popp​ :

To load a high volume of CSV rows in the fastest way possible into a Delta table in Databricks, you can follow these approaches and optimizations:

A. Uploading and loading multiple CSV files:

1. Yes, uploading multiple CSV files and loading them with a single stream reader/stream writer statement can introduce parallelism into the process. By leveraging multiple files, you can take advantage of parallelism in Databricks to load the data more quickly.
2. If you have a single CSV file, it will be loaded using a single thread, which can be slower compared to parallel processing with multiple files.

B. Optimal number of files and file sizes:

1. The optimal number of files can be determined based on the number of worker nodes available in your Databricks cluster. To achieve parallelism, you can have a number of files equal to or multiple of the worker nodes.
2. Additionally, the file sizes also matter. It is recommended to have reasonably sized files that can be processed efficiently. Very large files may cause memory and performance issues, so you may want to split the data into smaller files.
3. Experimentation with different file sizes and numbers of files can help you determine the optimal configuration for your specific use case. You can try adjusting the file sizes and number of files to find the best balance between parallelism and efficient processing.

C. Other optimizations to improve load times:

1. Utilize cluster auto-scaling: Enable cluster auto-scaling to automatically add or remove worker nodes based on the workload. This helps handle increased load during data ingestion and speeds up the process.
2. Partitioning and bucketing: If possible, consider partitioning and bucketing your data in the Delta table. Partitioning can improve query performance, and bucketing can further enhance the efficiency of data retrieval.
3. Use proper compression: Choose the appropriate compression algorithm for your data to reduce storage and improve I/O performance. Snappy or gzip compression is commonly used.
4. Optimize the number of columns: If your target table has many columns, consider only selecting the required columns during the load process. This can reduce the data size and improve loading performance.
5. Tune cluster settings: Adjust the cluster configuration based on the workload. Increase the number of cores, memory, and shuffle partitions to optimize the performance during data ingestion.
6. Monitor and optimize the write performance: Keep an check on the write performance of your Delta table and tune the batch sizes and other parameters accordingly.

By considering these approaches and optimizations, you can significantly improve the load times for your high-volume CSV data ingestion into a Delta table in Databricks.