Repository: TobikoData/sqlmesh
Pull request #3253 (2024-10-15 10:55:40)

feat: adding ability to specify table/view type

benfdking: Two issues:

* [ ] Not working in duckdb with 

```
MODEL (
    name sqlmesh_example.full_model,
    kind FULL,
    cron '@daily',
    grain item_id,
    audits (assert_positive_order_ids),
    physical_properties (
      creatable_type = TEMP
    )
  );

  SELECT
    item_id,
    COUNT(DISTINCT id) AS num_orders,
  FROM
    sqlmesh_example.incremental_model
  GROUP BY item_id
  ```

* [ ] Change is marked as non-breaking
benfdking: ```
MODEL (
    name sqlmesh_example.full_model,
    kind FULL,
    cron '@daily',
    grain item_id,
    audits (assert_positive_order_ids),
    virtual_properties (
      creatable_type = SECURE
    ),
    physical_properties (
      creatable_type = TRANSIENT
    )
  );

  SELECT
    item_id,
    COUNT(DISTINCT id) AS num_orders,
  FROM
    sqlmesh_example.incremental_model
  GROUP BY item_id
  ```

In Snowflake gives you a secure view and a transient physical layer. 

Patch:
diff --git a/docs/concepts/glossary.md b/docs/concepts/glossary.md
index 9add9aee..15ffbc2d 100644
--- a/docs/concepts/glossary.md
+++ b/docs/concepts/glossary.md
@@ -57,6 +57,9 @@ Combining data from various sources (such as from a data warehouse) into one uni
 ## Lineage
 The lineage of your data is a visualization of the life cycle of your data as it flows from data sources downstream to consumption.
 
+## Physical Layer
+The physical layer is where SQLMesh stores and manages data in database tables and materialized views. It is the concrete data storage layer of the SQL engine, in contrast to the [SQLMesh virtual layer's](#virtual-layer) views. SQLMesh handles the management and maintenance of the physical layer automatically, and users should rarely interact with it directly.
+
 ## Plan Summaries
 An upcoming feature that allows users to see a summary of changes applied to a given environment.
 
@@ -78,6 +81,9 @@ A view is the result of a SQL query on a database.
 ## Virtual Environments
 SQLMesh's unique approach to environment that allows it to provide both environment isolation and the ability to share tables across environments. This is done in a way to ensure data consistency and accuracy. See [plan application](plans.md#plan-application) for more information.
 
+## Virtual Layer
+The virtual layer is SQLMesh's abstraction layer over the [physical layer and physical data storage](#physical-layer). While the physical layer consists of tables where data is actually stored, the virtual layer consists of views that expose tables in the underlying physical layer. Most users should only interact with the virtual layer when building models or querying data.
+
 ## Virtual Update
 Term used to describe a plan that can be applied without having to load any additional data or build any additional tables. See [Virtual Update](plans.md#virtual-update) for more information.
 
diff --git a/docs/integrations/engines/snowflake.md b/docs/integrations/engines/snowflake.md
index 2881451b..fedfc514 100644
--- a/docs/integrations/engines/snowflake.md
+++ b/docs/integrations/engines/snowflake.md
@@ -22,10 +22,10 @@ Snowflake provides multiple methods of authorizing a connection (e.g., password,
 Before working through this connection quickstart, ensure that:
 
 1. You have a Snowflake account and know your username and password
-1. Your Snowflake account has at least one [warehouse](https://docs.snowflake.com/en/user-guide/warehouses-overview) available for running computations
-2. Your computer has [SQLMesh installed](../../installation.md) with the [Snowflake extra available](../../installation.md#install-extras)
+2. Your Snowflake account has at least one [warehouse](https://docs.snowflake.com/en/user-guide/warehouses-overview) available for running computations
+3. Your computer has [SQLMesh installed](../../installation.md) with the [Snowflake extra available](../../installation.md#install-extras)
     - Install from the command line with the command `pip install "sqlmesh[snowflake]"`
-3. You have initialized a [SQLMesh example project](../../quickstart/cli#1-create-the-sqlmesh-project) on your computer
+4. You have initialized a [SQLMesh example project](../../quickstart/cli#1-create-the-sqlmesh-project) on your computer
     - Open a command line interface and navigate to the directory where the project files should go
     - Initialize the project with the command `sqlmesh init snowflake`
 
@@ -549,3 +549,14 @@ MODEL (
   ),
 );
 ```
+
+## Troubleshooting
+
+### Frequent Authentication Prompts
+
+When using Snowflake with security features like Multi-Factor Authentication (MFA), you may experience repeated prompts for authentication while running SQLMesh commands. This typically occurs when your Snowflake account isn't configured to issue short-lived tokens.
+
+To reduce authentication prompts, you can enable token caching in your Snowflake connection configuration:
+
+- For general authentication, see [Connection Caching Documentation](https://docs.snowflake.com/en/user-guide/admin-security-fed-auth-use#using-connection-caching-to-minimize-the-number-of-prompts-for-authentication-optional)
+- For MFA specifically, see [MFA Token Caching Documentation](https://docs.snowflake.com/en/user-guide/security-mfa#using-mfa-token-caching-to-minimize-the-number-of-prompts-during-authentication-optional).
diff --git a/docs/reference/model_configuration.md b/docs/reference/model_configuration.md
index b9d0fdaf..4390ed3c 100644
--- a/docs/reference/model_configuration.md
+++ b/docs/reference/model_configuration.md
@@ -8,35 +8,35 @@ Learn more about specifying SQLMesh model properties in the [model concepts over
 
 Configuration options for SQLMesh model properties. Supported by all model kinds other than [`SEED` models](#seed-models).
 
-| Option                | Description                                                                                                                                                                                                                                                                                                                                                 | Type              | Required |
+| Option                | Description                                                                                                                                                                                                                                                                                                                                                 |       Type        | Required |
 |-----------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:-----------------:|:--------:|
-| `name`                | The model name. Must include at least a qualifying schema (`<schema>.<model>`) and may include a catalog (`<catalog>.<schema>.<model>`). Can be omitted if [infer_names](#model-naming) is set to true.                                                                                                                                                     | str               | N        |
-| `project`             | The name of the project the model belongs to - used in multi-repo deployments                                                                                                                                                                                                                                                                            | str               | N        |
-| `kind`                | The model kind ([Additional Details](#model-kind-properties)). (Default: `VIEW`)                                                                                                                                                                                                                                                                            | str \| dict       | N        |
-| `audits`              | SQLMesh [audits](../concepts/audits.md) that should run against the model's output                                                                                                                                                                                                                                                                          | array[str]        | N        |
-| `dialect`             | The SQL dialect in which the model's query is written. All SQL dialects [supported by the SQLGlot library](https://github.com/tobymao/sqlglot/blob/main/sqlglot/dialects/dialect.py) are allowed.                                                                                                                                                           | str               | N        |
-| `owner`               | The owner of a model; may be used for notification purposes                                                                                                                                                                                                                                                                                                 | str               | N        |
-| `stamp`               | Arbitrary string used to indicate a model's version without changing the model name                                                                                                                                                                                                                                                                         | str               | N        |
-| `tags`                | Arbitrary strings used to organize or classify a model                                                                                                                                                                                                                                                                                                      | array[str]        | N        |
-| `cron`                | The cron expression specifying how often the model should be refreshed. (Default: `@daily`)                                                                                                                                                                                                                                                                 | str               | N        |
-| `interval_unit`       | The temporal granularity of the model's data intervals. Supported values: `year`, `month`, `day`, `hour`, `half_hour`, `quarter_hour`, `five_minute`. (Default: inferred from `cron`)                                                                                                                                                                       | str               | N        |
-| `start`               | The date/time that determines the earliest date interval that should be processed by a model. Can be a datetime string, epoch time in milliseconds, or a relative datetime such as `1 year ago`. (Default: `yesterday`)                                                                                                                                     | str \| int        | N        |
-| `end`                 | The date/time that determines the latest date interval that should be processed by a model. Can be a datetime string, epoch time in milliseconds, or a relative datetime such as `1 year ago`.                                                                                                                                                              | str \| int        | N        |
-| `description`         | Description of the model. Automatically registered in the SQL engine's table COMMENT field or equivalent (if supported by the engine).                                                                                                                                                                                                                      | str               | N        |
-| `column_descriptions` | A key-value mapping of column names to column comments that will be registered in the SQL engine's table COMMENT field (if supported by the engine). Specified as key-value pairs (`column_name = 'column comment'`). If present, [inline column comments](../concepts/models/overview.md#inline-column-comments) will not be registered in the SQL engine. | dict              | N        |
-| `grains`              | The column(s) whose combination uniquely identifies each row in the model                                                                                                                                                                                                                                                                                   | str \| array[str] | N        |
-| `references`          | The model column(s) used to join to other models' grains                                                                                                                                                                                                                                                                                                    | str \| array[str] | N        |
-| `depends_on`          | Models on which this model depends, in addition to the ones inferred from the model's query. (Default: dependencies inferred from model code)                                                                                                                                                                                                               | array[str]        | N        |
-| `table_format`        | The table format that should be used to manage the physical files (eg `iceberg`, `hive`, `delta`); only applicable to engines such as Spark and Athena                                                                                                                                                                                                      | str               | N        |
-| `storage_format`      | The storage format that should be used to store physical files (eg `parquet`, `orc`); only applicable to engines such as Spark and Athena                                                                                                                                                                                                                   | str               | N        |
-| `partitioned_by`      | The column(s) and/or column expressions used define a model's partitioning key. Required for the `INCREMENTAL_BY_PARTITION` model kind. Optional for all other model kinds; used to partition the model's physical table in engines that support partitioning.                                                                                              | str \| array[str] | N        |
-| `clustered_by`        | The column(s) used to cluster the model's physical table; only applicable to engines that support clustering                                                                                                                                                                                                                                                | str               | N        |
-| `columns`             | The column names and data types returned by the model. Disables [automatic inference of column names and types](../concepts/models/overview.md#conventions) from the SQL query.                                                                                                                                                                             | array[str]        | N        |
-| `physical_properties` | A key-value mapping of arbitrary properties specific to the target engine that are applied to the model table / view in the physical layer. Specified as key-value pairs (`key = value`).                                                                                                                                                                   | dict              | N        |
-| `virtual_properties`  | A key-value mapping of arbitrary properties specific to the target engine that are applied to the model view in the virtual layer. Specified as key-value pairs (`key = value`).                                                                                                                                                                            | dict              | N        |
-| `session_properties`  | A key-value mapping of arbitrary properties specific to the target engine that are applied to the engine session. Specified as key-value pairs (`key = value`).                                                                                                                                                                                             | dict              | N        |
-| `allow_partials`      | Whether this model can process partial (incomplete) data intervals                                                                                                                                                                                                                                                                                          | bool              | N        |
-| `enabled`             | Whether the model is enabled. This attribute is `true` by default. Setting it to `false` causes SQLMesh to ignore this model when loading the project.                                                                                                                                                                                                      | bool              | N        |
+| `name`                | The model name. Must include at least a qualifying schema (`<schema>.<model>`) and may include a catalog (`<catalog>.<schema>.<model>`). Can be omitted if [infer_names](#model-naming) is set to true.                                                                                                                                                     |        str        |    N     |
+| `project`             | The name of the project the model belongs to - used in multi-repo deployments                                                                                                                                                                                                                                                                               |        str        |    N     |
+| `kind`                | The model kind ([Additional Details](#model-kind-properties)). (Default: `VIEW`)                                                                                                                                                                                                                                                                            |    str \| dict    |    N     |
+| `audits`              | SQLMesh [audits](../concepts/audits.md) that should run against the model's output                                                                                                                                                                                                                                                                          |    array[str]     |    N     |
+| `dialect`             | The SQL dialect in which the model's query is written. All SQL dialects [supported by the SQLGlot library](https://github.com/tobymao/sqlglot/blob/main/sqlglot/dialects/dialect.py) are allowed.                                                                                                                                                           |        str        |    N     |
+| `owner`               | The owner of a model; may be used for notification purposes                                                                                                                                                                                                                                                                                                 |        str        |    N     |
+| `stamp`               | Arbitrary string used to indicate a model's version without changing the model name                                                                                                                                                                                                                                                                         |        str        |    N     |
+| `tags`                | Arbitrary strings used to organize or classify a model                                                                                                                                                                                                                                                                                                      |    array[str]     |    N     |
+| `cron`                | The cron expression specifying how often the model should be refreshed. (Default: `@daily`)                                                                                                                                                                                                                                                                 |        str        |    N     |
+| `interval_unit`       | The temporal granularity of the model's data intervals. Supported values: `year`, `month`, `day`, `hour`, `half_hour`, `quarter_hour`, `five_minute`. (Default: inferred from `cron`)                                                                                                                                                                       |        str        |    N     |
+| `start`               | The date/time that determines the earliest date interval that should be processed by a model. Can be a datetime string, epoch time in milliseconds, or a relative datetime such as `1 year ago`. (Default: `yesterday`)                                                                                                                                     |    str \| int     |    N     |
+| `end`                 | The date/time that determines the latest date interval that should be processed by a model. Can be a datetime string, epoch time in milliseconds, or a relative datetime such as `1 year ago`.                                                                                                                                                              |    str \| int     |    N     |
+| `description`         | Description of the model. Automatically registered in the SQL engine's table COMMENT field or equivalent (if supported by the engine).                                                                                                                                                                                                                      |        str        |    N     |
+| `column_descriptions` | A key-value mapping of column names to column comments that will be registered in the SQL engine's table COMMENT field (if supported by the engine). Specified as key-value pairs (`column_name = 'column comment'`). If present, [inline column comments](../concepts/models/overview.md#inline-column-comments) will not be registered in the SQL engine. |       dict        |    N     |
+| `grains`              | The column(s) whose combination uniquely identifies each row in the model                                                                                                                                                                                                                                                                                   | str \| array[str] |    N     |
+| `references`          | The model column(s) used to join to other models' grains                                                                                                                                                                                                                                                                                                    | str \| array[str] |    N     |
+| `depends_on`          | Models on which this model depends, in addition to the ones inferred from the model's query. (Default: dependencies inferred from model code)                                                                                                                                                                                                               |    array[str]     |    N     |
+| `table_format`        | The table format that should be used to manage the physical files (eg `iceberg`, `hive`, `delta`); only applicable to engines such as Spark and Athena                                                                                                                                                                                                      |        str        |    N     |
+| `storage_format`      | The storage format that should be used to store physical files (eg `parquet`, `orc`); only applicable to engines such as Spark and Athena                                                                                                                                                                                                                   |        str        |    N     |
+| `partitioned_by`      | The column(s) and/or column expressions used define a model's partitioning key. Required for the `INCREMENTAL_BY_PARTITION` model kind. Optional for all other model kinds; used to partition the model's physical table in engines that support partitioning.                                                                                              | str \| array[str] |    N     |
+| `clustered_by`        | The column(s) used to cluster the model's physical table; only applicable to engines that support clustering                                                                                                                                                                                                                                                |        str        |    N     |
+| `columns`             | The column names and data types returned by the model. Disables [automatic inference of column names and types](../concepts/models/overview.md#conventions) from the SQL query.                                                                                                                                                                             |    array[str]     |    N     |
+| `physical_properties` | A key-value mapping of arbitrary properties specific to the target engine that are applied to the model table / view in the physical layer. Specified as key-value pairs (`key = value`). The view/table type (e.g. `TEMPORARY`, `TRANSIENT`) can be added with the `creatable_type` key.                                                                   |       dict        |    N     |
+| `virtual_properties`  | A key-value mapping of arbitrary properties specific to the target engine that are applied to the model view in the virtual layer. Specified as key-value pairs (`key = value`). The view type (e.g. `SECURE`) can be added with the `creatable_type` key.                                                                                                  |       dict        |    N     |
+| `session_properties`  | A key-value mapping of arbitrary properties specific to the target engine that are applied to the engine session. Specified as key-value pairs (`key = value`).                                                                                                                                                                                             |       dict        |    N     |
+| `allow_partials`      | Whether this model can process partial (incomplete) data intervals                                                                                                                                                                                                                                                                                          |       bool        |    N     |
+| `enabled`             | Whether the model is enabled. This attribute is `true` by default. Setting it to `false` causes SQLMesh to ignore this model when loading the project.                                                                                                                                                                                                      |       bool        |    N     |
 
 ### Model defaults
 
@@ -59,9 +59,9 @@ The SQLMesh project-level `model_defaults` key supports the following options, d
 
 Configuration option for name inference. Learn more in the [model naming guide](../guides/configuration.md#model-naming).
 
-| Option          | Description                                                                             |  Type   | Required |
-| --------------- | --------------------------------------------------------------------------------------- | :-----: | :------: |
-| `infer_names`   | Whether to automatically infer model names based on the directory structure (Default: `False`) | bool |    N     |
+| Option        | Description                                                                                    | Type | Required |
+|---------------|------------------------------------------------------------------------------------------------|:----:|:--------:|
+| `infer_names` | Whether to automatically infer model names based on the directory structure (Default: `False`) | bool |    N     |
 
 
 ## Model kind properties
@@ -75,7 +75,7 @@ Learn more about model kinds at the [model kind concepts page](../concepts/model
 Configuration options for models of the [`VIEW` kind](../concepts/models/model_kinds.md#view) (in addition to [general model properties](#general-model-properties)).
 
 | Option         | Description                                                                                          | Type | Required |
-| -------------- | ---------------------------------------------------------------------------------------------------- | :--: | :------: |
+|----------------|------------------------------------------------------------------------------------------------------|:----:|:--------:|
 | `materialized` | Whether views should be materialized (for engines supporting materialized views). (Default: `False`) | bool |    N     |
 
 Python model kind `name` enum value: [ModelKindName.VIEW](https://sqlmesh.readthedocs.io/en/stable/_readthedocs/html/sqlmesh/core/model/kind.html#ModelKindName)
diff --git a/sqlmesh/core/engine_adapter/base.py b/sqlmesh/core/engine_adapter/base.py
index 6716507b..39a3fd07 100644
--- a/sqlmesh/core/engine_adapter/base.py
+++ b/sqlmesh/core/engine_adapter/base.py
@@ -63,6 +63,8 @@ logger = logging.getLogger(__name__)
 MERGE_TARGET_ALIAS = "__MERGE_TARGET__"
 MERGE_SOURCE_ALIAS = "__MERGE_SOURCE__"
 
+KEY_FOR_CREATABLE_TYPE = "CREATABLE_TYPE"
+
 
 @set_catalog()
 class EngineAdapter:
@@ -407,6 +409,33 @@ class EngineAdapter:
         )
         self.execute(expression)
 
+    def _pop_creatable_type_from_properties(
+        self,
+        properties: t.Dict[str, exp.Expression],
+    ) -> t.Optional[exp.Property]:
+        """Pop out the creatable_type from the properties dictionary (if exists (return it/remove it) else return none).
+        It also checks that none of the expressions are MATERIALIZE as that conflicts with the `materialize` parameter.
+        """
+        for key in list(properties.keys()):
+            upper_key = key.upper()
+            if upper_key == KEY_FOR_CREATABLE_TYPE:
+                value = properties.pop(key).name
+                parsed_properties = exp.maybe_parse(
+                    value, into=exp.Properties, dialect=self.dialect
+                )
+                property, *others = parsed_properties.expressions
+                if others:
+                    # Multiple properties are unsupported today, can look into it in the future if needed
+                    raise SQLMeshError(
+                        f"Invalid creatable_type value with multiple properties: {value}"
+                    )
+                if isinstance(property, exp.MaterializedProperty):
+                    raise SQLMeshError(
+                        f"Cannot use {value} as a creatable_type as it conflicts with the `materialize` parameter."
+                    )
+                return property
+        return None
+
     def create_table(
         self,
         table_name: TableName,
@@ -951,6 +980,11 @@ class EngineAdapter:
         if not properties:
             properties = exp.Properties(expressions=[])
 
+        if view_properties:
+            table_type = self._pop_creatable_type_from_properties(view_properties)
+            if table_type:
+                properties.append("expressions", table_type)
+
         if materialized and self.SUPPORTS_MATERIALIZED_VIEWS:
             properties.append("expressions", exp.MaterializedProperty())
 
@@ -988,7 +1022,11 @@ class EngineAdapter:
         )
         if create_view_properties:
             for view_property in create_view_properties.expressions:
-                properties.append("expressions", view_property)
+                # Small hack to make sure SECURE goes at the beginning before materialized as required by Snowflake
+                if isinstance(view_property, exp.SecureProperty):
+                    properties.set("expressions", view_property, index=0, overwrite=False)
+                else:
+                    properties.append("expressions", view_property)
 
         if properties.expressions:
             create_kwargs["properties"] = properties
@@ -2124,6 +2162,10 @@ class EngineAdapter:
                 )
             )
 
+        if table_properties:
+            table_type = self._pop_creatable_type_from_properties(table_properties)
+            properties.extend(ensure_list(table_type))
+
         if properties:
             return exp.Properties(expressions=properties)
         return None
diff --git a/sqlmesh/core/engine_adapter/snowflake.py b/sqlmesh/core/engine_adapter/snowflake.py
index 8d7395ca..5e26ac11 100644
--- a/sqlmesh/core/engine_adapter/snowflake.py
+++ b/sqlmesh/core/engine_adapter/snowflake.py
@@ -7,6 +7,7 @@ import typing as t
 import pandas as pd
 from pandas.api.types import is_datetime64_any_dtype  # type: ignore
 from sqlglot import exp
+from sqlglot.helper import ensure_list
 from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
 from sqlglot.optimizer.qualify_columns import quote_identifiers
 
@@ -189,12 +190,12 @@ class SnowflakeEngineAdapter(GetCurrentCatalogFromFunctionMixin, ClusteredByMixi
                 for prop in {"WAREHOUSE", "TARGET_LAG", "REFRESH_MODE", "INITIALIZE"}:
                     table_properties.pop(prop, None)
 
-            properties.extend(self._table_or_view_properties_to_expressions(table_properties))
+            table_type = self._pop_creatable_type_from_properties(table_properties)
+            properties.extend(ensure_list(table_type))
 
-        if properties:
-            return exp.Properties(expressions=properties)
+            properties.extend(self._table_or_view_properties_to_expressions(table_properties))
 
-        return None
+        return exp.Properties(expressions=properties) if properties else None
 
     def _df_to_source_queries(
         self,


Test patch:
diff --git a/tests/core/engine_adapter/test_duckdb.py b/tests/core/engine_adapter/test_duckdb.py
index 79cfd8d9..42394e61 100644
--- a/tests/core/engine_adapter/test_duckdb.py
+++ b/tests/core/engine_adapter/test_duckdb.py
@@ -75,3 +75,17 @@ def test_set_current_catalog(make_mocked_engine_adapter: t.Callable, duck_conn):
     assert to_sql_calls(adapter) == [
         'USE "test_catalog"',
     ]
+
+
+def test_temporary_table(make_mocked_engine_adapter: t.Callable, duck_conn):
+    adapter = make_mocked_engine_adapter(DuckDBEngineAdapter)
+
+    adapter.create_table(
+        "test_table",
+        {"a": exp.DataType.build("INT"), "b": exp.DataType.build("INT")},
+        table_properties={"creatable_type": exp.Column(this=exp.Identifier(this="Temporary"))},
+    )
+
+    assert to_sql_calls(adapter) == [
+        'CREATE TEMPORARY TABLE IF NOT EXISTS "test_table" ("a" INT, "b" INT)',
+    ]
diff --git a/tests/core/engine_adapter/test_snowflake.py b/tests/core/engine_adapter/test_snowflake.py
index a1c9863a..c1f8f8c0 100644
--- a/tests/core/engine_adapter/test_snowflake.py
+++ b/tests/core/engine_adapter/test_snowflake.py
@@ -429,7 +429,7 @@ def test_replace_query_snowpark_dataframe(
     ]
 
 
-def test_materialized_view_properties(make_mocked_engine_adapter: t.Callable):
+def test_creatable_type_materialized_view_properties(make_mocked_engine_adapter: t.Callable):
     adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
 
     adapter.create_view(
@@ -449,3 +449,175 @@ def test_materialized_view_properties(make_mocked_engine_adapter: t.Callable):
     assert sql_calls == [
         'CREATE OR REPLACE MATERIALIZED VIEW "test_table" CLUSTER BY ("a") AS SELECT 1',
     ]
+
+
+def test_creatable_type_secure_view(make_mocked_engine_adapter: t.Callable):
+    adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
+
+    adapter.create_view(
+        "test_table",
+        parse_one("SELECT 1"),
+        view_properties={
+            "creatable_type": exp.Column(this=exp.Identifier(this="secure")),
+        },
+    )
+
+    sql_calls = to_sql_calls(adapter)
+    # https://docs.snowflake.com/en/sql-reference/sql/create-view.html
+    assert sql_calls == [
+        'CREATE OR REPLACE SECURE VIEW "test_table" AS SELECT 1',
+    ]
+
+
+def test_creatable_type_secure_materialized_view(make_mocked_engine_adapter: t.Callable):
+    adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
+
+    adapter.create_view(
+        "test_table",
+        parse_one("SELECT 1"),
+        materialized=True,
+        view_properties={
+            "creatable_type": exp.Column(this=exp.Identifier(this="secure")),
+        },
+    )
+
+    sql_calls = to_sql_calls(adapter)
+    # https://docs.snowflake.com/en/sql-reference/sql/create-view.html
+    assert sql_calls == [
+        'CREATE OR REPLACE SECURE MATERIALIZED VIEW "test_table" AS SELECT 1',
+    ]
+
+
+def test_creatable_type_temporary_view(make_mocked_engine_adapter: t.Callable):
+    adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
+
+    adapter.create_view(
+        "test_table",
+        parse_one("SELECT 1"),
+        view_properties={
+            "creatable_type": exp.column("temporary"),
+        },
+    )
+
+    sql_calls = to_sql_calls(adapter)
+    assert sql_calls == [
+        'CREATE OR REPLACE TEMPORARY VIEW "test_table" AS SELECT 1',
+    ]
+
+
+def test_creatable_type_temporary_table(make_mocked_engine_adapter: t.Callable):
+    adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
+
+    adapter.create_table(
+        "test_table",
+        {"a": exp.DataType.build("INT"), "b": exp.DataType.build("INT")},
+        table_properties={
+            "creatable_type": exp.column("temporary"),
+        },
+    )
+
+    sql_calls = to_sql_calls(adapter)
+    assert sql_calls == [
+        'CREATE TEMPORARY TABLE IF NOT EXISTS "test_table" ("a" INT, "b" INT)',
+    ]
+
+
+def test_creatable_type_transient_table(make_mocked_engine_adapter: t.Callable):
+    adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
+
+    adapter.create_table(
+        "test_table",
+        {"a": exp.DataType.build("INT"), "b": exp.DataType.build("INT")},
+        table_properties={
+            "creatable_type": exp.column("transient"),
+        },
+    )
+
+    sql_calls = to_sql_calls(adapter)
+    assert sql_calls == [
+        'CREATE TRANSIENT TABLE IF NOT EXISTS "test_table" ("a" INT, "b" INT)',
+    ]
+
+
+def test_creatable_type_materialize_creatable_type_raise_error(
+    make_mocked_engine_adapter: t.Callable,
+):
+    adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
+
+    with pytest.raises(SQLMeshError):
+        adapter.create_view(
+            "test_view",
+            parse_one("SELECT 1"),
+            view_properties={
+                "creatable_type": exp.column("materialized"),
+            },
+        )
+
+
+def test_creatable_type_transient_type_from_model_definition(
+    make_mocked_engine_adapter: t.Callable,
+):
+    adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
+
+    model: SqlModel = t.cast(
+        SqlModel,
+        load_sql_based_model(
+            d.parse(
+                """
+MODEL (
+    name external.test.table,
+    kind full,
+    physical_properties (
+        creatable_type = transient
+    )
+);
+SELECT a::INT;
+    """
+            )
+        ),
+    )
+    adapter.create_table(
+        model.name,
+        columns_to_types=model.columns_to_types_or_raise,
+        table_properties=model.physical_properties,
+    )
+
+    sql_calls = to_sql_calls(adapter)
+    assert sql_calls == [
+        'CREATE TRANSIENT TABLE IF NOT EXISTS "external"."test"."table" ("a" INT)',
+    ]
+
+
+def test_creatable_type_transient_type_from_model_definition_with_other_property(
+    make_mocked_engine_adapter: t.Callable,
+):
+    adapter = make_mocked_engine_adapter(SnowflakeEngineAdapter)
+
+    model: SqlModel = t.cast(
+        SqlModel,
+        load_sql_based_model(
+            d.parse(
+                """
+MODEL (
+    name external.test.table,
+    kind full,
+    physical_properties (
+        creatable_type = transient,
+        require_partition_filter = true
+    )
+);
+SELECT a::INT;
+    """
+            )
+        ),
+    )
+    adapter.create_table(
+        model.name,
+        columns_to_types=model.columns_to_types_or_raise,
+        table_properties=model.physical_properties,
+    )
+
+    sql_calls = to_sql_calls(adapter)
+    assert sql_calls == [
+        'CREATE TRANSIENT TABLE IF NOT EXISTS "external"."test"."table" ("a" INT) REQUIRE_PARTITION_FILTER=TRUE'
+    ]