Since 2023, and we live in a world where AI chatbots can serve as SQL co-pilots and QA checkers!. In this lab, I copy pasted code that was invalid because of the double quote formatting. To debug this in ChatGPT, you can copy and paste your SQL and then copy/paste the error. I have included some prompts that you can use to help you debug code.

In mid 2024 Snowflake also introduced their own native Copilot intended to help you write and review SQL having context of your tables and fields. As you learn SQL, you will be able to quickly enhance and super charge your work using co-pilot. Using LLM generated code without understanding SQL is particularly dangerous if you are responsible and accountable for delivering guidance on data.

Let’s explore the same problematic code in ChatGPT. If you don’t use ChatGPT, you can use any LLM like Google Bard and get the same result. As you can see, my prompt clarifies that I am asking for help specifically for Snowflake. 

Important note when using LLMs for query debugging:

1. Make sure you include the error returned by Snowflake when you create your prompt. This will help provide context.

2. Make sure when you copy code and and aerror into a LLM chat bot, you provide proper context in your prompt. I have provided a good prompt template below;

You are a Snowflake SQL expert. You will help guide me through troubleshooting and refining my code. I am expecting the SQL below to: [EXPLAIN THE OUTCOME OR OUTPUT]

[INSERT YOUR SQL CODE]

When I run this code, I get this error:

[INSERT YOUR ERROR HERE]

Now that you have written your first SQL statement we are going to take one step back before we charge forward to understand the anatomy of a relational database. The information you learn here is universal to any modern database.

Without getting into the detailed nuances and technology architecture of Snowflake and a Relational Database Management System (DDBMS), the concepts you will learn in this course for Data Query Language (DQL) will translate to databases like Oracle, Microsoft SQL, MySQL, PostgreSQL, and others.

Navigating your Database

1. Database

• Definition: A collection of schemas, tables, and other objects
• Purpose: Organizes and stores data in a structured way
• Salesforce Analogy: Comparable to a Salesforce Org, which contains multiple objects, fields, and records

2. Schema

• Definition: A logical container within a database that holds tables, views, and other objects.
• Purpose: Helps organize and manage data at a more granular level within the database and allows for fine grained control including but not limited to access and security.
• Salesforce Analogy: Comparable to a group of related objects (like Account and Contact objects) within a Salesforce Org

3. Table

• Definition: A structured set of data held in rows and columns
• Purpose: Stores actual data entries
• Salesforce Analogy: Comparable to an individual Salesforce object (like the Account or Contact object) that holds records (data entries)

Working with Fields

Within your database table, you will spend a lot of time referencing and selecting fields as you build queries. We won’t get into the depths of database definition language SQL, meta data management or or query optimization. 

1. Field (Column)

• Definition: A single piece of data stored in a table, representing a specific attribute of the data. Fields are the columns in a table.
• Purpose: Holds individual pieces of data for each record (row) in the table. Each field has a data type (e.g., integer, varchar, date) that defines the kind of data it can store.
• Salesforce Analogy: Comparable to a field in a Salesforce object (like the FirstName or LastName field in the Account object).

Field Configuration and Metadata

Data Types

• Definition: Specifies the type of data that can be stored in a field. Common data types include integer, varchar (variable character), date, boolean, etc.
• Purpose: Ensures that the data stored in a field is of the correct type, which helps maintain data integrity and supports efficient data processing.
• Example:
  • Integer: Used for whole numbers.
  • Varchar: Used for text data.
  • Date: Used for dates.
  • Boolean: Used for true/false values.
• Salesforce Analogy: Similar to the data types of Salesforce fields, such as text, number, date, and checkbox.

Primary Keys

• Definition: A field (or combination of fields) that uniquely identifies each record in a table.
• Purpose: Ensures that each record in the table is unique and can be efficiently retrieved.
• Example: A CustomerID field in the Customers table.
• Salesforce Analogy: Similar to the Id field in Salesforce objects, which uniquely identifies each record.

Foreign Key

• Definition: A field in one table that links to the primary key of another table, establishing a relationship between the two tables.
• Purpose: Enforces referential integrity and allows for the creation of relationships between tables.
• Example: An OrderID field in the OrderDetails table that links to the OrderID in the Orders table.
• Salesforce Analogy: Similar to a lookup or master-detail relationship field in Salesforce, which links one object to another.

Snowflake vs traditional RBDMS

All of these concepts you have learned about in this section are good for context and understanding of the anatomy of a relational database. Snowflake itself originally was not designed to operate as a RBDMS, but rather a cloud data platform that inherits many of the common properties of a RBDMS in its innovative cloud artchitecture. 

Concepts like database, schema, and table help organize and secure data. With this basic understanding of the anatomy of your database, we can return to creating and exploring SQL data query language to access and manipulate data!

Basics SQL Statement

Now, we will dig into the components of a SELECT statement so you understand the building blocks of the code you just ran and how we will build upon Hello World to start querying our database.

SELECT

Most of the SQL you will learn early on starts with a command that instructs the database what we are going to do. All of our queries in this course will generally start with a “SELECT” command. The role of the SELECT command is to fetch data from a database. This is one of several “Data Manipulation Language” DML commands used to insert, update, delete, and select data from a database.

Literal

Instead of specifying a column name from a table, we entered a simple text string also known as a literal: ‘Hello World’. This demonstrates that a SELECT statement can also be used to output fixed, specified values, not just data from tables. Literals are commonly used any time you need to declare a value that is known and has meaning. For example when you filter or write a conditional statement. We will cover all of these use cases together.

Literals are declared using single quotes: Half the battle in learning any new programming language is how single and double quotes are used.

Column Name

When we get to the next lab we will upload data to Snowflake and SELECT specific columns. Column names are encapsulated in double-quotes in Snowflake anytime you require case sensitivity or have special characters like spaces in your column names. For example, if we wanted to select an Id and Name field in 1 query, it would look like this. We will get to create a query like this in our next lab.

SELECT “Id”, “Name”

Column names are declared in double quotes when you need case sensitivity. If you do not include double quotes your column names will be represented in all caps.

“Id” is interpreted by Snowflake as Id

Id is interpreted by Snowflake as ID

Alias

In our SELECT statement, we have our ‘Hello World’ literal value but we don’t have a column name specified to hold this value. To provide a clear, user-friendly name we specified an Alias in double quotes to name the column.  This is an important concept that you will use in future labs where we may change an existing column name.

 as “My First Field”

It is important to note that the word “as” is not required in SQL syntax. It does help for readability as you start learning. For consistency, I will include the word “as” anytime I implement an alias. In the same way you have API names and field Labels in Salesforce, aliases provide a clean and easy-to-read label for a column.

Practical Use Case for an Alias

Typically, when working with Salesforce data in Snowflake, you are using API names (which is preferred). Aliases are a great way to deliver labels or without underscores and trailing “__c”.  Here is an example

SELECT "my_customfield__c" as "My Custom Field" FROM "Leads"

NOTE: When you review SQL written by other developers you may notice the “as” keyword is not included. This is an optional keyword for readability of code but not necessary. For the sake of consistency and learning, we will always include the “as” keyword.

FROM

The FROM clause indicates the database object from which you are pulling data. When you use a SELECT statement, you’re usually interested in getting data from a particular table or view in your database. The FROM clause is where you specify that table. Syntax: In a simple query, the FROM clause:

SELECT column1, column2 FROM table_name;

In the next lab we will get to execute the following SQL and view the results:

SELECT "Id", "Name" FROM "Leads"

Debugging Code

A lot of your adventures in SQL will be debugging and troubleshooting code that does not execute. 

Character Formatting When Copy Pasting Into Snowflake

Luckily in Hello World, because we are not querying a database, the only thing that could go wrong is copy and paste into Snowflake. These are tough to pick out to the naked eye. As you can see, when I copy pasted from Google Sheets, there was a problem with our double quotes. The fix was re-typing the double quotes.