Azure Synapse - how to have header rows present in CSV files?

Question

Hi all,
I am new to the world of Azure Synapse and so I may not use the correct wording/terms when asking this.

My current reporting process is as follows:
Dynamics >> Azure Synapse (Lake database tables) >> Azure Synapse (Serverless SQL pool SQL Views) >> Power BI

All works except for the locking issue faced when the Power BI reporting attempts to refresh its data at the same time that Azure Synapse is receiving a Dynamics update. I understand this locking issue is expected behaviour and so I am seeking an alternative method avoid this issue. The use of the _partitioned tables is not suitable to us as we will still face the locking issue.

I am seeking the use of allowing inconsistent reads upon the CSV files. The immediate blocker on using this approach is non-existent header rows in each CSV file.
Please can someone explain/point me to information on how I retrieve the CSV file's header row?
Are the header rows missing due to the Dynamics to Synapse export mechanism not being correctly configured, or do I need to include specific SQL code when querying the CSV file data?

Thanks in advance.

Answer 1

Hello @Anon101 ,
Thanks for the question and using MS Q&A platform.

As I understand, you have a couple issues.

Resource locking is causing you issues. I thought scheduling would help with this, assuming the reporting process you described in all handled in the same pipeline.

The main ask is about headers in CSV files. If I understand correctly, these files are pulled from Dynamics and written to Synapse? Are these writing normal flat csv files, or database tables?

If they are normal csv created by a copy activity, then you need to look at the datasets for the "First row as header option" , both on the writing of the file and the reading of the file.

In the case of Lake Database, headers are not stored as part of the data. The metedata is separate. I'm looking into that now.

Please do let me if you have any queries.

Thanks
Martin

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators