Synapse Serverless SQL external table returns old data

Nelia Steenkamp 0

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.

Smaran Thoomu 16,230 Reputation points Microsoft Vendor

2024-09-30T10:34:38.0933333+00:00

@Nelia Steenkamp We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Nelia Steenkamp 0 Reputation points

2024-09-30T13:23:16.0933333+00:00

@Smaran Thoomu I will get back to you as soon as I have a moment to implement the suggestions.
Nelia Steenkamp 0 Reputation points

2024-10-01T06:33:21.3033333+00:00
@Smaran Thoomu

I have a few questions pertaining to your suggestions:

Manual Refresh

I had previously dropped and recreated the External Table without success, but will retry. Or is there another method of refreshing an External Table in Synapse Serverless SQL?

Check Cache Settings

I did a search regarding this, but only found references to cache settings with a dedicated SQL pool. The issue I am experiencing relates to the Serverless SQL pool. Do you have any information or links you can share with regards to caching and the Serverless SQL pool?

Data Source Configuration:

I have verified that the External Table's External Data Source and Location settings match the specific Delta Table in the Azure Gen2 Storage account.

I might try dropping and recreating the External Data Source if this keeps persisting, but am hesitant as I have a number of other External Tables that make use of the same External Data Source and does not have this issue. Or maybe creating a new one just for this External Table to test if it will solve anything.
Smaran Thoomu 16,230 Reputation points Microsoft Vendor

2024-10-03T16:11:30+00:00

Hi @Nelia Steenkamp

Thanks for your prompt response and for trying out the suggestions I provided earlier.

Regarding your questions:

Manual Refresh You're correct that dropping and recreating the external table is one way to refresh it. However, I'd like to clarify that there's no explicit "refresh" method for external tables in Synapse Serverless SQL. Dropping and recreating the external table is the recommended approach to trigger a re-scan of the data.

Check Cache Settings You're correct that the cache settings I mentioned are applicable to dedicated SQL pools. Unfortunately, I couldn't find any specific documentation on cache settings for Serverless SQL pools. It's possible that caching might not be enabled by default for Serverless SQL pools, but I'd like to investigate this further. I'll check with our internal teams to see if we have any information on caching for Serverless SQL pools.

Data Source Configuration I understand your concerns about dropping and recreating the external data source, given that it's used by other external tables. Creating a new external data source for this specific external table might be a good troubleshooting step, as it would help isolate the issue. If you're hesitant to drop the existing external data source, creating a new one for testing purposes could be a viable alternative.

If you're willing, could you please try creating a new external data source for this specific external table and see if that resolves the issue?

I hope this helps. Please let me know if you have any further queries.
Smaran Thoomu 16,230 Reputation points Microsoft Vendor

2024-10-04T12:48:13.45+00:00

@Nelia Steenkamp We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Smaran Thoomu 16,230 Reputation points Microsoft Vendor

2024-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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Synapse Serverless SQL external table returns old data

1 answer

Your answer