Other than the table design piece, which is important because without detailed knowledge of the data, designing and offering guidance on a solution is limiting. You mentioned Azure SQL Database but reference data warehousing terminology or perhaps a data warehouse solution is a better option? Based upon best practices the below is how this process is accomplished with Synapse Analytics. Not everyone's data is based upon best practices. What I didn't find in this thread, were any best practices as it relates to Azure Synapse Analytics.
There is a good guidance with regard to distributed tables and the common distribution methods for tables:
The table category often determines which option to choose for distributing the table.
Fact Tables:
Use hash-distribution with clustered columnstore index. Performance improves when two hash tables are joined on the same distribution column.
Dimension Tables:
Use replicated for smaller tables. If tables are too large to store on each Compute node, use hash-distributed.
There is also some key guidance on data migrations and data loading and strategies for distributed and replicated tables.
In attempting to go through your dataset to determine table category, agree that the single table approach for keywords is efficient but I don't have clarity on your metrics
or fact data? For the keyword or dimensional data, I would use replicated tables based upon the size of that table in relation to each compute node in your SQL Pool. Queries can be built to join the keyword values between the fact data and the dimensional data.
Other questions remain:
How often are keywords updated and do multiple iterations of a keywords needs to exist based upon date? I am trying to understand your Dimension1Id and Dimension2Id column headers?
Please also see: