Missing records in Datalake

[vineethalisyam]

I am a newbie to Azure and Business Central. Our vendor built bc2adls for us and I joined the team recently. I am unable to figure out one of the issues we have been having, so I was wondering if I could get help here. We noticed that we are missing some records in the Datalake and that is so random. What I do as a temporary fix is to reset table in the Export to Azure Datalake Extension in BC and that adds missing records back to the tables. We see the records go missing again could be at the next run or the next day or after a few days. Yesterday, there were about 14 records missing from one of the tables and when I did the reset, they all appeared back.

[Bertverbeek4PS]

Hi @vineethalisyam, Which table do you try to export? Is that for all tables? And what is the frequency that you are exporting? And also run the pipelines. Because the export to data lake has to be finished to start your pipelines.

[vineethalisyam]

Hi @Bertverbeek4PS,

Thanks for getting back, appreciate it. Both the export and pipelines run every hour. We export around 15 tables. One of the tables we have issues with the most is the valueentry_5802, and yes it happens with more than one table. I agree that the export must finish before the pipeline starts. However, I am talking about the data from a month ago or from 15 days ago which should already be in the data lake. Because of this, we are using reports with source as O-data and that's making things very slow.

The other day, we were missing records from SalesShipmentLine table. The query was run on the 18th of October but some of the records that were missing are from 16th of October. After I did the reset in BC, they appeared again. The data is not massive either, we have less than 300000 rows in all these tables.

Also, I don't see any errors in the pipelines or in the BC Execution Logs.

[Bertverbeek4PS]

If it is old data that is disappeared then maybe the problem is in the dataflow inside Synapse @vineethalisyam. Never seen that issue. Is it possible that I can have the data of the valueentry table to investigate (strange is that value entries you cannot delete)? And maybe a look in the dataflow (is there a modification on it?).

[tglink72]

Hello All,

  Good day, hope all is well. I am seeing this same issue with a client. In our case the Customer-18 table seems to be losing records in Azure Data Lake (ADL). We then reset and resend the data and it is synched. The client noticed customers missing in their reports from ADL. We did some digging and found that there were 75 Customers referenced in the Customer Ledger Entry table that did not exist in the Customer table in ADL. We confirmed the customers did exist in BC and then ran the reset process to synch yesterday. Post reset we confirmed the tables were in synch. Today, after the automated nightly delta extract and processing, when checking on the tables in ADL I notice 6 customers not in the Customer table that are referenced in Customer Ledger Entry and that do exist in BC.
   We have not made any changes to the pipelines themselves. We are in the process of troubleshooting and research on what may cause this issue. If others are seeing it I would love to work together to see if we can solve this :)

Thanks

Tom Link

[Bertverbeek4PS]

Thanks @tglink72 for your comment. So it is a general issue. Is it possible to safe the delta files in the folder? Then we can exclude it it is done in exporting the data from BC of in the dataflow in synapse.

[tglink72]

Hello,

           Thanks so much for the reply! I will change the Synapse process to not delete the deltas for our mid-day processing, which occurs at noon easter time US. I will then download the files so we can review.

Thanks

Tom Link

From: Bert Verbeek @.> Sent: Friday, October 27, 2023 6:53 AM To: Bertverbeek4PS/bc2adls @.> Cc: Tom Link @.>; Mention @.> Subject: Re: [Bertverbeek4PS/bc2adls] Missing records in Datalake (Issue #53)

Thanks @tglink72https://github.com/tglink72 for your comment. So it is a general issue. Is it possible to safe the delta files in the folder? Then we can exclude it it is done in exporting the data from BC of in the dataflow in synapse.

- Reply to this email directly, view it on GitHubhttps://github.com/Bertverbeek4PS/bc2adls/issues/53#issuecomment-1782784873, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AZOTVUI4NF4REHUJCX6UEOLYBOOCNAVCNFSM6AAAAAA6MZSLSOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBSG44DIOBXGM. You are receiving this because you were mentioned.Message ID: @.***>

[Bertverbeek4PS]

@tglink72 Do you have multiple companies flowing in the same folder in Azure Data Lake? Because it is not filtering on company column. So that could be the issue. If yes I will look into the dataflow.

Maybe the same issue as: https://github.com/Bertverbeek4PS/bc2adls/issues/56 But then on the Data Lake side.

[tglink72]

Bert,

           Thanks for the email. We do not have multiple companies flowing into the Azure Data Lake, just a single prod company. I did capture the deltas for a run last week as you requested and I would be happy to provide them or review them with you.

Thanks

Tom Link

From: Bert Verbeek @.> Sent: Monday, October 30, 2023 1:47 AM To: Bertverbeek4PS/bc2adls @.> Cc: Tom Link @.>; Mention @.> Subject: Re: [Bertverbeek4PS/bc2adls] Missing records in Datalake (Issue #53)

@tglink72https://github.com/tglink72 Do you have multiple companies flowing in the same folder in Azure Data Lake? Because it is not filtering on company column. So that could be the issue. If yes I will look into the dataflow.

Maybe the same issue as:

56https://github.com/Bertverbeek4PS/bc2adls/issues/56

But then on the Data Lake side.

- Reply to this email directly, view it on GitHubhttps://github.com/Bertverbeek4PS/bc2adls/issues/53#issuecomment-1784575388, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AZOTVUPIAL2U4VGX4QN4OHDYB5ENNAVCNFSM6AAAAAA6MZSLSOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBUGU3TKMZYHA. You are receiving this because you were mentioned.Message ID: @.***>

[Bertverbeek4PS]

Hi @tglink72 It is maybe possible that I can have some delta file's that doesn't contain any customer data? Together with the manufest files. Then I can debug it maybe better.

[tglink72]

Bert,

           Thanks again for the email. Unfortunately, the deltas will have customer data in them and we are not seeing this issue in other master record tables (Item,Vendor...) Could we do a screenshare early tomorrow morning US central time? Or is there a secure way I can get you the data?

Thanks

Tom Link

From: Bert Verbeek @.> Sent: Monday, October 30, 2023 8:00 AM To: Bertverbeek4PS/bc2adls @.> Cc: Tom Link @.>; Mention @.> Subject: Re: [Bertverbeek4PS/bc2adls] Missing records in Datalake (Issue #53)

Hi @tglink72https://github.com/tglink72 It is maybe possible that I can have some delta file's that doesn't contain any customer data? Together with the manufest files. Then I can debug it maybe better.

- Reply to this email directly, view it on GitHubhttps://github.com/Bertverbeek4PS/bc2adls/issues/53#issuecomment-1785143098, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AZOTVUOGSTFGMHDASBPK7ATYB6QDXAVCNFSM6AAAAAA6MZSLSOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBVGE2DGMBZHA. You are receiving this because you were mentioned.Message ID: @.***>

[Bertverbeek4PS]

Well you can always mail me at bverbeek@4ps.nl and maybe remove the customer specific details. This week I'm attending directions so a meeting is doable next week.

[Bertverbeek4PS]

@tglink72 I have tested with multiple companies and same GUID and that will run fine. To there is another issue in the pipelines.

[tglink72]

Hello Bert,

           Good day, hope all is well. I have captured the issue in a delta process. I have the delta files from prior to the Synapse pipeline run. Prior to the run the customers were in synch with the Customer Ledger entry. After the run the 3 customers below were no longer in ADL. The customer records were in the Delta file for this pipeline process. I am confirming with the customer that I can provide the deltas. If approved and if there is a way I can securely provide them I will. If you would like to do a screenshare at a time that works for you I can send an invite.

C0245979 C0400739 C0247201

Thanks

           Tom Link

From: Bert Verbeek @.> Sent: Monday, October 30, 2023 9:32 AM To: Bertverbeek4PS/bc2adls @.> Cc: Tom Link @.>; Mention @.> Subject: Re: [Bertverbeek4PS/bc2adls] Missing records in Datalake (Issue #53)

@tglink72https://github.com/tglink72 I have tested with multiple companies and same GUID and that will run fine. To there is another issue in the pipelines.

- Reply to this email directly, view it on GitHubhttps://github.com/Bertverbeek4PS/bc2adls/issues/53#issuecomment-1785344974, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AZOTVUJYORPOZK3SNVWB5VTYB624VAVCNFSM6AAAAAA6MZSLSOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBVGM2DIOJXGQ. You are receiving this because you were mentioned.Message ID: @.***>

[Bertverbeek4PS]

Thanks @tglink72 thst would be great to have and analyse! Sure a screenshare and trandlsfer isn't a problem for me.

[tglink72]

Bert,

           Thanks for the reply. What times work best for you to review? I am US Central time and can make early morning work, if that works for you.

Thanks

TL

From: Bert Verbeek @.> Sent: Tuesday, October 31, 2023 5:32 PM To: Bertverbeek4PS/bc2adls @.> Cc: Tom Link @.>; Mention @.> Subject: Re: [Bertverbeek4PS/bc2adls] Missing records in Datalake (Issue #53)

Thanks @tglink72https://github.com/tglink72 thst would be great to have and analyse! Sure a screenshare and trandlsfer isn't a problem for me.

- Reply to this email directly, view it on GitHubhttps://github.com/Bertverbeek4PS/bc2adls/issues/53#issuecomment-1788124904, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AZOTVUOLIZ4HXGLNF5KX3UDYCF36XAVCNFSM6AAAAAA6MZSLSOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBYGEZDIOJQGQ. You are receiving this because you were mentioned.Message ID: @.***>

[Bertverbeek4PS]

@tglink72 could you email me on bverbeek@4ps.nl so I can share the dates in a private email? It will be next week because this week I attend Directions Emea.

[Bertverbeek4PS]

@tglink72 issue with merge of customers, vendors and contact is fixed in: https://github.com/Bertverbeek4PS/bc2adls/pull/59