Database : A logical collection of schemas is a database, serves as containers for organizing and managing your data. Each snowflake account owns a distinct database.
Snowflake Create Database Example :
To create a new database in Snowflake run the following Sql.
sql : create database my_database ;
This command will create a database with name my_database. If database is already present then this will through error.
Snowflake Create Or Replace Database :
To create a new database or replace an existing DB if it already exists run the following sql.
sql : create or replace database my_database;
This command will create a database with name my_database or replace my_database database, if already present.
Here we will not encounter any error if the DB is present.
Snowflake Create Database Clone :
Cloning a database in Snowflake allows you to duplicate an existing database’s structure, including schemas and objects, but without the data. Here’s how you can do it:
sql : create database new_database clone existing_database;
This command will create a new database with name new_database that copies the structure of existing_database.
Snowflake Create Database Like :
Creating a database like another database is a great way to replicate the structure without copying the data. Run the below command :
sql : create database new_empdb like existing_empdb;
This command will create a new database with the name new_empdb that copies the same structure as existing_empdb.
Data Sharing in Snowflake:
Snowflake’s Data Sharing feature enables you to share the databases and other selected objects in a database in your account through shares to other snowflake accounts, which are created by data providers and imported by data consumers. Here data movement never happens between provider accounts to consumer accounts. Everything is managed by snowflake cloud services layer and metadata store, Shared data never adds any storage to the consumer account.
Snowflake Create Database from Share :
To create a database from a share provided by another Snowflake account
First, you need to have access to the shared data share in provider account.Once you have the access to the shared data, you can create your new database within your Snowflake account that is essentially a mirror of the shared data.
Run the following SQL command to create a database from share.
sql : create database new_empdb from share shared_empdb;
This command creates a new database with name as new_empdb and populates it with the data shared through shared_empdb. Here the data remains in the original share – shared_empdb; this is only creating a structured environment to work with it.
Secondary Database :
In Snowflake Secondary database is a replica or copy of a standard (primary) database. This is created by replicating the structure and data of an existing primary database. Its like a mirror copy of the primary database, and any changes made to the primary database are automatically reflected in the secondary database.Secondary databases are used for various purposes, including disaster recovery, read scaling, data distribution, testing, and development.
Snowflake Creating Replica Database :
Example:
Lets say we have a standard (primary) database named “emp_db_p” and want to create a secondary database named “emp_db_s“.
Run the following SQL to create :
sql : create database emp_db_s as replica of emp_db_p ;
After executing the SQL command, Snowflake will create the secondary database, “emp_db_s” as a replica of the “emp_db_p” standard database.
Run the below command to verify the creation of the secondary database:
sql : show databases;
This command will display a list of all databases in your Snowflake account, including the newly created secondary database.
Snowflake Create Transient Database :
Run the following SQL command to create a transient database.
sql : create or replace transient database emp_db_transient;
This command will create a new transient database with the name emp_db_transient.
Here Transient database means do not have a Fail-safe period so there is no additional storage costs once they leave Time Travel, Here data is not protected by Fail-safe in the event of a data loss. All schemas and consequently all tables created in a transient database are transient. For more information, see Understanding & Viewing Fail-safe.
References : Snowflake create database documentation