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:
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
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:
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
Tables will be created as necessary when extracts and transforms run. But you can still create tables and columns in the console.
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 DATABASEin 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
Updated 2 months ago