Repository: TobikoData/sqlmesh
Pull request #3137 (2024-09-15 21:26:42)

Docs: Add docs for xdb-diff


Patch:
diff --git a/docs/guides/tablediff.md b/docs/guides/tablediff.md
index 0300d93c..5dd6debd 100644
--- a/docs/guides/tablediff.md
+++ b/docs/guides/tablediff.md
@@ -153,3 +153,142 @@ SQLMESH_EXAMPLE.INCREMENTAL_MODEL ONLY sample rows:
 ```
 
 The output matches, with the exception of the column labels in the `COMMON ROWS sample data differences`. The underlying table for each column is indicated by `s__` for "source" table (first table in the command's colon operator `:`) and `t__` for "target" table (second table in the command's colon operator `:`).
+
+## Diffing tables or views across gateways
+
+!!! info "Tobiko Cloud Feature"
+
+    Cross-database table diffing is available in [Tobiko Cloud](./observer.md#installation).
+
+SQLMesh executes a project's models with a single database system, specified as a [gateway](../guides/connections.md#overview) in the project configuration.
+
+The within-database table diff tool described above compares tables or environments within such a system. Sometimes, however, you might want to compare tables that reside in two different data systems.
+
+For example, you might migrate your data transformations from an on-premises SQL engine to a cloud SQL engine while setting up your SQLMesh project. To demonstrate equivalence between the systems you could run the transformations in both and compare the new tables to the old tables.
+
+The [within-database table diff](#diffing-models-across-environments) tool cannot make those comparisons, for two reasons:
+
+1. It must join the two tables being diffed, but with two systems no single database engine can access both tables.
+2. It assumes that data values can be compared across tables without modification. If the systems use different SQL engines, however, the diff must account for differences in the engines' data types (e.g., whether timestamps should include time zone information).
+
+SQLMesh's cross-database table diff tool is built for just this scenario. Its comparison algorithm efficiently diffs tables without moving them from one system to the other and automatically addresses differences in data types.
+
+### Configuration and syntax
+
+To diff tables across systems, first configure [Gateways](../reference/configuration#Gateways) for each database system in your SQLMesh configuration file.
+
+This example configures `bigquery` and `snowflake` gateways:
+
+```yaml linenums="1"
+gateways:
+  bigquery:
+    connection:
+      type: bigquery
+      [other connection parameters]
+
+  snowflake:
+    connection:
+      type: snowflake
+      [other connection parameters]
+```
+
+Then, specify each table's gateway in the `table_diff` command with this syntax: `[source_gateway]|[source table]:[target_gateway]|[target table]`.
+
+For example, we could diff the `landing.table` table across `bigquery` and `snowflake` gateways like this:
+
+```sh
+$ sqlmesh table_diff 'bigquery|landing.table:snowflake|lake.table'
+```
+
+This syntax tells SQLMesh to use the cross-database diffing algorithm instead of the normal within-database diffing algorithm.
+
+After adding gateways to the table names, use `table_diff` as described above - the same options apply for specifying the join keys, decimal precision, etc. See `sqlmesh table_diff --help` for a [full list of options](../reference/cli.md#table_diff).
+
+!!! warning
+
+    Cross-database diff works for data objects (tables / views).
+
+    Diffing _models_ is not supported because we do not assume that both the source and target databases are managed by SQLMesh.
+
+### Example output
+
+A cross-database diff is broken up into two stages.
+
+The first stage is a schema diff. This example shows that differences in column name case across the two tables are identified as schema differences:
+
+```bash
+$ sqlmesh table_diff 'bigquery|sqlmesh_example.full_model:snowflake|sqlmesh_example.full_model' --on item_id --show-sample
+
+Schema Diff Between 'BIGQUERY|SQLMESH_EXAMPLE.FULL_MODEL' and 'SNOWFLAKE|SQLMESH_EXAMPLE.FULL_MODEL':
+├── Added Columns:
+│   ├── ITEM_ID (DECIMAL(38, 0))
+│   └── NUM_ORDERS (DECIMAL(38, 0))
+└── Removed Columns:
+    ├── item_id (BIGINT)
+    └── num_orders (BIGINT)
+Schema has differences; continue comparing rows? [y/n]:
+```
+
+SQLMesh prompts you before comparing data values across table rows. The prompt provides an opportunity to discontinue the comparison if the schemas are vastly different (potentially indicating a mistake) or you need to exclude columns from the diff because you know they won't match.
+
+The second stage of the diff is comparing data values across tables. Within each system, SQLMesh divides the data into chunks, evaluates each chunk, and compares the outputs across systems. If a difference is found, it performs a row-level diff on that chunk by reading a sample of mismatched rows from each system.
+
+This example shows that 2 rows were present in each system but had different values, one row was in Bigquery only, and one row was in Snowflake only:
+
+```bash
+Dividing source dataset into 10 chunks (based on 10947709 total records)
+Checking chunks against target dataset
+Chunk 1 hash mismatch!
+Starting row-level comparison for the range (1 -> 3)
+Identifying individual record hashes that don't match
+Comparing
+
+Row Counts:
+├──  PARTIAL MATCH: 2 rows (66.67%)
+├──  BIGQUERY ONLY: 1 rows (16.67%)
+└──  SNOWFLAKE ONLY: 1 rows (16.67%)
+
+COMMON ROWS column comparison stats:
+            pct_match
+num_orders        0.0
+
+
+COMMON ROWS sample data differences:
+Column: num_orders
+┏━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━┓
+┃ item_id ┃ BIGQUERY ┃ SNOWFLAKE ┃
+┡━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━┩
+│ 1       │ 5        │ 7         │
+│ 2       │ 1        │ 2         │
+└─────────┴──────────┴───────────┘
+
+BIGQUERY ONLY sample rows:
+item_id num_orders
+      7          4
+
+
+SNOWFLAKE ONLY sample rows:
+item_id num_orders
+      4          6
+```
+
+If there are no differences found between chunks, the source and target datasets can be considered equal:
+
+```bash
+Chunk 1 (1094771 rows) matches!
+Chunk 2 (1094771 rows) matches!
+...
+Chunk 10 (1094770 rows) matches!
+
+All 10947709 records match between 'bigquery|sqlmesh_example.full_model' and 'snowflake|TEST.SQLMESH_EXAMPLE.FULL_MODEL'
+```
+
+!!! info
+
+    Don't forget to specify the `--show-sample` option if you'd like to see a sample of the actual mismatched data!
+
+    Otherwise, only high level statistics for the mismatched rows will be printed.
+
+### Supported engines
+
+Cross-database diffing is supported on all execution engines that [SQLMesh supports](../integrations/overview.md#execution-engines).
\ No newline at end of file
diff --git a/sqlmesh/core/model/kind.py b/sqlmesh/core/model/kind.py
index 06e16dc7..131258c6 100644
--- a/sqlmesh/core/model/kind.py
+++ b/sqlmesh/core/model/kind.py
@@ -797,6 +797,7 @@ class ManagedKind(_ModelKind):
 
 class EmbeddedKind(_ModelKind):
     name: Literal[ModelKindName.EMBEDDED] = ModelKindName.EMBEDDED
+    disable_restatement: t.Literal[True] = True
 
     @property
     def supports_python_models(self) -> bool:
@@ -805,6 +806,7 @@ class EmbeddedKind(_ModelKind):
 
 class ExternalKind(_ModelKind):
     name: Literal[ModelKindName.EXTERNAL] = ModelKindName.EXTERNAL
+    disable_restatement: t.Literal[True] = True
 
 
 class CustomKind(_ModelKind):
diff --git a/sqlmesh/core/plan/builder.py b/sqlmesh/core/plan/builder.py
index a157af92..8dcf5b96 100644
--- a/sqlmesh/core/plan/builder.py
+++ b/sqlmesh/core/plan/builder.py
@@ -600,7 +600,11 @@ class PlanBuilder:
                 raise PlanError("Effective date cannot be in the future.")
 
         for snapshot in self._context_diff.new_snapshots.values():
-            if not snapshot.disable_restatement and not snapshot.full_history_restatement_only:
+            if (
+                snapshot.evaluatable
+                and not snapshot.disable_restatement
+                and not snapshot.full_history_restatement_only
+            ):
                 snapshot.effective_from = self._effective_from
 
     def _is_forward_only_change(self, s_id: SnapshotId) -> bool:
diff --git a/sqlmesh/core/renderer.py b/sqlmesh/core/renderer.py
index 1156f59d..e395c1f6 100644
--- a/sqlmesh/core/renderer.py
+++ b/sqlmesh/core/renderer.py
@@ -424,6 +424,8 @@ class QueryRenderer(BaseExpressionRenderer):
             except ParsetimeAdapterCallError:
                 return None
 
+            expressions = [e for e in expressions if not isinstance(e, exp.Semicolon)]
+
             if not expressions:
                 raise ConfigError(f"Failed to render query at '{self._path}':\n{self._expression}")
 
diff --git a/sqlmesh/core/state_sync/engine_adapter.py b/sqlmesh/core/state_sync/engine_adapter.py
index 526ad9af..bc94db8c 100644
--- a/sqlmesh/core/state_sync/engine_adapter.py
+++ b/sqlmesh/core/state_sync/engine_adapter.py
@@ -430,7 +430,10 @@ class EngineAdapterStateSync(StateSync):
                 unpaused_snapshots[snapshot.unpaused_ts].append(snapshot.snapshot_id)
             elif not is_target_snapshot:
                 target_snapshot = target_snapshots_by_version[(snapshot.name, snapshot.version)]
-                if target_snapshot.normalized_effective_from_ts:
+                if (
+                    target_snapshot.normalized_effective_from_ts
+                    and not target_snapshot.disable_restatement
+                ):
                     # Making sure that there are no overlapping intervals.
                     effective_from_ts = target_snapshot.normalized_effective_from_ts
                     logger.info(


Test patch:
diff --git a/tests/core/test_model.py b/tests/core/test_model.py
index 92534d82..c07f3c73 100644
--- a/tests/core/test_model.py
+++ b/tests/core/test_model.py
@@ -5858,6 +5858,42 @@ def test_trailing_comments():
     assert not model.render_post_statements()
 
 
+def test_comments_in_jinja_query():
+    expressions = d.parse(
+        """
+        MODEL (name db.table);
+
+        JINJA_QUERY_BEGIN;
+        /* some comment A */
+
+        SELECT 1;
+        /* some comment B */
+
+        JINJA_END;
+        """
+    )
+    model = load_sql_based_model(expressions)
+    assert model.render_query().sql() == '/* some comment A */ SELECT 1 AS "1"'
+
+    expressions = d.parse(
+        """
+        MODEL (name db.table);
+
+        JINJA_QUERY_BEGIN;
+        /* some comment A */
+
+        SELECT 1;
+        SELECT 2;
+        /* some comment B */
+
+        JINJA_END;
+        """
+    )
+    model = load_sql_based_model(expressions)
+    with pytest.raises(ConfigError, match=r"Too many statements in query.*"):
+        model.render_query()
+
+
 def test_staged_file_path():
     expressions = d.parse(
         """
diff --git a/tests/core/test_plan.py b/tests/core/test_plan.py
index 71a16e7c..664991ec 100644
--- a/tests/core/test_plan.py
+++ b/tests/core/test_plan.py
@@ -1290,6 +1290,60 @@ def test_effective_from(make_snapshot, mocker: MockerFixture):
     assert updated_snapshot.effective_from is None
 
 
+def test_effective_from_non_evaluatble_model(make_snapshot, mocker: MockerFixture):
+    snapshot = make_snapshot(
+        SqlModel(
+            name="a",
+            kind="EMBEDDED",
+            query=parse_one("select 1, ds FROM b"),
+            start="2023-01-01",
+            dialect="duckdb",
+        )
+    )
+    snapshot.categorize_as(SnapshotChangeCategory.BREAKING)
+
+    updated_snapshot = make_snapshot(
+        SqlModel(
+            name="a",
+            kind="EMBEDDED",
+            query=parse_one("select 2, ds FROM b"),
+            start="2023-01-01",
+            dialect="duckdb",
+        )
+    )
+    updated_snapshot.previous_versions = snapshot.all_versions
+
+    context_diff = ContextDiff(
+        environment="test_environment",
+        is_new_environment=True,
+        is_unfinalized_environment=False,
+        normalize_environment_name=True,
+        create_from="prod",
+        added=set(),
+        removed_snapshots={},
+        modified_snapshots={updated_snapshot.name: (updated_snapshot, snapshot)},
+        snapshots={updated_snapshot.snapshot_id: updated_snapshot},
+        new_snapshots={updated_snapshot.snapshot_id: updated_snapshot},
+        previous_plan_id=None,
+        previously_promoted_snapshot_ids=set(),
+        previous_finalized_snapshots=None,
+    )
+
+    schema_differ = DuckDBEngineAdapter.SCHEMA_DIFFER
+    plan_builder = PlanBuilder(
+        context_diff,
+        schema_differ,
+        forward_only=True,
+        start="2023-01-01",
+        end="2023-03-01",
+        is_dev=True,
+    )
+
+    plan_builder.set_effective_from("2023-02-01")
+    assert plan_builder.build().effective_from == "2023-02-01"
+    assert not updated_snapshot.effective_from
+
+
 def test_new_environment_no_changes(make_snapshot, mocker: MockerFixture):
     snapshot = make_snapshot(SqlModel(name="a", query=parse_one("select 1, ds")))
     snapshot.categorize_as(SnapshotChangeCategory.BREAKING)