Generate Metric on how frequently an individual table attribute changes , ideally relative to another

Question

I am building out a data vault & specifically working through when to breakout a satellite table into sub-satellite's by attributes that change at a different rate

That being said, I only have the modified date at the record level ie when any attribute change was made, not a modified date by attribute.

Any advice on how to write a query against a table do tease out frequency of change by attribute?

I am thinking something along the lines of group the attribute by business id & count the number of distinct attribute values... but wouldn't boolean values through that off?

Answer 1

You would have to put that attribute in it's own satellite as records in a satellite are inserted each time a change to the Hdiff occurs, thus all you know is out of the 10 monitored attributes one changed, with no way to know which one. This is by design as hashing all of them is much less intensive than comparing each attribute one by one.

Once this attribute is in it's own satellite you can use the load date timestamp to reflect when it changed.