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.
Community ChampionThanks 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?
Advocate IIII'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.
- dzav
Interesting! I will test it out!
- dzav
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.