Catalog Integration

Lexega can operate fully offline with no database connection. However, when you provide a catalog snapshot, analysis becomes significantly more precise—detecting issues that are impossible to find from semantics alone.

The catalog is an enhancer, not a perimeter. Tables missing from the snapshot are analyzed normally—just without catalog-enhanced signals. An incomplete snapshot makes analysis better where it covers, never worse where it doesn't.

Supported Platforms

What the Catalog Enables

Catalog Contents

A catalog snapshot is a JSON file containing metadata extracted from your data platform:

{
  "schema_version": 2,
  "generated_at": "2025-01-15T10:30:00Z",
  "provider": "snowflake",
  "databases": [
    {
      "name": "ANALYTICS",
      "schemas": [
        {
          "name": "PUBLIC",
          "tables": [
            {
              "name": "CUSTOMERS",
              "kind": "Table",
              "row_count_estimate": 1200000,
              "columns": [
                { "name": "ID", "data_type": "NUMBER(38,0)", "nullable": false },
                { "name": "EMAIL", "data_type": "VARCHAR(256)", "nullable": true }
              ],
              "constraints": [
                { "kind": "PrimaryKey", "columns": ["ID"] }
              ]
            }
          ]
        }
      ]
    }
  ],
  "policies": [],
  "policy_references": [],
  "grants": { "object_privileges": [] }
}

Names can be plain strings (as above) or objects with an optional case_sensitive flag for mixed-case identifiers: { "name": "My Table", "case_sensitive": true }. The sidecar generates the appropriate format automatically.

The provider field enables automatic platform-aware behavior—identifier normalization, privilege canonicalization, and grant analysis all adapt accordingly.

Key Metadata Fields

Generating a Catalog Snapshot

lexega-sql catalog pull runs the sidecar for your platform and writes the snapshot. Each platform has its own sidecar binary, so you install only the one you need:

Download the sidecar for your platform from GitHub Releases and place it beside lexega-sql or anywhere on your PATH, under the name shown above. Options after -- are passed to the sidecar.

Snowflake

# With external browser auth
lexega-sql catalog pull \
  --provider snowflake \
  --out .lexega/catalog.json \
  -- --account ACCT --user USER --auth externalbrowser

# With password auth (the password is read from SNOWFLAKE_PASSWORD)
lexega-sql catalog pull \
  --provider snowflake \
  --out .lexega/catalog.json \
  -- --account ACCT --user USER

# Include grant graph (role hierarchy + object privileges)
lexega-sql catalog pull \
  --provider snowflake \
  --out .lexega/catalog.json \
  -- --account ACCT --user USER --auth externalbrowser --include-grants

Snowflake authentication: --auth selects snowflake (password, the default), externalbrowser, jwt (with --private-key-path) or oauth. Secrets are never passed on the command line: the password is read from SNOWFLAKE_PASSWORD and an OAuth token from SNOWFLAKE_TOKEN (--password-env / --token-env name another variable; --password-stdin / --token-stdin read from standard input).

Databricks

# Basic catalog pull (Unity Catalog); the token is read from DATABRICKS_TOKEN
lexega-sql catalog pull \
  --provider databricks \
  --out .lexega/catalog.json \
  -- --workspace-url https://your-workspace.cloud.databricks.com

# With grants (Unity Catalog permissions, users and groups)
lexega-sql catalog pull \
  --provider databricks \
  --out .lexega/catalog.json \
  -- --workspace-url https://your-workspace.cloud.databricks.com --include-grants

# Filter to specific catalogs
lexega-sql catalog pull \
  --provider databricks \
  --out .lexega/catalog.json \
  -- --workspace-url https://your-workspace.cloud.databricks.com --include-db my_catalog

Databricks authentication: Set DATABRICKS_TOKEN to a personal access token (PAT) from your workspace settings (--token-env names another variable; --token-stdin reads the token from standard input). The sidecar uses the Unity Catalog REST API (/api/2.1/unity-catalog/) — no SQL warehouse or cluster required.

Row counts: For Delta tables with statistics collected (via ANALYZE TABLE or auto-stats), row counts and byte sizes are automatically extracted from spark.sql.statistics.numRows and spark.sql.statistics.totalSize in table properties.

SQL Server

# SQL login (the password is read from MSSQL_PASSWORD)
lexega-sql catalog pull \
  --provider mssql \
  --out .lexega/catalog.json \
  -- --server sql.example.com --user lexega_reader

# With grants (object privileges + role membership)
lexega-sql catalog pull \
  --provider mssql \
  --out .lexega/catalog.json \
  -- --server sql.example.com --user lexega_reader --include-grants

# Azure AD or integrated authentication: supply a full connection string
lexega-sql catalog pull \
  --provider mssql \
  --out .lexega/catalog.json \
  -- --connection-string-env MSSQL_DSN

SQL Server authentication: The password is read from MSSQL_PASSWORD (--password-env names another variable). --connection-string-env names a variable holding a complete sqlserver:// connection string, which covers Azure AD and integrated authentication. System databases are skipped unless --include-system-db is given.

Provider Auto-Detection

When analyzing SQL with a catalog snapshot, Lexega automatically detects the provider from the snapshot's provider field:

# No --provider flag needed — auto-detected from snapshot
lexega-sql analyze model.sql --catalog .lexega/catalog.json

You can also explicitly override the provider:

lexega-sql analyze model.sql --catalog .lexega/catalog.json --provider databricks

Sidecar Data Sources

Snowflake Queries

The sidecar runs read-only queries against Snowflake:

Schema Metadata (INFORMATION_SCHEMA)

Grant Graph (ACCOUNT_USAGE) — requires --include-grants

Governance Policies (ACCOUNT_USAGE)

Databricks API Endpoints

The sidecar uses the Unity Catalog REST API (no SQL warehouse needed):

Every request is a GET to the workspace you name.

SQL Server Catalog Views

The sidecar reads sys.* catalog views in each selected database; it does not read table data:

Manual Generation

You can also write the catalog JSON by hand. The full structure and every metadata field are documented in the Catalog Contents section above.

Catalog Commands

# Inspect a catalog snapshot (summary)
lexega-sql catalog inspect .lexega/catalog.json

# Diff two catalog snapshots
lexega-sql catalog diff old.json new.json

catalog inspect prints the snapshot's SHA-256. A report made with --catalog records the same value as catalog_info.catalog_sha256, and a decision record carries it as inputs.catalog_sha256, so you can check which catalog a report or decision was computed against. When a run adds dbt-declared column tags to the snapshot, the value covers the combined state and differs from the file alone.

Using the Catalog in Analysis

Pass the catalog to any analysis command with --catalog:

# Semantic diff with catalog
lexega-sql diff main..HEAD models/ -r --catalog .lexega/catalog.json

# Review with catalog
lexega-sql review main..HEAD models/ -r --catalog .lexega/catalog.json

# Static analysis with catalog
lexega-sql analyze model.sql --catalog .lexega/catalog.json

The provider is auto-detected from the snapshot. No additional flags needed.

Catalog-Enhanced Signals

Q-JOIN-NULL-CENH: Nullable JOIN Column

Without catalog: Cannot detect (no column metadata)

With catalog:

[HIGH] Q-JOIN-NULL-CENH: Silent Data Loss Risk
  JOIN on `orders.user_id = users.id` where `user_id` is nullable
  ↳ NULL values in user_id will be silently dropped from results

Q-JOIN-TYPEMIS-CENH: Type Mismatch in JOIN

Without catalog: Cannot detect

With catalog:

[MEDIUM] Q-JOIN-TYPEMIS-CENH: Type Mismatch in JOIN
  JOIN comparing `orders.customer_id` (VARCHAR) to `customers.id` (NUMBER)
  ↳ Implicit cast may cause performance issues or unexpected matches

Cross-Join Product Estimation

Without catalog: "CROSS JOIN detected" (no size info)

With catalog:

[MEDIUM] Explicit CROSS JOIN between system.lakeflow.zerobus_ingest and
  system.lakeflow.zerobus_stream. Cartesian product (199,439,331,565,824
  rows) may cause performance issues on large tables.
  ↳ left_rows=33,059,072 right_rows=6,032,817

Large Unbounded Table Scan

Without catalog: Cannot detect

With catalog:

[HIGH] Unbounded query on large table without WHERE clause
  ↳ tables=system.access.audit, total_rows=229,743,971,240

Q-JOIN-DISTINCT-MASK-CENH: DISTINCT on JOINs with Non-Unique Keys

Without catalog: Warns about potential duplicates

With catalog:

[HIGH] Q-JOIN-DISTINCT-MASK-CENH: DISTINCT with Non-Unique JOIN Keys
  DISTINCT applied after JOIN on `orders.product_id`
  ↳ `product_id` is not unique (no PK/UNIQUE constraint)
  ↳ products has 50,000 rows—potential fanout before DISTINCT

Q-JOIN-FANOUT-CENH: Unconstrained JOIN Fanout

Without catalog: Cannot detect

With catalog:

[HIGH] Q-JOIN-FANOUT-CENH: Unconstrained JOIN Fanout
  JOIN on `events.session_id = sessions.id` lacks FK or UNIQUE constraint
  ↳ events has 10M rows, sessions has 500K rows
  ↳ Result set may be unexpectedly large

Grant Graph Analysis (GRT-ACCESS-EXP, GRT-ACCESS-EXP-HI, GRT-SYSROLE-EXP, GRT-BROAD-PRIV, GRT-BROAD-PRIV-HI)

With the grants section populated, Lexega can compute effective access changes:

[CRITICAL] GRT-SYSROLE-EXP: Critical System Role Grant
  GRANT ROLE ACCOUNTADMIN TO ROLE data_team
  ↳ data_team inherits all ACCOUNTADMIN privileges
  ↳ Affects 15 users via role inheritance

Identifier Normalization

Lexega automatically normalizes identifiers based on the catalog provider:

This means SQL like SELECT * FROM my_catalog.my_schema.my_table resolves against a Databricks catalog where names are stored in matching case, while a Snowflake query select * from my_table correctly resolves against an uppercase-stored MY_TABLE because Snowflake folds unquoted identifiers to uppercase.

CI/CD Integration

Refresh Catalog in CI

For accurate analysis, refresh the catalog periodically:

Snowflake

# .github/workflows/catalog-refresh.yml
on:
  schedule:
    - cron: '0 6 * * *'  # Daily at 6 AM

jobs:
  refresh:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: |
          lexega-sql catalog pull \
            --provider snowflake \
            --out .lexega/catalog.json \
            -- --account ${{ secrets.SNOWFLAKE_ACCOUNT }} \
               --user ${{ secrets.SNOWFLAKE_USER }} \
               --include-grants
        env:
          SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
      - run: |
          git config user.name "github-actions"
          git config user.email "[email protected]"
          git add .lexega/catalog.json
          git commit -m "chore: refresh catalog snapshot" || exit 0
          git push

Databricks

# .github/workflows/catalog-refresh.yml
on:
  schedule:
    - cron: '0 6 * * *'

jobs:
  refresh:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: |
          lexega-sql catalog pull \
            --provider databricks \
            --out .lexega/catalog.json \
            -- --workspace-url ${{ secrets.DATABRICKS_WORKSPACE_URL }} \
               --include-grants
        env:
          DATABRICKS_TOKEN: ${{ secrets.DATABRICKS_TOKEN }}
      - run: |
          git config user.name "github-actions"
          git config user.email "[email protected]"
          git add .lexega/catalog.json
          git commit -m "chore: refresh catalog snapshot" || exit 0
          git push

Use Cached Catalog in PR Checks

# .github/workflows/sql-review.yml
on: [pull_request]

jobs:
  review:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }
      - run: |
          lexega-sql review ${{ github.event.pull_request.base.sha }}..${{ github.sha }} \
            models/ -r \
            --catalog .lexega/catalog.json \
            --policy .lexega/policy.yaml \
            --env prod

Catalog Staleness

Catalog snapshots can become stale as your schema evolves. Lexega uses row_count_estimate_as_of and bytes_estimate_as_of timestamps to track freshness when available (Snowflake populates these automatically).

Recommendations:

• Refresh daily for actively changing schemas
• Refresh weekly for stable schemas
• For Databricks, run ANALYZE TABLE periodically to keep Delta statistics current

License Requirements

Pulling, inspecting and analyzing with a catalog needs no license key. Without a key, each statement is analyzed as written. The catalog-enhanced signals that depend on semantic analysis need a Team license; they include nullable and unconstrained JOINs, DISTINCT masking and grant graph analysis. Every report says how many rules that affects.