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.
4,903 questions
Synapse Serverless SQL external table returns old data
![](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(128, 13%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENS%3C/text%3E%3C/svg%3E)
Nelia Steenkamp
0
Reputation points
An external table created in a Synapse Serverless SQL pool for an Azure Storage account Delta Table is not showing the data as it is in the Delta table.
When the external table was initially created the data in the table was incorrect. The decision was made to drop and recreate the Delta Table in the Azure Storage Account. Please take note that the structure of the table was not changed, just the data.
When the external table is queried the data that is returned is still the old incorrect data. However, when the data is queried from Azure Databricks the data is correct. Also, when queried from Synapse Serverless SQL using OPENROWSET the data is correct.
The external table was dropped and recreated, but the data returned remains the old incorrect data. Also, the Delta Table Optimize and Vacuum functions have been run.
Any guidance would be appreciated.
{count} votes
-
![](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(268.8, 8%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EST%3C/text%3E%3C/svg%3E)
Smaran Thoomu 15,520 Reputation points Microsoft Vendor2024-09-27T21:56:15.6466667+00:00 Hi @Nelia Steenkamp
Greetings & Welcome to Microsoft Q&A forum!
Thanks for providing such a detailed explanation of the issue you're facing with the Synapse Serverless SQL external table. From what you've described, it seems the problem might be linked to caching or metadata inconsistencies.Here are a few things you could try:
- Manual Refresh: After dropping and recreating the Delta table, make sure you've manually refreshed the external table to trigger a re-scan of the data.
- Check Cache Settings: See if query caching is enabled for the external table. Try disabling it temporarily to see if that resolves the issue.
- Data Source Configuration: Double-check the location and credentials in the external table definition to ensure they match the correct Azure Storage account and Delta table.
Delta Table Metadata: You might also want to run Delta Lake's OPTIMIZE and VACUUM commands again to ensure data integrity and fix any potential metadata issues.
If these steps don’t resolve the issue, feel free to share more details about your Synapse Serverless SQL pool configuration, the external table definition, and any error messages you're seeing.
Hope this helps! Let me know if you have any further questions.
Sign in to comment
Sign in to answer