Zero to Snowflake: Tips for Query Building in Snowflake

Data

Zero to Snowflake: Tips for Query Building in Snowflake

This series takes you from zero to hero with the latest and greatest cloud data warehousing platform, Snowflake.

The more you use the Snowflake user interface (UI) for query building, the more layers, panels and tricks you discover it has. I’ve come to take the different screens and shortcuts for granted, but whenever I sit with a new client, I run through these tips to help improve their understanding and productivity.

To get the most from this article, let’s first refresh our memories on what each part of the Snowflake worksheet UI is called:

Tip 1: Finding Database Objects Easily

When it comes to finding tables, the search bar has built-in functionality that can help you filter down to which database and schema the search applies. Go ahead and type SNOWFLAKE_SAMPLE_DATA into the search bar, but instead of pressing Enter, press Tab. You’ll see the database is now locked into the search, and you could search for the table you’re interested in, or you could go ahead and lock in the schema for the search as well by repeating with the schema name and pressing Tab again. To undo, simply hover over the locked-in object and a cross appears that, once clicked, will remove the item:

Tip 2: Previewing Data in a Table

When you find a table you’re interested in, you can quickly look at a snapshot of the contents by hovering over it, right-clicking and selecting Preview Data. This will preview 100 rows of data from that table. When that preview appears, you can press the Details button in the Results pane to see the structure of the table, its columns and their field types:

Tip 3: Right-clicking Doubles Your Productivity

Once you find the database and schema you’re interested in, you can use one of two right-click options to make your query writing quicker.

Right-click on the database or schema in the object browser you want to use. Now, you can add it to the context, and all queries on your selected worksheet will use that selection for any query you write.

If you know exactly which table you want to use, you can right-click and place name in SQL to save you having to write the table out in its entirety!

Tip 4: Which SQL Code Am I About to Run?

Got a bunch of SQL statements on the same worksheet? I like code highlighting as a quick visual reference when I have multiple queries to show me which bit I’m about to run. Pressing CTRL+SHIFT+K will quickly highlight all code in the query where your cursor is currently located, allowing you to check that you’re only running what you want to run. Pressing that key combo a second time turns off the highlight:

Tip 5: Find and Replace

Most people find the Find function very quickly. CTRL+F is a standard key combo for a Find in Windows, but then you try CTRL+H (the standard for a Find and Replace) and nothing. Pressing CTRL+SHIFT+F is the combo you want. Now you can find and replace while looking for a specific instance or replacing all instances of your search:

Tip 6: Commenting Code the Easy Way

Do you find yourself commenting out multiple lines of code one by one? Well, stop because there is a quicker way! Highlight the code you want to comment and press CTRL+ / to comment all the lines and again to uncomment. Simple as that:

Tip 7: Updating Multiple Lines of Code

This next tip allows you to make the same update to multiple lines of code simultaneously. Place your cursor at the point on the first line of code you want to update, left-click and press and hold the ALT key, then drag your cursor down to the last line you want to update. A flashing black line is drawn to the left of your lines of code as you do this. Now, make your update:

Tip 8: Moving and Copying Code

When it comes to moving or copying code, CTRL and ALT are your friends.

  • To move a piece of code, highlight the code, press ALT and drag to where you want the code to be pasted.
  • To copy a piece of code, highlight the code, press CTRL and drag to where you want the code to be pasted.

Tip 9: Monitoring Query History

I always encourage new users to open and keep open the History tab, so you can keep an eye on your query performance and record selections. There’s nothing worse than working away on a query for a while only to realise that, at some point, you introduced something, and now your record count or query execution has increased beyond what you expected. Keeping the History tab open is a quick on-screen reference I check after every execution just to make sure the numbers are where they should be:

Tip 10: Using Results, Not More Queries

When you first look at a big table, you suddenly realise that you’re getting back more columns than you need, or you need to filter to get the records you want. If you’re not after much more than refining your Select clause or adding a Where clause—which all takes time—I encourage you to use two functions on the Results pane to make your selections quickly.

You can use the Filter Result box on the Results pane to quickly find specific data; you don’t even need to stipulate the field it comes from.

You can also select only the fields you are interested in by clicking on the Columns button and making your selections there. Now, isn’t that so much easier than updating your code? Just be aware both are one-time use. When you run your next query, any filters you’ve applied to the Results pane are reset:

Tip 11: Scripting Objects the Easy Way

If you’ve never scripted the creation of a warehouse, a database or a user, the temptation is to head straight for the help and look up the syntax. You should use the help to finalise your code, but a quicker way to learn is to head to the appropriate area in the web UI, run through the creation using the interface and then show the SQL. Now you have a pre-made script that incorporates most of the things you can do during that object’s creation, but don’t forget to look at the help for more detail on the options available:

Tip 12: Administering Your Scripts

If you’re like me when I first started out with Snowflake, you probably have 10 worksheets open, and you’re probably up to worksheet 500 using Snowflake’s native naming. This final tip is a mixture of practices I use to make finding that elusive worksheet that much easier, as well as simplifying sharing my scripts with the team:

  • Always rename a sheet as you start working on it. You can guarantee you’ll forget to do it later and will end up with a lot of worksheet XXX to search through.
  • If you get done with a worksheet and don’t need the queries again, delete the sheet immediately using the option on the control ellipsis in the Context area.
  • If you’re using Snowflake’s native worksheet repository, don’t forget to open it up from time to time and delete old and unused worksheets.
  • Always put a comment header section at the top of your worksheet with some query details, so you have a good reference of what the queries do as you open the worksheet. At minimum, I put the author, a query explanation and a section for version history.
  • Use a separate file or content repository to securely share any worksheets with team members.

Special thanks to Danny and Chris for collaborating on this article.

I hope these tips help you as you continue your work in Snowflake. If you need more guidance in your query building or just want to reach out for some other data support, please don’t hesitate to leave something in the comments below or contact our team. We’d love to assist!

More About the Author

Paul Middlewick

Data Engineer
Zero to Snowflake: Tips for Query Building in Snowflake The more you use the Snowflake user interface (UI) for query building, the more layers, panels and tricks you discover it has. I’ve ...
Zero to Snowflake: Defining Virtual Warehouses One of the first things you’ll do when setting up your Snowflake environment is to establish your virtual warehouses. What this boils ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072