CREATE ICEBERG TABLE AS SELECT

Reference and syntax for the CREATE ICEBERG TABLE AS SELECT SQL command.

Firebolt’s Apache Iceberg write capabilities are currently in public preview. We are gathering feedback and expanding support for this feature.

Creates an Apache Iceberg table in object storage, and exports data into it based on the SELECT query. The table column names and types are automatically inferred based on the output columns of the SELECT.

This command exports data from Firebolt into a new Iceberg table. To register an existing external Iceberg table so you can query it like a regular table, use CREATE ICEBERG TABLE instead.

Syntax

CREATE ICEBERG TABLE <table_name>
[PARTITION BY (<partition_expression> [, ...])]
AS <select_query>
WITH LOCATION = <location_name> [<setting_name> = <setting_value> ...]

Parameters

Parameter	Description
<table_name>	A name for the exported Iceberg table.
<select_query>	Any valid select query.
<location_name>	The name of an Iceberg LOCATION object with CATALOG = FILE_BASED.
<setting_name>	The name of a system setting.
<setting_value>	The value to assign to system setting <setting_name>.

PARTITION BY

The PARTITION BY clause defines how the Iceberg table is partitioned. You can partition by column names or by Iceberg partition transform functions.

PARTITION BY (<column_name> | <partition_transform_expression>[, ...])

The following Iceberg partition transform functions can be used in PARTITION BY expressions:

• ICEBERG_YEAR
• ICEBERG_MONTH
• ICEBERG_DAY
• ICEBERG_HOUR
• ICEBERG_BUCKET
• ICEBERG_TRUNCATE

Examples

The following example writes the results of a SELECT query into a new Iceberg table at s3://my-bucket/path/to/iceberg/output.

CREATE LOCATION my_location
WITH 
  SOURCE = ICEBERG
  CATALOG = FILE_BASED
  CATALOG_OPTIONS = (
    URL = 's3://my-bucket/path/to/iceberg/output'
  )
  CREDENTIALS = ( ... );

CREATE ICEBERG TABLE my_iceberg_table
  AS SELECT * FROM my_firebolt_table
    WHERE timestamp_col >= TIMESTAMP '2025-10-01'
WITH LOCATION = my_location;

The following example creates an Iceberg table partitioned by the column region:

CREATE ICEBERG TABLE my_partitioned_table
  PARTITION BY region
  AS SELECT * FROM my_firebolt_table
WITH LOCATION = my_location;

The following example uses a partition transform function to partition by month:

CREATE ICEBERG TABLE my_monthly_table
  PARTITION BY iceberg_month(timestamp_col)
  AS SELECT * FROM my_firebolt_table
WITH LOCATION = my_location;

You can combine column names and partition transform functions:

CREATE ICEBERG TABLE my_multi_partitioned_table
  PARTITION BY region, iceberg_year(timestamp_col), iceberg_bucket(user_id, 16)
  AS SELECT * FROM my_firebolt_table
WITH LOCATION = my_location;

Remarks

You can use the iceberg_insert_sharding setting to control the trade-off between parallelism and the number of output files per partition. By default, Firebolt tries to group files of the same partition on the same nodes, resulting in less load per node and fewer output files, which is usually preferred for query performance.

Limitations:

• Catalogs: only supports a FILE_BASED catalog in AWS S3 object storage. I.e. the data and metadata files will be written to the specified location in S3.

• Data types: supports the Iceberg equivalents of Firebolt’s data types, except GEOGRAPHY.

• Metadata: only writes limited metadata that is optional per the Iceberg spec. Does not write column statistics, etc.

• DML operations on exported Iceberg tables are not supported. To query the exported data, use READ_ICEBERG(...) or register it with CREATE ICEBERG TABLE.