DQX compare table check

[neerajborana]

I have two tables and want to use DQX to automatically compare their columns, data types, and row counts (not manually via DataFrames). What is the recommended approach or function in DQX for this type of table-to-table comparison? Sample code or references would be appreciated!

[mwojtyczka]

You should use compare_datasets check. You can find more details about this check here: https://databrickslabs.github.io/dqx/docs/reference/quality_checks/#dataset-level-checks-reference

At the bottom of the examples table you will find more details on how to analyze the comparison results.

There are also usage examples using yaml and classes for each check, and info on how to provide the Reference DataFrames or reference tables.

Example:

from databricks.labs.dqx.rule import DQDatasetRule, DQForEachColRule
from pyspark.sql.types import StructType, StructField, StringType, IntegerType
from databricks.labs.dqx import check_funcs
from datetime import datetime
from databricks.labs.dqx.engine import DQEngine
from databricks.sdk import WorkspaceClient

checks = [
    # compare_datasets check using reference table, exclude columns and check for missing records
    DQDatasetRule(
      criticality="error",
      check_func=check_funcs.compare_datasets,
      columns=["col1", "col2"],
      check_func_kwargs={
        "ref_columns": ["ref_col1", "ref_col2"],
        "ref_df_name": "ref_df_key"
        "exclude_columns": ["col7"],
        "check_missing_records": True,
        "null_safe_row_matching": True,  # treat NULLs as equal when matching rows, default True
        "null_safe_column_value_matching": True  # treat NULLs as equal when comparing column values, default True
      },
    ),
]

ref_df = spark.table("catalog1.schema1.ref_table")
ref_dfs = {"ref_df_key": ref_df}

dq_engine = DQEngine(WorkspaceClient())
output_df = dq_engine.apply_checks(df, checks, ref_dfs=ref_dfs)

[hski-bayer]

@neerajborana Guess you can mark your question as "answered"?

[neerajborana]

My question is how can i count number of distinct records in both tables, or how can i check or validate schema?

[mwojtyczka]

For schema validation please use has_valid_schema check. For row count please use aggregation checks, e.g. is_aggr_not_greater_than (omit group by to get total number of rows).

https://databrickslabs.github.io/dqx/docs/reference/quality_checks/#dataset-level-checks-reference

compare_datasets is for row-level comparison.