Another developer created workflow that runs a slow query and inserts it into an output table using In-DB tools.
The workflow runs every few minutes (scheduler is configured so workflows run one at a time)
Essentially the workflow was designed as an Alternative to using a Materialised View.
The Write Data In-DB is set to use the "Delete & Append" option, and the target database is a SQL Server, meaning it is a Truncate operation. (and the incoming records are less than the 10000 record transaction size option)
When I first saw it, I was concerned about whether there was a risk that an incoming query would hit the output table right after it was truncated, but before it was fully loaded. I was told that in Alteryx, In-DB tools chained together are part of a single transaction. (so when querying the table, you would only see the records after a full load, not a partial load)
Problem is, sometimes when I query the table I have seen it return Zero Records, and other times a few thousand records. I'm worried that the table is in a truncated state while the initial query is still running and the data is not yet loaded.
Is my understanding of the Write Data In-DB incorrect? Or can the Delete & Append option result in an empty table? (I've been advised by another developer to try adding a middle step in between to write the data to a Temp Table, before then loading to the final one)
I read an explanation that In-DB tools were like translating operations together into a large SQL statement sent to a database as one transaction.
