Snowflake & Count: A New SQL Notebook Experience for the Analyst
This almost needs to go unstated, but I write several SQL statements every day in Snowflake. The native Snowflake worksheet experience feels like a reskinning of HUE and does the task just fine. The Snowsight skinning of Numeracy is a superior analytical experience, especially for dashboarding and sharing SQL with my team working in my Snowflake organization. However, I still have a desire for developer-oriented experience with branch and merge of worksheets, SQL that builds on itself in a secondary metadata experience, along with the ability to share my SQL workbooks beyond my organization. To sum it up, I feel I am in need of the Jupyter notebook experience for SQL.
I was floored last week browsing through LinkedIn when I saw an MP4 in my feed of Count.co. After I saw a SQL notebook experience that looked Tableau easy with some very powerful concepts of iterative sheets (cells in Count parlance) at play, I was hooked. I immediately signed up for an account and started developing SQL on my Snowflake account. I was very impressed with the intuitive UX and flow similar to a Medium blog authoring experience. This was a superior experience to any SQL IDE I have used. I am in LOVE with cells and their iterative nature. Being able to essentially use a sheet as a datasource is something I used to beg product at Tableau for… you all know who you are! You can populate parameters from a field and reference them in subsequent queries, and not to mention you get atomic visualizations as well. Their support is right in the product via chat. I sent some feedback and within minutes the dedicated co-founder/CEO reached out to me over the help chat. This is a fantastic 1.0, and I only see future promise.
Getting Started and Connecting to Snowflake
Rock on over to Count.co and sign up for a free account (no software to install, this is the cloud after all). Then create a new database connection to your Snowflake account.
Warning! My user has a default Warehouse and Role so this works for me. If you don’t have these configured this might not work for you yet as Warehouse and Role are not exposed.
First Selects
Now, we get all of our tables down the left-hand side and we can start authoring new cells by hitting the backslash “/”. There are additional controls other than SQL, we will get to some of those later.
I like to SQL first. I had to try SELECT * FROM DUAL, but alas it was not in our meta-data from the information schema. The scope of the cell seems to be limited to the meta-data returned from the database INFORMATION_SCHEMA.
Using Query Results (Cell)
The thing I wanted to try the most was querying query results. You can do this with Snowflake via TABLE(RESULT_SCAN(LAST_QUERY_ID())), but this is a mostly stateless object and is tough to interact with using many double quotes and working through errors..
With Count you get a “table” of your query results with columns, data types, and selectability. Alas, I could not run the SHOW commands, but you have access to the INFORMATION_SCHEMA as a conduit to object query.
CTEs All the Way Down
I was curious how deep I could take the iterations of SQL by Cell stacking. So I built a quick 3 part query on TPCDS for Day, Month, and Year aggregations in stacking iterations.
It handled the first 2 stacks with no problems. Notice the case sensitive nature of the Cell name in the from clause, but being called out in double-quotes and red indicates to me I am calling an object local to Count.
The third leg of the stool was handled just as smooth as the rest, you can go SQL all the way up and down, join these cells to each other as well. I am loving the Cell stacking and iteration.
How is it Doing This?
I had to go look into query history in Snowflake. Let’s just say I liked what I saw.
A full-on iterative CTE, bravo! Exactly how I would accomplish some of this if the analysis SQL I was going for was more complex.
Single Select and Multiple Select Cells
Now I need to drive deeper and give the user or myself the ability to select values to query over. Notice the use of the parameter with the WHERE clause. SELECT “value” from “SingleSelect”. Again, case sensitive in double-quotes with a “value” member. How did you know how to use “value”? Well… expand the Cell on the left-hand pane, very intuitive.
This works great for single values, let’s apply this to multiple values. A quick SELECT * and an IN() clause is all we need.
Time to Drag and Drop
I had to play with something else I saw in that MP4, “Tokens”. These are the things that look like Rows and Columns from other tools. I can select items from Tables or Cells and add them to the token layout and get back the data I am looking for.
I Need Pictures
They also have visuals at the atomic level (like Tableau) in the product. I love this flexibility as an author because I don’t want clunky chart wizards. I want atomic control to build and alter charts in the analytical flow.
I will dive deeper into visuals in my next blog article in this 2 part series. I am just scratching the surface.
Workflow Built In
Merge, Publish, Fork, and Share are all built right in. This will allow teams to collaborate and iterate SQL Notebooks in a DevOps (DataOps) discipline. No need for third-party source control. However… a Git integration wouldn’t hurt.
I Want the SQL
Dave, I noticed you didn’t share any of your SQL… it’s all just PNGs. Hey, this is Count, and I can share it with YOU publically! Enjoy, as I know I did: https://count.co/n/m4Gq3X5USdF.
Friendly Support
When I was chatting with the co-founder/CEO (Oliver Hughes) over help-chat he was so friendly and this experience was so refreshing I had to have a Zoom with them. I really enjoyed the Zoom conversation, geeking out on data, and a peek into the vision/direction with Ollie.
Some Features Snowflake Needs
We need to have the Warehouse and Role exposed to the connection dialog. Further, we need to be able to move Cells up and down. I also want to resize the data window to see my tables, cells, and fields better. For the Snowflake community, we need access to the SHOW commands.
Conclusion
My experience using Count was refreshing, to say the least. I highly recommend you check it out for yourself. I will be adding some more content about visuals and other items about using Count in the future. Happy (Notebook) Querying!
Originally published at http://bigdatadave.com on March 28, 2021.
