Repository: TobikoData/sqlmesh
Pull request #4321 (2025-05-06 22:36:37)

Fix!: use sql() instead of gen() for time data type data hash computation
This aims to fix an issue where `sqlmesh plan` would show an unexpected diff for the `time_data_type` after a migration was applied for Databricks.

The diff was due to the fact that the local model had the default `time_data_type` which is `Type.TIMESTAMP`, but the deserialized model would parse the `"TIMESTAMP"` according to the databricks dialect, resulting in `Type.TIMESTAMPTZ`. The two types produce a different value when generated with `gen()`, leading to a difference in the data hash.

Context: https://tobiko-data.slack.com/archives/C044BRE5W4S/p1745437282778639


Patch:
diff --git a/docs/cloud/features/alerts_notifications.md b/docs/cloud/features/alerts_notifications.md
index bc47a353..98fe65a0 100644
--- a/docs/cloud/features/alerts_notifications.md
+++ b/docs/cloud/features/alerts_notifications.md
@@ -32,7 +32,7 @@ Tobiko Cloud sends an alert based on a *trigger*. There are two types of trigger
 
 Events are tied to steps in the SQLMesh `plan` and `run` processes. For example, you could alert whenever a `plan` succeeded or a `run` failed.
 
-Measures are based on either [automatically calculated measures](../../guides/observer.md#measures) like run time or [custom measure you define](../../guides/observer.md#custom-measures) via SQL queries.
+Measures are [automatically calculated](../../guides/observer.md#measures) at run time.
 
 Choose whether the alert will be triggered by a Measure or Event in the alert's Trigger Type field.
 
@@ -75,8 +75,6 @@ Some measures, like run time, are most useful when accumulated over an entire `p
 
 Configure a cumulative measure alert by choosing an Artifact type of Plan or Run.
 
-Other measure alerts are useful when applied to individual models or steps in a `plan` or `run`. For example, you might know that your `users` model should process around 1000 new users each day. You could [create a measure](../../guides/observer.md#custom-measures) to track the number of new users and an alert that notifies you if that number drops below 500.
-
 Configure a non-cumulative measure alert by choosing an Artifact type of Measure.
 
 ![Image showing the add measure Alert page Artifact field](./alerts_notifications/add_measure_alert_artifact.png)
diff --git a/docs/guides/observer.md b/docs/guides/observer.md
index f19bf37c..734e634d 100644
--- a/docs/guides/observer.md
+++ b/docs/guides/observer.md
@@ -51,7 +51,7 @@ The five entities in bold - models, audits, environments, runs, and plans - prov
 
 We now describe the specific measures SQLMesh captures about each entity.
 
-SQLMesh performs its primary actions during **plans** and **runs**, so most measures are generated when they occur. Both plans and runs are executed in a specific **environment**, so all of their measures are environment-specific.
+SQLMesh performs its primary actions during **plans** and **runs**, so measures are automatically generated when they occur. Both plans and runs are executed in a specific **environment**, so all of their measures are environment-specific.
 
 These measures are recorded and stored for each plan or run in a specific environment:
 
@@ -62,8 +62,6 @@ These measures are recorded and stored for each plan or run in a specific enviro
 - The model versions evaluated during the plan/run
 - Each model's run time
 
-Additionally, you can define [custom measures](#custom-measures) that will be captured for each model.
-
 ## Installation
 
 SQLMesh Observer is part of the `sqlmesh-enterprise` Python library and is installed via `pip`.
@@ -209,100 +207,3 @@ Next, the Loaded Intervals section displays the time intervals that have been lo
 The model information page concludes with a list of most frequent audits the model has failed, the most frequent time intervals that failed, and the largest historical model run times:
 
 ![SQLMesh Observer historical outliers](./observer/observer_model-information-4.png){ loading=lazy }
-
-## Custom measures
-
-SQLMesh Observer allows you to calculate and track custom measures in addition to the ones it [automatically calculates](#data).
-
-### Definition
-
-Each custom measure is associated with a model and is defined by a SQL query in the model file.
-
-The `@measure` macro is used to define custom measures. The body of the `@measure` macro is the query, and each column in the query defines a separate measure.
-
-A measure's name is the name of the column that defined it. Measure names must be unique within a model, but a name may be used in multiple models.
-
-A model may contain more than one `@measure` macro specification. The `@measure` macros must be specified after the model's primary query. They will be executed during a SQLMesh `plan` or `run` after the primary model query is executed.
-
-This example shows a model definition that includes a measure query defining two measures: `row_count` (the total number of rows in the table) and `num_col_avg` (the average value of the model's `numeric_col` column).
-
-```sql
-MODEL (
-  name custom_measure.example,
-  kind FULL
-);
-
-SELECT
-  numeric_col
-FROM
-  custom_measure.upstream;
-
-@measure( -- Measure query specified in the `@measure` macro
-  SELECT
-    COUNT(*) AS row_count, -- Table's row count
-    AVG(numeric_col) AS num_col_avg -- Average value of `numeric_col`
-  FROM custom_measure.example -- Select FROM the name of the model
-);
-```
-
-Every time the `custom_measure.example` model is executed, Observer will execute the measure query and store the value it returns.
-
-By default, the measure's timestamp will be the execution time of the `plan`/`run` that captured the measure. [Incremental by time range](../concepts/models/model_kinds.md#incremental_by_time_range) models may specify [custom timestamps](#custom-time-column).
-
-An Observer chart allows you to select which measure to display. The chart displays the value of the selected measure on the y-axis and the execution time of the associated `plan`/`run` on the x-axis, allowing you to monitor whether the value has meaningfully changed since the previous execution.
-
-### Incremental by time models
-
-#### Custom time column
-
-In the previous example, Observer automatically associated each measure value with the execution time of the `plan` or `run` that executed it.
-
-For [incremental by time range models](../concepts/models/model_kinds.md#incremental_by_time_range), you can customize how measures are associated with time by including your own time column in the measure query.
-
-The time column must be named `ts` and may be of any datetime data type (e.g., date string, `DATE`, `TIMESTAMP`, etc.). Custom times are typically derived from a datetime column in the model data and are most useful when the measure groups by the datetime.
-
-For example, this incremental model stores the date of each data point in the `event_datestring` column. We could measure each day's row count and numeric column average with this measure query:
-
-```sql
-MODEL (
-  name custom_measure.incremental_example
-  kind INCREMENTAL_BY_TIME_RANGE (
-    time_column event_datestring
-  )
-);
-
-SELECT
-  event_datestring,
-  numeric_col
-FROM
-  custom_measure.upstream
-WHERE
-  event_datestring BETWEEN @start_ds AND @end_ds;
-
-@measure(
-  SELECT
-    event_datestring AS ts, -- Custom measure time column `ts`
-    COUNT(*) AS daily_row_count, -- Daily row count
-    AVG(numeric_col) AS daily_num_col_avg -- Daily average value of `numeric_col`
-  FROM custom_measure.incremental_example
-  WHERE event_datestring BETWEEN @start_ds AND @end_ds -- Filter measure on time
-  GROUP BY event_datestring -- Group measure by time
-);
-```
-
-The measure query both filters and groups the data based on the model's time column `event_datestring`. The filtering and grouping ensures that only one measure value is ever calculated for a specific day of data.
-
-NOTE: the custom time column approach will not work correctly if the model's [`lookback` argument](../concepts/models/overview.md#lookback) is specified because a given day's data will be processed every time it is in the lookback window.
-
-#### Execution and custom times
-
-A model may contain multiple measure queries, so both execution time and custom time measures may be specified for the same model.
-
-These two measure types help answer different questions:
-
-1. Execution time: has something meaningfully changed **on this `plan`/`run`** compared to previous plans/runs?
-2. Custom time: has something meaningfully changed **in a specific time point's data** compared to other time points?
-
-If multiple time points of data are processed during each model execution, an anomaly at a specific time may not be detectable from an execution time measure alone.
-
-Custom time measures enable monitoring at the temporal granularity of the data itself.
diff --git a/sqlmesh/core/model/kind.py b/sqlmesh/core/model/kind.py
index aef9ca56..e59287ad 100644
--- a/sqlmesh/core/model/kind.py
+++ b/sqlmesh/core/model/kind.py
@@ -711,7 +711,7 @@ class _SCDType2Kind(_Incremental):
             gen(self.valid_from_name),
             gen(self.valid_to_name),
             str(self.invalidate_hard_deletes),
-            gen(self.time_data_type),
+            self.time_data_type.sql(self.dialect),
             gen(self.batch_size) if self.batch_size is not None else None,
         ]
 
diff --git a/sqlmesh/migrations/v0083_use_sql_for_scd_time_data_type_data_hash.py b/sqlmesh/migrations/v0083_use_sql_for_scd_time_data_type_data_hash.py
new file mode 100644
index 00000000..a6ad2bb5
--- /dev/null
+++ b/sqlmesh/migrations/v0083_use_sql_for_scd_time_data_type_data_hash.py
@@ -0,0 +1,5 @@
+"""Use sql(...) instead of gen when computing the data hash of the time data type."""
+
+
+def migrate(state_sync, **kwargs):  # type: ignore
+    pass


Test patch:
diff --git a/tests/core/test_model.py b/tests/core/test_model.py
index 20f08a25..6a7d7f34 100644
--- a/tests/core/test_model.py
+++ b/tests/core/test_model.py
@@ -9662,3 +9662,43 @@ def test_invalid_audit_reference():
 
     with pytest.raises(ConfigError, match="Audit 'not_nulll' is undefined"):
         load_sql_based_model(expressions)
+
+
+def test_scd_time_data_type_does_not_cause_diff_after_deserialization() -> None:
+    for dialect in (
+        "athena",
+        "bigquery",
+        "clickhouse",
+        "databricks",
+        "duckdb",
+        "dune",
+        "hive",
+        "mysql",
+        "postgres",
+        "presto",
+        "redshift",
+        "snowflake",
+        "spark",
+        "trino",
+        "tsql",
+    ):
+        sql = f"""
+        MODEL (
+          name test_schema.test_model,
+          kind SCD_TYPE_2_BY_COLUMN (
+            unique_key ARRAY(col),
+            columns ARRAY(col),
+            invalidate_hard_deletes TRUE,
+            on_destructive_change error
+          ),
+          dialect {dialect}
+        );
+
+        SELECT
+          1 AS col
+        """
+
+        model = load_sql_based_model(d.parse(sql))
+        deserialized_model = SqlModel.parse_raw(model.json())
+
+        assert model.data_hash == deserialized_model.data_hash