Forum Discussion
Continued ContributorInconsistent ingest of json file
Hi All,
We use a pipeline that pulls json data from an API. in the next step of that pipeline, we ingest those json files into a lakehause "raw" table. Once the 5 json files are in the raw table, the next step is an ETL to the production tables.
The problem we are facing is inconsitency in the ingestion of the json files to the raw table. The step automatically runs at 5 am, but we see that some data in the json , from the day before, is actualy not ingested into the raw table, while other data is.
When I look at the actual json file, I see the data is actually in the file itself, so there is no reason for it not to be in the raw table. The realy weird thing is, if i run the pipeline manualy at say like 11 am, new files are created, which than get ingested in the raw table correctly.
I have done a compare between the 5 am and the 11 am file and they are identical, except of course for the date they were created.
The pipeline itself completes without any error. The create json and ingest json loop does run concurrent, since it involves 5 entities and the supplier API allows us to run concuurent sessions.
Since there seems to be no logic to why it will work in one run and not in the other, I have no clue where to even start troubleshooting this.
I initially thought it was maybe because of the concurrent connections, but than I would expect it to miss data on the 11am manual run as well, which it appears not to. (I would think lakehouse would have no isses dealing with 5 consecutive datastreams anyway)
Could the concurrency still be the problem, or does anyone have any ideas where to start to trouble shoot this one?
Warm regards
Hans.
Good morning all,
A small update and a recap.
MS has been in touch and I had a teams call with them. The "pipeline" team has now also been involved. No answer yet, but I am not sure if we ever will get a solution, looking at the article MS posted re: the fact there can be a delay of on average 1 minute (but can be more, and we have seen it as more) between the lakehouse sql endpoint and the data in the lakehouse itself, even though this issue by the looks of it was introduced in the July updates (we havent experienced this before and colleagues of mine hadn't either)So where does this leave us? the below workaround works for us, but we will have to re-write a few pipelines
Workaround:Use the lakehouse_raw to get the files and store them as files in the lakehouse, next use a copy data activity to ingest the files and copy the the data to a warehause_raw. The warehouse_raw being a new bronze medallion.
From there you can run a copy data activity (or a stored procedure, or a script, or whatever you need to do) to ETL the data. In our case that would be staged into a staging table (silver medallion) in the warehouse_raw, but that would vary per use case of course and last but not least, use another activity to copy the data to the warehouse for the BA's to use.
Of course the steps above are for our scenario, yours may vary.
CheersHans
14 Replies
smeetshGood morning all,
A small update and a recap.
MS has been in touch and I had a teams call with them. The "pipeline" team has now also been involved. No answer yet, but I am not sure if we ever will get a solution, looking at the article MS posted re: the fact there can be a delay of on average 1 minute (but can be more, and we have seen it as more) between the lakehouse sql endpoint and the data in the lakehouse itself, even though this issue by the looks of it was introduced in the July updates (we havent experienced this before and colleagues of mine hadn't either)So where does this leave us? the below workaround works for us, but we will have to re-write a few pipelines
Workaround:Use the lakehouse_raw to get the files and store them as files in the lakehouse, next use a copy data activity to ingest the files and copy the the data to a warehause_raw. The warehouse_raw being a new bronze medallion.
From there you can run a copy data activity (or a stored procedure, or a script, or whatever you need to do) to ETL the data. In our case that would be staged into a staging table (silver medallion) in the warehouse_raw, but that would vary per use case of course and last but not least, use another activity to copy the data to the warehouse for the BA's to use.
Of course the steps above are for our scenario, yours may vary.
CheersHans
Hi smeetsh ,
While you mentioned that there is nothing wrong with concurrent pipeline runs, concurrency can still cause intermittent problems. You may want to check if there are any contention conditions or resource contention during the 5am run.
Also how you are introducing the json file, you may need to specify format='multijson' instead of format='json'. Please provide relevant screenshot information and describe it.
Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- smeetsh
Good afternoon all, a little update on this. It looks like the SQl endpoint for the lakehouse doesn't immediately refelect the data changes in the lake. Which causes these weird intermittent issues.
Is this a known issue?
frithjof_vCommunity Champion
I have seen several users who have mentioned confusing results due to delay between Lakehouse and SQL Analytics Endpoint.
Why/how are you querying the SQL Analytics Endpoint?
Could you query the Lakehouse tables directly instead?
- smeetsh
when moving data from the raw lakehouse table to the staging table (also in the same lakehouse) we have to do a lot of grouping and ETL Using SQL is the easiest way of doing that.
When researching further we found that the data is actually in the raw table, but not in the staging table and that step is the one where we use the sql end point. We could create a notbook to do the same, but if the same delay happens in the staging table, than there is no garuantee that we wont face the same issue when moving the cleaned, grouped and relational data to the warehouse. Our data scientist uses the warehouse to do his work.
