Knobit of Data modelling · K-809 · Skill

Recognises an expression running on one engine only

What it means

Check-constraint and default expressions pass through verbatim — the generator orders the column clauses per dialect (Oracle and Snowflake want `DEFAULT` before `NOT NULL`) but it cannot translate an expression. Look for vendor functions, regex operators, `||` versus `+`, and date arithmetic.

How it is assessed

Flags the deficient case and passes the acceptable one. Deficient: `ck_fact_payment_reference` written as `payment_reference ~ '^[A-Z0-9]{8,24}$'`; it generates on all five targets and deploys only on Postgres. `COALESCE(valid_to_date, 'infinity')` in `ck_dim_account_version_dates` fails everywhere but Postgres Acceptable: Portable expressions only — comparisons, `BETWEEN`, `IN`, `IS NULL`, `LENGTH`: `LENGTH(payment_reference) BETWEEN 8 AND 24` as the constraint and the pattern as a `validity` DQ rule; a closed end date instead of `'infinity'`

Builds on

Explains SQL dialect differencesK-852 · Knowledge

Needed by

None