Write Data In-DB - Delete Data & Append - Single Transaction?

[cj89]

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.

[HenrietteH]

Hi @cj89 

With a workflow like yours, we actually send two statements (the exact statements depend on your database):

1. a truncate statement to truncate the destination table 

2. the load to load the data

So yes, if something happens to your load, you could end up with an empty table. And if you query the destination table while the load is happening, you could see it contain any number of records (that depends on how often a commit is happening.

If you want to see the actual queries being sent, you could try turning on logging. Depending on your database and driver, you can do that in the ODBC DSN config or through the logging screen in the ODBC DSN manager.