Best Practice for Incremental Data Load with Slowly Changing Dimensions?

Hi everyone,

I’m working on a data warehouse where we need to handle daily incremental loads from multiple source systems. One of the tables involves Slowly Changing Dimensions (SCD Type 2), and I’m trying to find the most efficient way to manage the delta logic.

Does Advanced ETL Processor have built-in transformation steps or templates to detect changes and create historical rows automatically? Or do most people use staging tables and custom scripts for this?

Would love to hear how others are tackling this — especially when dealing with 100K+ row deltas.

Thanks in advance!

Jhonn Mick
 

Thank you fro asking such a good question it will benefit a lot of users

It is very hard to invent something new when working with datawarehouses, (Although some of the companies think they can).

Everything was already invented a very log ago by Ralph Kimball and Bill Imnon

In my option the best approach is to load data into the staging table as fast and possible and run a script/stored procedure to update a SCD 

I have created an article explaining how it can be done and a video tutorial, I hope you will find it useful.

www.etl-tools.com/articles/how-to-implem...d-etl-processor.html

OK wow, I didn't know AETL could do this, I think great end of the year project for me to create scd regarding our financial statements and accounts data. our ERP stores everything in sql anyways, and we always keep asking, how did the number change so much.

Now this could be a good video series