Supporting SQL query Expectations on PandasExecutionEngine via DuckDB?

[mbkranz]

Today, the query metrics used by UnexpectedRowsExpectation support SQL execution through SqlAlchemyExecutionEngine and Spark SQL through SparkDFExecutionEngine. However, the same SQL-oriented pattern is not available when a Batch is a pandas DataFrame using PandasExecutionEngine.

I'm curious whether there would be interest in using DuckDB as an embedded SQL execution layer for pandas-backed Batches. I’ve been trying this, it seems useful, I may be missing existing context, and I’d like to understand how this fits GX’s architecture.

Conceptually:

Pandas DataFrame
      |
PandasExecutionEngine
      |
    DuckDB
      |
SQL query metrics
      |
UnexpectedRowsExpectation

The DataFrame could be registered with an in-memory DuckDB connection as a relation representing {batch}, allowing SQL query Expectations to work against pandas data without requiring users to first persist or expose the data through a SQLAlchemy Data Source.

For example:

gx.expectations.UnexpectedRowsExpectation(
    unexpected_rows_query="""
        SELECT *
        FROM {batch}
        WHERE end_date < start_date
    """
)

could potentially work against a pandas DataFrame in essentially the same way it already works against SQL- and Spark-backed Batches.

I see a few potential benefits:

• Consistent SQL Expectation authoring across execution engines. The same logical validation could often be expressed similarly for pandas, Spark, and database-backed data.
• Better support for complex cross-column and table-level rules. SQL is often a natural representation for constraints that become cumbersome as pandas masks.
• No separate SQL infrastructure required. DuckDB can query pandas DataFrames directly and run entirely in-process.
• Reuse of GX's existing query-based Expectation model. Rather than introducing a separate validation mechanism for pandas, this could extend the existing query metric pattern.
• A foundation for more portable custom Expectations. Query-based Expectations could potentially become less dependent on the physical representation of the Batch.

Note, this is different then the SQLAlchemy engine. The idea here would be to use DuckDB primarily as an implementation detail or embedded query engine for a pandas-backed Batch, rather than requiring users to model their data as a DuckDB SQL Data Source.

This also relates to a separate discussion I opened around constraint-oriented SQL Expectations built on UnexpectedRowsExpectation, where the user-facing rule is expressed as a constraint that should hold rather than directly as a query returning violating rows see here

A related architectural question is SQL dialect portability. I've also been experimenting with SQLGlot for representing predicates and queries as an AST rather than manipulating SQL strings directly. In principle, an AST/transpilation layer could make it possible for a logical constraint to be rendered for DuckDB, Spark SQL, or the SQL dialect used by a SQLAlchemy-backed Data Source.

Conceptually:

Declarative constraint
        |
      SQL AST
        |
   +----+-------------+
   |          |       |
 DuckDB    Spark SQL  SQLAlchemy dialect
   |          |       |
Pandas DF   Spark DF    Database

I see SQL parsing/transpilation as a separate concern from the basic DuckDB/Pandas integration, and I wouldn't necessarily expect GX to adopt SQLGlot specifically. But if query-based Expectations are intended to become more portable across execution engines, it seems worth considering whether a SQL AST or transpilation layer should be part of that architecture rather than relying entirely on dialect-specific SQL strings.

I'm interested in how the GX maintainers/community would think about this direction:

1. Would DuckDB-backed query metrics for PandasExecutionEngine fit GX's execution engine architecture?
2. Would a separate DuckDBExecutionEngine be more appropriate?
3. Would this be better handled as a documented custom metric/Expectation recipe rather than built into GX?
4. Is there interest in having SQL-based custom Expectations behave consistently across pandas, Spark, and SQL-backed Batches?
5. If portability across SQL dialects is desirable, does GX have a preferred approach to parsing or transpiling SQL, or would that be considered outside the scope of the project?

I've been prototyping this pattern locally and would be interested in contributing either an implementation or a recipe if it aligns with the direction of the project.

[mbkranz]

And... I just saw that there was #12084 that creates a duckdb engine -- is there a way to integrate dataframe sources here (or does it already do this)? @dataders

[joshua-stauffer]

Hey @mbkranz, thanks for the question! looks like duckdb is in the water 🦆 There are a couple interesting threads here, but I'm going to focus on your questions 1-3, and suggest we push the portability questions (4 and 5) to a separate discussion.

Your approach here of putting duckdb behind pandas would definitely be the leaner diff, but implementing the whole ExecutionEngine to support duckdb natively looks cleaner architecturally. If we take that approach, I'd like to see equal support for dataframes, files, and a local duckdb database, but suggest we exclude duckdb as a client to a remote SQL database as well as reading files off of remote object storage (at least for the first pass). #12084 is a POC to derisk the work. @dataders implemented the aggregates and map metrics; that's not quite half the metrics we would need to ship this. That PR doesn't look like it supports dataframes yet, but support is already there at the ExecutionEngine level, and only the data source API would need to be plugged in. Notably, that PR is missing all query metrics, so your UnexpectedRowsExpectation example would not yet work.

Would a first class duckdb data source/execution engine work for you? Or was part of the value specifically getting SQL expectations to work against Pandas data sources? Sounds like you might be interested in making this happen - I'm happy work through an RFC with you, and get this on the roadmap.

Prior art: there was interest in duckdb support in #10738 and #11579, and relevant to the portability discussion, a user on discourse asked about validating an UnexpectedRowsExpectation against Pandas.

[mbkranz]

Thanks for the reply, @joshua-stauffer ! I agree that making duckdb a first-class execution engine is the cleaner long-term architecture, and it would work well for my use case.

The important part for me is a supported in-memory dataframe path. Our workflows span both Pandas and AWS Glue / PySpark, and I am trying to establish a consistent validation approach across them.

I may run this particular production workflow in Spark, but the pandas/dataframe path remains valuable for local validation, development workflows, and other teams’ jobs and -- more broadly -- other project contexts . I am enthusiastic about GX because it bridges detailed data definitions and understandable data-quality rules for engineers and nontechnical stakeholders, while mapping naturally to the data-contract and metadata work we are developing.

Thank you again for the guidance. I agree that the broader portability questions are best kept separate, and I’ll keep following the POC and related discussion as I apply these patterns in our work.