&"Interesting&" Table Design...need Opinions

Need some opinions on how to query a set of tables designed like this:

Main table: Subscriber

Table has 80 columns, each column has a table which is written to via an update trigger everytime the column changes. Subscriber table is Type 1 dimension but the "Change" tables are Type 2 dimension. The "change" tables have 3 columns, SubscriberID, ChangeDate, NewValue.

The requirement is to push a Type 2'd version of the Subscriber table into a data warehouse for historical reporting. Meaning the users need to be able to see what a Subscriber looked like on an exact date.

Has anyone had any experience with this type of structure? If so, how did you join together the Subscriber table to all the change tables to extrapolate a "view" of the subscriber on each given day? You can assume that a record can only change once in a day, but not every column will change on the record.

I was thinking of something like this:


SELECT Subscriber.ID, (SELECT TOP 1 NewValue FROM Subscriber_FirstNameChanges b WHERE b.SubscriberID = Subscriber.ID AND b.ChangeDate <= @GivenDate ORDER BY b.ChangeDate DESC)
FROM Subscriber

This would pull the most recent version of the subscribers FirstName based on a given date (I'd have to add in the 79 other change columns with almost identical sub queries). This would also take into consideration that there may not be change records for particular columns on particular dates because not all columns change at the same time/day and pull the most recent "version" of the subs FirstName since @GivenDate.