Validation tooling: cross-dialect metric drift and expression purity (happy to upstream)

[gulmezeren2-byte]

Hi — I've been building tooling on top of the spec and the reference validation/validate.py, and I ran into a gap I think is worth discussing.

validate.py verifies that every dialect expression parses. It doesn't verify the two things that make a multi-dialect metric trustworthy:

1. Do a metric's dialects actually agree?

- name: revenue
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: SUM(amount)
      - dialect: SNOWFLAKE
        expression: AVG(amount)      # parses fine; different number per engine

Both expressions are valid SQL, so validation passes — and the same metric silently returns a different number on every warehouse. The same class of bug covers a copy-paste that left one dialect on the wrong column, a drifted constant (* 1.08 vs * 1.18), and a drifted filter (region = 'EU' vs 'US', or > 100 vs >= 100).

2. Is the expression a pure, reproducible read?

A "metric" whose expression calls pg_read_file(...) or depends on NOW() / RANDOM() also parses fine — but it is either a side effect or non-reproducible.

Both are the kind of defect that stays invisible until a number is wrong on a dashboard downstream, which is where a semantic layer hurts most.

---

I wrote a small linter that adds these as a complementary pass, meant to run after validate.py. It is self-contained — pyyaml + sqlglot, the same two dependencies validate.py already uses — and Apache-2.0 licensed:

https://github.com/gulmezeren2-byte/ossie-guard

The design point I'd most like feedback on is how it avoids false positives, since the whole premise of multi-dialect expressions is that they legitimately differ. It never compares rendered SQL; it compares a normalised structural signature (aggregate classes, referenced columns, arithmetic constants, filter predicates). So these all compare equal:

one dialect	the other
AVG(COALESCE(price, 0))	AVG(NVL(price, 0))
SUM(CASE WHEN s = 1 THEN amt ELSE 0 END)	SUM(amt) FILTER (WHERE s = 1)
SUM(CASE WHEN s = 1 THEN amt ELSE 0 END)	SUM(IF(s = 1, amt, 0))
is_active = TRUE	is_active = 1
amt > 100	100 < amt
status IN (1, 2)	status IN (2, 1)
DATE_FORMAT(d, '%Y-%m')	FORMAT_DATE('%Y-%m', d)

It produces zero findings on examples/flights.yaml and examples/tpcds_semantic_model.yaml, which is the property I care about most — a linter that fires on valid models is worse than none.

I'm also explicit about the limits, because overclaiming here would be worse than staying quiet: this is a heuristic drift detector, not an equivalence prover (true SQL equivalence is undecidable). It will not catch a semantic difference that leaves the signature identical — a different join grain, or A AND B vs A OR B over the same columns and values.

---

My question: would checks like these be something you'd want in-tree?

I'd be glad to contribute them into validation/ as a PR — either folded into validate.py behind a flag, or as a separate lint.py — shaped to match your conventions (your DIALECT_MAP, your error format, no new dependencies). If you'd rather they stay an external community tool, that's completely fine too; either way I'm happy to take feedback on the approach, and to adjust the terminology so it matches the spec's language.

Thanks for the work on Ossie.