Examples: Exception for DISTINCT Removal

Goal: block DISTINCT removal (which can introduce duplicates), but allow reviewed changes via exceptions.

This example uses a builtin rule (DIFF-WRITE-WHERE-RMV) — no custom rule needed.

Files to commit

.lexega/
  policy.yaml
  exceptions.yaml

1) Policy: block DISTINCT removal in prod

# .lexega/policy.yaml
# yaml-language-server: $schema=/schemas/v1/policy.schema.json
schema_version: 1
policy_id: team-policy
policy_version: 1.0.0

default_action: allow

policies:
  - rule_id: DIFF-DISTINCT-RMV  # DistinctRemoved (builtin diff signal)
    description: Block DISTINCT removal in prod unless exception
    envs: [prod]
    action: block
    requires_exception: true

Builtin rules: Lexega ships with builtin diff signals like DIFF-DISTINCT-RMV (DistinctRemoved). You can reference them directly in policies — no custom rule needed.

2) Run review (CI gate)

lexega-sql review main..HEAD models/ -r \
  --policy .lexega/policy.yaml \
  --env prod \
  --decision-out decision.json \
  --format markdown > review.md

ALLOWED=$(jq -r '.outcome.allowed' decision.json)
test "$ALLOWED" = "true"

3) Exception (optional)

If the DISTINCT removal is reviewed and safe:

# .lexega/exceptions.yaml
# yaml-language-server: $schema=/schemas/v1/exceptions.schema.json
schema_version: 1

exceptions:
  - exception_id: EX-2025-001
    policy_id: team-policy
    rule_id: DIFF-DISTINCT-RMV
    approved_by: data-governance@company.com
    approved_at: "2025-12-31T00:00:00Z"
    reason: "Reviewed DISTINCT removal; query remains duplicate-safe via grouping"
    ticket: DATA-9999
    scope:
      scoped:
        path_prefixes:
          - "models/marts/"
        expires_at: "2026-03-01T00:00:00Z"

Run with exceptions:

lexega-sql review main..HEAD models/ -r \
  --policy .lexega/policy.yaml \
  --exceptions .lexega/exceptions.yaml \
  --env prod \
  --decision-out decision.json

Key Concepts

Builtin vs Custom rules:

• Builtin rules ship with Lexega (e.g., DIFF-DISTINCT-RMV for DistinctRemoved)
• Custom rules let you define your own detection logic
• Policies reference either type by rule ID

This separation keeps policies simple and detection testable.