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 IIIQuick update for anyone who's curious.
- Per the suggestions found in the linked Reddit thread, I added a stored procedure that queries the bronze lakehouse source table(s) (through the sql endpoint) to see if they exist and does this repeatedly with a WHILE loop and a WAITFOR DELAY until it succeeds or runs out of tries
- I've parameterized all of this as some processes will have different requirements
- In this case I do up to 20 checks every 30 seconds and fails the process if it doesn't locate the table after 10 minutes
- This works well because an earlier step already explicitly drops the source table(s) using Spark SQL in a Notebook task (you can only drop tables via the lakehouse thus necessitating Spark SQL, not via the SQL analytics endpoint
- Additionally, the subsequent step that that populates the target silver table has some retries because (and I don't know why) despite the earlier check, this can still sometimes fail to find the table
I'm going to keep monitoring this and see if I missed something, but the process is more stable and tight (as I'm only waiting as long as necessary), yet still not completely reliable. Ultimately, if Fabric was a more robust product, I shouldn't have to add all of these unnecessary checks. The sql endpoint objects should just be in sync with their lakehouse counterparts. I'll be speaking with our Microsoft reps.