Forum Discussion
Continued ContributorInconsistent ingest of json file
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
Community ChampionThanks for sharing.
Could it be that the data in the SQL Endpoint is not properly synced with the Lakehouse itself, so when you are running the queries to the SQL Endpoint, you get unexpected results.
Perhaps you could try to do the transformations in a Notebook instead of a script activity. Because the Notebook will use the Lakehouse tables directly, instead of the SQL Analytics Endpoint. Just to see if you get the expected results then.
I can see how using Notebook can become a bit tricky especially when loading from Lakehouse to Warehouse. One option is to just have the data scientist use the Lakehouse instead.
To use Notebook to write to Warehouse, there is something called pyodbc. I have never tried it myself and I don't know how performant it will be. Another option would perhaps be to use data pipeline to copy the data from Lakehouse to a staging table in the Warehouse, and then run a stored procedure in the Warehouse to upsert data from the staging table into the production table. This would be a way to avoid using the Lakehouse's SQL Analytics Endpoint, in order to try to isolate the error and see if the process works fine when avoiding the SQL Analytics Endpoint.
Good to hear that you have a support ticket. Please tell if your issue gets solved.
Continued ContributorWe have thought about using a notebook to transform the data to the staging table as well, which would solve that steps problem. But the step after is copying the data to the warehouse from the staging table and for that we would need to use the sql endpoint again, so we suspect that would not realy solve our problem, but just move it to the next step.
Moving the gold medallion to a lakehouse in stead of a warehouse is not an option right now, since that is no small change for us.
Once last thing, which I did actualy ask MS about while updating the case, is that we have seen in development, when doing a lot of testing and changing, sometimes you will have to force the table in the lakehouse to force a checkpoint, by using a notebook.
Since MS themself in your article advices to manually press the refresh button (which is vey hit and miss by the way in practice) I was wondering if including that "checkpoint notebook", before the ETL step would maybe force the update of the SQL end point ass well.
Last but not least, since this is a prodcution issue for us, we have now created a raw warehouse, which ingests the json files (created in the raw lakehouse) into a raw warehouse table and from there we do the ETL. This seems to work for now.