User should be able to auto-generate an expectation from a test written in SQL

[eugmandel]

Currently you must use Python and SQLAlchemy to create a new expectation that can be used against a database.

There’s a whole community of data analysts and scientists who would love to author new/custom expectations that can run against their databases for whom SQL is the primary language.

There should be a way to translate a SQL query into a new expectation without writing any Python.

[grauscher]

Would it be possible to do that by creating some kind of "custom expectation factory", that receives the custom query and outputs the custom expectation as a class/function?

[bhcastleton]

I changed the name of this issue to avoid confusion. We've had some people think this issue was about putting expectations on a custom sql dataset. That's not what this issue is about. That can be done currently using a custom sql query as a runtime parameter in validation. This issue is about auto-generating GE expectations from tests written in sql.

[viniciusdsmello]

Hi, I've found this issue during a search for the same issue. Here we want to translate some business rules to expectations, it would be great to build an expectation based on a SQL (i.e. columns created based on some SQL logic) query without the need to create a view only to do it. Does anyone have any news about it?

[kinthaiofficial]

Auto-generating Great Expectations expectations from SQL tests is a fantastic direction — it bridges the gap between "SQL-literate data engineers" and the GX ecosystem.

The value proposition:
Many data teams already have SQL-based data quality tests (dbt tests, SQL assertions in CI). Translating these automatically to GX Expectation Suites means: single source of truth for data quality logic, GX's profiling + documentation + observability for free, no need to learn two different testing syntaxes.

What the mapping looks like:

SQL Test Pattern	GX Expectation
COUNT(*) WHERE col IS NULL = 0	expect_column_values_to_not_be_null
MIN(col) >= 0	expect_column_min_to_be_between
COUNT(DISTINCT col) = COUNT(*)	expect_column_values_to_be_unique
col IN ('A', 'B', 'C')	expect_column_values_to_be_in_set
LENGTH(col) BETWEEN 5 AND 50	expect_column_value_lengths_to_be_between

The hard cases:

• Multi-table join assertions (referential integrity checks) — GX has expect_table_foreign_keys_to_match_reference_dataset but it's complex to auto-generate
• Custom business logic (revenue must equal sum of line items) — these don't map cleanly to built-in expectations; would need custom expectations
• Statistical assertions (fraud rate must be < 2%) — threshold-based expectations need domain knowledge to calibrate

Implementation approach:
SQL parser (sqlglot, sqlparse) → pattern matching on WHERE clauses + aggregation expressions → Expectation instantiation. Could be a GX contrib plugin.

Is this targeting specific SQL dialects (dbt tests, Airflow checks) or general SQL?

[joshua-stauffer]

Recent versions of GX support this use case with the UnexpectedRowsExpectation