Skip to content

Create Table

Create or replace a table. Generates any required DDL to manage an existing table of the same name, followed by a CREATE TABLE statement.

Warning

This component is potentially destructive. Take care when running this component as it may remove existing data.

Properties

Name = string

A human-readable name for the component.

Create/Replace = drop-down

Select one of:

  • Create: (default). This option creates a new table. Creating a new table will generate an error if a table with the same name already exists. However, existing data will not be destroyed.

  • Create if not exists: This option will only create a new table when a table of the same name does not already exist. This option will not destroy existing data. If the schema of the existing table does not match the schema defined in this component, no attempt is made to fix or correct the schema, which could lead to errors later on in the pipeline if:

    • Users did not expect a table to already exist.
    • Users did not expect to have a different schema to the one defined in this component.

  • Replace: this option drops any existing table of the same name and then creates a new table. Selecting this option guarantees that after the component succeeds, the table matches the schema defined in this component. However, any data from the existing table (to be replaced) will be lost. Since other database objects might depend upon this table, drop ... cascade is used in the "Comment" property, which may remove many other database objects.

Database = drop-down

The Snowflake database that the newly created table will belong to. The special value, [Environment Default], will use the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.

Schema = drop-down

The Snowflake schema that the newly created table will belong to. The special value, [Environment Default], will use the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.

New Table Name = string

The name of the table to create or replace. This field is case-sensitive by default, since Designer uses quoted identifiers. To change this behavior, please consult the Snowflake documentation.

Table Type = drop-down

Select the type of Snowflake table to create:

  • Permanent: A table that holds data indefinitely and that can be restored using Snowflake's Time Travel.
  • Temporary: A table that is automatically destroyed at the end of the Snowflake session.
  • Transient: A table that holds data indefinitely, but that can't be restored.

Columns = column editor

Enter the following details for each table column.

Column Name: The name of the new column.

Data Type: Select one of:

  • Varchar: This type is suitable for numbers and letters. A varchar or Variable Character Field is a set of character data of indeterminate length. More....
  • Number: This type is suitable for numeric types, with or without decimals. More....
  • Float: This type of values are approximate numeric values with fractional components. More....
  • Boolean: This type is suitable for data that is either "true" or "false". More....
  • Date: This type is suitable for dates without times. More....
  • Time: This type is suitable for time, independent of a specific date and timezone. More....
  • Timestamp: This type is a timestamp left unformatted (exists as Unix/Epoch Time). More....
  • Variant: Variant is a tagged universal type that can hold up to 16 MB of any data type supported by Snowflake. More....

Size: For Text types, this is the maximum length. This is a limit on the number of bytes, not characters. For Numeric types, this is the total number of digits allowed, whether before or after the decimal point.

Precision: The precision of the data in the column. Will be 0 (zero) for non-applicable types.

Default Value: The default value under this column for any row. You can enter AUTOINCREMENT or IDENTITY as the default value for any numeric-type column and the column will automatically give an incremented value on each new row starting from 1. This feature is useful when creating a unique key on the table.

Not Null: True if this column does not accept null values.

Unique: Mark this column as unique, declaring that all values in it are unique with respect to one another. You may have multiple unique columns per table.

Comment: A location to store descriptive VARCHAR comments against columns. Used to contextualize the content being stored in tables in your database.

Default DDL Collation = string

Set the default DDL collation. Setting this parameter forces all subsequently created columns in the affected table to have the specified collation as the default, unless the collation for the column is explicitly defined in the DDL. For more information, refer to the Snowflake documentation.

Primary Keys = dual listbox

Declare one column to be a primary key. Primary keys are, by default, unique. Users can only have one primary key per table.

Clustering Keys = dual listbox

Specify clustering key(s) to use. Defining clustering keys can allow for more efficient sorting and scanning of large tables. For more information, refer to the Snowflake documentation.

Data Retention Time in Days = integer

Set a number of days for which data is retained after deletion. For more information, refer to the Snowflake documentation.

Comment = string

Attach a comment to the table. For more information, refer to the Snowflake documentation.