Incremental refresh and dimension table surrogate keys

[emsrc]

Hello,  I need to know if the Power BI dataflow incremental refresh feature is able to help with this scenario.  I am building a star schema with a dimension table called Client.  The Client table grows as new clients sign up for our service.  Since it's a dimension table, I want to create a surrogate key for each Client row.  I would like to use the PBI dataflow Incremental Refresh feature to only add the new Client rows to the Client dimension table.  What I don't understand is if/how the surrogate key column will be managed with Incremental Refresh.  I can mark the surrogate key column to be the primary key in PBI.  Does that mean it will auto-increment the surrogate key for the new rows?  I don't want the surrogate keys for the existing rows to change since I'd then have to reprocess all of the fact tables.  Using previous ETL tools, I've handled this by creating an auto-incrementing, primary key column in a data warehouse  and simply inserted the new rows into the dimension table.  Is this what Incremental Refresh does behind the scenes?  Or is it not meant to handle this case.

Any insight you can provide would be very helpful.

Thanks,

Ed

[GilbertQ]

Yeah that is what I thought hopefully that will help you.

[emsrc]

@GilbertQ, thanks.  I can definitely add a date/time column to the dimension table.  I suppose I'll have to manage the starting index value using a parameter myself, but that shouldn't be a big deal.

[GilbertQ · Accepted solution]

Hi @emsrc 

The Power BI Incremental refresh can only be used on a column with Date/Time format. What you could do is to have an insert date on your dimension table.

Then use this Insert date to incrementally process the latest N days. And still use the surrogate key on your dimension to fact table relationships?