[Help Wanted] Clarification on how to process delta files for integration into BigQuery.

[Isarien]

Hello Everyone,

First thank you for the work done on this project. It very usefull.

Then my question :

My company has multiple business central servers managed by a software integrator. To feed our datalake hosted in Big Query, they advised us to use the ADLSE plugin. We directly use delta files as input to Big Query (aggregated into a raw table), and with DBT, we generate snapshots of the tables.

I understand that deleted rows correspond to rows with a null "systemCreatedAt" value. However, for data updates, I need clarification. Should I rely solely on the fields systemId, Company, and systemModifiedAt (keeping only the latest updated data like writed in spark code here), or should I consider the concept of keys defined in the source code that define the business central tables (especially the keys with the clustered attribute) ?

I ask this question because I found this information in the BC documentation :

[Bertverbeek4PS]

Hi @Isarien, You can indeed use the data files directly. In that way can do the following in Big Query (not familiar with that).

1. Combine the data that is already there with the delta files
2. Delete the deleted records from Business Central by filtering SystemCreatedAt-2000000001 and remove it the records with the same SystemId
3. Order the dataset by $Company and systemId-2000000000 and order by the field SystemModifiedAt-2000000003. And delete the last record with the same Company and SystemId.

Then you can save the dataset to your solution. That is also the same what the notebook does. So you don't have to look at the BC documentation.

Hope this will make it clear for you.

[Isarien]

@Bertverbeek4PS Thank you for your answer. It's clear.