Database in Snowflake. If I ask this question…
If I ask this question “What are Database?” the answer would be -
Databases are logical structures used to organize and store data for future processing, retrieval, or evaluation. In the context of computers, these structures are nearly always managed by an application called a database management system or DBMS. The DBMS manages dedicated files on the computer’s disk and presents a logical interface for users and applications.
From <https://www.prisma.io/dataguide/intro/what-are-databases#what-are-databases>
Now, If I ask the same question with a twist, “What are Snowflake Database?” answer would be different -
Snowflake provides a system-defined, read-only shared database named SNOWFLAKE that contains metatdata, as well as historical usage data, about the objects in your organization and accounts.
When an account is provisioned, Snowflake automatically imports the SNOWFLAKE database into the account from a share named ACCOUNT_USAGE. The database is an example of Snowflake utilizing Secure Data Sharing to provide object metadata and other usage metrics for your organization and accounts.
From <https://docs.snowflake.com/en/sql-reference/snowflake-db.html>
In simple terms, Snowflake databases are a more modern type of database architecture. Their rise in popularity reflects the way companies use data today and what platforms they prefer to access their information.
Unlike its predecessor, the star schema, snowflake schemas allow greater flexibility when designing and managing databases. They’re also easier to maintain and update than traditional relational databases.
Lets get quick overview of Database on Snowflake Portal -
Login into the snowflake portal and click on Database Tab, this will show you all the list of databases currently created/present in snowflake -
Below table show List of Database and all the other details.
Now, If you click any of the databases you will get more information regarding the tables, views, schema, etc.
Click on SNOWFLAKE_SAMPLE_DATA Database
If you click on the column name, it will sort in Ascending or Descending Order or there will be a Drop-Down arrow option will display to select and choose the required action.
Lets, click on the Rows and Sort it in Descending Order.
Click on STORES_SALES, snowflake will drill-down and give more information like column name, datatype, default type of column.
Likewise for Views, Schema, Stages and other options.
To go back to the previous page, click on this, it will take back you to previous page
Going back to Table and schema level, when you click Table multiple option will display like create, create like, clone, upload. Each action has its own meaning
- Create a Table
- Create Like
- Clone an existing table
- Load Data
- Drop
- Transferring Ownership
We will see this action step by step in later.
Staring with
Creating Database in Snowflake
Go to the Database page and above the list of database name there will be an option called “Create” with + sign. Click on it.
A window will pop up and write the database name you wish to be named.
Click on Finish
Now, Database is created but if you click on your database it will be blank with no Tables and Views. Note — If you click on Schema, two schema will be created with name INFORMATION_SCHEMA and PUBLIC
Next question comes to our mind how will you create table and add columns into the database. There will an option present “Create” click on it and follow the steps below -
Now, Table STUDENTS will be created with the following column
To check the data present in the Table ->Go to Worksheet and run the below command
select * from “SARVESHDB”.”PUBLIC”.”STUDENTS”
Next question comes to our mind is “How to load data? Is there any special method present in Snowflake or it use the Traditional methos?”
Well the answer is both Yes and No which depends on the scenario. If we look as a beginner perspective we will simply have to use “Insert” command to insert the records into the table but if we look from DBA perspective we need to LOAD the data.
There is lot of difference between INSERT and LOAD.
Insert -
INSERT[OVERWRITE]INTO<target_table>[(<target_col_name>[,…])]{VALUES({<value>|DEFAULT|NULL}[,…])[,(…)]|<query>}
From <https://docs.snowflake.com/en/sql-reference/sql/insert.html>
Series of Snowflake blog coming up soon. Stay connected!
Some of my other blog -