Proceed in three gated phases, all inside Snowflake
The repository already contains the hard parts (DDL, access model, risk rubric, offline POC). What changes for the full dataset is scale and the requirement that the walkthrough be runnable by hand in Snowsight. The board approved the full-dataset scope and methodology on 2026-10-16.
- Parse & normalize inside Snowflake. Load the CUAD JSON into a
VARIANTtable, then flatten it withLATERAL FLATTENin SQL. No external pipeline, no API. - Generate simulated RBAC + risk metadata at scale. One matter per contract (510),
SQL-generated synthetic layer, role buckets +
matter_access_map, row access policy, masking, and the 9-rule simulated rubric. - Web app on Streamlit in Snowflake. It runs as the caller's role, so RBAC + row access policies apply with no separate login store. External portal (with External OAuth) is a later step.
Gap analysis: 30-doc demo to full dataset
| Area | Demo core | Full dataset | Action |
|---|---|---|---|
| Ingestion | extract_cuad.py --limit 30 | 510 docs, ~40 MB JSON | Add VARIANT load + SQL flatten |
contract_type | 25 inferred types | 249 noisy strings | CORE.ref_contract_type map |
| Matter graph | 5 matters, 6 contracts each | 510 matters (1:1) | Scale synthetic generator |
| Roles | ATTORNEY_0/1, PARALEGAL… | same model, more buckets | Add ATTORNEY_0/1/2 |
| Full text | snippets only | ~27 MB available | Store full text |
| Risk register | 9 rules, 5 matters | 9 rules, 510 matters | Re-tune bands on distribution |
Parse & normalize the full CUAD dataset
Snowflake caps a single VARIANT value at ~16 MB, and the master file is ~40 MB, so it is
split into one document per line (NDJSON) for transport only — all parsing and normalization
runs in SQL. The runbook has the copy-paste steps.
-- 1. stage the NDJSON and land it (one document per row)
COPY INTO RAW.raw_cuad_json (src, source_file)
FROM (SELECT $1, METADATA$FILENAME FROM @RAW.cuad_stage)
FILE_FORMAT = RAW.json_ff PATTERN = '.*cuad_docs.*';
-- 2. normalize in SQL with LATERAL FLATTEN
CREATE OR REPLACE TABLE RAW.raw_cuad_contracts AS
SELECT 'CUAD-' || LPAD(src:ord::string, 4, '0') AS contract_id,
src:title::string AS contract_name,
UPPER(TRIM(SPLIT_PART(src:title::string,'-',-1))) AS contract_type,
src:paragraphs[0]:context::string AS contract_text
FROM RAW.raw_cuad_json;CUADv1.json.Assets: ingest/split_cuad_json.py (NDJSON split), sql/full_dataset/06_full_dataset.sql
(consolidated DDL), and docs/SNOWFLAKE_WALKTHROUGH.md (the manual runbook).
Simulated RBAC + risk metadata
One matter per contract (510 matters). Everything synthetic is prefixed SYNTHETIC and
generated deterministically in SQL — no external generator required.
| Control | Where |
|---|---|
| Access decision is data, not code | SECURITY.matter_access_map(role_name, matter_id, …) |
| Matter wall | SECURITY.matter_access_policy attached to every matter_id object |
| PII protection | SECURITY.pii_mask + data_classification tags |
| Risk rubric | CORE.ref_risk_rule → MARTS.mart_matter_risk (simulated) |
Web application — Streamlit in Snowflake first
| Option | Auth / login | Governance | Verdict |
|---|---|---|---|
| Streamlit in Snowflake | Snowflake SSO/MFA; app runs as caller's role | Inherits RBAC + row access + masking | Recommended first |
| External portal | Build login/SSO + session→role mapping | Duplicates unless External OAuth | Later, if a standalone portal is required |
| Snowpark Container Services / Native App | Snowflake auth | Inherits | Overkill for the demo |
Admin/auditability: native QUERY_HISTORY / ACCESS_HISTORY / LOGIN_HISTORY
and POLICY_REFERENCES for data touches, plus an app-level audit table for UI actions mirroring
poc/access.py.
Board decisions
| Decision | Resolution |
|---|---|
| Snowflake account | New trial account (Enterprise) |
| Normalization location | In-Snowflake VARIANT + SQL |
| Synthetic scale | 510 matters (1:1) |
| Full contract text | Store full text (~27 MB) |
| Web app approach | Streamlit in Snowflake first |
| Publish this roadmap | This page |
Risks & mitigations
| Risk | Impact | Mitigation |
|---|---|---|
Manual steps drift from sql/*.sql | Medium | Runbook embeds the same DDL; keep files authoritative |
contract_type noise (249 types) | Medium | Controlled-vocabulary map; report unmapped as Other |
Unstable contract_id across loads | Medium | Derive from the NDJSON ordinal; document load order |
| Row-policy gaps via joins | High | Attach policy to every matter_id object; run the verification matrix |
| Demo mistaken for real client data | High | SYNTHETIC prefixes + CUAD CC BY 4.0 attribution + disclaimers |
| External app weakens governance | High | Prefer Streamlit in Snowflake; if external, mandate External OAuth + per-user role |
Walk it through
The full step-by-step runbook (load → normalize → synthetic layer → RBAC → risk → verify → teardown)
is docs/SNOWFLAKE_WALKTHROUGH.md, with the consolidated SQL in
sql/full_dataset/06_full_dataset.sql. It needs only a Snowflake trial account and Snowsight — no API.
# transport shaping only (one JSON document per line)
python3 ingest/split_cuad_json.py --json CUADv1.json --out build/cuad_docs.jsonl
# then: upload to @RAW.cuad_stage and run sql/full_dataset/06_full_dataset.sql