CDC when source table is used in multiple stars?

[kardnal]

For incremental loading, how do people track changes (inserts, updates, deletes) in tables that are used in multiple stars?

For example, source table A is the driving table for records in dimension table B, and is used in the calculation of a measurement (e.g. a sum or max) in fact C. Again, assume dimension B and fact C are not necessarily in the same star schema.

Now, say there is a change to table A. How do I make sure this change is reflected in both dimension table B and the measurement in fact C, while keeping the mechanism reliable and as simple as possible?

It is not viable to delete and reload either the dimensions or facts...it is important to only load the delta changes after the initial load.

Thanks for any help!

 

[RiverGuy]

Here's how I handle a similar situation.

I have one particular source system which feeds three tables in two databases.

Firstly, based on my last-date-updated in my metadata database, I truncate and load any changes to the source table into my staging table.

Next, I load those records into a persistent copy of the source table I keep in my database. After the load is complete, I update my metadata to the new last-date-updated value. I also keep my own last updated column in the persistent copy based on when my ETL updated the record.

As you can probably guess, my source for my three final destination tables is my persistent copy of the source table. This helps a lot because if I need to reload just one or two of my final destination tables, I don't have to bring in a full load from the original source and reload all of my final destinations. I can simply utilize different queries based on different last updated values to load a different set of data for each final destination.