Forum Discussion
Data Ingestion Troubles
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 glad you found a workaround, though I am curious if you have considered a path of using a mirror for your on-prem SQL database and CDC in your data pipeline. I take it that your upsert strategy is aiming at incremental data load which technically should be easier to implement with CDC as it's supposed to be natively supported for SQL databases.
Yes I was aware of this set up, but unfortunately I believe that I need to expose my on prem SQL servers to the public to use this method (from my understanding), whitch is not an option 😞
I believe that exposure of on-prem database to the public network is not necessary as mirroring is supposed to be working via On-premise Data Gateway Microsoft Fabric Mirrored Databases From SQL Server
