Finding Out What’s Going On in your Database

Over the last few days, a task that only runs once per day has failed for unknown reasons. First, I’ve thought back on the regular responses to understand why it could have failed.

• Did something get published to Production that caused this new issue? (Thankfully, I’m the only one that pushes to Production in Snowflake and I know I didn’t promote anything recently.)

• Did Snowflake publish something without telling me? (No, as well. Thankfully Snowflake is very good at communication.)
• Does it work correctly when I run the process manually? Also, sadly, no.

Since this task runs multiple processes, and logging each query seems like overkill, there must be a better way.

Well, my friends, I can use one of my favorite tables to find out everything that’s going on behind the scenes of our wayward process! Yes, that’s right – Query_History to the rescue!

WITH LatestSession AS (
     SELECT SESSION_ID
     FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
     WHERE QUERY_TEXT = 'CALL DailyDataLoad();'
     AND START_TIME > DATEADD(d, -1, current_date)
     ORDER BY START_TIME DESC
     LIMIT 1)
SELECT Q.*
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY Q
JOIN LatestSession LS ON LS.SESSION_ID = Q.SESSION_ID
ORDER BY Q.START_TIME;

Within our CTE named LatestSession, we find the session_id that was last used for our wayward procedure, DailyDataLoad. Notice that I am checking for the entire process name and how I call it in the task. That and narrowing our Start_Time down to the last 24 hours will significantly help us find the session that was used.

Now you may be asking yourself, “Self!” and I knew it was me because I recognized my voice and I was wearing my underwear, “Self! Why did Sherpa have to get the session ID instead of narrowing it down to one particular stored procedure like in SQL Profiler?” Great Question!

Snowflake AFAIK doesn’t get that granular on how it allows you to grab data from query history. Since the task will generate a new session, then it should grab that session run by the task, and – Bob’s your Uncle – you have all of the data you need to follow along with the task.

So, my friends, you can now follow along with Snowflake and see what all kinds of queries it runs or where it stops running as the case may be. Both can be very important in the grand scheme of things. Until next time, let’s look to see what’s happening with Snowflake and see what’s a-changing…