topic Re: How to use dlt.expect to validate table level constraints? in Data Engineering

How to use dlt.expect to validate table level constraints?

guangyi — Thu, 05 Sep 2024 07:27:21 GMT

I know how to validate the column level constraint, like checking whether the specified column value is larger than target value.

Can I validate some table level constraints? For example, validate whether the total records count of a table is larger than the specified number. Or validate the foreign key between two tables.

I tried this way but it is now working:

@Dlt.expect("valid records count", "count('*') > 7500000")

I also tried to do `expect` inside the DLT pipeline function. The function can run successfully but I cannot find the result in the quality tab or the in event logs

@Dlt.table def bronze_table(): df = spark.read.table("samples.tpch.orders") dlt.expect("valid records count", df.count() > 7500000) return df

Can I achieve this with dlt.expect?

Re: How to use dlt.expect to validate table level constraints?

szymon_dybczak — Thu, 05 Sep 2024 08:12:23 GMT

Hi @guangyi ,

Unfortunately, there is no out of the box solution for this requirement in dlt. But as a workaround you can add an additional view/table to your pipeline that defines an expectation in similar way to below:

CREATE OR REFRESH MATERIALIZED VIEW count_verification( CONSTRAINT no_rows_dropped EXPECT (a_count == your_numbers) ) AS SELECT * FROM (SELECT COUNT(*) AS a_count FROM LIVE.tbla)

The results of this expectation appear in the event log and the Delta Live Tables UI.

Manage data quality with Delta Live Tables - Azure Databricks | Microsoft Learn

Re: How to use dlt.expect to validate table level constraints?

guangyi — Thu, 05 Sep 2024 08:57:59 GMT

Thank you