Synapse serverless pool snapshot file error

rajanisqldev-42 201 Reputation points


I am querying synapse serverless pool(synapse link for dataverse from D365 CE).

I have queried contact table

SELECT COUNT(*) FROM contact -- generated error that the file 2010-11.csv is not accessible.

When we had call with MS, they said that it is known issue as the files in data lake are constantly in sync with synapse link. And MS has asked me to query snapshot views which al just 1 hour behind live files and synced with live hourly.

So, I have queried SELECT COUNT(*) FROM contact_partitioned -- Unfortunately, the row counts doesn't match(I mean way less than the live)
Its reasonable if it is almost close to live but nearly 05m rows diff. Our system doesn't have those many transaction in a week.

Actually, I am trying to import this data to Azure SQL DB.

What is the best method query these tables from serverless pool and insert into azure sql db

Thanks in advance

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,193 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,818 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 35,786 Reputation points Microsoft Employee

    Hi @rajanisqldev-42 ,

    Thank you for posting query in Microsoft Q&A platform.

    Unfortunately I don't have dataverse environment to repro. But I can say that you may need to query snapshot data to avoid above error.

    Kindly check below link. Where it explained that snapshot data will be available under each table folder and snapshot folder. Kindly check snapshot data section from below blog.

    Hope this helps. Please let me know how it goes.