I'm implementing Azure Synapse and there is a very large fact table on which I want to implement the hash distribution - because accordingly to the guide on Microsoft's docs, this type of distribution improve the query performance and that's what I'm looking for when implementing this.
From what I understand, the best practices when choosing the hash column is:
Column that is evenly distributed: this means the number of rows is generally the same over different values of this columns. The number of distinct values is greater than 60 (because there are 60 nodes in total).
Column that minimizes data movement: according to the guide, this means the column is usually not in the WHERE clause, but in GROUP BY, JOIN, DISTINCT, OVER or HAVING clause.
The two conditions above make the situation extremely difficult. In my case
I have a column that is quite evenly distributed. However, the number of unique values of this column is unfortunately less than 60 (it has only 48 unique values right now).
The rest of the columns are not very evenly distributed.
And most of all, in general, our data analyst will use WHERE clause on any of the columns as they are calling different queries for different reasons. The requirements are quite dynamic. So it's really difficult to find a column that is not being used in the WHERE clause and have a great number of unique values and evenly distributed.
Do you guys have any advice on this generally?
Specifically, if the column is not evenly distributed, will it affect the performance a lot?
Thank you guys.