Forum Discussion

michaelgambling's avatar
michaelgambling
Advocate I
1 year ago
Solved

Data Ingestion Troubles

Hi All,   Pulling my teath out a bit trying to get my head around data ingestion.    I am new to Fabric and data engineering in general. I am trying to set up a metadata driven pipeline that impl...
data engineering
  • smeetsh's avatar
    smeetsh
    1 year ago

    Is it an option for you to use SQL and the gateway connector to get your data from the on-prem SQL server and load that in , what i would call, a raw table (for instance tablename:  mydata_raw) and next  use a merge in a notebook to merge your raw data with your existing data? Since you only ingest specific data from your sql server, the overwrite of your "mydata_raw" table, should not impact CU's to much.

    The notebook code would look something like below ("name" is just an example column)

    %%sql
    MERGE INTO mydata AS target
    USING mydata_raw AS source
    ON target.id = source.id
    WHEN MATCHED THEN
    UPDATE SET target.name = source.name 
    WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);

     

    Cheers

    Hans
     

michaelgambling's avatar
michaelgambling
Advocate I
1 year ago

Thanks all to whom have written, just to provide an update i did utilise some merge logic as smeetsh reccomended,

 

I have used this as a workaround to the upsert issues should anyone else be facing simular problems when trying to implement there watermark strategy. I have structured my pipeline as per the below canvas image:
 
 
The pipeline is derived mainly from this article. However i have made some changes,
 
1) I used a notebook activity to:
 
1) Create an ainitial staging.TABLE.
2) Create another destination.TABLE.
 
My copy data activity now copies initially to the 'staging' table, and then a notebook follows to compare the pre-existing contents of the destination table to that of the staging, and merge any changes, once done the staging table truncates to clear the data ready for the next run.
 
 
Although this takes more time it is the only workaround i have found to conduct a working upsert operation, if anyone has any suggestions or reccomendations please do let me know?
 
I had to tweak it a bit to meet my on-prem scenario (.4 of Step 4) due to the fact I am ingesting tables and not files. Besides that the only other additional step assed was the MERGE & TRUNCATE notebook alongside pre-creating the two tables prior to running the pipeline.
 
I now have a schedule to run hourly overnight to ensure data intergrity is kept between the source and sink.