CREATE TABLE ... FLOW
Applies to: Databricks SQL
This feature is in Beta.
You don't need to enable a preview to use CREATE TABLE ... FLOW.
Creates a Unity Catalog managed table whose data is written by a single append flow. When you run the statement, Databricks creates a serverless pipeline that runs the flow to populate the table. You refresh the table on demand or on a schedule, the same way you refresh a streaming table.
Unlike a streaming table, the result is a standard managed Delta table with no backing table. Downstream users and tools work with it like any other managed table.
To define this kind of table in a pipeline, including with more than one flow writing to it, see CREATE TABLE ... FLOW (pipelines).
Requirements
CREATE TABLE ... FLOW has the same requirements as other standalone pipelines, including a serverless-enabled account and a Unity Catalog-enabled workspace. To create the table, you need the CREATE TABLE privilege on the target schema. See Requirements.
Syntax
CREATE TABLE table_name
[ table_specification ]
[ table_clauses ]
flow_clause
table_specification
( { column_identifier column_type [ column_properties ] } [, ...]
[ CONSTRAINT expectation_name EXPECT (expectation_expr)
[ ON VIOLATION { FAIL UPDATE | DROP ROW } ] ] [, ...] )
column_properties
{ NOT NULL |
GENERATED ALWAYS AS ( expr ) |
COMMENT column_comment |
MASK clause } [ ... ]
table_clauses
{ PARTITIONED BY (col [, ...]) |
CLUSTER BY clause |
COMMENT table_comment |
TBLPROPERTIES clause |
schedule |
WITH { ROW FILTER clause } } [...]
schedule
{ SCHEDULE [ REFRESH ] schedule_clause |
TRIGGER ON UPDATE [ AT MOST EVERY trigger_interval ] }
schedule_clause
{ EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } |
CRON cron_string [ AT TIME ZONE timezone_id ]}
flow_clause
FLOW INSERT BY NAME query
Put all table clauses before the FLOW keyword.
Parameters
-
The name of the table to create. The table must be in a Unity Catalog catalog. If the name is not qualified, the table is created in the current catalog and schema.
The name must not belong to an existing streaming table or materialized view.
-
table_specification
This optional clause defines the columns, their types, properties, descriptions, and data quality expectations. If you omit it, the schema is inferred from the flow query.
-
A unique name for the column.
-
Specifies the data type of the column.
-
NOT NULL
If specified, the column does not accept
NULLvalues. -
GENERATED ALWAYS AS ( expr )
When you specify this clause, the value of this column is determined by the specified
expr. -
COMMENT column_comment
A
STRINGliteral that describes the column. -
MASK clause
Adds a column mask function to anonymize sensitive data. See Row filters and column masks.
-
CONSTRAINT expectation_name EXPECT (expectation_expr) [ ON VIOLATION { FAIL UPDATE | DROP ROW } ]
Adds a data quality expectation to the table. A
FAIL UPDATEexpectation causes the refresh to fail when a row violates it. ADROP ROWexpectation drops each row that violates it. See Manage data quality with pipeline expectations.
-
-
PARTITIONED BY (col [, ...])
Optionally partitions the table by a subset of columns. You cannot combine
PARTITIONED BYandCLUSTER BY. -
CLUSTER BY clause
Optionally enables liquid clustering on the table.
-
COMMENT table_comment
A
STRINGliteral that describes the table. -
TBLPROPERTIES clause
Optionally sets one or more user-defined table properties.
-
schedule
Optionally refreshes the table automatically.
SCHEDULEandTRIGGER ON UPDATEfollow the same rules and limits as they do for CREATE STREAMING TABLE. If you omit the schedule, the table refreshes only when you runREFRESH. -
WITH ROW FILTER clause
Adds a row filter function to the table. Queries on the table return only the rows for which the function evaluates to
TRUE. See Row filters and column masks. -
FLOW INSERT BY NAME query
Defines the append flow that writes to the table. Columns in the result of
queryare matched to table columns by name.queryis typically a streaming read, such asSTREAM(source_table)orSTREAM read_files(...). Each refresh appends only the data that has arrived in the source since the last refresh.
Refresh the table
Although the result is a managed table, you refresh it with the REFRESH STREAMING TABLE statement, the same as a streaming table:
-- Process new data and wait for the refresh to finish
REFRESH STREAMING TABLE main.sales.orders_bronze;
-- Start a refresh and return immediately
REFRESH STREAMING TABLE main.sales.orders_bronze ASYNC;
-- Reprocess all source data
REFRESH STREAMING TABLE main.sales.orders_bronze FULL;
A full refresh truncates the table and reprocesses all data available in the source. If the source keeps data for a limited time, such as Kafka, data that has aged out of the source is lost.
Between refreshes, you can run DML commands such as DELETE against the table.
Limitations
- The table must be in Unity Catalog. The
hive_metastorecatalog is not supported. - A
CREATE TABLE ... FLOWstatement defines one table with one flow. To write to a table from more than one flow, define the table in a pipeline. See CREATE TABLE ... FLOW (pipelines). CREATE TABLE ... FLOWfails if the table already exists.CREATE OR REFRESH TABLE ... FLOWis not valid syntax, andCREATE OR REPLACE TABLE ... FLOWis not allowed.CREATE TEMPORARY TABLE ... FLOWandCREATE EXTERNAL TABLE ... FLOWare not allowed.FLOW AUTO CDCis not supported and fails withMANAGED_TABLE_DOES_NOT_SUPPORT_CDC. To apply change data capture (CDC), use AUTO CDC FLOW Clause.FLOW REPLACE WHEREis not supported and fails withUNSUPPORTED_FEATURE.REPLACE_WHERE_ON_MANAGED_TABLE.- You cannot reuse the name of an existing streaming table or materialized view. Redefining a name as a different table type fails with
CANNOT_CHANGE_TABLE_MODEL_TYPE. Drop the existing table first. TARGET FRESHNESSis not supported and fails withTARGET_FRESHNESS_REQUIRES_ETL_PIPELINE. To use target freshness, define the table in a pipeline.PRIMARY KEYandFOREIGN KEYconstraints are accepted but are not applied to the table.- The following clauses are not supported and fail when the statement is parsed:
DEFAULT COLLATIONOPTIONSCLUSTERED BY ... INTO num_buckets BUCKETSROW FORMATandSTORED ASWITH CREDENTIAL
Examples
-- Incrementally copy new rows from a Delta table
CREATE TABLE main.sales.orders_bronze
FLOW INSERT BY NAME
SELECT * FROM STREAM(main.sales.orders_raw);
-- Create a partitioned table with an explicit schema, an expectation, and table properties
CREATE TABLE main.sales.orders_by_region (
order_id BIGINT NOT NULL,
region STRING,
amount DECIMAL(10, 2),
CONSTRAINT positive_amount EXPECT (amount > 0) ON VIOLATION DROP ROW
)
PARTITIONED BY (region)
TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true')
FLOW INSERT BY NAME
SELECT order_id, region, amount FROM STREAM(main.sales.orders_raw);
-- Create a table with liquid clustering
CREATE TABLE main.sales.orders_clustered (order_id BIGINT)
CLUSTER BY (order_id)
FLOW INSERT BY NAME
SELECT order_id FROM STREAM(main.sales.orders_raw);
-- Refresh the table on demand
REFRESH STREAMING TABLE main.sales.orders_bronze;