Cuallee satisfies check usage

[FShinoda]

Greetings!

Can anyone give me an example of how should I use the .satisfies() check?
I know it is supposed to be a SQL expression, but could I use something like this?:

SELECT 1 FROM table_a INNER JOIN table_b ON table_a.id = table_b.id

If this expression is not appropriate, how could I assert a FK constraint?
I am using pySpark btw.

Thanks in advance!!

[canimus]

Data Prep

from cuallee import Check, CheckLevel
from pyspark.sql import SparkSession, Row, DataFrame

spark = SparkSession.builder.getOrCreate()

data = [
    Row(country="Brazil", color="green", population=214e6),
    Row(country="Mexico", color="white", population=126e6),
    Row(country="China", color="red", population=1.4e9),
]

df = spark.createDataFrame(data)
df.show(truncate=False)

DataFrame

+-------+-----+----------+
|country|color|population|
+-------+-----+----------+
|Brazil |green|2.14E8    |
|Mexico |white|1.26E8    |
|China  |red  |1.4E9     |
+-------+-----+----------+

Check

check = Check(CheckLevel.WARNING, "Satisfies")
check.satisfies("color", "(color like 'green') AND (population > 2000000)")
check.validate(df).show()

Result

+---+-------------------+---------+-------+------+---------+--------------------+----+----------+------------------+--------------+------+
| id|          timestamp|    check|  level|column|     rule|               value|rows|violations|         pass_rate|pass_threshold|status|
+---+-------------------+---------+-------+------+---------+--------------------+----+----------+------------------+--------------+------+
|  1|2023-07-19 19:41:21|Satisfies|WARNING| color|satisfies|(color like 'gree...|   3|       2.0|0.3333333333333333|           1.0|  FAIL|
+---+-------------------+---------+-------+------+---------+--------------------+----+----------+------------------+--------------+------+

Only one row matches the predicate of color == green AND population > 2M.
As you can see, you may use this check for conditional validations, or when you have combination of multiple predicates at once.

About FK

I personally haven't thought about the use of cuallee for referential integrity constraints, however I think spark itself can help you with that.

data2 = [
    Row(country="Brazil", continent="America"),
    Row(country="Slovenia", continent="Europe"),
]
df2 = spark.createDataFrame(data2)
df2.show()

Second Dataframe

+--------+---------+
| country|continent|
+--------+---------+
|  Brazil|  America|
|Slovenia|   Europe|
+--------+---------+

FK Check

check = Check(CheckLevel.ERROR, "ForeignKey")
check.is_complete("continent")
check.validate(df.join(df2, "country", how="left")).show()

Final Result

+---+-------------------+----------+-----+---------+-----------+-----+----+----------+------------------+--------------+------+
| id|          timestamp|     check|level|   column|       rule|value|rows|violations|         pass_rate|pass_threshold|status|
+---+-------------------+----------+-----+---------+-----------+-----+----+----------+------------------+--------------+------+
|  1|2023-07-19 19:41:21|ForeignKey|ERROR|continent|is_complete|  N/A|   3|       2.0|0.3333333333333333|           1.0|  FAIL|
+---+-------------------+----------+-----+---------+-----------+-----+----+----------+------------------+--------------+------+

As you can see, there is only one row that matches the integrity predicate. left_semi and left_anti join types in pyspark can help you in that area.

Hope it helps @FShinoda . Success!

[maltzsama]

Could a foreign key validation be created? Something like a has_fk? Does it make sense to add this to the project? @canimus

[canimus]

Hi @maltzsama considering a fk will require evaluating 2 dataframes at a time, onless otherwise joined. I think the foreign key validation can be achieved through the use of semi and anti joins. Perhaps one idea will be to build under the Control entity, not on the check, and in the control, set operations like inclusion or intersections, like keys could make sense.