Repository: TobikoData/sqlmesh
Pull request #2644 (2024-05-21 17:45:23)

Feat: Improve the CLI output of the table diff command
The following improvements have been introduced:
- Split `COMMON`, which represented the number of joined rows ,into `FULL MATCH` and `PARTIAL MATCH` to show the number of rows that are identical vs the number of rows with only  a partial match accordingly.
- Add percentage values for `FULL MATCHES` and `PARTIAL MATCHES`.
- Only display the number of rows if it's not 0.
- Add ability to set the number of decimal places to use when comparing floating point columns. This is set to 3 by default.

<img width="982" alt="Screenshot 2024-05-21 at 10 46 42 AM" src="https://github.com/TobikoData/sqlmesh/assets/7747479/5468e743-75fa-4d0a-91cd-8c27a5751c94">



sungchun12: This is out of the scope for this PR, but is there a reason we don't show data diffs side by side like a git diff? We do it in the UI but not for the CLI. I imagine a lot of people want something similar in the CLI.

Example library that does this: https://github.com/paulfitz/daff

![image](https://github.com/TobikoData/sqlmesh/assets/19176976/dd6e894e-b775-4e4f-8980-c42545e85bee)



izeigerman: @sungchun12 , thanks, this is definitely on my mind! I even submitted an issue at the same time as this PR: https://github.com/TobikoData/sqlmesh/issues/2645. Any chance you can share this info there?
treysp: I naturally compare the row counts percentages and counts, and full match 6 is not 92.31% of 7. (I recognize why it's calculated the way it is, but I don't think we can expect users to.)

I think that calculation is going to be confusing unless we display percentages that sum to 100%.
izeigerman: @treysp I'm not sure we can, in good faith, show 100% when the number of rows doesn't match, right?
treysp: I meant show a percentage next to each count such that all the percentages sum to 100% - that way the user can tell how the denominator was calculated
izeigerman: @treysp ah, that's a good point, thanks. Updated my PR:

![Uploading Screenshot 2024-05-21 at 11.57.48 AM 2.png…]()

sungchun12: > @sungchun12 , thanks, this is definitely on my mind! I even submitted an issue at the same time as this PR: #2645. Any chance you can share this info there?

will do!

Patch:
diff --git a/.circleci/continue_config.yml b/.circleci/continue_config.yml
index d662de4a..9924fd86 100644
--- a/.circleci/continue_config.yml
+++ b/.circleci/continue_config.yml
@@ -116,9 +116,6 @@ jobs:
     steps:
       - halt_unless_core
       - checkout
-      - run:
-          name: Install dependencies
-          command: make install-dev
       - run:
           name: Run the migration test
           command: ./.circleci/test_migration.sh
diff --git a/docs/guides/tablediff.md b/docs/guides/tablediff.md
index a4eb8754..c0c02080 100644
--- a/docs/guides/tablediff.md
+++ b/docs/guides/tablediff.md
@@ -88,24 +88,20 @@ $ sqlmesh table_diff prod:dev sqlmesh_example.incremental_model --show-sample
 Schema Diff Between 'PROD' and 'DEV' environments for model 'sqlmesh_example.incremental_model':
 └── Schemas match
 
-
 Row Counts:
-├──  COMMON: 6 rows
-├──  PROD ONLY: 1 rows
-└──  DEV ONLY: 0 rows
+├──  FULL MATCH: 6 rows (92.31%)
+└──  PROD ONLY: 1 rows
 
 COMMON ROWS column comparison stats:
          pct_match
-item_id       83.3
+item_id      100.0
 
 
 COMMON ROWS sample data differences:
- id         ds  PROD__item_id  DEV__item_id
-  3 2020-01-03              3           4.0
-
+  All joined rows match
 
 PROD ONLY sample rows:
- id         ds  item_id
+ id event_date  item_id
   1 2020-01-01        2
 ```
 
@@ -124,29 +120,26 @@ Recall that SQLMesh models are accessible via views in the database. In the `pro
 We can replicate the comparison in the previous section by comparing the model views directly. Because we are passing the view names directly, the command needs to manually specify that the join should be on the `id` and `ds` columns with the `-o id -o ds` flags.
 
 ```bash linenums="1"
-$ sqlmesh table_diff sqlmesh_example.incremental_model:sqlmesh_example__dev.incremental_model -o id -o ds --show-sample
+$ sqlmesh table_diff sqlmesh_example.incremental_model:sqlmesh_example__dev.incremental_model -o id -o event_date --show-sample
 
 Schema Diff Between 'SQLMESH_EXAMPLE.INCREMENTAL_MODEL' and 'SQLMESH_EXAMPLE__DEV.INCREMENTAL_MODEL':
 └── Schemas match
 
 
 Row Counts:
-├──  COMMON: 6 rows
-├──  SQLMESH_EXAMPLE.INCREMENTAL_MODEL ONLY: 1 rows
-└──  SQLMESH_EXAMPLE__DEV.INCREMENTAL_MODEL ONLY: 0 rows
+├──  FULL MATCH: 6 rows (92.31%)
+└──  SQLMESH_EXAMPLE.INCREMENTAL_MODEL ONLY: 1 rows
 
 COMMON ROWS column comparison stats:
          pct_match
-item_id       83.3
+item_id      100.0
 
 
 COMMON ROWS sample data differences:
- id         ds  s__item_id  t__item_id
-  3 2020-01-03           3         4.0
-
+  All joined rows match
 
 SQLMESH_EXAMPLE.INCREMENTAL_MODEL ONLY sample rows:
- id         ds  item_id
+ id event_date  item_id
   1 2020-01-01        2
 ```
 
diff --git a/docs/reference/cli.md b/docs/reference/cli.md
index 1c517942..aea05992 100644
--- a/docs/reference/cli.md
+++ b/docs/reference/cli.md
@@ -389,13 +389,16 @@ Usage: sqlmesh table_diff [OPTIONS] SOURCE:TARGET [MODEL]
   Show the diff between two tables.
 
 Options:
-  -o, --on TEXT    The column to join on. Can be specified multiple times. The
-                   model grain will be used if not specified.
-  --where TEXT     An optional where statement to filter results.
-  --limit INTEGER  The limit of the sample dataframe.
-  --show-sample    Show a sample of the rows that differ. With many columns,
-                   the output can be very wide.
-  --help           Show this message and exit.
+  -o, --on TEXT           The column to join on. Can be specified multiple
+                          times. The model grain will be used if not
+                          specified.
+  --where TEXT            An optional where statement to filter results.
+  --limit INTEGER         The limit of the sample dataframe.
+  --show-sample           Show a sample of the rows that differ. With many
+                          columns, the output can be very wide.
+  -d, --decimals INTEGER  The number of decimal places to keep when comparing
+                          floating point columns. Default: 3
+  --help                  Show this message and exit.
 ```
 
 ## table_name
diff --git a/setup.py b/setup.py
index 5f706c4f..6add3dd7 100644
--- a/setup.py
+++ b/setup.py
@@ -46,7 +46,7 @@ setup(
         "requests",
         "rich[jupyter]",
         "ruamel.yaml",
-        "sqlglot[rs]~=23.17.0",
+        "sqlglot[rs]~=24.0.0",
     ],
     extras_require={
         "bigquery": [
diff --git a/sqlmesh/cli/main.py b/sqlmesh/cli/main.py
index 26c62bdc..217a7de6 100644
--- a/sqlmesh/cli/main.py
+++ b/sqlmesh/cli/main.py
@@ -702,6 +702,13 @@ def create_external_models(obj: Context) -> None:
     is_flag=True,
     help="Show a sample of the rows that differ. With many columns, the output can be very wide.",
 )
+@click.option(
+    "-d",
+    "--decimals",
+    type=int,
+    default=3,
+    help="The number of decimal places to keep when comparing floating point columns. Default: 3",
+)
 @click.pass_obj
 @error_handler
 @cli_analytics
diff --git a/sqlmesh/core/console.py b/sqlmesh/core/console.py
index bd3db557..e386484e 100644
--- a/sqlmesh/core/console.py
+++ b/sqlmesh/core/console.py
@@ -966,9 +966,22 @@ class TerminalConsole(Console):
             target_name = row_diff.target_alias.upper()
 
         tree = Tree("[b]Row Counts:[/b]")
-        tree.add(f" [b][blue]COMMON[/blue]:[/b] {row_diff.join_count} rows")
-        tree.add(f" [b][yellow]{source_name} ONLY[/yellow]:[/b] {row_diff.s_only_count} rows")
-        tree.add(f" [b][green]{target_name} ONLY[/green]:[/b] {row_diff.t_only_count} rows")
+        if row_diff.full_match_count:
+            tree.add(
+                f" [b][cyan]FULL MATCH[/cyan]:[/b] {row_diff.full_match_count} rows ({row_diff.full_match_pct}%)"
+            )
+        if row_diff.partial_match_count:
+            tree.add(
+                f" [b][blue]PARTIAL MATCH[/blue]:[/b] {row_diff.partial_match_count} rows ({row_diff.partial_match_pct}%)"
+            )
+        if row_diff.s_only_count:
+            tree.add(
+                f" [b][yellow]{source_name} ONLY[/yellow]:[/b] {row_diff.s_only_count} rows ({row_diff.s_only_pct}%)"
+            )
+        if row_diff.t_only_count:
+            tree.add(
+                f" [b][green]{target_name} ONLY[/green]:[/b] {row_diff.t_only_count} rows ({row_diff.t_only_pct}%)"
+            )
         self.console.print("\n", tree)
 
         self.console.print("\n[b][blue]COMMON ROWS[/blue] column comparison stats:[/b]")
diff --git a/sqlmesh/core/context.py b/sqlmesh/core/context.py
index 2717c773..c03dd35d 100644
--- a/sqlmesh/core/context.py
+++ b/sqlmesh/core/context.py
@@ -1206,6 +1206,7 @@ class GenericContext(BaseContext, t.Generic[C]):
         limit: int = 20,
         show: bool = True,
         show_sample: bool = True,
+        decimals: int = 3,
     ) -> TableDiff:
         """Show a diff between two tables.
 
@@ -1219,6 +1220,7 @@ class GenericContext(BaseContext, t.Generic[C]):
             limit: The limit of the sample dataframe.
             show: Show the table diff output in the console.
             show_sample: Show the sample dataframe in the console. Requires show=True.
+            decimals: The number of decimal places to keep when comparing floating point columns.
 
         Returns:
             The TableDiff object containing schema and summary differences.
@@ -1263,6 +1265,7 @@ class GenericContext(BaseContext, t.Generic[C]):
             target_alias=target_alias,
             model_name=model.name if model_or_snapshot else None,
             limit=limit,
+            decimals=decimals,
         )
         if show:
             self.console.show_schema_diff(table_diff.schema_diff())
diff --git a/sqlmesh/core/lineage.py b/sqlmesh/core/lineage.py
index 95b674fc..e133fca8 100644
--- a/sqlmesh/core/lineage.py
+++ b/sqlmesh/core/lineage.py
@@ -90,6 +90,9 @@ def column_description(context: Context, model_name: str, column: str) -> t.Opti
     """Returns a column's description, inferring if needed."""
     model = context.get_model(model_name)
 
+    if not model:
+        return None
+
     if column in model.column_descriptions:
         return model.column_descriptions[column]
 
diff --git a/sqlmesh/core/table_diff.py b/sqlmesh/core/table_diff.py
index 05d91468..fbd58156 100644
--- a/sqlmesh/core/table_diff.py
+++ b/sqlmesh/core/table_diff.py
@@ -86,16 +86,49 @@ class RowDiff(PydanticModel, frozen=True):
         """Count of successfully joined rows."""
         return int(self.stats["join_count"])
 
+    @property
+    def full_match_count(self) -> int:
+        """The number of rows for which shared columns have same values."""
+        return int(self.stats["full_match_count"])
+
+    @property
+    def full_match_pct(self) -> float:
+        """The percentage of rows for which shared columns have same values."""
+        return self._pct(2 * self.full_match_count)
+
+    @property
+    def partial_match_count(self) -> int:
+        """The number of rows for which some shared columns have same values."""
+        return self.join_count - self.full_match_count
+
+    @property
+    def partial_match_pct(self) -> float:
+        """The percentage of rows for which some shared columns have same values."""
+        return self._pct(2 * self.partial_match_count)
+
     @property
     def s_only_count(self) -> int:
         """Count of rows only present in source."""
         return int(self.stats["s_only_count"])
 
+    @property
+    def s_only_pct(self) -> float:
+        """The percentage of rows that are only present in source."""
+        return self._pct(self.s_only_count)
+
     @property
     def t_only_count(self) -> int:
         """Count of rows only present in target."""
         return int(self.stats["t_only_count"])
 
+    @property
+    def t_only_pct(self) -> float:
+        """The percentage of rows that are only present in target."""
+        return self._pct(self.t_only_count)
+
+    def _pct(self, numerator: int) -> float:
+        return round((numerator / (self.source_count + self.target_count)) * 100, 2)
+
 
 class TableDiff:
     """Calculates differences between tables, taking into account schema and row level differences."""
@@ -111,6 +144,7 @@ class TableDiff:
         source_alias: t.Optional[str] = None,
         target_alias: t.Optional[str] = None,
         model_name: t.Optional[str] = None,
+        decimals: int = 3,
     ):
         self.adapter = adapter
         self.source = source
@@ -119,6 +153,7 @@ class TableDiff:
         self.where = exp.condition(where, dialect=self.dialect) if where else None
         self.limit = limit
         self.model_name = model_name
+        self.decimals = decimals
 
         # Support environment aliases for diff output improvement in certain cases
         self.source_alias = source_alias
@@ -187,9 +222,20 @@ class TableDiff:
                     t_index.append(col)
             index_cols = list(dict.fromkeys(index_cols))
 
+            matched_columns = {
+                c: t for c, t in self.source_schema.items() if t == self.target_schema.get(c)
+            }
+
+            def _column_expr(name: str, table: str) -> exp.Expression:
+                if matched_columns[name].this in exp.DataType.FLOAT_TYPES:
+                    return exp.func(
+                        "ROUND", exp.column(name, table), exp.Literal.number(self.decimals)
+                    )
+                return exp.column(name, table)
+
             comparisons = [
                 exp.Case()
-                .when(exp.column(c, "s").eq(exp.column(c, "t")), exp.Literal.number(1))
+                .when(_column_expr(c, "s").eq(_column_expr(c, "t")), exp.Literal.number(1))
                 .when(
                     exp.column(c, "s").is_(exp.Null()) & exp.column(c, "t").is_(exp.Null()),
                     exp.Literal.number(1),
@@ -200,8 +246,7 @@ class TableDiff:
                 )
                 .else_(exp.Literal.number(0))
                 .as_(f"{c}_matches")
-                for c, t in self.source_schema.items()
-                if t == self.target_schema.get(c)
+                for c, t in matched_columns.items()
             ]
 
             def name(e: exp.Expression) -> str:
@@ -231,7 +276,7 @@ class TableDiff:
                         ),
                         1,
                         0,
-                    ).as_("rows_joined"),
+                    ).as_("row_joined"),
                     *comparisons,
                 )
                 .from_(exp.alias_(self.source, "s"))
@@ -244,6 +289,22 @@ class TableDiff:
                 .where(self.where)
             )
 
+            query = exp.select(
+                "*",
+                exp.Case()
+                .when(
+                    exp.and_(
+                        *[
+                            exp.column(f"{c}_matches").eq(exp.Literal.number(1))
+                            for c in matched_columns
+                        ]
+                    ),
+                    exp.Literal.number(1),
+                )
+                .else_(exp.Literal.number(0))
+                .as_("row_full_match"),
+            ).from_(query.subquery("stats"))
+
             query = quote_identifiers(query, dialect=self.dialect)
             temp_table = exp.table_("diff", db="sqlmesh_temp", quoted=True)
 
@@ -251,7 +312,8 @@ class TableDiff:
                 summary_query = exp.select(
                     exp.func("SUM", "s_exists").as_("s_count"),
                     exp.func("SUM", "t_exists").as_("t_count"),
-                    exp.func("SUM", "rows_joined").as_("join_count"),
+                    exp.func("SUM", "row_joined").as_("join_count"),
+                    exp.func("SUM", "row_full_match").as_("full_match_count"),
                     *(exp.func("SUM", name(c)).as_(c.alias) for c in comparisons),
                 ).from_(table)
 
@@ -272,7 +334,7 @@ class TableDiff:
                         )
                     )
                     .from_(table)
-                    .where(exp.column("rows_joined").eq(exp.Literal.number(1)))
+                    .where(exp.column("row_joined").eq(exp.Literal.number(1)))
                 )
                 column_stats = (
                     self.adapter.fetchdf(column_stats_query, quote_identifiers=True)
@@ -283,7 +345,7 @@ class TableDiff:
                     .drop(index=index_cols)
                 )
 
-                sample_filter_cols = ["s_exists", "t_exists", "rows_joined"]
+                sample_filter_cols = ["s_exists", "t_exists", "row_joined", "row_full_match"]
                 sample_query = (
                     exp.select(
                         *(sample_filter_cols),
@@ -332,13 +394,13 @@ class TableDiff:
                         )
                         for c, n in joined_renamed_cols.items()
                     }
-                joined_sample = sample[sample["rows_joined"] == 1][joined_sample_cols]
+                joined_sample = sample[sample["row_joined"] == 1][joined_sample_cols]
                 joined_sample.rename(
                     columns=joined_renamed_cols,
                     inplace=True,
                 )
 
-                s_sample = sample[(sample["s_exists"] == 1) & (sample["rows_joined"] == 0)][
+                s_sample = sample[(sample["s_exists"] == 1) & (sample["row_joined"] == 0)][
                     [
                         *[f"s__{c}" for c in index_cols],
                         *[f"s__{c}" for c in self.source_schema if c not in index_cols],
@@ -348,7 +410,7 @@ class TableDiff:
                     columns={c: c.replace("s__", "") for c in s_sample.columns}, inplace=True
                 )
 
-                t_sample = sample[(sample["t_exists"] == 1) & (sample["rows_joined"] == 0)][
+                t_sample = sample[(sample["t_exists"] == 1) & (sample["row_joined"] == 0)][
                     [
                         *[f"t__{c}" for c in index_cols],
                         *[f"t__{c}" for c in self.target_schema if c not in index_cols],
diff --git a/sqlmesh/magics.py b/sqlmesh/magics.py
index 1ae6762f..dd7c7987 100644
--- a/sqlmesh/magics.py
+++ b/sqlmesh/magics.py
@@ -598,6 +598,12 @@ class SQLMeshMagics(Magics):
         action="store_true",
         help="Show a sample of the rows that differ. With many columns, the output can be very wide.",
     )
+    @argument(
+        "--decimals",
+        type=int,
+        default=3,
+        help="The number of decimal places to keep when comparing floating point columns. Default: 3",
+    )
     @line_magic
     @pass_sqlmesh_context
     def table_diff(self, context: Context, line: str) -> None:
@@ -615,6 +621,7 @@ class SQLMeshMagics(Magics):
             where=args.where,
             limit=args.limit,
             show_sample=args.show_sample,
+            decimals=args.decimals,
         )
 
     @magic_arguments()


Test patch:
diff --git a/.circleci/test_migration.sh b/.circleci/test_migration.sh
index eb2ed9f2..fc869eb4 100755
--- a/.circleci/test_migration.sh
+++ b/.circleci/test_migration.sh
@@ -16,6 +16,9 @@ fi
 
 git checkout $LAST_TAG
 
+# Install dependencies from the previous release.
+make install-dev
+
 cp -r ./examples/sushi $TMP_DIR
 
 # Run initial plan
@@ -27,6 +30,9 @@ popd
 # Switch back to the starting state of the repository
 git checkout -
 
+# Install updated dependencies.
+make install-dev
+
 # Migrate and make sure the diff is empty
 pushd $SUSHI_DIR
 sqlmesh --config $CONFIG_NAME migrate
diff --git a/tests/core/test_table_diff.py b/tests/core/test_table_diff.py
index fb4fb685..b4b17e79 100644
--- a/tests/core/test_table_diff.py
+++ b/tests/core/test_table_diff.py
@@ -1,4 +1,5 @@
 import pytest
+import pandas as pd
 from sqlglot import exp
 
 from sqlmesh.core.config import AutoCategorizationMode, CategorizerConfig
@@ -81,9 +82,55 @@ def test_data_diff(sushi_context_fixed_date):
     assert row_diff.source_count == 17
     assert row_diff.target_count == 18
     assert row_diff.join_count == 17
+    assert row_diff.full_match_count == 17
+    assert row_diff.full_match_pct == 97.14
+    assert row_diff.partial_match_count == 0.0
+    assert row_diff.partial_match_pct == 0.0
     assert row_diff.s_only_count == 0
     assert row_diff.t_only_count == 1
     assert row_diff.sample.shape == (1, 12)
     assert row_diff.joined_sample.shape == (0, 2)
     assert row_diff.s_sample.shape == (0, 6)
     assert row_diff.t_sample.shape == (1, 6)
+
+
+@pytest.mark.slow
+def test_data_diff_decimals(sushi_context_fixed_date):
+    engine_adapter = sushi_context_fixed_date.engine_adapter
+
+    engine_adapter.ctas(
+        "table_diff_source",
+        pd.DataFrame(
+            {
+                "key": [1, 2, 3],
+                "value": [1.0, 2.0, 3.1233],
+            }
+        ),
+    )
+
+    engine_adapter.ctas(
+        "table_diff_target",
+        pd.DataFrame(
+            {
+                "key": [1, 2, 3],
+                "value": [1.0, 2.0, 3.1234],
+            }
+        ),
+    )
+
+    diff = sushi_context_fixed_date.table_diff(
+        source="table_diff_source",
+        target="table_diff_target",
+        on=["key"],
+    )
+    assert diff.row_diff().full_match_count == 3
+    assert diff.row_diff().partial_match_count == 0
+
+    diff = sushi_context_fixed_date.table_diff(
+        source="table_diff_source",
+        target="table_diff_target",
+        on=["key"],
+        decimals=4,
+    )
+    assert diff.row_diff().full_match_count == 2
+    assert diff.row_diff().partial_match_count == 1
diff --git a/tests/integrations/jupyter/test_magics.py b/tests/integrations/jupyter/test_magics.py
index 662729bf..a84a2b28 100644
--- a/tests/integrations/jupyter/test_magics.py
+++ b/tests/integrations/jupyter/test_magics.py
@@ -636,9 +636,7 @@ def test_table_diff(notebook, loaded_sushi_context, convert_all_html_output_to_t
         """Schema Diff Between 'DEV' and 'PROD' environments for model 'sushi.top_waiters':
 └── Schemas match""",
         """Row Counts:
-├──  COMMON: 8 rows
-├──  DEV ONLY: 0 rows
-└──  PROD ONLY: 0 rows""",
+└──  FULL MATCH: 8 rows (100.0%)""",
         """COMMON ROWS column comparison stats:""",
         """pct_match
 revenue      100.0""",
diff --git a/tests/schedulers/airflow/test_dag_generator.py b/tests/schedulers/airflow/test_dag_generator.py
index fe03748d..1a12715e 100644
--- a/tests/schedulers/airflow/test_dag_generator.py
+++ b/tests/schedulers/airflow/test_dag_generator.py
@@ -19,6 +19,8 @@ from sqlmesh.utils.date import to_datetime
 
 
 class TestSubmitOperator(BaseOperator):
+    __test__ = False  # prevent pytest trying to collect this as a test class
+
     def __init__(
         self,
         *,