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: string enum (INCREMENTAL, FULL)
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 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/"
  }
}

~~~hcl



### `external_table` attributes \{#external_table-attributes}



<table class="attributes-table">
  <tr>
    <th>Name</th>
    <th>Required</th>
    <th>Value</th>
  </tr>
  <tr>
      <td id="external_table.comment"><a href="#external_table.comment"><code>comment</code></a></td>
    <td><code>false</code></td>
  <td><code>string</code></td>
  </tr>
  <tr>
      <td id="external_table.depends_on"><a href="#external_table.depends_on"><code>depends_on</code></a></td>
    <td><code>false</code></td>
  <td class="no-margin-bottom">

List of object references
  </td>

  </tr>
  <tr>
      <td id="external_table.file_format"><a href="#external_table.file_format"><code>file_format</code></a></td>
    <td><code>true</code></td>
  <td><code>map</code></td>
  </tr>
  <tr>
      <td id="external_table.retention_time"><a href="#external_table.retention_time"><code>retention_time</code></a></td>
    <td><code>false</code></td>
  <td><code>int</code></td>
  </tr>
  <tr>
      <td id="external_table.schema"><a href="#external_table.schema"><code>schema</code></a></td>
    <td><code>true</code></td>
  <td class="no-margin-bottom">

Object reference to [`schema`](#schema)
  </td>

  </tr>
</table>



### `external_table` blocks \{#external_table-blocks}



    
#### `external_table.column` \{#external_table.column}


##### `external_table.column` attributes \{#external_table.column-attributes}



<table class="attributes-table">
  <tr>
    <th>Name</th>
    <th>Required</th>
    <th>Value</th>
  </tr>
  <tr>
      <td id="external_table.column.as"><a href="#external_table.column.as"><code>as</code></a></td>
    <td><code>false</code></td>
  <td><code>string</code></td>
  </tr>
  <tr>
      <td id="external_table.column.comment"><a href="#external_table.column.comment"><code>comment</code></a></td>
    <td><code>false</code></td>
  <td><code>string</code></td>
  </tr>
  <tr>
      <td id="external_table.column.null"><a href="#external_table.column.null"><code>null</code></a></td>
    <td><code>false</code></td>
  <td><code>bool</code></td>
  </tr>
  <tr>
      <td id="external_table.column.type"><a href="#external_table.column.type"><code>type</code></a></td>
    <td><code>true</code></td>
<td>

Column type can be one of:
1. Schema type
2. Raw expression defined with `sql("expr")`

</td>
  </tr>
</table>






##### `external_table.column` constraints \{#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}


##### `external_table.foreign_key` attributes \{#external_table.foreign_key-attributes}



<table class="attributes-table">
  <tr>
    <th>Name</th>
    <th>Required</th>
    <th>Value</th>
  </tr>
  <tr>
      <td id="external_table.foreign_key.columns"><a href="#external_table.foreign_key.columns"><code>columns</code></a></td>
    <td><code>true</code></td>
<td>

Foreign key columns can be one of:
1. List of object reference to [`column`](#external_table.column)
2. List of object reference to [`external_table.column`](#external_table.column)

</td>
  </tr>
  <tr>
      <td id="external_table.foreign_key.comment"><a href="#external_table.foreign_key.comment"><code>comment</code></a></td>
    <td><code>false</code></td>
  <td><code>string</code></td>
  </tr>
  <tr>
      <td id="external_table.foreign_key.ref_columns"><a href="#external_table.foreign_key.ref_columns"><code>ref_columns</code></a></td>
    <td><code>true</code></td>
<td>

Foreign key reference columns can be one of:
1. List of object reference to [`column`](#external_table.column)
2. List of object reference to [`external_table.column`](#external_table.column)

</td>
  </tr>
</table>






##### `external_table.foreign_key` constraints \{#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}


##### `external_table.location` attributes \{#external_table.location-attributes}



<table class="attributes-table">
  <tr>
    <th>Name</th>
    <th>Required</th>
    <th>Value</th>
  </tr>
  <tr>
      <td id="external_table.location.path"><a href="#external_table.location.path"><code>path</code></a></td>
    <td><code>false</code></td>
  <td><code>string</code></td>
  </tr>
  <tr>
      <td id="external_table.location.stage"><a href="#external_table.location.stage"><code>stage</code></a></td>
    <td><code>true</code></td>
  <td class="no-margin-bottom">

Object reference to [`stage`](#stage)
  </td>

  </tr>
</table>






##### `external_table.location` constraints \{#external_table.location-constraints}



|    Constraint               |           Value
|-----------------------------|------------------------------------
| Required                    |      `true`
| Require Name   |  `false`




    
#### `external_table.primary_key` \{#external_table.primary_key}


##### `external_table.primary_key` attributes \{#external_table.primary_key-attributes}



<table class="attributes-table">
  <tr>
    <th>Name</th>
    <th>Required</th>
    <th>Value</th>
  </tr>
  <tr>
      <td id="external_table.primary_key.columns"><a href="#external_table.primary_key.columns"><code>columns</code></a></td>
    <td><code>true</code></td>
<td>

Index columns can be one of:
1. List of object reference to [`column`](#external_table.column)
2. List of object reference to [`external_table.column`](#external_table.column)

</td>
  </tr>
  <tr>
      <td id="external_table.primary_key.comment"><a href="#external_table.primary_key.comment"><code>comment</code></a></td>
    <td><code>false</code></td>
  <td><code>string</code></td>
  </tr>
</table>










    
#### `external_table.unique` \{#external_table.unique}


##### `external_table.unique` attributes \{#external_table.unique-attributes}



<table class="attributes-table">
  <tr>
    <th>Name</th>
    <th>Required</th>
    <th>Value</th>
  </tr>
  <tr>
      <td id="external_table.unique.columns"><a href="#external_table.unique.columns"><code>columns</code></a></td>
    <td><code>true</code></td>
<td>

Index columns can be one of:
1. List of object reference to [`column`](#external_table.column)
2. List of object reference to [`external_table.column`](#external_table.column)

</td>
  </tr>
  <tr>
      <td id="external_table.unique.comment"><a href="#external_table.unique.comment"><code>comment</code></a></td>
    <td><code>false</code></td>
  <td><code>string</code></td>
  </tr>
</table>











### `external_table` constraints \{#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` \{#hybrid_table}
The `hybrid` block describes a hybrid table in a database schema.

~~~hcl
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 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 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 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

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 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.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 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

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

view blocks

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 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