New Metadata Sync FAQ
We recently released a new update the SQL analytic endpoint, a couple of improvements to the Metadata sync (MD Sync).
The offical docs are here.
Q: What happens when I enable the toggle to use the new metadata sync in the workspace setting?
A: Nothing. All existing behaviour is kept, nothing will change. Only new SQL analytics endpoints will get the new metadata sync. All existing SQL analytics endpoints will keep using the old one.
Q: Does that mean I could have a workspace with SQL analytics endpoints using the new and old version of Metadata sync?
A: Yes, older lakehouses before the change will be using the old (v1) and the lakehouses created after you enabled the preview feature will use (v2).
You can use the new DMV or stored proc to determine the version, but I can’t see a way today in the UX (UI or User Experience).
Q: Can I enable this setting or query this setting from the REST API?
A: Not, that I can see.
Q: Is there actually much difference between the old version (v1) and new version (v2)?
A: Yes, alot. It’s been rewritten. So its very different under the covers.
Get Mark Pryce-Maher’s stories in your inbox
Join Medium for free to get updates from this writer.
Q: Can I throw away all my code using the MD Sync REST API?
A: Not, yet. Keep it, but there is an additional way of refreshing the table on demand using a stored proc. It could save you some money if you are using a Spark Notebook to call the MD Sync REST API to switch to the stored proc.
EXEC sys.sp_dw_refresh_ext_table “dbo.publicholidays”;
Q: Will the new Metadata sync get rid of the the very long waits I had when calling MD Sync before (via the REST API or clicking the button in the UX) ?
A: Yes, if you had issues because you were changing lots of tables or had tables with very large delta logs.
Q: But will there still be a delay?
A: Short answer, Yes. This is an incremental improvement, so aim will be to remove the delay together, but the V2 will have a delay.
The docs say: “ensuring your data is available for querying with minimal delay.” and “The new metadata sync option works to keep the data available for querying within seconds of it landing in the lakehouse.”.
Q: I am using Metadata sync v2 and when I call the MD Sync REST API, I am still seeing long delays!! I thought you said this was fixed?
A: Yes, if you use the REST API, think of this a serial process. one table at a time, so this has not changed from V1 and V2. But in V2, the refresh is happening as part of the query execution and it can do things in parallel. So if you are okay with a small delay, then you dont need the REST API any more.
Q: So do I still need the REST API/new stored proc when I do ETL type workloads?
A: Yes, because there is still a delay, you still need it. So I would recommend if you only need to refresh a single table use the stored proc after you have updated it (instead of the REST API) — then it only updates that one table and you know the data is good.
Q: Does it work with Private Endpoint enabled workspaces?
A: There is an issue right now and a fix is being rolled out.
Q: Does the new MD Sync v2 support varchar(max)?
A: Yes, it does, go and play!! :-)
Q: Does the new MD Sync v2 support v2 checkpoints in delta?
A: There is limited support, so in most cases the data will appear. Any issues should be address before GA.
Q: Will there be a way to convert my existing SQL analyitcs endpoints/lakehouses to use the new MD Sync?
A: Not yet, but its planned.
