Load large fact table incrementally takes "long" time.

  • 12 September 2023
  • 1 reply

Is it normal when loading fact table incrementally with new data, that it can take up to one hour?
Are there any tips to have this incremental load to perform better?

Moving records from staging table to fact table
fact table is 140 million records, new load is around 500 thousand records with new timestamp

here is the execution log overview


the table is not that complex.

17 fields are moved over, from staging to fact table

1 field is from keystore

3 fields are have custom value script


table settings are..



we are running 

TX, version.  6346.1
Azure database, running with 6 cpu


Best answer by daniel 12 September 2023, 20:08

View original

1 reply

Userlevel 4
Badge +6

Dear @gislihei ,

What i've found is that the supernatural key might be the bottleneck in this case.

Supernatural keys will check every record every incremental load for changes and that keeps the keys at full integrity, but it will take a long time.

I've posted a similar topic here: 

I'm using the option that @erik.van.mastrigt offers here:

using supernatural keys in the dimension and doing a lookup on the business key to the fact table.

Compression might also be an issue, but that really depends on several other factors. You might want to test that but i'm pretty sure the supernatural key is the performance killer here

Hope this helps

= Daniel