Help with incrementally refreshing an entity computed from two separate dataflows

[purple_SP]

Hi everyone, 

I am have a dataflow that I am trying to optimize in my workspace and was hoping to get some advice regarding the incremental refresh. Essentially the dataflow is generated from merging two other dataflows.

The first dataflow stages some data from sharepoint in an entity like so:

date	id	used
22/02/2024	AEF	Y
23/02/2024	BCP	N

And the second dataflow stages some data from the data warehouse into an entity like this:

date	code	group	metric
22/02/2024	ACP	A	1
22/02/2024	AOP	A	5
22/02/2024	AEF	B	3
22/02/2024	AEF	C	4
22/02/2024	BCP	B	2

Essentially I want to filter rows in the second dataflow to only contain codes that match to ids in the first entity.

The dates in the first entity are irrelevant.

At the moment, I have been grouping the first entity by "id" and "used", then merging it (left joining) with the second entity and filtering rows where code is null:

grouped entity:

code	used
AEF	Y
BCP	N

After merge and filtering:

date	code	group	metric
22/02/2024	AEF	B	3
22/02/2024	AEF	C	4
22/02/2024	BCP	B	2

This is working, but it is very slow. I suspect this is due to the size of the second entity.

The data should be refreshed daily. However, while the table coming from the datawarehouse is being incrementally refreshed, I suspect that the full table for the final entity is doing a full refresh everyday.

Is there a way to ensure that only the most recent data is being refreshed in the final entity? 

Would it make more sense to left join the second entity to the first instead?

[Anonymous · Accepted solution]

Hi  @purple_SP ,

Instead of merging the entire second entity with the grouped first entity, consider filter the second entity firs, you may apply a filter to the second entity to only include rows with codes that exist in the first entity. This can be done using a list of unique IDs from the first entity. If possible, use an inner join instead of a left join, as it can be more efficient when you only need matching rows.

You can use parameters to dynamically filter the data being loaded. Create a parameter for the date range you want to refresh. then apply this parameter to filter the second entity before merging.

Finally you may consider using staging tables to store intermediate results. This can help break down the process into smaller.

You can stage the filtered second entity, create a staging table that only contains the filtered rows from the second entity and then merge with the first entity, which perform the merge operation on the staged data.

Best regards.
Community Support Team_Caitlyn