Full Disclosure:
I’ll be the first to tell you I’m not a DBA. I have a working knowledge of databases and “mainstream” SQL statements. I’m usually content to stay in my area of expertise and just query the data, make sure that the data is correct, and build some great content for clients. If you read my recent blog post, you’ll notice that I left my safe zone recently and started inserting and deleting records from a table in Snowflake. I had a few issues, and this post is about how Snowflake helped me resolve it.
It Was a Day Like Any Other…
….Arrive at the office, start brewing the french press, and get logged into my Snowflake account. Today’s task was to make sure that the Time Travel SQL was going to work on an OBIEE dashboard, and put the finishing touches on my blog post. I had deleted the records previously inserted into the Snowflake tables when I was finished working the previous day, meaning that I would have to re-insert the rows with the dummy data. I executed my insert statement, and got a message that it failed to insert. Strange… it worked perfectly the day before. I checked my connections — no issues there. So, I headed into the Snowflake History to see if there was anything helpful there.
Based on the Snowflake History, it certainly looked like it wasn’t a syntax issue, but that something was preventing me from inserting. I checked permissions, and found that nothing had changed there either. So, unable to figure out what was happening, I begrudgingly headed over to the Support Portal (I’ve been working with Oracle Support for years; so yeah… begrudgingly), I filed a low impact, low priority ticket detailing my Database and Data Warehouse, the insert statement, and the situation.
Imagine my surprise when less than three hours later the Snowflake team had responded to my ticket with a recommended resolution. Under three hours! All I could say was:
The Snowflake support technician gave a detailed explanation as to why the block was happening, and then proceeded to walk me through how to solve this problem. (Spoiler alert: it worked on the first try.)
The cause of the problem was that the “autocommit” feature was set to false. This means that the table was waiting for an explicit commit or rollback after deleting my rows the previous evening before releasing the lock on the table. Since that session had ended, I was able to use a few queries and commands that the Snowflake technician suggested to find the database transactions that were causing the lock, and subsequently end those transactions (thus releasing the lock). Problem solved… easy
I’m not sure if you’ve ever filed a support ticket with any companies in Silicon Valley, but I have found that the customer service aspect is… somewhat lacking. Between personal accounts for myself, accounts for when I was an independent consultant, and accounts for enterprise clients I’ve worked with, there have not been many interactions that were stellar. Snowflake completely blew me away with the accuracy of the solution, and the speed of response and resolution. Part of that is surely has to do with being a cloud platform and being able to see everything that I had done without needing endless specs on the machine, software versions, etc. But the other part… well, I guess that is just due to good-ole-fashioned customer service.
Thanks to Stewart Bryson.
