[BUG] column.standard_deviation fails on SQLite when the datasource is created with add_sql instead of add_sqlite

What's wrong

SQLite has no native stddev_samp, so GX ships a two-pass override for the column.standard_deviation metric. That override is registered against SqliteExecutionEngine (great_expectations/execution_engine/sqlite_execution_engine.py:30), and only SqliteDatasource selects that engine.

A user who reaches the same SQLite file through the generic context.data_sources.add_sql(connection_string="sqlite:///...") gets a plain SqlAlchemyExecutionEngine, so the override is not in the registry that engine consults and the base implementation emits stddev_samp(...). Expected: the sample standard deviation, the same value add_sqlite returns. Actual: sqlite3.OperationalError: no such function: stddev_samp, surfaced as a failed Expectation carrying an exception rather than a result.

Both spellings are supported ways to reach a SQLite database, and SQLite is a first-tier data source in docs/docusaurus/docs/help/compatibility_reference.md. add_sql already detects the sqlite connector well enough to warn that SqliteDatasource "may be more appropriate" (great_expectations/datasource/fluent/sql_datasource.py:1393) — but it warns, and then hands back an engine that cannot compute the metric.

Impact

• Crashes; does not return a wrong number. The user gets an exception naming a missing SQL function, with no hint that the fix is to construct the datasource differently.
• Affects every Expectation that resolves column.standard_deviation on a SQLite datasource built with add_sql: ExpectColumnStdevToBeBetween and ExpectColumnValueZScoresToBeLessThan both declare SQLite support, and column.descriptive_stats is affected too.
• Reproduced on develop at e512cd3e5 (1.22.0+14.ge512cd3e5), Python 3.13, SQLite. The divergence is in engine selection, not in any recent change, so it is not specific to a release.
• Workaround: build SQLite datasources with context.data_sources.add_sqlite(...). That selects SqliteExecutionEngine and the override applies.

Reproduction

Reproduced on 1.22.0+14.ge512cd3e5 (e512cd3e5), Python 3.13, SQLite.

This one cannot go through @parameterize_batch_for_data_sources: SqliteBatchTestSetup.make_asset (tests/integration/test_utils/data_source_config/sqlite.py) always builds its datasource with add_sqlite, so the harness has no way to express the generic entry point. The test below is self-contained instead.

Add to tests/integration/data_sources_and_expectations/data_sources/test_sqlite.py:

@pytest.mark.sqlite
@pytest.mark.filterwarnings("ignore:You are using a generic SQLDatasource")
def test_stdev_on_sqlite_reached_through_add_sql(tmp_path: pathlib.Path) -> None:
    """column.standard_deviation must work on SQLite however the datasource was built."""
    db_file = tmp_path / "database.db"
    pd.DataFrame({"amount": list(range(1, 21))}).to_sql(
        "t", sa.create_engine(f"sqlite:///{db_file}"), index=False
    )

    context = gx.get_context(mode="ephemeral")
    batch = (
        context.data_sources.add_sql(
            name="generic_sqlite", connection_string=f"sqlite:///{db_file}"
        )
        .add_table_asset(name="t_asset", table_name="t")
        .add_batch_definition_whole_table("bd")
        .get_batch()
    )

    result = batch.validate(
        gxe.ExpectColumnStdevToBeBetween(column="amount", min_value=0, max_value=100)
    )

    assert result.success
    # Sample standard deviation of 1..20 is sqrt(35).
    assert result.result["observed_value"] == pytest.approx(5.916079783099616)

It needs import pathlib and import great_expectations as gx alongside the imports already in that file.

pytest tests/integration/data_sources_and_expectations/data_sources/test_sqlite.py::test_stdev_on_sqlite_reached_through_add_sql -m sqlite

Observed:

E   assert False
 +  where False = ExpectationValidationResult(success=False, ...).success

great_expectations.exceptions.exceptions.ExecutionEngineError: An SQL execution Exception
occurred.  OperationalError: "(sqlite3.OperationalError) no such function: stddev_samp
[SQL: SELECT stddev_samp(amount) AS "column.standard_deviation"
FROM (SELECT * FROM t WHERE 1 = 1) AS anon_1]"

Expected: the test passes — observed_value == 5.916079783099616. The same test with add_sqlite in place of add_sql passes today.

Requirements

1. When column.standard_deviation is resolved against a SQLite database, GX must return the sample standard deviation regardless of whether the datasource was created with add_sql or add_sqlite — the two must agree to floating-point tolerance on the same table.
2. When an Expectation that depends on column.standard_deviation runs against a SQLite datasource built with add_sql, it must produce a result rather than an ExecutionEngineError.
3. column.standard_deviation must be unchanged on every other backend and on the add_sqlite path, including its current behavior for null values and for columns with fewer than two non-null values (see #12164).
4. The fix must not alter the public signature or return type of data_sources.add_sql, data_sources.add_sqlite, or SQLDatasource.
5. The generic-datasource warning emitted by add_sql for a sqlite connection string must still be emitted; a fix must not silence it.

Out of scope: the separate ExpectColumnStdevToBeBetween failure on columns with fewer than two non-null values (#12164); extending the same treatment to non-SQLite dialects; and any change to which execution engine add_sql picks for dialects other than SQLite.

Context

• Root cause: metric providers are registered per execution-engine class. SqliteDatasource.execution_engine_type returns SqliteExecutionEngine (great_expectations/datasource/fluent/sqlite_datasource.py:172); SQLDatasource returns the base SqlAlchemyExecutionEngine. Nothing in the metric lookup dispatches on dialect_name.
• Two fix shapes, neither attempted: (a) branch on execution_engine.dialect_name inside the base ColumnStandardDeviation._sqlalchemy, the way column_quantile_values.py already branches per dialect — contained, but only fixes this one metric; (b) have SQLDatasource select SqliteExecutionEngine when the connection string resolves to the sqlite dialect — fixes every present and future engine-subclass override at once, but changes engine selection for existing configurations. (b) is the larger blast radius; whoever picks it up should say which they chose and why.
• @column_aggregate_partial(engine=SqliteExecutionEngine) at sqlite_execution_engine.py:30 is the only such registration in the tree today, so this is currently a one-metric problem — but any future SQLite override inherits the same split.
• Ruled out: not a missing optional dependency (SQLite is stdlib), and not a local SQLite build quirk — stddev_samp is not a SQLite function in any build.
• Not verified: whether other dialects with an execution-engine subclass have the same reachable-from-one-entry-point-only problem.