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
Continued ContributorHi frithjof_v
1 correct this is what we do.
2 we use a web activity to do the logon for the API, it uses a bearer token,
3 We use a for each loop to query the api and and create the JSON files (7 of them), They can have multiple days of data. In the same loop we read the json file and ingest into the lakehouse raw table.
4 once the files are read into the raw table, there is a copy data step, which uses the lakehouse sql end point to run a sql script to normalise and transform the data. The data goes to another lakehouse "staging table". Here is where we see the discrepancy occur, for instance earlier this aftenoon I ran the API and in the raw table were 11 records for one specific type of data. But in the staging table were only 9 reconds for that dataset, and the last record was 0.00000, in stead of its correct value. (see below)
Once we have ETL'ed the data and normalised it, the staging tabel gets split into 4 tables and loaded into the warehouse, using 4 script activities, (production, production-history and quality and quality-history).
These 4 scripts check for new data to be inserted or older data to be updated, since some data can be updated.
The activities we use are all very basic (some variables, a web activity a loop , a copy data and script activities.
We have also raised a case with MS, and I showed them the discrepancies we are seeing beween the lakehouse data and the sql end point data.
I hope this clarifies it a bit 🙂
cheers
Hans
Community ChampionMy 2cents - I am doing similar processing currently for bulk ingestion
A. Define an array. In my case it is [0,1,2,...19].
forEach A (
A. Get data from the data vendor through an on-prem gateway from their sql server and dump the raw data to file layer (opposed to directly writing to a lakehouse table as it is not recommended at all)
(Copy Activity - {src: on-prem SQL, destination :- Lakehouse files section (in a dedicated folder) as a parquet file})
B. Notebook Activity (defaulted to the same lakehouse as landed file and final target table) - progrmatically look into the the dedicated folder from A and pick up the most recent parquet file that landed and transform using Notebook as opposed to using SQL Endpoint and write to the final target lakehouse table. (factor in upsert, append, overwrite wriritng components)
)
I am currently doing this for 20 tables and it has been good so far.
- smeetsh
We indeed do not write to a lakehouse table directly, the REST API creates json files in a dedicated folder, which then get read and ingested ina for each loop. That step works well.