Semantics Generator

Automates the creation of dbt semantic_view models for Snowflake by extracting live metadata and generating production-ready SQL via an LLM.

How it works

Snowflake metadata  ──>  LLM (OpenRouter)  ──>  dbt semantic_view .sql  ──>  GitHub PR

1. Metadata extraction (metadata_extractor.py) connects to Snowflake and pulls:
   • Certified metrics from GOVERNANCE_DB.SEMANTICS.METRIC_INVENTORY
   • Column catalog with data types, descriptions, and Horizon privacy tags
   • Table-level comments from INFORMATION_SCHEMA
2. SQL generation (yaml_generator.py) sends the metadata context to an LLM via OpenRouter which produces a valid CREATE OR REPLACE SEMANTIC VIEW statement following Snowflake's clause ordering rules.
3. PR automation (github_pr.py) creates a branch, commits the generated .sql file, and opens a pull request on your GitHub repo.

PII-tagged columns are automatically excluded from facts, dimensions, and metrics.

Quick start

pip install -r requirements.txt

Create a .env file (or export env vars):

# Snowflake
SNOWFLAKE_ACCOUNT=xy12345.us-east-1
SNOWFLAKE_USER=your_user
SNOWFLAKE_PASSWORD=your_password
SNOWFLAKE_WAREHOUSE=COMPUTE_WH
SNOWFLAKE_ROLE=ANALYST
SNOWFLAKE_DATABASE=PROD_DB

# OpenRouter (required for LLM generation)
OPENROUTER_API_KEY=sk-or-...

# GitHub (optional, only for --pr mode)
GITHUB_TOKEN=ghp_...
GITHUB_REPO=rsandy94/enterprise_semantics

Usage

Generate a semantic view

python run_semantic_pipeline.py \
  --schemas SALES ANALYTICS \
  --tables DIM_CUSTOMERS FCT_SAAS_BILLING

With GitHub PR creation

python run_semantic_pipeline.py \
  --schemas SALES \
  --tables DIM_CUSTOMERS FCT_SAAS_BILLING \
  --source-name raw \
  --model minimax \
  --pr \
  --pr-repo rsandy94/enterprise_semantics

Dry run (extract metadata only, skip LLM)

python run_semantic_pipeline.py \
  --schemas SALES \
  --tables DIM_CUSTOMERS FCT_SAAS_BILLING \
  --dry-run \
  --context-out debug_context.json

Extract metadata standalone

python metadata_extractor.py \
  --database PROD_DB \
  --schemas SALES \
  --tables DIM_CUSTOMERS FCT_SAAS_BILLING \
  --out context.json

CLI reference

Flag	Description
--schemas	One or more Snowflake schemas (required)
--tables	One or more table names (required)
--database	Target database (or SNOWFLAKE_DATABASE env var)
--model	OpenRouter model: minimax, qwen, or full org/model id (default: minimax)
--source-name	dbt source name — emits {{ source(...) }} instead of {{ ref(...) }}
--dry-run	Extract context only, skip LLM call
--context-out	Path to dump raw Snowflake context JSON
--out-dir	Output directory for the .sql file
--out-name	Override the output filename
--no-local	Skip writing the .sql file locally
--pr	Create a branch and open a GitHub PR
--pr-repo	owner/repo target (default: $GITHUB_REPO)
--pr-base	Base branch for the PR (default: master)
--pr-draft	Open the PR as a draft
--pr-branch	Override the new branch name
--pr-title	Override the PR title

Authentication

Snowflake auth is configured via CLI flags or env vars. Supported methods:

--authenticator	Description
snowflake	Password auth (default)
externalbrowser	SSO browser flow
oauth	OAuth token (SNOWFLAKE_OAUTH_TOKEN)
snowflake_jwt	Key-pair auth (SNOWFLAKE_PRIVATE_KEY_PATH)

License

MIT