This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(25.6, 13%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET%3C/text%3E%3C/svg%3E)
I have a pipeline set up in Synapse which runs 2 dataflow activities which is supposed to 1. copy data from Fhir as source to Azure SQL Server as sink and 2. copy data from Azure SQL Server as source to Azure Lake as sink.
First, is this the best practice flow? Is it necessary to have 2 dataflow activities to copy data from Fhir (via a CDM source) to Azure SQL database and then to Azure Lake database? I feel like there are efficiencies to be had.
Second, in my 2nd dataflow activity I use Azure SQL database as the source, add a couple columns via derivedcolumn and sink the data to Azure Lake database. In the settings of the lake sink there is only a single option "Table action". For which I have selected "Recreate table". The first time it runs it is accurate. If the table doesn't exist it is created and populated with data appropriately. If I then make a change in Fhir (example changing address from 930 to 929) and I run it again the first activity works and upserts the data to Azure SQL database. A subsequent select reveals the address does indeed now start with 930. The second activity reports success, however the new data is not there (it still says 929). The relevant tables are fhir.LocalPatient and fhir.LocalPatientAddress from the Azure SQL database side. When the pipeline runs these tables are created on the dbo schema (as dbo.LocalPatient and dbo.LocalPAtientAddress). I would rather they are on the fhir schema. I added the schema to the Lake database, but there is no option for specifying the schema in the data flow. I am using dynamic tables so enabling mapping is not an option. I tried a concatenation of 'fhir.' and $TableName (for which it does not error), and then attempt to query fhir.LocalPatient in the Lake Database. I get invalid object name error. I checked fhir.dbo.LocalPatient as well - same error.
After the run reports success I go in and find that the address is not updated for the Lake Database table dbo.LocalPAtientAddress, but it is updated for the Azure SQL database table fhir.LocalPatientAddress.
What am I missing?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(86.4, 36%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBM%3C/text%3E%3C/svg%3E)
Hello @TerriblyVexed ,
Welcome to the Microsoft Q&A platform, and thanks for posting your query.
Please correct me if My understanding is wrong. You do not see any option to specify the schema in the data flow on the sink. As a result, your tables were created with DBO schema.
You can see the schema option when you click edit on the table in the synapse dataset.
For the other question, you want to know whether it is a best practice to use two dataflows to copy data to synapse analytics from FHIR as a source.
Could you please let me know why you are using two separate dataflows? Do you have any issues using the sink as synapse in the first dataflow?
Hello @TerriblyVexed ,
I am just checking in to see if you have any updates here. If you have any further questions, please let us know.
The schema option is not available in the Workspace DB connector for Lake Database
The bigger issue is that when I select "Recreate table" it does NOT recreate the table. If it exists, it ignores it, if it doesn't exist it creates it. This causes me to have to add additional steps to remove the parquet tables manually. Which causes more issues.
Hello @TerriblyVexed ,
Thanks for more details. You are correct. The schema option is not available from the workspace db on the dataflow. Currently, this is expected behavior.
Regarding your other question, you have mentioned when you select "Recreate table" it does NOT recreate the table. If it exists, it ignores it, if it doesn't exist it creates it.
I have tested this from my end. and I confirmed when you select this option, it re-creates the table even if it exists.
I have deleted/inserted a few rows at the source and run the dataflow again with ‘’Recreate table” option. I can see the new/deleted records
When you click on the pipeline run, did you check the rows written on the pipeline run? From my end, I can be able to see the Rows Written every time when the data flow runs with "Recreate table" option.
In case, If you are still having the same issue from your end, I would suggest opening a support case for a deeper investigation. If you don't have a support plan, please let me know. I can provide you one-time free support request.
I am looking forward to hearing from you.
I have logged a ticket with support.
Hello @TerriblyVexed ,
Thank you for the update. In case if you have any resolution please do share that same with the community as it can be helpful to others.