Data Warehouse Object Hierarchy

Your Data Warehouse, whether Snowflake or Redshift, contains a hierarchy of objects:

• One or more databases
• Each database contains one or more schemas
• Each schema contains one or more tables
• Each table has many columns

For more information on these objects, please read the warehouse specific documentation for Snowflake and Redshift

Below, is an example of the database hierarchy as presented in our SQL console:

Referencing Objects

Objects are referenced using dot notation. [DATABASE].[SCHEMA].[TABLE].[COLUMN] e.g.

SELECT 
MY_FAVOURITE_COLUMN
FROM KLEENE.BUSINESS.ANALYTICS

This query would retrieve the data from a single column called MY_FAVOURITE_COLUMN from a table called ANALYTICS which is stored within a schema called BUSINESS which is stored within a database called KLEENE.

If you reference any object that does not exist within the parent object (e.g. a table that does not exist in the referenced schema) you will get an error. This also applies to the destination tables of extracts. You will get an error like the one below:

❗️

Extract Failure

Error running extract ingest schema does not exist or not authorized.
If you get this error it is likely there is a typo in one of the objects referenced in the destination, or you have not created the database or schema referenced.

Creating, Modifying and Dropping Objects

Databases and schemas must be created from the console. These can be created by executing a CREATE statement:

CREATE SCHEMA BUSINESS.MY_FAVOURITE_SCHEMA

This will create a schema called MY_FAVOURITE_SCHEMA in the database business.

Tables will be created as necessary when extracts and transforms run. But you can still create tables and columns in the console.

📘

Default Database

If you do not specify a database the default database for the session will be used. To check what this database is you can execute the command SHOW DATABASE in Snowflake and SELECT CURRENT_DATATBASE() in Redshift

Once an object is created it can be modified using the UPDATE command in Snowflake and Redshift.

An object can be removed from the warehouse using the DROP command in Snowflake and Redshift.

🚧

DROP object Warning

All child objects are also dropped.
An object can be restored in Snowflake using the UNDROP command in Snowflake as long as it is executed within the data retention period (default 24hrs).
An object can be restored in Redshift using the ROLLBACK command(https://docs.aws.amazon.com/redshift/latest/dg/r_ROLLBACK.html) or from Redshift directly using table restoration