Forum Discussion
Advocate IIISQL endpoint sync issues
frithjof_v , thank you! Looks like that was the optimal solution. I now how the following pattern:
1) Load the bronze lakehouse
2) A copy task to load the bronze warehouse from the bronze lakehouse
3) A stored procedure to copy from the bronze warehouse to the silver warehouse
I don't know how scalabale this is because we have over a hundred tables from Synapse Link and adding tables to a copy task is a bit onerous, but for now this seems to be working.
Advocate IIISince this was still not working, I suspected that the OBJECT_ID function was checking a system table which was updated before the sql analytics endpoint table was fully created and synced. So, I updated the logic to query the sql analytics endpoint table directly via a select statement (e.g. SELECT TOP 1 1 from dbo.table). As before, I put this into a stored procedure that used a WHILE loop to check for table creation every 30 seconds for 20 times, failing after 10 minutes.
The check would succeed on the first try, then proceed to try to load the table and fail. In other words, despite a SELECT TOP 1 1 succeeding in the check, the subsequent SELECT/JOIN query of that table still failed to find it. I then suspected that the using 1 instead of * erroneously succeeded because it wasn't checking any of the column metadata, so I changed this to SELECT TOP 1 * FROM dbo.table WHERE 1 = 2. What I found was that each query now took 2+ minutes and would sometimes succeed and othertimes fail with the same old "Failed to complete the command because the underlying location does not exist." message.
It turns out that this is intermittent and misleading. I now suspect that each query is hitting a hidden query timeout limit when it fails and it has nothing to do with the table not existing.
Back to square one. Again.
EDIT: After futher testing, I can confirm that this is the sequence of events.
1) The Notebook task succeeds in dropping and reloading the table (~2.5 minutes)
2) Queries against the table in SQL produce stale results (for 0-15 minutes)
3) Certain queries begin to fail (for another 0-15 minutes)
4) Queries begin to succeed again with fresh data
frithjof_vCommunity Champion
Thanks for sharing!
I'm struggling a bit to keep up with all the details. Also, I'm not so well versed with SQL.
Does it seem to you that when your WHILE loop finally finds the newly created table, then subsequent SQL queries will also consistenly find the table?
Or does the table seem to sometimes "go missing" again (i.e. not stable)?
I guess step 3) is what I am confused about. Is the table "unstable" in this period?
- dzav
I'm finding that the table goes missing again (in step 3) after being found earlier in the process (step 2).
When I tested this, what I found was that when it is found in step 2, it's actually returning stale data (from prior to the load in step 1).
The latest and most stable (but still unstable) version of my process goes like this:
1) Drop the tables (using Spark SQL, so it's doing this via the delta lake)
2) Recreate and reload the tables
3) Wait 5 minutes (this is crucial)
4) Query each loaded table in SQL to make sure it exists every 30 seconds for up to 20 minutes
5) Query these tables in SQL to load into the next set of tables (silver warehouse)
With this much delay (up to 25 minutes), the process tends to succeed about 50% of the time (better than <10% of the time ).
- frithjof_v
I've seen some people suggest to only use Lakehouse and Notebook, and not use the SQL Analytics Endpoint. So only Lakehouse + Direct Lake for Power BI.
I've also seen some people suggest to use Data Pipeline Copy Activity when they need to move data from Lakehouse to Warehouse, instead of using SQL query.
And then do stored procedure inside the Warehouse, if needed to further transform the data.
These are some suggested methods to avoid being dependent on the Lakehouse SQL Analytics Endpoint.