Based on my experience with data partitioning, it often diminishes performance rather than enhancing it. There are exceptions, like when handling tables over 1TB, or when EVERY single query utilizes partition in the WHERE clause - for instance, a PowerBI table constantly filtering on user or date. In other situations, predicting table usage becomes challenging, and partitioning may lead to decreased performance due to issues such as small file problems. Fortunately, databricks now offers a solution to this problem. Your data will automatically adapt to frequently used patterns, thanks to the introduction of liquid partitioning!
