Snowflake Schema

dynamic_table

The dynamic block describes a dynamic table in a database schema.

dynamic_table "users" {
  schema = schema.public
  column "id" {
    comment = "User ID"
  }
  target_lag = DOWNSTREAM // or "<num> { seconds | minutes | hours | days }"
  refresh_mode = INCREMENTAL, or FULL
  as = "SELECT * FROM users"
  ...
}

dynamic_table attributes

Name	Required	Value
as	true	string
cluster_by	false	List of strings
comment	false	string
depends_on	false	List of object references
refresh_mode	false	Dynamic table refresh mode can be one of: string enum (INCREMENTAL, FULL, AUTO)
retention_time	false	int
schema	true	Object reference to schema
target_lag	true	Dynamic table target lag can be one of: string enum (DOWNSTREAM)
transient	false	bool

dynamic_table blocks

dynamic_table.column

dynamic_table.column attributes

Name	Required	Value
comment	false	string
type	false	Column type can be one of: Schema type Raw expression defined with sql("expr")

dynamic_table.column blocks

dynamic_table.column.annotation

dynamic_table.column.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

dynamic_table.column constraints

Constraint	Value
Required	false
Require Name (e.g., dynamic_table.column "name" )	true

dynamic_table constraints

Constraint	Value
Required	false
Require Name (e.g., dynamic_table "name" )	true
Allow Qualifier (e.g., dynamic_table "schema" "name" )	true

external_table

The external_table block describes an external table in a database schema.

external_table "events" {
  schema = schema.public
  column "event_id" {
	type = VARCHAR(255)
	as {
	  expr = "value:c1::varchar"
	}
  }
  location {
    stage = stage.mystage
	path = "data/events/"
  }
}

external_table attributes

Name	Required	Value
comment	false	string
depends_on	false	List of object references
file_format	false	File format options or reference can be one of: Object reference to file_format map
retention_time	false	int
schema	true	Object reference to schema

external_table blocks

external_table.column

external_table.column attributes

Name	Required	Value
as	false	string
comment	false	string
null	false	bool
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr")

external_table.column blocks

external_table.column.annotation

external_table.column.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

external_table.column constraints

Constraint	Value
Required	false
Require Name (e.g., external_table.column "name" )	true

external_table.foreign_key

external_table.foreign_key attributes

Name	Required	Value
columns	true	Foreign key columns can be one of: List of object reference to column List of object reference to external_table.column
comment	false	string
ref_columns	true	Foreign key reference columns can be one of: List of object reference to column List of object reference to external_table.column

external_table.foreign_key constraints

Constraint	Value
Required	false
Require Name (e.g., external_table.foreign_key "name" )	true

external_table.location

external_table.location attributes

Name	Required	Value
path	false	string
stage	true	Object reference to stage

external_table.location constraints

Constraint	Value
Required	true
Require Name	false

external_table.primary_key

external_table.primary_key attributes

Name	Required	Value
columns	true	Index columns can be one of: List of object reference to column List of object reference to external_table.column
comment	false	string

external_table.unique

external_table.unique attributes

Name	Required	Value
columns	true	Index columns can be one of: List of object reference to column List of object reference to external_table.column
comment	false	string

external_table.unique constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

external_table constraints

Constraint	Value
Required	false
Require Name (e.g., external_table "name" )	true
Allow Qualifier (e.g., external_table "schema" "name" )	true

external_volume

The external_volume block describes a Snowflake external volume for Iceberg tables.

external_volume "my_vol" {
  allow_writes = true
  comment      = "My external volume"
  storage_location "S3" "my-s3-location" {
    storage_base_url     = "s3://my-bucket/path/"
    storage_aws_role_arn = "arn:aws:iam::123456789012:role/myrole"
    encryption {
      type       = "AWS_SSE_KMS"
      kms_key_id = "1234abcd-12ab-34cd-56ef-1234567890ab"
    }
  }
}

external_volume attributes

Name	Required	Value
allow_writes	false	bool
comment	false	string

external_volume blocks

external_volume.storage_location.AZURE

external_volume.storage_location.AZURE attributes

Name	Required	Value
azure_tenant_id	true	string
storage_base_url	true	string

external_volume.storage_location.AZURE constraints

Constraint	Value
Required	false
Repeatable	true

external_volume.storage_location.GCS

external_volume.storage_location.GCS attributes

Name	Required	Value
storage_base_url	true	string

external_volume.storage_location.GCS blocks

external_volume.storage_location.encryption

external_volume.storage_location.encryption attributes

Name	Required	Value
kms_key_id	false	string
type	false	string

external_volume.storage_location.GCS constraints

Constraint	Value
Required	false
Repeatable	true

external_volume.storage_location.S3

external_volume.storage_location.S3 attributes

Name	Required	Value
storage_aws_access_point_arn	false	string
storage_aws_external_id	false	string
storage_aws_role_arn	true	string
storage_base_url	true	string

external_volume.storage_location.S3 blocks

external_volume.storage_location.encryption

external_volume.storage_location.encryption attributes

Name	Required	Value
kms_key_id	false	string
type	false	string

external_volume.storage_location.S3 constraints

Constraint	Value
Required	false
Repeatable	true

external_volume.storage_location.S3COMPAT

external_volume.storage_location.S3COMPAT attributes

Name	Required	Value
storage_base_url	true	string
storage_endpoint	true	string

external_volume.storage_location.S3COMPAT constraints

Constraint	Value
Required	false
Repeatable	true

external_volume.storage_location.S3GOV

external_volume.storage_location.S3GOV attributes

Name	Required	Value
storage_aws_access_point_arn	false	string
storage_aws_external_id	false	string
storage_aws_role_arn	true	string
storage_base_url	true	string

external_volume.storage_location.S3GOV blocks

external_volume.storage_location.encryption

external_volume.storage_location.encryption attributes

Name	Required	Value
kms_key_id	false	string
type	false	string

external_volume.storage_location.S3GOV constraints

Constraint	Value
Required	false
Repeatable	true

external_volume constraints

Constraint	Value
Required	false
Require Name (e.g., external_volume "name" )	true

file_format

The file_format block describes a named Snowflake file format in a database schema.

file_format "my_csv_format" {
  schema  = schema.public
  comment = "My CSV file format"
  type    = CSV
  options = {
    BINARY_AS_TEXT  = false
    COMPRESSION     = GZIP
    FIELD_DELIMITER = "|"
    SKIP_HEADER     = 1
  }
}

file_format attributes

Name	Required	Value
comment	false	string
options	false	map
schema	true	Object reference to schema
type	true	enum (AVRO, CSV, JSON, ORC, PARQUET, XML)

file_format constraints

Constraint	Value
Required	false
Require Name (e.g., file_format "name" )	true
Allow Qualifier (e.g., file_format "schema" "name" )	true

function

The function block describes a function in a database schema.

function "positive" {
  schema = schema.public
  lang   = SQL
  arg "v" {
    type = integer
  }
  ...
}

function attributes

Name	Required	Value
as	false	string
comment	false	string
depends_on	false	List of object references
execute_as	false	enum (OWNER, CALLER)
external_access_integrations	false	List of strings
handler	false	string
lang	false	enum (SQL, JAVASCRIPT, PYTHON, JAVA, SCALA)
null_call	false	enum (CALLED, RETURNS_NULL)
packages	false	List of strings
return	false	Return type can be one of: Schema type Raw expression defined with sql("expr")
runtime_version	false	string
schema	true	Object reference to schema
secure	false	bool

function blocks

function.annotation

function.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

function.arg

function.arg attributes

Name	Required	Value
default	false	Argument default value can be one of: number string bool Raw expression defined with sql("expr")
type	true	Argument type can be one of: Schema type Raw expression defined with sql("expr")

function.arg constraints

Constraint	Value
Required	false
Require Name (e.g., function.arg "name" )	true

function.imports

function.imports attributes

Name	Required	Value
file	true	string
stage	true	Object reference to stage

function.imports constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

function.return_table

function.return_table blocks

function.return_table.column

function.return_table.column attributes

Name	Required	Value
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr")

function.return_table.column blocks

function.return_table.column.annotation

function.return_table.column.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

function.return_table.column constraints

Constraint	Value
Required	false
Require Name (e.g., function.return_table.column "name" )	true

function constraints

Constraint	Value
Required	false
Require Name (e.g., function "name" )	true
Allow Qualifier (e.g., function "schema" "name" )	true
Repeatable	true
Mutually exclusive sets	[return, return_table]

hybrid_table

The hybrid block describes a hybrid table in a database schema.

hybrid_table "users" {
  schema = schema.public
  column "id" {
    comment = "User ID"
    type = VARCHAR(255)
  }
  primary_key {
    columns = ["id"]
  }
  ...
}

hybrid_table attributes

Name	Required	Value
comment	false	string
retention_time	false	int
schema	true	Object reference to schema

hybrid_table blocks

hybrid_table.column

hybrid_table.column attributes

Name	Required	Value
collate	false	string
comment	false	string
default	false	Column default value can be one of: bool string number Raw expression defined with sql("expr")
null	false	bool
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr")

hybrid_table.column blocks

hybrid_table.column.annotation

hybrid_table.column.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

hybrid_table.column.identity

hybrid_table.column.identity attributes

Name	Required	Value
increment	false	int
order	false	enum (ORDER, NOORDER)
start	false	int

hybrid_table.column constraints

Constraint	Value
Required	false
Require Name (e.g., hybrid_table.column "name" )	true

hybrid_table.foreign_key

hybrid_table.foreign_key attributes

Name	Required	Value
columns	true	Foreign key columns can be one of: List of object reference to column List of object reference to hybrid_table.column
comment	false	string
ref_columns	true	Foreign key reference columns can be one of: List of object reference to column List of object reference to hybrid_table.column

hybrid_table.foreign_key constraints

Constraint	Value
Required	false
Require Name (e.g., hybrid_table.foreign_key "name" )	true

hybrid_table.primary_key

hybrid_table.primary_key attributes

Name	Required	Value
columns	true	Index columns can be one of: List of object reference to column List of object reference to hybrid_table.column
comment	false	string

hybrid_table.unique

hybrid_table.unique attributes

Name	Required	Value
columns	true	Index columns can be one of: List of object reference to column List of object reference to hybrid_table.column
comment	false	string

hybrid_table.unique constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

hybrid_table constraints

Constraint	Value
Required	false
Require Name (e.g., hybrid_table "name" )	true
Allow Qualifier (e.g., hybrid_table "schema" "name" )	true

iceberg_table

The iceberg_table block describes an Iceberg table in a database schema.

iceberg_table "events" {
  schema          = schema.public
  external_volume = "my_ext_vol"
  catalog         = "SNOWFLAKE"
  base_location   = "iceberg/tables/"
  column "id" {
    type = NUMBER(38,0)
  }
}

iceberg_table attributes

Name	Required	Value
base_location	false	string
catalog	true	string
catalog_sync	false	string
cluster_by	false	List of strings
comment	false	string
enable_data_compaction	false	bool
enable_iceberg_merge_on_read	false	bool
error_logging	false	bool
external_volume	true	External volume name or reference can be one of: Object reference to external_volume string
log_event_level	false	Log event level can be one of: enum (OFF, ERROR, WARN, DEBUG) string
partition_by	false	List of strings
replace_invalid_characters	false	bool
retention_time	false	int
schema	true	Object reference to schema
target_file_size	false	string
transient	false	bool

iceberg_table blocks

iceberg_table.column

iceberg_table.column attributes

Name	Required	Value
comment	false	string
default	false	Column default value can be one of: bool string number Raw expression defined with sql("expr")
null	false	bool
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr")

iceberg_table.column blocks

iceberg_table.column.annotation

iceberg_table.column.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

iceberg_table.column constraints

Constraint	Value
Required	false
Require Name (e.g., iceberg_table.column "name" )	true

iceberg_table.foreign_key

iceberg_table.foreign_key attributes

Name	Required	Value
columns	true	Foreign key columns can be one of: List of object reference to column List of object reference to iceberg_table.column
comment	false	string
ref_columns	true	Foreign key reference columns can be one of: List of object reference to column List of object reference to iceberg_table.column

iceberg_table.foreign_key constraints

Constraint	Value
Required	false
Require Name (e.g., iceberg_table.foreign_key "name" )	true

iceberg_table.primary_key

iceberg_table.primary_key attributes

Name	Required	Value
columns	true	Index columns can be one of: List of object reference to column List of object reference to iceberg_table.column
comment	false	string

iceberg_table.unique

iceberg_table.unique attributes

Name	Required	Value
columns	true	Index columns can be one of: List of object reference to column List of object reference to iceberg_table.column
comment	false	string

iceberg_table.unique constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

iceberg_table constraints

Constraint	Value
Required	false
Require Name (e.g., iceberg_table "name" )	true
Allow Qualifier (e.g., iceberg_table "schema" "name" )	true
Mutually exclusive sets	[partition_by, cluster_by]

pipe

The pipe block describes a Snowflake pipe for Snowpipe data loading.

pipe "mypipe" {
  schema = schema.public
  as = "COPY INTO mytable FROM @mystage FILE_FORMAT = (TYPE = 'JSON')"
  auto_ingest = true
  comment = "My data loading pipe"
}

pipe attributes

Name	Required	Value
as	true	string
auto_ingest	false	bool
aws_sns_topic	false	string
comment	false	string
error_integration	false	string
integration	false	string
schema	true	Object reference to schema

pipe constraints

Constraint	Value
Required	false
Require Name (e.g., pipe "name" )	true
Allow Qualifier (e.g., pipe "schema" "name" )	true

procedure

The procedure block describes a procedure in a database schema.

procedure "proc" {
  schema = schema.public
  lang   = SQL
  arg "a" {
    type = integer
  }
  ...
}

procedure attributes

Name	Required	Value
as	false	string
comment	false	string
depends_on	false	List of object references
execute_as	false	enum (OWNER, CALLER, RESTRICTED_CALLER)
external_access_integrations	false	List of strings
handler	false	string
lang	false	enum (SQL, JAVASCRIPT, PYTHON, JAVA, SCALA)
null_call	false	enum (CALLED, RETURNS_NULL)
packages	false	List of strings
return	false	Return type can be one of: Schema type Raw expression defined with sql("expr")
runtime_version	false	string
schema	true	Object reference to schema
secure	false	bool

procedure blocks

procedure.annotation

procedure.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

procedure.arg

procedure.arg attributes

Name	Required	Value
default	false	Argument default value can be one of: number string bool Raw expression defined with sql("expr")
mode	false	enum (IN, OUT)
type	true	Argument type can be one of: Schema type Raw expression defined with sql("expr")

procedure.arg constraints

Constraint	Value
Required	false
Require Name (e.g., procedure.arg "name" )	true

procedure.imports

procedure.imports attributes

Name	Required	Value
file	true	string
stage	true	Object reference to stage

procedure.imports constraints

Constraint	Value
Required	false
Require Name	false
Repeatable	true

procedure.return

procedure.return attributes

Name	Required	Value
null	false	bool
type	true	Return type can be one of: Schema type Raw expression defined with sql("expr")

procedure.return_table

procedure.return_table blocks

procedure.return_table.column

procedure.return_table.column attributes

Name	Required	Value
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr")

procedure.return_table.column blocks

procedure.return_table.column.annotation

procedure.return_table.column.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

procedure.return_table.column constraints

Constraint	Value
Required	false
Require Name (e.g., procedure.return_table.column "name" )	true

procedure constraints

Constraint	Value
Required	false
Require Name (e.g., procedure "name" )	true
Allow Qualifier (e.g., procedure "schema" "name" )	true
Repeatable	true
Mutually exclusive sets	[return, return_table]

schema

The schema block describes a database schema.

schema "public" {
  ...
}

schema attributes

Name	Required	Value
comment	false	string
managed_access	false	bool
name	false	string
retention_time	false	int
transient	false	bool

schema blocks

schema.annotation

schema.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

schema constraints

Constraint	Value
Required	false
Require Name (e.g., schema "name" )	true

stage

The stage block describes a Snowflake stage in a database schema.

stage "mystage" {
  schema = schema.public
  url = "s3://mybucket/data/"
  storage_integration = "my_integration"
  directory_enabled = true
  comment = "This is my stage"
}

stage attributes

Name	Required	Value
comment	false	string
directory_enabled	false	bool
file_format	false	File format options or reference can be one of: Object reference to file_format map
schema	true	Object reference to schema
storage_integration	false	string
url	false	string

stage constraints

Constraint	Value
Required	false
Require Name (e.g., stage "name" )	true
Allow Qualifier (e.g., stage "schema" "name" )	true

table

The table block describes a table in a database schema.

table "users" {
  schema = schema.public
  column "id" {
    type = int
  }
  ...
}

table attributes

Name	Required	Value
comment	false	string
retention_time	false	int
schema	true	Object reference to schema
transient	false	bool

table blocks

table.annotation

table.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

table.column

table.column attributes

Name	Required	Value
collate	false	string
comment	false	string
default	false	Column default value can be one of: bool string number Raw expression defined with sql("expr")
null	false	bool
type	true	Column type can be one of: Schema type Raw expression defined with sql("expr")

table.column blocks

table.column.annotation

table.column.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

table.column.identity

table.column.identity attributes

Name	Required	Value
increment	false	int
order	false	enum (ORDER, NOORDER)
start	false	int

table.column constraints

Constraint	Value
Required	false
Require Name (e.g., table.column "name" )	true

table.foreign_key

table.foreign_key attributes

Name	Required	Value
columns	true	Foreign key columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string
ref_columns	true	Foreign key reference columns can be one of: List of object reference to column List of object reference to table.column

table.foreign_key constraints

Constraint	Value
Required	false
Require Name (e.g., table.foreign_key "name" )	true

table.primary_key

table.primary_key attributes

Name	Required	Value
columns	true	Index columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string

table.unique

table.unique attributes

Name	Required	Value
columns	true	Index columns can be one of: List of object reference to column List of object reference to table.column
comment	false	string

table.unique constraints

Constraint	Value
Required	false
Require Name	false
Mutually exclusive sets	[columns, on]
One of required sets	[columns, on]

table constraints

Constraint	Value
Required	false
Require Name (e.g., table "name" )	true
Allow Qualifier (e.g., table "schema" "name" )	true

task

The task block describes a Snowflake task in a database schema.

task "my_task" {
  schema     = schema.public
  schedule   = "5 MINUTE"
  warehouse  = "my_wh"
  as         = "INSERT INTO t1 SELECT * FROM t2"
  comment    = "This is my task"
}

task attributes

Name	Required	Value
after	false	List of object references
as	true	string
comment	false	string
condition	false	string
config	false	string
depends_on	false	List of object references
error_integration	false	string
execute_as	false	string
log_level	false	string
schedule	false	string
schema	true	Object reference to schema
serverless_task_max_statement_size	false	string
serverless_task_min_statement_size	false	string
success_integration	false	string
suspend_task_after_num_failures	false	int
target_completion_interval	false	string
task_auto_retry_attempts	false	int
user_task_minimum_trigger_interval_in_seconds	false	int
user_task_timeout_ms	false	int
warehouse	false	string

task constraints

Constraint	Value
Required	false
Require Name (e.g., task "name" )	true
Allow Qualifier (e.g., task "schema" "name" )	true

view

The view block describes a view in a database schema.

view "clean_users" {
  schema = schema.public
  column "id" {
    type = int
  }
  ...
}

view attributes

Name	Required	Value
as	true	string
comment	false	string
depends_on	false	List of object references
schema	true	Object reference to schema
secure	false	bool

view blocks

view.annotation

view.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

view.column

view.column attributes

Name	Required	Value
comment	false	string
default	false	Column default value can be one of: bool string number Raw expression defined with sql("expr")
null	false	bool
type	false	Column type can be one of: Schema type Raw expression defined with sql("expr")

view.column blocks

view.column.annotation

view.column.annotation constraints

Constraint	Value
Required	false
Require Name	false
Allow unknown blocks	true
Allow unknown attributes	true

view.column constraints

Constraint	Value
Required	false
Require Name (e.g., view.column "name" )	true

view constraints

Constraint	Value
Required	false
Require Name (e.g., view "name" )	true
Allow Qualifier (e.g., view "schema" "name" )	true