I'm currently working with a table in Azure Data Explorer (ADX) and facing a challenge where I need to identify columns that have been consistently empty over the past year. This table is quite extensive, both in terms of the number of columns and the volume of data it contains, making the task more complex.
My primary goal is to ensure that the method I use to find these empty columns is not only accurate but also optimized for performance, considering the large dataset.
Could you provide guidance or a query strategy on how to effectively approach this problem in ADX?
Additionally, if there are any best practices or performance considerations to keep in mind when dealing with such large-scale data in ADX, that would be immensely helpful.
My question is how can I efficiently identify all empty columns in a table from the past year in ADX?
(Please keep in mind that I have a table with 400 columns and billions of records so I need it to be as efficient as possible)