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 |
comment | false | string |
depends_on | false | List of object references |
refresh_mode | false | Dynamic table refresh mode can be one of:
|
retention_time | false | int |
schema | true | Object reference to |
target_lag | true | Dynamic table target lag can be one of:
|
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:
|
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 | true | map |
retention_time | false | int |
schema | true | Object reference to |
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:
|
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:
|
comment | false | string |
ref_columns | true | Foreign key reference columns can be one of:
|
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 |
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:
|
comment | false | string |
external_table.unique
external_table.unique attributes
| Name | Required | Value |
|---|---|---|
columns | true | Index columns can be one of:
|
comment | false | string |
external_table constraints
| Constraint | Value |
|---|---|
| Required | false |
Require Name (e.g., external_table "name" ) | true |
Allow Qualifier (e.g., external_table "schema" "name" ) | true |
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 |
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:
|
null | false | bool |
type | true | Column type can be one of:
|
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:
|
comment | false | string |
ref_columns | true | Foreign key reference columns can be one of:
|
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:
|
comment | false | string |
hybrid_table.unique
hybrid_table.unique attributes
| Name | Required | Value |
|---|---|---|
columns | true | Index columns can be one of:
|
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 |
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 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 | map |
schema | true | Object reference to |
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 |
transient | false | bool |
table blocks
table.column
table.column attributes
| Name | Required | Value |
|---|---|---|
collate | false | string |
comment | false | string |
default | false | Column default value can be one of:
|
null | false | bool |
type | true | Column type can be one of:
|
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:
|
comment | false | string |
ref_columns | true | Foreign key reference columns can be one of:
|
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:
|
comment | false | string |
table.unique
table.unique attributes
| Name | Required | Value |
|---|---|---|
columns | true | Index columns can be one of:
|
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 |
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 |
view blocks
view.column
view.column attributes
| Name | Required | Value |
|---|---|---|
comment | false | string |
default | false | Column default value can be one of:
|
null | false | bool |
type | false | Column type can be one of:
|
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 |