[BUG] Regex Expectations match the whole value on Snowflake but a substring on every other backend

What's wrong

On Snowflake, the four regex Expectations — ExpectColumnValuesToMatchRegex, ExpectColumnValuesToNotMatchRegex, ExpectColumnValuesToMatchRegexList, ExpectColumnValuesToNotMatchRegexList — require the pattern to match the entire column value. On every other backend GX supports (pandas, Spark, and every other SQL dialect) an unanchored pattern matches a substring, as regex users expect. No error is raised: the Expectation simply answers a different question and returns the wrong verdict.

All four metrics share one code path, get_dialect_regex_expression in great_expectations/expectations/metrics/util.py:191-204, which emits <column> REGEXP '<pattern>' for Snowflake. Snowflake documents REGEXP (and its aliases RLIKE / REGEXP_LIKE) as implicitly anchored at both ends — 'ABC' is evaluated as '^ABC$' — so the emitted SQL has whole-value semantics regardless of the pattern the user wrote.

Impact

• Fails open. ExpectColumnValuesToNotMatchRegex(regex="^A") over values that all start with A reports success. A suite that certifies data as clean on PostgreSQL certifies the same bad data as clean on Snowflake.
• Fails closed in the other direction: MatchRegex / MatchRegexList with a partial pattern reports failure on values that do match.
• Present since the Snowflake regex branch was added in 0.17.7 (#8403, 2023) through 1.23.0 — every 1.x release; every user with an unanchored pattern on Snowflake.
• Invisible in the common case: a fully anchored pattern (^…$) behaves identically under both semantics, which is why existing tests pass. Measured against 15 data sources in one CI run; Snowflake was the only one to diverge.
• Workaround: rewrite every partial pattern as .*(pattern).* by hand. Nothing in the result tells a user this is needed.

Reproduction

Observed in CI on da5be9d34 (1.22.0), Python 3.13, Snowflake (run 33717210634, Snowflake cell). The emitting code is unchanged at 673082d4b (1.23.0). The test below is the observed configuration transposed into the per-expectation module; it passes on SQLite and PostgreSQL (the control) and fails when whole-value anchoring is applied.

Add to tests/integration/data_sources_and_expectations/expectations/test_expect_column_values_to_not_match_regex.py (import SnowflakeDatasourceTestConfig alongside the module's other configs):

@parameterize_batch_for_data_sources(
    data_source_configs=[SnowflakeDatasourceTestConfig()], data=DATA
)
def test_unanchored_regex_matches_anywhere_in_value_snowflake(batch_for_datasource: Batch) -> None:
    """An unanchored pattern must match a substring, as it does on every other SQL dialect.

    Every non-null value in COL_A starts with "a", so ``^a`` matches all three and the
    expectation must fail with three unexpected values. Snowflake's ``REGEXP`` operator
    anchors the pattern to the whole value, so ``^a`` matches nothing there and the
    expectation reports success against data that violates it.
    """
    result = batch_for_datasource.validate(
        gxe.ExpectColumnValuesToNotMatchRegex(column=COL_A, regex="^a")
    )
    assert result.result["unexpected_count"] == 3
    assert not result.success

pytest tests/integration/data_sources_and_expectations/expectations/test_expect_column_values_to_not_match_regex.py::test_unanchored_regex_matches_anywhere_in_value_snowflake -m snowflake

Observed (the same configuration in the CI run above — ExpectColumnValuesToNotMatchRegex(column="pattern_code", regex=r"^A") over A100…A600, which should fail, reported success; two siblings likewise):

FAILED test_standard_case[snowflake-expect_column_values_to_not_match_regex] - AssertionError:
  case 'expect_column_values_to_not_match_regex' is not discriminating: its `failing`
  configuration reported success against the shared fixture data
FAILED test_standard_case[snowflake-expect_column_values_to_not_match_regex_list] - (same)
FAILED test_standard_case[snowflake-expect_column_values_to_match_regex_list] - AssertionError:
  the configuration declared as `passing` reported failure   # regex_list=[r"^A", r"[0-9]{3}$"], match_on="all"
3 failed, 49 passed

The test above, under the same whole-value semantics, fails at its first assertion:

E   assert 0 == 3

Expected:

success=False, unexpected_count=3   # "aa", "ab", "ac" all match ^a

Requirements

1. When any of the four regex Expectations runs against a Snowflake data source with a pattern that is not anchored at both ends, a value must count as matching when the pattern matches any substring of it — the same semantics as every other SQL dialect, pandas and Spark.
2. When the pattern is fully anchored (^…$), Snowflake results must be unchanged.
3. Regex results on every other backend must be unchanged; the fix is confined to the Snowflake branch of get_dialect_regex_expression.
4. The public signatures and parameters of the four Expectations must not change.
5. Each of the four Expectations must have at least one unanchored-pattern case running against Snowflake in tests/integration/data_sources_and_expectations/expectations/, so the divergence cannot return unnoticed.

Out of scope: the other dialect branches in get_dialect_regex_expression (ClickHouse's regexp_like call and SQL Server's lack of regex support are separate defects); match_on="all" semantics on Spark (#12194); the LIKE-pattern Expectations, which take a different code path.

Context

• Root cause: great_expectations/expectations/metrics/util.py:191-204. The branch emits custom_op("REGEXP") / custom_op("NOT REGEXP"). Snowflake's REGEXP, RLIKE and REGEXP_LIKE all anchor implicitly; REGEXP_COUNT, REGEXP_INSTR and REGEXP_SUBSTR search substrings.
• Preferred fix: REGEXP_COUNT(column, pattern) > 0 for the positive case and = 0 for the negative. This keeps the user's pattern intact. Wrapping the pattern in .* on both sides is not equivalent — .*a|b.* is not .*(a|b).* — and rewriting patterns is where parity bugs would live.
• Edges to verify on a live Snowflake account once the fix is in: the empty pattern (regex="" matches every value on the other dialects — see the empty_regex case in test_expect_column_values_to_not_match_regex.py), and case sensitivity (REGEXP and REGEXP_COUNT both default to case-sensitive; keep it that way).
• Why it stayed hidden: three of the four regex test modules under tests/integration/data_sources_and_expectations/expectations/ do not run Snowflake at all; the fourth (..._not_match_regex_list.py) runs it on one case only, test_golden_path with patterns x. / a.., which produce the same verdict under both semantics.
• Ruled out: a fixture or harness fault — the same fixture data and the same four configurations pass on BigQuery, Redshift, Databricks, Trino, MySQL, PostgreSQL, SQLite, Spark and both pandas configurations in the same run. Not verified: the fix itself against Snowflake (credential-gated; the snowflake CI lane is the check).
• History: #8048 (closed) is from before Snowflake had a regex branch at all; this is the branch that was added since behaving differently from every other one.
• Also recorded in tests/integration/data_sources_and_expectations/data_source_backlog.md ("Fifteen candidates were measured…"), which names this as the one defect holding Snowflake out of the gallery tier.