❄️ Snowflake in a Nutshell — ChatGPT 🤖

It’s no coincidence that ChatGPT gained 1 million users in 5 days. Just about everybody in or around tech has awareness of it at this stage.

While the possible use cases are vast, we will focus on using ChatGPT as an assistant for coding on the Snowflake Data Cloud. We will explore asking it to write, optimise, and comment our code from within a developer environment. We will also cover some tips, tricks, and caveats.

Let’s start with Visual Studio Code.

Visual Studio Code

According to the 2022 Stack Overflow Developer Survey, Visual Studio Code — also known as VS Code — is the most popular developer environment tool. It’s a free Microsoft source code editor which can run on Windows, Linux and MacOS.

The Snowflake Extension for Visual Studio Code

This free extension enables us to write and execute Snowflake code within VS Code. Extensions are pre-packaged functionality which provide additional features, and are available in the Visual Studio Marketplace.

The Snowflake VS Code extension gives us functionality such as:

• Snowflake script execution
• Snowflake session context
• Snowflake database explorer
• Snowflake query history
• Intellisense

Additionally through VS Code we get e.g. Git integration.

Using the Snowflake extension for Microsoft Visual Studio code. Credit: Dan Galavan

Caveat: This extension is in Public Preview so may not be suitable for production workloads.

Using ChatGPT in VS Code

There are a number of VS Code — ChatGPT extensions available through the Visual Studio Marketplace. In this case we will use the VS Code ChatGPT Genie AI extension. Installation and configuration steps are detailed here.

Edit: This blog previously referred to the VS Code ChatGPT extension by Ali Gençay. This extension has since moved to the ChatGPT — Genie AI extension in the Visual Studio Marketplace.

From within VS Code we can ask ChatGPT to write, debug, optimise, comment, and create tests for Snowflake code.

Caveat — ChatGPT does not always get things right. See this blog’s Conclusion for details.

ChatGPT + VS Code — generate DDL SQL

We can use DDL (Data Definition Language) SQL to create and modify Snowflake database schema and objects e.g. CREATE TABLE, CREATE VIEW.

Tip: Let ChatGPT know what the target environment is by including “Snowflake SQL syntax” in your question

Generating Snowflake DDL from within VS Code using ChatGPT. Credit: Dan Galavan

Steps:

1. Question for ChatGPT: “Create a product database table using Snowflake SQL Syntax”
2. Review the SQL. If it is suitable, click on Insert to add the new code to our coding dialog
3. Click “Execute” to run the SQL on Snowflake

Caveat: We need to be logged into a Snowflake test account using the Snowflake VS Code extension. Free Snowflake trial accounts are available here.

Tip: This blog is for research purposes. For actual database design I would strongly recommend using data modeling processes combined with suitable tooling such as SqlDBM.

ChatGPT + VS Code — generate SQL (DML)

We can use DML (Data Manipulation Language) SQL to add or change data on Snowflake e.g. INSERT, UPDATE, DELETE.

Generating Snowflake DML from within VS Code using ChatGPT. Credit: Dan Galavan

Steps:

1. Question for ChatGPT: “Write 10 sql insert statements for the product table using Snowflake SQL Syntax”
2. ChatGPT provides a response. Again, review the SQL. If it is suitable, click on Insert to add the new code to our coding dialog
3. Click “Execute” on the code canvas to run the SQL on Snowflake

Tip: ChatGPT understands this question in the context of the previous DDL question, and generates the DML accordingly.

ChatGPT + VS Code — optimise code

We use code optimization to make our code more efficient and easier to maintain.

Optimizing Snowflake SQL from within VS Code using ChatGPT. Credit: Dan Galavan

Steps:

1. We highlight the SQL to optimise. Then, right click; select “ChatGPT: Optimise”
2. The question and context is automatically passed back to ChatGPT
3. ChatGPT recommends replacing the hard coded timestamps with the Snowflake CURRENT_TIMESTAMP() function
4. We review the proposed optimization, and noting that is a suitable optimization in this context, we click “Insert”. The optimised code automatically replaces the highlighted code. We can click “Execute” on the code canvas to run the SQL on Snowflake

ChatGPT + VS Code — comment code

We use code comments to explain what code does, and also to add additional context regarding why this code is required.

Commenting Snowflake SQL from within VS Code using ChatGPT. Credit: Dan Galavan

Steps:

1. We highlight the SQL we want to comment. Right click, select “ChatGPT: Add comments”
2. The question and context is automatically passed back to ChatGPT
3. ChatGPT proposes code comments
4. We review the proposed comments, and if acceptable we click on “Insert”
5. The comments are added to the DML SQL script

ChatGPT at capacity / throttling

Given its preview nature along with worldwide demand, ChatGPT can reach capacity limits, and can also throttle requests. Therefore we may receive responses such as “ChatGPT is at capacity right now”, or:

ChatGPT is at capacity. Credit: Dan Galavan

Conclusion

We can use ChatGPT as a coding assistant from within Visual Studio Code in combination with the Snowflake extension. We have explored use cases such as generating, optimising, and commenting Snowflake Data Cloud SQL. There are many additional use cases such as unit test generation and code debugging.

With each example in this blog, I mention that we review ChatGPT outputs. ChatGPT does not always get things right. According to Peter Relan, a co-founder and chairman with Got It AI, it is estimated that 80% of the time, ChatGPT does things well, and 20% of the time it just makes things up (hallucinations). So while it can act as a coding assistant, if it gets things wrong, then issues could exacerbate quickly! In addition, we can be faced with ChatGPT capacity limitations.

Nonetheless, I would recommend checking these extensions out, and everything referenced in this blog is free to try. The ChatGPT genie is firmly out of the bottle, and this is a great way to explore what it might be like as a coding assistant for Snowflake.

(If you found this article helpful, do please 👏 🙂)

© 2023 Dan Galavan, Independent Data Architecture Consultant. Dan is a leading Independent Data Architecture Consultant who has spent 24 years advising clients and leading the delivery of data solutions in both the private and public sector. He has been a member of the Snowflake Data Superhero program for 5 years running. Further details: galavan.com