Forum Discussion

smeetsh's avatar
smeetsh
Icon for Continued Contributor rankContinued Contributor
2 years ago
Solved

Inconsistent 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,...
pipelines
  • smeetsh's avatar
    smeetsh
    2 years ago

    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.

    Cheers

    Hans

frithjof_v's avatar
frithjof_v
Icon for Community Champion rankCommunity Champion
2 years ago

Thanks 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.

  • smeetsh's avatar
    smeetsh
    Icon for Continued Contributor rankContinued Contributor
    2 years ago

    This certainly matches up with other rumours we have heard. Looking at the date of the document it is extremely new. Rumours we have picked up from others sources indicated this was introduced after a preview release not to long ago, which makes sense looking at the document date.