iceberg_tables

Creates, updates, deletes, gets or lists an iceberg_tables resource.

Overview

Name	iceberg_tables
Type	Resource
Id	snowflake.tables.iceberg_tables

Fields

The following fields are returned by SELECT queries:

get

successful

Name	Datatype	Description
name	string	Name of the iceberg table (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
catalog_table_name	string	Name of the table as recognized by the catalog.
database_name	string	Database in which the iceberg table is stored (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
schema_name	string	Schema in which the iceberg table is stored (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
auto_refresh	boolean	Specifies whether to automatically refresh the table metadata
base_location	string	The path to a directory where Snowflake can write data and metadata files for the table.
can_write_metadata	string	Signifies whether Snowflake can write metadata to the location specified by the file_path.
catalog	string	Name of the catalog integration to use for iceberg tables
catalog_namespace	string	Catalog namespace for the table. The namespace defined when the table was created. Otherwise, the default namespace associated with the catalog integration used by the table. If you’re syncing the table to Snowflake Open Catalog, the default is null.
catalog_sync	string	Name of the catalog integration to sync this table
change_tracking	boolean	True if change tracking is enabled, allowing streams and CHANGES to be used on the entity.
cluster_by	array	Specifies one or more columns or column expressions in the table as the clustering key.
columns	array
comment	string	user comment associated to an object in the dictionary
constraints	array
created_on	string (date-time)	Date and time when the iceberg table was created.
data_retention_time_in_days	integer	number of days to retain the old version of deleted/updated data
external_volume	string	Name of an external volume that will be used for persisted Iceberg metadata and data files.
iceberg_table_type	string	Type of Iceberg table. UNMANAGED if the table is not managed by Snowflake. NOT ICEBERG otherwise.
max_data_extension_time_in_days	integer	Maximum number of days to extend data retention beyond the retention period to prevent a stream becoming stale.
metadata_file_path	string	Specifies the relative path of the Iceberg metadata file to use for column definitions.
owner	string	Role that owns the iceberg table (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
owner_role_type	string	The type of role that owns the iceberg table (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
replace_invalid_characters	boolean	Specifies whether to replace invalid characters in the column names
storage_serialization_policy	string	Storage serialization policy used for managed Iceberg table. This include encodings and compressions (COMPATIBLE, OPTIMIZED)

list

A Snowflake iceberg table

Name	Datatype	Description
name	string	Name of the iceberg table (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
catalog_table_name	string	Name of the table as recognized by the catalog.
database_name	string	Database in which the iceberg table is stored (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
schema_name	string	Schema in which the iceberg table is stored (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
auto_refresh	boolean	Specifies whether to automatically refresh the table metadata
base_location	string	The path to a directory where Snowflake can write data and metadata files for the table.
can_write_metadata	string	Signifies whether Snowflake can write metadata to the location specified by the file_path.
catalog	string	Name of the catalog integration to use for iceberg tables
catalog_namespace	string	Catalog namespace for the table. The namespace defined when the table was created. Otherwise, the default namespace associated with the catalog integration used by the table. If you’re syncing the table to Snowflake Open Catalog, the default is null.
catalog_sync	string	Name of the catalog integration to sync this table
change_tracking	boolean	True if change tracking is enabled, allowing streams and CHANGES to be used on the entity.
cluster_by	array	Specifies one or more columns or column expressions in the table as the clustering key.
columns	array
comment	string	user comment associated to an object in the dictionary
constraints	array
created_on	string (date-time)	Date and time when the iceberg table was created.
data_retention_time_in_days	integer	number of days to retain the old version of deleted/updated data
external_volume	string	Name of an external volume that will be used for persisted Iceberg metadata and data files.
iceberg_table_type	string	Type of Iceberg table. UNMANAGED if the table is not managed by Snowflake. NOT ICEBERG otherwise.
max_data_extension_time_in_days	integer	Maximum number of days to extend data retention beyond the retention period to prevent a stream becoming stale.
metadata_file_path	string	Specifies the relative path of the Iceberg metadata file to use for column definitions.
owner	string	Role that owns the iceberg table (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
owner_role_type	string	The type of role that owns the iceberg table (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$)
replace_invalid_characters	boolean	Specifies whether to replace invalid characters in the column names
storage_serialization_policy	string	Storage serialization policy used for managed Iceberg table. This include encodings and compressions (COMPATIBLE, OPTIMIZED)

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
get	select	database_name, schema_name, name, endpoint		Describe an iceberg table
list	select	database_name, schema_name, endpoint	like, startsWith, showLimit, fromName, deep	Lists the Apache Iceberg™ tables for which you have access privileges.
create	insert	database_name, schema_name, endpoint, name	createMode, copyGrants	Create a snowflake managed iceberg table (clone and undrop are separate subresources)
delete	delete	database_name, schema_name, name, endpoint	ifExists, type	Drop an iceberg table
as_select	exec	database_name, schema_name, query, endpoint, name	createMode, copyGrants	Create a snowflake managed iceberg table as select
from_aws_glue_catalog	exec	database_name, schema_name, endpoint, name, catalog_table_name	createMode	Create an unmanaged iceberg table from AWS Glue catalog
from_delta	exec	database_name, schema_name, endpoint, name, base_location	createMode	Create an unmanaged iceberg table from Delta
from_iceberg_files	exec	database_name, schema_name, endpoint, name, metadata_file_path	createMode	Create an unmanaged iceberg table from Iceberg files
from_iceberg_rest	exec	database_name, schema_name, endpoint, name, catalog_table_name	createMode	Create an unmanaged iceberg table from Iceberg REST
resume_recluster	exec	database_name, schema_name, name, endpoint	ifExists	Resume recluster of an iceberg table (iceberg tables managed by an external catalog do not allow clustering)
suspend_recluster	exec	database_name, schema_name, name, endpoint	ifExists	Suspend recluster of an iceberg table (iceberg tables managed by an external catalog do not allow clustering)
refresh	exec	database_name, schema_name, name, endpoint	ifExists	Refreshes the metadata for an Apache Iceberg table that uses an external Iceberg catalog
convert_to_managed	exec	database_name, schema_name, name, endpoint	ifExists	Convert unmanaged iceberg table to managed iceberg table
undrop	exec	database_name, schema_name, name, endpoint		Undrop an iceberg table
clone	exec	database_name, schema_name, name, endpoint, name	createMode, copyGrants, targetDatabase, targetSchema	Clone a snowflake managed iceberg table
create_like	exec	database_name, schema_name, name, endpoint, name	createMode, copyGrants, targetDatabase, targetSchema	Creates a new table with the same column definitions as an existing table, but without copying data from the existing table.

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
database_name	string	Identifier (i.e. name) for the database to which the resource belongs. You can use the /api/v2/databases GET request to get a list of available databases.
endpoint	string	Organization and account identifier (orgname-accountname) (default: orgname-accountname)
name	string	Identifier (i.e. name) for the resource.
query	string	The SQL select query to run to set up the table values (and possibly columns).
schema_name	string	Identifier (i.e. name) for the schema to which the resource belongs. You can use the /api/v2/databases/{database}/schemas GET request to get a list of available schemas for the specified database.
copyGrants	boolean	Parameter to enable copy grants when creating the object.
createMode	string	Parameter allowing support for different modes of resource creation. Possible values include: - errorIfExists: Throws an error if you try to create a resource that already exists. - orReplace: Automatically replaces the existing resource with the current one. - ifNotExists: Creates a new resource when an alter is requested for a non-existent resource.
deep	boolean	Optionally includes dependency information of the table.
fromName	string	Parameter to enable fetching rows only following the first row whose object name matches the specified string. Case-sensitive and does not have to be the full name.
ifExists	boolean	Parameter that specifies how to handle the request for a resource that does not exist: - true: The endpoint does not throw an error if the resource does not exist. It returns a 200 success response, but does not take any action on the resource. - false: The endpoint throws an error if the resource doesn't exist.
like	string	Parameter to filter the command output by resource name. Uses case-insensitive pattern matching, with support for SQL wildcard characters.
showLimit	integer	Parameter to limit the maximum number of rows returned by a command.
startsWith	string	Parameter to filter the command output based on the string of characters that appear at the beginning of the object name. Uses case-sensitive pattern matching.
targetDatabase	string	Database of the newly created table. Defaults to the source table's database.
targetSchema	string	Schema of the newly created table. Defaults to the source table's schema.
type	string	Specifies whether the table can be dropped if foreign keys exist that reference the table.

SELECT examples

get

Describe an iceberg table

SELECT
name,
catalog_table_name,
database_name,
schema_name,
auto_refresh,
base_location,
can_write_metadata,
catalog,
catalog_namespace,
catalog_sync,
change_tracking,
cluster_by,
columns,
comment,
constraints,
created_on,
data_retention_time_in_days,
external_volume,
iceberg_table_type,
max_data_extension_time_in_days,
metadata_file_path,
owner,
owner_role_type,
replace_invalid_characters,
storage_serialization_policy
FROM snowflake.tables.iceberg_tables
WHERE database_name = '{{ database_name }}' -- required
AND schema_name = '{{ schema_name }}' -- required
AND name = '{{ name }}' -- required
AND endpoint = '{{ endpoint }}' -- required
;

list

Lists the Apache Iceberg™ tables for which you have access privileges.

SELECT
name,
catalog_table_name,
database_name,
schema_name,
auto_refresh,
base_location,
can_write_metadata,
catalog,
catalog_namespace,
catalog_sync,
change_tracking,
cluster_by,
columns,
comment,
constraints,
created_on,
data_retention_time_in_days,
external_volume,
iceberg_table_type,
max_data_extension_time_in_days,
metadata_file_path,
owner,
owner_role_type,
replace_invalid_characters,
storage_serialization_policy
FROM snowflake.tables.iceberg_tables
WHERE database_name = '{{ database_name }}' -- required
AND schema_name = '{{ schema_name }}' -- required
AND endpoint = '{{ endpoint }}' -- required
AND "like" = '{{ like }}'
AND startsWith = '{{ startsWith }}'
AND showLimit = '{{ showLimit }}'
AND fromName = '{{ fromName }}'
AND deep = '{{ deep }}'
;

INSERT examples

create

Create a snowflake managed iceberg table (clone and undrop are separate subresources)

INSERT INTO snowflake.tables.iceberg_tables (
name,
comment,
change_tracking,
max_data_extension_time_in_days,
external_volume,
data_retention_time_in_days,
catalog_sync,
catalog,
storage_serialization_policy,
catalog_table_name,
catalog_namespace,
cluster_by,
columns,
base_location,
replace_invalid_characters,
metadata_file_path,
constraints,
database_name,
schema_name,
endpoint,
createMode,
copyGrants
)
SELECT 
'{{ name }}' /* required */,
'{{ comment }}',
{{ change_tracking }},
{{ max_data_extension_time_in_days }},
'{{ external_volume }}',
{{ data_retention_time_in_days }},
'{{ catalog_sync }}',
'{{ catalog }}',
'{{ storage_serialization_policy }}',
'{{ catalog_table_name }}',
'{{ catalog_namespace }}',
'{{ cluster_by }}',
'{{ columns }}',
'{{ base_location }}',
{{ replace_invalid_characters }},
'{{ metadata_file_path }}',
'{{ constraints }}',
'{{ database_name }}',
'{{ schema_name }}',
'{{ endpoint }}',
'{{ createMode }}',
'{{ copyGrants }}'
RETURNING
status
;

Manifest

# Description fields are for documentation purposes
- name: iceberg_tables
props:
  - name: database_name
    value: "{{ database_name }}"
    description: Required parameter for the iceberg_tables resource.
  - name: schema_name
    value: "{{ schema_name }}"
    description: Required parameter for the iceberg_tables resource.
  - name: endpoint
    value: "{{ endpoint }}"
    description: Required parameter for the iceberg_tables resource.
  - name: name
    value: "{{ name }}"
    description: |
      Name of the iceberg table
  - name: comment
    value: "{{ comment }}"
    description: |
      user comment associated to an object in the dictionary
  - name: change_tracking
    value: {{ change_tracking }}
    description: |
      True if change tracking is enabled, allowing streams and CHANGES to be used on the entity.
  - name: max_data_extension_time_in_days
    value: {{ max_data_extension_time_in_days }}
    description: |
      Maximum number of days to extend data retention beyond the retention period to prevent a stream becoming stale.
  - name: external_volume
    value: "{{ external_volume }}"
    description: |
      Name of an external volume that will be used for persisted Iceberg metadata and data files.
  - name: data_retention_time_in_days
    value: {{ data_retention_time_in_days }}
    description: |
      number of days to retain the old version of deleted/updated data
  - name: catalog_sync
    value: "{{ catalog_sync }}"
    description: |
      Name of the catalog integration to sync this table
  - name: catalog
    value: "{{ catalog }}"
    description: |
      Name of the catalog integration to use for iceberg tables
  - name: storage_serialization_policy
    value: "{{ storage_serialization_policy }}"
    description: |
      Storage serialization policy used for managed Iceberg table. This include encodings and compressions
    valid_values: ['COMPATIBLE', 'OPTIMIZED']
  - name: catalog_table_name
    value: "{{ catalog_table_name }}"
    description: |
      Name of the table as recognized by the catalog.
  - name: catalog_namespace
    value: "{{ catalog_namespace }}"
    description: |
      Catalog namespace for the table. The namespace defined when the table was created. Otherwise, the default namespace associated with the catalog integration used by the table. If you’re syncing the table to Snowflake Open Catalog, the default is null.
  - name: cluster_by
    value:
      - "{{ cluster_by }}"
    description: |
      Specifies one or more columns or column expressions in the table as the clustering key.
  - name: columns
    value:
      - name: "{{ name }}"
        datatype: "{{ datatype }}"
        comment: "{{ comment }}"
        nullable: {{ nullable }}
        default_value: "{{ default_value }}"
  - name: base_location
    value: "{{ base_location }}"
    description: |
      The path to a directory where Snowflake can write data and metadata files for the table.
  - name: replace_invalid_characters
    value: {{ replace_invalid_characters }}
    description: |
      Specifies whether to replace invalid characters in the column names
  - name: metadata_file_path
    value: "{{ metadata_file_path }}"
    description: |
      Specifies the relative path of the Iceberg metadata file to use for column definitions.
  - name: constraints
    value:
      - name: "{{ name }}"
        column_names: "{{ column_names }}"
        constraint_type: "{{ constraint_type }}"
  - name: createMode
    value: "{{ createMode }}"
    description: Parameter allowing support for different modes of resource creation. Possible values include: - `errorIfExists`: Throws an error if you try to create a resource that already exists. - `orReplace`: Automatically replaces the existing resource with the current one. - `ifNotExists`: Creates a new resource when an alter is requested for a non-existent resource.
    description: Parameter allowing support for different modes of resource creation. Possible values include: - `errorIfExists`: Throws an error if you try to create a resource that already exists. - `orReplace`: Automatically replaces the existing resource with the current one. - `ifNotExists`: Creates a new resource when an alter is requested for a non-existent resource.
  - name: copyGrants
    value: {{ copyGrants }}
    description: Parameter to enable copy grants when creating the object.
    description: Parameter to enable copy grants when creating the object.

DELETE examples

delete

Drop an iceberg table

DELETE FROM snowflake.tables.iceberg_tables
WHERE database_name = '{{ database_name }}' --required
AND schema_name = '{{ schema_name }}' --required
AND name = '{{ name }}' --required
AND endpoint = '{{ endpoint }}' --required
AND ifExists = '{{ ifExists }}'
AND type = '{{ type }}'
;

Lifecycle Methods

as_select

Create a snowflake managed iceberg table as select

EXEC snowflake.tables.iceberg_tables.as_select 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@query='{{ query }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@createMode='{{ createMode }}', 
@copyGrants={{ copyGrants }} 
@@json=
'{
"name": "{{ name }}", 
"columns": "{{ columns }}", 
"external_volume": "{{ external_volume }}", 
"cluster_by": "{{ cluster_by }}", 
"base_location": "{{ base_location }}", 
"comment": "{{ comment }}"
}'
;

from_aws_glue_catalog

Create an unmanaged iceberg table from AWS Glue catalog

EXEC snowflake.tables.iceberg_tables.from_aws_glue_catalog 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@createMode='{{ createMode }}' 
@@json=
'{
"name": "{{ name }}", 
"external_volume": "{{ external_volume }}", 
"catalog_table_name": "{{ catalog_table_name }}", 
"catalog_namespace": "{{ catalog_namespace }}", 
"replace_invalid_characters": {{ replace_invalid_characters }}, 
"auto_refresh": {{ auto_refresh }}, 
"catalog": "{{ catalog }}", 
"comment": "{{ comment }}"
}'
;

from_delta

Create an unmanaged iceberg table from Delta

EXEC snowflake.tables.iceberg_tables.from_delta 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@createMode='{{ createMode }}' 
@@json=
'{
"name": "{{ name }}", 
"external_volume": "{{ external_volume }}", 
"replace_invalid_characters": {{ replace_invalid_characters }}, 
"base_location": "{{ base_location }}", 
"catalog": "{{ catalog }}", 
"comment": "{{ comment }}"
}'
;

from_iceberg_files

Create an unmanaged iceberg table from Iceberg files

EXEC snowflake.tables.iceberg_tables.from_iceberg_files 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@createMode='{{ createMode }}' 
@@json=
'{
"name": "{{ name }}", 
"external_volume": "{{ external_volume }}", 
"replace_invalid_characters": {{ replace_invalid_characters }}, 
"metadata_file_path": "{{ metadata_file_path }}", 
"catalog": "{{ catalog }}", 
"comment": "{{ comment }}"
}'
;

from_iceberg_rest

Create an unmanaged iceberg table from Iceberg REST

EXEC snowflake.tables.iceberg_tables.from_iceberg_rest 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@createMode='{{ createMode }}' 
@@json=
'{
"name": "{{ name }}", 
"external_volume": "{{ external_volume }}", 
"catalog_table_name": "{{ catalog_table_name }}", 
"catalog_namespace": "{{ catalog_namespace }}", 
"replace_invalid_characters": {{ replace_invalid_characters }}, 
"auto_refresh": {{ auto_refresh }}, 
"catalog": "{{ catalog }}", 
"comment": "{{ comment }}"
}'
;

resume_recluster

Resume recluster of an iceberg table (iceberg tables managed by an external catalog do not allow clustering)

EXEC snowflake.tables.iceberg_tables.resume_recluster 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}
;

suspend_recluster

Suspend recluster of an iceberg table (iceberg tables managed by an external catalog do not allow clustering)

EXEC snowflake.tables.iceberg_tables.suspend_recluster 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}
;

refresh

Refreshes the metadata for an Apache Iceberg table that uses an external Iceberg catalog

EXEC snowflake.tables.iceberg_tables.refresh 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }} 
@@json=
'{
"metadata_file_relative_path": "{{ metadata_file_relative_path }}"
}'
;

convert_to_managed

Convert unmanaged iceberg table to managed iceberg table

EXEC snowflake.tables.iceberg_tables.convert_to_managed 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }} 
@@json=
'{
"base_location": "{{ base_location }}", 
"storage_serialization_policy": "{{ storage_serialization_policy }}"
}'
;

undrop

Undrop an iceberg table

EXEC snowflake.tables.iceberg_tables.undrop 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required
;

clone

Clone a snowflake managed iceberg table

EXEC snowflake.tables.iceberg_tables.clone 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@createMode='{{ createMode }}', 
@copyGrants={{ copyGrants }}, 
@targetDatabase='{{ targetDatabase }}', 
@targetSchema='{{ targetSchema }}' 
@@json=
'{
"name": "{{ name }}", 
"point_of_time": "{{ point_of_time }}"
}'
;

create_like

Creates a new table with the same column definitions as an existing table, but without copying data from the existing table.

EXEC snowflake.tables.iceberg_tables.create_like 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@createMode='{{ createMode }}', 
@copyGrants={{ copyGrants }}, 
@targetDatabase='{{ targetDatabase }}', 
@targetSchema='{{ targetSchema }}' 
@@json=
'{
"name": "{{ name }}", 
"cluster_by": "{{ cluster_by }}", 
"external_volume": "{{ external_volume }}", 
"base_location": "{{ base_location }}", 
"comment": "{{ comment }}"
}'
;