CREATE TABLE

Creates a new table in the current database.

Syntax

CREATE [FACT|DIMENSION] TABLE [IF NOT EXISTS] <table_name>
(
    <column_name> <column_type> [constraints] [DESCRIPTION = <column_description>] [STATISTICS [(<statistics_type> [, <statistics_type> [, ...n]])]]
    [, <column_name> <column_type> [constraints] [DESCRIPTION = <column_description>] [STATISTICS [(<statistics_type> [, <statistics_type> [, ...n]])]]
    [, ...n]]
    [, STATISTICS [ALL|(<column_name>)] [TYPE <statistics_type>] [TYPE ...n]
    [, STATISTICS ...n]]
    [, UNIQUE (<column_name>[, <column_name>[, ...n]])
    [, UNIQUE ...n]]
    [, FOREIGN KEY (<column_name>[, ...n]) REFERENCES <referenced_table> (<referenced_column>[, ...n]) NOT ENFORCED
    [, FOREIGN KEY ...n]]
)
[PRIMARY INDEX <column_name>[, <column_name>[, ...k]]]
[PARTITION BY <column_name>[, <column_name>[, ...m]]]
[WITH ( <storage_parameter> = <storage_parameter_value>[, <storage_parameter> = <storage_parameter_value>[, ...p]], [DESCRIPTION = <table_description>] ) ]

Parameters

Parameter	Description
`<table_name>`	An identifier that specifies the name of the table. This name should be unique within the database.
`<column_name>`	An identifier that specifies the name of the column. This name should be unique within the table.
`<column_type>`	Specifies the data type for the column.
`<column_description>`	The optional description of the column.
`<storage_parameter>`	The name of a storage parameter for controlling behaviors related to storage and indexes.
`<storage_parameter_value>`	The value assigned to a `<storage_parameter>`.
`<table_description>`	The optional description of the table.
`<statistics_type>`	Type of automated column statistics to collect. Possible values: `NDISTINCT`, `MINMAX`

All identifiers are case-insensitive unless enclosed in double-quotes. For more information, see Object identifiers.

Column constraints and the default expression

Firebolt supports the following column constraints:

<column_name> <column_type> [NULL | NOT NULL] [DEFAULT <expression>] [UNIQUE] [GENERATED ALWAYS AS (<expression>) STORED]

Constraint	Description	Default value
`DEFAULT <expression>`	Determines the default value used when no value is provided, instead of inserting a `NULL` value.
`NULL \| NOT NULL`	Determines if the column may or may not contain `NULL` values.	`NULL`
`UNIQUE`	Provides metadata to the query optimizer about column uniqueness. Does not enforce uniqueness - ensuring unique values is the user’s responsibility.
`GENERATED ALWAYS AS (<expression>) STORED`	Makes the column a generated column: its value is always the given expression over the row’s other columns, computed on every write.

Only literals and the following functions are supported in default expressions: CURRENT_DATE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, and NOW, which is an alias for CURRENT_TIMESTAMP.

STRUCT fields within ARRAY columns cannot have UNIQUE constraints

Generated columns

A generated column’s value is always the expression it is declared with, recomputed from the row’s other columns on every write:

CREATE FACT TABLE events (
    ts TIMESTAMPNTZ,
    amount DOUBLE,
    ts_minute TIMESTAMPNTZ GENERATED ALWAYS AS (date_trunc('minute', ts)) STORED,
    fee DOUBLE GENERATED ALWAYS AS (amount * 0.03) STORED
) PRIMARY INDEX ts_minute;

A generated column is an ordinary stored column in every other respect: it appears in SELECT * and in information_schema.columns, and it can be used in PRIMARY INDEX and PARTITION BY. information_schema.columns reports it with is_generated = 'ALWAYS' and the expression in generation_expression. No value may be assigned to a generated column, so INSERT and UPDATE cannot target one. A generated column still occupies a position in the table’s column order, which means an INSERT without a column list must supply values only up to the first generated column:

CREATE FACT TABLE t (a INT, b INT, g INT GENERATED ALWAYS AS (a + b) STORED) PRIMARY INDEX a;

INSERT INTO t VALUES (1, 2);       -- succeeds: the values stop before g
INSERT INTO t VALUES (1, 2, 3);    -- fails: 3 would be assigned to g
INSERT INTO t (a, b) VALUES (1, 2);-- succeeds

STORED means the value is materialized on disk. It is mandatory: GENERATED ALWAYS AS (<expression>) on its own is a syntax error. VIRTUAL generated columns are not supported. The defining expression must be deterministic. It cannot reference the column it defines, another generated column, or a TIMESTAMPTZ column, and it cannot contain aggregates, window functions, subqueries or user-defined functions. The column’s declared type wins: the expression is cast to it. A generated column may be declared NOT NULL, in which case a row whose expression evaluates to NULL is rejected when it is written. A generated column may be of any type, STRUCT and ARRAY included. A STRUCT column is generated as a whole: the clause is declared once for the column and cannot be declared on an individual STRUCT field. Generated columns are supported on fact and dimension tables only. A generated column cannot be added to an existing table with ALTER TABLE ADD COLUMN, and a column referenced by one cannot be dropped.

Table-level UNIQUE constraints

A UNIQUE constraint over one or more columns can also be declared as a separate element of the column list:

UNIQUE (<column_name>[, <column_name>[, ...n]])

A multi-column constraint declares that the combination of values in the covered columns is unique across all rows. A single-column UNIQUE (a) is equivalent to declaring UNIQUE on the column itself and is stored as such. A constraint may cover a STRUCT column, declaring the combination of all the struct’s fields unique. Like the column-level constraint, table-level constraints are not enforced; they provide metadata to the query optimizer. See UNIQUE constraints as query hints. Constraints can be added to and removed from an existing table with ALTER TABLE ADD UNIQUE and ALTER TABLE DROP UNIQUE.

Table-level FOREIGN KEY constraints

A FOREIGN KEY declares that the listed columns reference a unique key of another table:

FOREIGN KEY (<column_name>[, ...n]) REFERENCES <referenced_table> (<referenced_column>[, ...n]) NOT ENFORCED

The referencing and referenced column lists must have the same length and matching column types, and the referenced columns must be a unique key of the referenced table: either a single column declared UNIQUE, or the full set of columns of a table-level UNIQUE constraint. The referenced table must be in the same database. NOT ENFORCED is required. Firebolt stores the foreign key as query-optimizer metadata and never verifies that the referencing values exist in the referenced table, so preserving referential integrity is the user’s responsibility.

Creating a FOREIGN KEY requires the SELECT privilege on the referenced table. The constraint reads the referenced table’s schema, and it restricts later DDL on that table: a referenced column, or the UNIQUE key it relies on, cannot be dropped while an inbound foreign key exists. Requiring SELECT prevents a user from placing a constraint on, and thereby affecting the DDL of, a table they cannot read. Without SELECT on the referenced table, the CREATE TABLE statement is rejected.

The following example creates a fact table with a foreign key onto the UNIQUE key of a dimension table:

CREATE DIMENSION TABLE customers (customer_id INT NOT NULL UNIQUE, name TEXT);

CREATE FACT TABLE orders (
    order_id    INT NOT NULL,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id) NOT ENFORCED
);

Example: Creating a table with `NULL` and `NOT NULL` values

The following example illustrates different use cases for column definitions and INSERT statements:

An Explicit NULL insert – a direct insertion of a NULL value into a particular column.
An Implicit NULL insert – an INSERT statement with missing values for a particular column.

The following example creates a fact table t1 with five columns, specifying if each column can contain NULL values, their default values, and a primary index on col2:

CREATE FACT TABLE t1
(
    col1 INTEGER  NULL,
    col2 INTEGER  NOT NULL,
    col3 INTEGER  NULL DEFAULT 1,
    col4 INTEGER  NOT NULL DEFAULT 1,
    col5 TEXT
)
PRIMARY INDEX col2;

After creating a table, you can manipulate the values using different INSERT statements, as shown in the following examples:

INSERT statement	Results and explanation
`INSERT INTO t1 VALUES (1,1,1,1,1)`	This code example inserts `1` into each column.
`INSERT INTO t1 VALUES (NULL,1,1,1,1)`	This code example explicitly inserts a `NULL` value into `col1`. Because `col1` can contain `NULL` values, this operation is successful.
`INSERT INTO t1 (col2,col3,col4,col5) VALUES (1,1,1,1)`	This code example shows both explicit and implicit `INSERT` statements. Because `col1` has no value specified, and lacks a default expression, it is implicitly set to `NULL`.
`INSERT INTO t1 VALUES (1,NULL,1,1,1)` `INSERT INTO t1 (col1,col3,col4,col5) VALUES (1,1,1,1)`	This code example shows how a null mismatch error is generated. Because `col2` is defined as `NOT NULL` with no default expression, both `INSERT` statements implicitly try to insert `NULL` values into `col2`, and generate “null mismatch” events.
`INSERT INTO t1 VALUES (1,1,NULL,1,1)`	This code example explicitly inserts a `NULL` value into `col3`. Because `col3` is defined as `NULL DEFAULT 1`, the operation is successful.
`INSERT INTO t1 (col1,col2,col4,col5) VALUES (1,1,1,1)`	By not specifying a value for `col3`, this code example implicitly inserts the default value `1` into `col3`, which is defined as `NULL DEFAULT 1`.
`INSERT INTO t1 VALUES (1,1,1,NULL,1)`	This code example shows how another null mismatch error is generated. Because `col4` is defined as `NOT NULL DEFAULT 1`, the explicit insertion of a `NULL` value violates the `NOT NULL` constraint, and results in a null mismatch event.
`INSERT INTO t1 (col1,col2,col3,col5) VALUES (1,1,1,1)`	This code example shows how omitting a value in an implicit insert invokes the default value `1` for `col4`.
`INSERT INTO t1 VALUES (1,1,1,1,NULL)` `INSERT INTO t1 (col1,col2,col3,col4) VALUES (1,1,1,1)`	This code example shows how explicit and implicit inserts cause a null mismatch error. Because `col5` was neither defined with a default expression nor allowed to contain `NULL` values, Firebolt treats `col5` as `NOT NULL DEFAULT NULL`. Both `INSERT` statements attempt to insert a `NULL` value into a `NOT NULL TEXT` column, invoking in a null mismatch event.

Example creating table with UNIQUE constraint over columns

The following examples illustrates UNIQUE constraint over different column definitions : Primitive types:

CREATE FACT TABLE users (
    id INTEGER UNIQUE,
    email TEXT UNIQUE,
    score DOUBLE UNIQUE,
    active BOOLEAN UNIQUE,
    created_date DATE UNIQUE
);

Multi-column constraints:

CREATE FACT TABLE events (
    device_id INTEGER,
    event_time TIMESTAMP,
    payload TEXT,
    UNIQUE (device_id, event_time)
);

STRUCT fields and columns:

CREATE FACT TABLE user_profiles (
    user_data STRUCT (
        id INTEGER UNIQUE,
        email TEXT UNIQUE,
        score DOUBLE UNIQUE
    ),
    profile STRUCT (
        name TEXT,
        age INTEGER
    ) UNIQUE
);

ARRAY columns:

CREATE FACT TABLE collections (
    numbers ARRAY(INTEGER) UNIQUE,
    names ARRAY(TEXT) UNIQUE,
    complex_data ARRAY(STRUCT (
        id INTEGER,
        name TEXT
    )) UNIQUE
);

PRIMARY INDEX

The PRIMARY INDEX is an optional sparse index that sorts and organizes data based on the indexed field as it is ingested, without affecting data scan performance. For more information, see Primary index.

Syntax

PRIMARY INDEX <column_name>[, <column_name>[, ...n]]

The following table describes the primary index parameters:

Parameter.	Description	Mandatory?
`<column_name>`	Specifies the name of the column in the Firebolt table which composes the index. At least one column is required.	Y

PARTITION BY

The PARTITION BY clause defines one or more columns that determine how the table is divided into physical parts. These columns serve as the partition key and cannot allow NULL values. When multiple columns are used as the partition key, the combination of all of these columns define the partition boundaries.

PARTITION BY <column_name>[, <column_name>[, ...n]]

The following subset of SQL functions can be used in PARTITION BY expressions:

TO_YYYYMM
TO_YYYYMMDD
EXTRACT(year|month|day|hour from <column_name>)
DATE_TRUNC

For more information, see Working with partitions.

Table type

Firebolt supports two types of tables:

FACT table - the data is distributed across all nodes of the engine.
DIMENSION table - the entire table is replicated in every node of the engine.

The CREATE TABLE command defaults to a FACT table. DIMENSION tables are ideal for relatively small tables, up to tens of gigabytes, that are used in joins with FACT tables.

Storage Parameters

Storage parameters are specified in the optional WITH (...) clause as comma separated <storage_parameter> = <storage_parameter_value> assignments.

Storage Parameter	Description
`<index_granularity>`	The maximum number of rows in each granule. `<storage_parameter_value>` must be a power of 2 between 128 and 8192. The default value is 8192. For more information, see Index granularity.
`<compression>`	The compression to use at the table level. The default value is `LZ4`. For more information, see Custom Compression.
`<compression_level>`	The compression level to use for the specified compression. This parameter cannot be set without `compression`. For more information, see Custom Compression.

All identifiers are case-insensitive unless enclosed in double-quotes. For more information, see Object identifiers.

DESCRIPTION

It is possible to add a description to the table as well as to the columns during the table creation.

CREATE TABLE table_with_description
(
    col1 INTEGER NULL DESCRIPTION 'this is a column col1',
    col2 INTEGER NOT NULL DEFAULT 1,
    col3 TEXT DESCRIPTION 'this is a column col3'
)
WITH (DESCRIPTION = 'this is a table')

Firebolt also supports the following related functions:

CREATE TABLE AS SELECT (CTAS) – - Creates a table and loads data into it based on a SELECT query.
CREATE TABLE CLONE – Creates a table that is a copy of an existing table in the database.

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Column constraints and the default expression

Generated columns

Table-level UNIQUE constraints

Table-level FOREIGN KEY constraints

Example: Creating a table with `NULL` and `NOT NULL` values

Example creating table with UNIQUE constraint over columns

PRIMARY INDEX

Syntax

PARTITION BY

Table type

Storage Parameters

DESCRIPTION

​Syntax

​Parameters

​Column constraints and the default expression

​Generated columns

​Table-level UNIQUE constraints

​Table-level FOREIGN KEY constraints

​Example: Creating a table with NULL and NOT NULL values

​Example creating table with UNIQUE constraint over columns

​PRIMARY INDEX

​Syntax

​PARTITION BY

​Table type

​Storage Parameters

​DESCRIPTION

​Related functions

Syntax

Parameters

Column constraints and the default expression

Generated columns

Table-level UNIQUE constraints

Table-level FOREIGN KEY constraints

Example: Creating a table with `NULL` and `NOT NULL` values

Example creating table with UNIQUE constraint over columns

PRIMARY INDEX

Syntax

PARTITION BY

Table type

Storage Parameters

DESCRIPTION

Related functions