Azure SQL / SQL Server Table Partitioning Strategies

Data Vault 2.0
1

Does any one have any strategies for partitioning link and satellites in Azure SQL / SQL server

I have a link / satellite pair with 132 million rows. I would use the load date, but I am loading the data from historical sources, so all 132 million rows will have the same load date.

The link table obviously has very few columns and these are all hashkeys, I have also included the driving business key (integer), so I could use this. The partitioning of the Link and the Satellite should match for obvious performance reasons.

2

I’m no expert on Azure but…

  • 132 million record link table or just the link-sat?
  • is hashing optimal on Azure? @neil.strange did a whitepaper some years ago maybe he can answer
  • load date would be the same date, but how then are you tracking true changes in the satellite? Is it the applied date? (I advocate for bi-temporal data vaults and each artefact has a load an applied date)