In the article I will try to give you a small introduction in how to create tables and views in SNOWFLAKE.
Snowflake is basically a Data Warehouse where you can have many databases.
For this article let’s assume that you have already a created user, and also you user has been added to one or many specific roles that have grant permissions of creating tables. When you enter the first time in you may have the following view:
We are going to click on the option “Classic Console” that is on the left pane of the page, as you can see above.
You’ll be redirected to sign into Snowflake again, that is normal, after you’ve signed the classic console view will have 7 tabs.
For this article only Databases and Worksheets will be described. The “Databases” section shows all the databases you have permissions to see, “Worksheets” section is where the queries can be executed, in this link of Snowflake Documentation there is a very detailed explanation of how it works.
Is SQL Supported in Snowflake?
Snowflake supports a common standardized version of SQL: ANSI. This means that most of the common operations needed in warehousing operations, like create, update, insert, select are supported.
Here’s an example of creating a table:
Before you run the query, make sure you’ve chosen the right Data warehouse, database and schema where the table is going to be:
Then you can write your SQL statement:
Now that our table exists, we can load data from a csv file :
1- Go to the Databases section
2- Choose The database of the table:
3- Choose the table and click on “Load Table”
4- Follow the steps of the pop-up window:
- Its important to set the property of the “File Format” step: “Header lines to skip” to 1 in order to avoid loading the columns headers of the csv.
5- Check that your data has been uploaded.
Let’s create a simple view in snowflake.
1- As same as tables, make sure to select the correct, data warehouse, database and schema
2- Insert the SQL statement in the worksheet console:
3- Refresh and check the table has been created:
4- Preview data to check the view is the desired one.
SNOFLAKE AND POWER BI
Now that we have our table and view let’s connect our source to Power BI.
1- Go to “Get Data” and then search for Snowflake source:
2- Need to know the server url and the name of the warehouse, also for advanced options you can insert a SQL statement as a part of the connection to the source, in our case we won’t use that.
3- We must enter the credentials and once the identity is verified we will have access to choose the table or view we need.
4- You can choose “Load” or “Transform Data” according to your convenience.
5- Finally you can use the data from Snowflake in Power BI
“So… this has been a very quick and practical guide to start familiarizing with Snowflake, hope this article can be useful.”