# Phase 11.1: Review DDL and Query Syntax — Context

**Gathered:** 2026-03-02
**Status:** Ready for planning

<domain>
## Phase Boundary

Review current DDL and query syntax against Snowflake Semantic Views and redesign both interfaces to be as close to Snowflake's model as possible given our constraints (scalar functions only — native SQL DDL is impossible in Python DuckDB bundles due to `-fvisibility=hidden`).

Deliverables: a short design document (DESIGN.md in phase directory) plus code changes implementing the approved interfaces, plus test updates.

This phase does NOT add new semantic capabilities — it reshapes the existing interface.

</domain>

<decisions>
## Implementation Decisions

### Deliverable
- Write a short `DESIGN.md` in the phase directory with before/after syntax comparison and rationale
- Implement the new DDL and query interfaces in Rust
- Update all tests to use the new syntax
- Reference: Snowflake only (no dbt SL / Cube comparison needed)

### Query function rename
- Rename `semantic_query` → `semantic_view` (no backward-compat alias — clean rename)
- Keep named array parameters: `dimensions := ['region', 'category']` — DuckDB idiomatic
- Keep `WHERE` clause as the filter mechanism — no inline filter parameter
- Query syntax supports table-qualified dimension/metric names: `dimensions := ['o.region', 'c.tier']`

### DDL: replace JSON blob with typed DuckDB STRUCT/LIST parameters
- Replace `define_semantic_view('name', '{"base_table":...}')` (raw JSON string) with named parameters using DuckDB STRUCT and LIST types
- `define_or_replace_semantic_view` and drop variants also updated consistently

New DDL signature:
```sql
SELECT define_semantic_view(
    'sales_view',
    tables           := [{'alias': 'o', 'table': 'orders'}, {'alias': 'c', 'table': 'customers'}],
    relationships    := [{'from_table': 'o', 'to_table': 'c', 'join_columns': [{'from': 'customer_id', 'to': 'id'}]}],
    dimensions       := [{'name': 'region', 'expr': 'o.region', 'source_table': 'o'}],
    time_dimensions  := [{'name': 'order_date', 'expr': 'o.created_at', 'granularity': 'day'}],
    metrics          := [{'name': 'revenue', 'expr': 'sum(o.amount)', 'source_table': 'o'}]
);
```

### DDL struct types (strongly typed, separate lists)
- `tables`: `LIST(STRUCT(alias VARCHAR, table VARCHAR))`
- `relationships`: `LIST(STRUCT(from_table VARCHAR, to_table VARCHAR, join_columns LIST(STRUCT(from VARCHAR, to VARCHAR))))`
  — array of column-pair structs (not parallel arrays) to avoid implicit positional matching
  — supports composite FK keys
- `dimensions`: `LIST(STRUCT(name VARCHAR, expr VARCHAR, source_table VARCHAR))`
  — separate list from `time_dimensions`; no `type`/`granularity` fields needed
- `time_dimensions`: `LIST(STRUCT(name VARCHAR, expr VARCHAR, granularity VARCHAR))`
  — dedicated list, strongly typed; `granularity` values: `day | week | month | year`
- `metrics`: `LIST(STRUCT(name VARCHAR, expr VARCHAR, source_table VARCHAR))`

### Table scoping model
- Flat top-level lists (not nested per-table), with `source_table` field for disambiguation
- **Constraint (documented, not prevented):** dimension/metric names must be unique across all tables in a view. Two tables with a column both called `name` must use distinct dimension names (e.g., `order_name`, `customer_name`).
- Deferred to v0.3.0: nested per-table scoping (Snowflake YAML style), which would allow namespace reuse

### Relationships: column-pair structs
- `join_columns` is `LIST(STRUCT(from VARCHAR, to VARCHAR))` — explicit pair per column
- Handles composite FK keys
- The `from_cols: Vec<String>` / `on: String` legacy fields in the internal model are replaced

### Claude's Discretion
- Internal `SemanticViewDefinition` model migration (can keep or restructure JSON storage)
- Rust VScalar signature details for nested STRUCT reading
- Whether to keep backward compat for existing stored JSON definitions or require re-definition
- Error message wording

</decisions>

<specifics>
## Specific Ideas

- Snowflake YAML spec (https://docs.snowflake.com/en/user-guide/views-semantic/semantic-view-yaml-spec) is the canonical reference for struct field naming — use `time_dimensions` (not `temporal_dimensions`), `granularity`, `expr`, `source_table` terminology
- Snowflake uses `relationship_columns: [{left_column, right_column}]` — we use `join_columns: [{from, to}]` as shorter field names
- The `tables` parameter uses `{alias, table}` matching Snowflake's logical table / base table distinction

</specifics>

<code_context>
## Existing Code Insights

### Reusable Assets
- `src/model.rs`: `SemanticViewDefinition`, `Dimension`, `Metric`, `Fact`, `Join` — will all need updating to match new struct shapes
- `src/ddl/define.rs`: `DefineSemanticView` VScalar — signature and invoke need new STRUCT parsing
- `src/ddl/drop.rs`, `src/ddl/list.rs`, `src/ddl/describe.rs`: minor updates
- `src/query/table_function.rs`: `QueryState`, `SemanticViewBindData` — rename + table-qualified name parsing
- `src/expand.rs`: `QueryRequest` / `expand()` — needs to resolve `o.region` → correct dimension

### Established Patterns
- VScalar `invoke()` reads `flat_vector(n)` for each positional arg — STRUCT columns require `struct_vector()` API
- Internal model is serialized to JSON for catalog storage — the JSON storage format can remain even if the user-facing input changes
- Tests use sqllogictest `.test` files — each must be updated to new syntax

### Integration Points
- `src/lib.rs`: function registration names must change (`semantic_query` → `semantic_view`)
- All `.test` files under `test/sql/`: DDL and query calls must be updated

</code_context>

<deferred>
## Deferred Ideas

- Nested per-table scoping (Snowflake YAML style: `tables := [{alias:'o', dimensions:[...], metrics:[...]}]`) — deferred to v0.3.0; requires deeply nested DuckDB STRUCT types
- Backward-compat alias `semantic_query` — explicitly not added; clean rename only
- Inline filter parameter in query function — WHERE clause is sufficient
- Composite FK in relationships not blocked, but test coverage may be minimal in this phase

</deferred>

---

*Phase: 11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views*
*Context gathered: 2026-03-02*
