Snowflake Create, Clone, Drop Database

  • Post author:
  • Post category:Snowflake
  • Post last modified:December 13, 2022
  • Reading time:5 mins read

In this section, you will learn Snowflake DDL database commands which include creating a database, cloning database, dropping database and more. You can run all these commands either from Snowflake web console or from your local machine using SnowSQL CLI.

Create a Snowflake Database

Using CREATE DABATAE you can create a Snowflake database.


CREATE DATABASE EMPLOYEE;
CREATE DATABASE EMPLOYEE DATA_RETENTION_TIME_IN_DAYS 5;

Create or Replace Database

Use OR REPLACE in order to drop the existing Snowflake database and create a new database. You can also do this first by running DROP DATABASE and running CREATE DATABASE


CREATE OR REPLACE DATABASE EMPLOYEE;

Create a Transient database

Use TRASIENT option to create a trasient database. transient databases are not fail-safe and take less storage hence, there is no recovery when data loss.


CREATE TRANSIENT DATABASE EMPLOYEE;

Clone Database

use optional CLONE optional parameter in order to clone the existing database, Cloning database copies all schema, tables and everything from the database.


CREATE DATABASE EMPLOYEE_CLONE CLONE EMPLOYEE ;
+-----------------------------------------------+
| status                                        |
|-----------------------------------------------|
| Database EMPLOYEE_CLONE successfully created. |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.644s

Drop Database

DROP DATABSE is used to drop the existing snowflake database


DROP DATABASE EMPLOYEE;
+--------------------------------+
| status                         |
|--------------------------------|
| EMPLOYEE successfully dropped. |
+--------------------------------+
1 Row(s) produced. Time Elapsed: 0.132s

Show Database

Using SHOW DATABSE, you can see all the tables in the Snowflake account.


SHOW DATABASES;
+-------------------------------+-----------------------+------------+------------+-------------------------+--------------+----------------------------+---------+----------------+
| created_on                    | name                  | is_default | is_current | origin                  | owner        | comment                    | options | retention_time |
|-------------------------------+-----------------------+------------+------------+-------------------------+--------------+----------------------------+---------+----------------|
| 2020-02-23 17:13:40.755 -0800 | DEMO_DB               | N          | N          |                         | SYSADMIN     | demo database              |         | 1              |
| 2020-03-07 19:25:04.549 -0800 | EMPLOYEE_CLONE        | N          | Y          |                         | SYSADMIN     |                            |         | 1              |
| 2020-02-23 17:13:41.635 -0800 | SNOWFLAKE_SAMPLE_DATA | N          | N          | SFC_SAMPLES.SAMPLE_DATA | ACCOUNTADMIN | TPC-H, OpenWeatherMap, etc |         | 1              |
| 2020-02-23 17:13:35.624 -0800 | UTIL_DB               | N          | N          |                         | SYSADMIN     | utility database           |         | 1              |
+-------------------------------+-----------------------+------------+------------+-------------------------+--------------+----------------------------+---------+----------------+
4 Row(s) produced. Time Elapsed: 0.142s

It also shows all default databases that come with account creation.

Set default Database

If you wanted to set the default database.


USE employee_clone

References

Naveen (NNK)

Naveen (NNK) is a Data Engineer with 20+ years of experience in transforming data into actionable insights. Over the years, He has honed his expertise in designing, implementing, and maintaining data pipelines with frameworks like Apache Spark, PySpark, Pandas, R, Hive and Machine Learning. Naveen journey in the field of data engineering has been a continuous learning, innovation, and a strong commitment to data integrity. In this blog, he shares his experiences with the data as he come across. Follow Naveen @ LinkedIn and Medium

Leave a Reply