I asked this same question years ago as my org at the time was experiencing the same and I wanted to promote best practices. What I've learned is it really depends on the situation. Your population of users who are used to connecting directly to databases via ODBC connections - are going to continue to connect to ODBC connections. They are more likely to be used to the parameters around direct connections and the inherent do's & don'ts. The ones who may not be experienced connecting via ODBC connection and/or are limited in the data that they need, pulling data from a "sandbox" environment such as LAN drive locations for .YXDB files adds that extra layer of protection and keeps the process simple. As long as both sources are the same(updated at the same time), you avoid a risk of creating solutions that may have different results due to a difference in underlying data.
To answer your other questions, ODBC connections do a new call every time the workflow is run on the gallery. If you are using a file as your data source, you would want to make sure that the assets box remains unchecked when you upload your solution to the gallery and that way each time the workflow is run on the gallery, it connects to that file directly - allowing for updates to occur and be accounted for. If you select the assets box when uploading, that means you are pulling that file, as is, to the gallery and the workflow will use that version in perpetuity every time its run moving forward(not accounting for changes)
