# Roadmap: DuckDB Semantic Views

## Milestones

- ✅ **v0.1.0 MVP** -- Phases 1-7 (shipped 2026-02-28)
- ✅ **v0.2.0 Native DDL + Time Dimensions** -- Phases 8-14 (shipped 2026-03-03)
- ✅ **v0.3.0 Zero-Copy Query Pipeline** -- (shipped 2026-03-03)
- ✅ **v0.4.0 Simplified Dimensions** -- (shipped 2026-03-03)
- ✅ **v0.5.0 Parser Extension Spike** -- Phases 15-18 (shipped 2026-03-08)
- ✅ **v0.5.1 DDL Polish** -- Phases 19-23 (shipped 2026-03-09)
- ✅ **v0.5.2 SQL DDL & PK/FK Relationships** -- Phases 24-28 (shipped 2026-03-13)
- ✅ **v0.5.3 Advanced Semantic Features** -- Phases 29-32 (shipped 2026-03-15)
- ✅ **v0.5.4 Snowflake-Parity & Registry Publishing** -- Phases 33-36 (shipped 2026-03-27)
- ✅ **v0.5.5 SHOW/DESCRIBE Alignment & Refactoring** -- Phases 37-42 (shipped 2026-04-05)
- **v0.6.0 Snowflake SQL DDL Parity** -- Phases 43-50 (in progress)

## Phases

<details>
<summary>v0.1.0 MVP (Phases 1-7) -- SHIPPED 2026-02-28</summary>

- [x] Phase 1: Scaffold (3/3 plans) -- completed 2026-02-24
- [x] Phase 2: Storage and DDL (4/4 plans) -- completed 2026-02-24
- [x] Phase 3: Expansion Engine (3/3 plans) -- completed 2026-02-25
- [x] Phase 4: Query Interface (3/3 plans) -- completed 2026-02-25
- [x] Phase 5: Hardening and Docs (2/2 plans) -- completed 2026-02-26
- [x] Phase 6: Tech Debt Code Cleanup (1/1 plan) -- completed 2026-02-26
- [x] Phase 7: Verification & Formal Closure (2/2 plans) -- completed 2026-02-27

Full details: [milestones/v1.0-ROADMAP.md](milestones/v1.0-ROADMAP.md)

</details>

<details>
<summary>v0.2.0 Native DDL + Time Dimensions (Phases 8-14) -- SHIPPED 2026-03-03</summary>

- [x] Phase 8: C++ Shim Infrastructure (2/2 plans) -- completed 2026-03-01
- [x] Phase 9: Time Dimensions (2/2 plans) -- completed 2026-03-01
- [x] Phase 10: pragma_query_t Catalog Persistence (3/3 plans) -- completed 2026-03-01
- [x] Phase 11: Scalar Function DDL (4/4 plans) -- completed 2026-03-02
- [x] Phase 11.1: Snowflake-aligned STRUCT/LIST DDL Syntax (5/5 plans) -- completed 2026-03-02
- [x] Phase 12: EXPLAIN + Typed Output (4/4 plans) -- completed 2026-03-02
- [x] Phase 13: Type-mapping + PBTs (2/2 plans) -- completed 2026-03-02
- [x] Phase 14: DuckLake Integration Test + CI (3/3 plans) -- completed 2026-03-02

Full details: [milestones/v0.2-ROADMAP.md](milestones/v0.2-ROADMAP.md)

</details>

<details>
<summary>v0.3.0 Zero-Copy Query Pipeline -- SHIPPED 2026-03-03</summary>

Replaced binary-read dispatch with zero-copy vector references (`duckdb_vector_reference_vector`).
Eliminated ~600 LOC of per-type read/write code. Type mismatches handled at SQL generation time
via `build_execution_sql` cast wrapper. Streaming chunk-by-chunk instead of collect-all-then-write.

</details>

<details>
<summary>v0.4.0 Simplified Dimensions -- SHIPPED 2026-03-03</summary>

Breaking change: removed `time_dimensions` DDL parameter and `granularities` query parameter.
Time truncation expressed via dimension `expr` directly (e.g., `date_trunc('month', created_at)`).
DDL simplified from 6 to 4 named params; query function from 3 to 2 named params.

</details>

<details>
<summary>v0.5.0 Parser Extension Spike (Phases 15-18) -- SHIPPED 2026-03-08</summary>

- [x] Phase 15: Entry Point POC (2/2 plans) -- completed 2026-03-07
- [x] Phase 16: Parser Hook Registration (1/1 plan) -- completed 2026-03-07
- [x] Phase 17: DDL Execution (1/1 plan) -- completed 2026-03-07
- [x] Phase 17.1: Python vtab crash investigation (2/2 plans) -- completed 2026-03-08
- [x] Phase 18: Verification and Integration (2/2 plans) -- completed 2026-03-08

Full details: [milestones/v0.5-ROADMAP.md](milestones/v0.5-ROADMAP.md)

</details>

<details>
<summary>v0.5.1 DDL Polish (Phases 19-23) -- SHIPPED 2026-03-09</summary>

- [x] Phase 19: Parser Hook Validation Spike (1/1 plan) -- completed 2026-03-09
- [x] Phase 20: Extended DDL Statements (2/2 plans) -- completed 2026-03-09
- [x] Phase 21: Error Location Reporting (3/3 plans) -- completed 2026-03-09
- [x] Phase 22: Documentation (1/1 plan) -- completed 2026-03-09
- [x] Phase 23: Parser Proptests and Caret Integration Tests (2/2 plans) -- completed 2026-03-09

Full details: [milestones/v0.5.1-ROADMAP.md](milestones/v0.5.1-ROADMAP.md)

</details>

<details>
<summary>v0.5.2 SQL DDL & PK/FK Relationships (Phases 24-28) -- SHIPPED 2026-03-13</summary>

- ~~Phase 24: PK/FK Model~~ -- CANCELLED (absorbed into Phase 25-01)
- [x] Phase 25: SQL Body Parser (4/4 plans) -- completed 2026-03-12
- [x] Phase 25.1: Parser Robustness & Security (2/2 plans) -- completed 2026-03-13
- [x] Phase 26: PK/FK Join Resolution (2/2 plans) -- completed 2026-03-13
- [x] Phase 27: Alias-Based Query Expansion (3/3 plans) -- completed 2026-03-13
- [x] Phase 28: Integration Testing & Docs (3/3 plans) -- completed 2026-03-13

Full details: [milestones/v0.5.2-ROADMAP.md](milestones/v0.5.2-ROADMAP.md)

</details>

<details>
<summary>v0.5.3 Advanced Semantic Features (Phases 29-32) -- SHIPPED 2026-03-15</summary>

- [x] Phase 29: FACTS Clause & Hierarchies (2/2 plans) -- completed 2026-03-14
- [x] Phase 30: Derived Metrics (2/2 plans) -- completed 2026-03-14
- [x] Phase 31: Fan Trap Detection (2/2 plans) -- completed 2026-03-14
- [x] Phase 32: Role-Playing Dimensions & USING RELATIONSHIPS (2/2 plans) -- completed 2026-03-14

Full details: [milestones/v0.5.3-ROADMAP.md](milestones/v0.5.3-ROADMAP.md)

</details>

<details>
<summary>v0.5.4 Snowflake-Parity & Registry Publishing (Phases 33-36) -- SHIPPED 2026-03-27</summary>

- [x] Phase 33: UNIQUE Constraints & Cardinality Inference (2/2 plans) -- completed 2026-03-15
- [x] Phase 34: DuckDB 1.5 Upgrade & LTS Branch (2/2 plans) -- completed 2026-03-16
- [x] Phase 34.1: DDL Surface Parity (3/3 plans) -- completed 2026-03-22
- [x] Phase 34.1.1: SHOW Command Filtering (1/1 plan) -- completed 2026-03-22
- [x] Phase 35: Documentation Site (1/1 plan) -- completed 2026-03-27
- [x] Phase 36: Registry Publishing & Maintainer Docs (2/2 plans) -- completed 2026-03-27

Full details: [milestones/v0.5.4-ROADMAP.md](milestones/v0.5.4-ROADMAP.md)

</details>

<details>
<summary>v0.5.5 SHOW/DESCRIBE Alignment & Refactoring (Phases 37-42) -- SHIPPED 2026-04-05</summary>

- [x] Phase 37: Extract Shared Utilities (1/1 plan) -- completed 2026-04-01
- [x] Phase 38: Module Directory Splits (2/2 plans) -- completed 2026-04-01
- [x] Phase 39: Metadata Storage (1/1 plan) -- completed 2026-04-02
- [x] Phase 40: SHOW Command Alignment (2/2 plans) -- completed 2026-04-02
- [x] Phase 41: DESCRIBE Rewrite (2/2 plans) -- completed 2026-04-02
- [x] Phase 42: Refactor & Test Reorg (3/3 plans) -- completed 2026-04-05

Full details: [milestones/v0.5.5-ROADMAP.md](milestones/v0.5.5-ROADMAP.md)

</details>

### v0.6.0 Snowflake SQL DDL Parity (In Progress)

**Milestone Goal:** Close all remaining feature gaps against Snowflake's SQL DDL semantic views -- metadata annotations (COMMENT, SYNONYMS, PRIVATE/PUBLIC), ALTER DDL, GET_DDL round-trip, semi-additive metrics, window function metrics, queryable FACTS, wildcard selection, and introspection enhancements.

- [x] **Phase 43: Metadata Foundation** - Model fields, body parser extensions, and backward-compatible persistence for COMMENT, SYNONYMS, and PRIVATE/PUBLIC (completed 2026-04-10)
- [x] **Phase 44: SHOW/DESCRIBE Metadata Surface + Enhancements** - Surface metadata in SHOW/DESCRIBE output, add TERSE mode, IN SCHEMA/DATABASE scope, and SHOW COLUMNS (completed 2026-04-11)
- [x] **Phase 45: ALTER COMMENT + GET_DDL** - ALTER SET/UNSET COMMENT DDL and round-trip DDL reconstruction via GET_DDL (completed 2026-04-11)
- [x] **Phase 46: Wildcard Selection + Queryable FACTS** - Expansion pipeline modifications for table_alias.* wildcards and row-level fact queries (completed 2026-04-12)
- [x] **Phase 47: Semi-Additive Metrics** - CTE-based snapshot aggregation via NON ADDITIVE BY with correct multi-metric and multi-table interaction (completed 2026-04-12)
- [x] **Phase 48: Window Function Metrics** - PARTITION BY EXCLUDING DDL, non-aggregated expansion, and aggregate/window mixing prohibition (completed 2026-04-12)
- [x] **Phase 49: Security & Correctness Hardening** - Expression validation at definition time, FFI panic boundaries, cycle/depth limits for derived metrics/facts, poisoned lock handling (completed 2026-04-14)
- [x] **Phase 50: Code Quality & Test Coverage** - Unit tests for untested expand modules, resolution loop deduplication, DimensionName/MetricName newtypes, dead code removal (completed 2026-04-14)

## Phase Details

### Phase 43: Metadata Foundation
**Goal**: Semantic view definitions support COMMENT, SYNONYMS, and PRIVATE/PUBLIC annotations that persist correctly and remain backward-compatible with pre-v0.6.0 stored views
**Depends on**: Phase 42
**Requirements**: META-01, META-02, META-03, META-04, META-05, META-06, META-07
**Success Criteria** (what must be TRUE):
  1. User can write COMMENT = '...' on a semantic view and on individual tables, dimensions, metrics, and facts in CREATE DDL, and the view is stored successfully
  2. User can write WITH SYNONYMS = ('alias1', 'alias2') on tables, dimensions, metrics, and facts in DDL, and the view is stored successfully
  3. User can mark facts and metrics as PRIVATE, and those items are excluded from query results while still usable in derived metric/dimension expressions
  4. A semantic view stored by v0.5.5 loads without error after upgrading to v0.6.0 (all new fields default to empty/PUBLIC)
  5. All metadata fields survive DuckDB restart (persist and deserialize correctly)
**Plans:** 2/2 plans complete
Plans:
- [x] 43-01-PLAN.md -- Model types, serde attributes, and backward compatibility tests
- [x] 43-02-PLAN.md -- Body parser annotations, view-level COMMENT, PRIVATE enforcement, integration tests

### Phase 44: SHOW/DESCRIBE Metadata Surface + Enhancements
**Goal**: Users can see metadata annotations in SHOW/DESCRIBE output and use new introspection modes (TERSE, IN SCHEMA/DATABASE, SHOW COLUMNS)
**Depends on**: Phase 43
**Requirements**: SHOW-01, SHOW-02, SHOW-03, SHOW-04, SHOW-05, SHOW-06
**Success Criteria** (what must be TRUE):
  1. SHOW SEMANTIC VIEWS/DIMENSIONS/METRICS/FACTS output includes synonyms and comment columns populated from stored metadata
  2. SHOW SEMANTIC VIEWS IN SCHEMA schema_name returns only views matching the specified schema; IN DATABASE db_name returns only views matching the specified database
  3. SHOW TERSE SEMANTIC VIEWS returns a reduced column set (name and essential identifiers only)
  4. SHOW COLUMNS IN SEMANTIC VIEW returns a unified list of all dims, facts, and metrics with a kind column distinguishing them
  5. DESCRIBE SEMANTIC VIEW includes COMMENT, SYNONYMS, and ACCESS_MODIFIER properties in its property-per-row output
**Plans:** 2/2 plans complete
Plans:
- [x] 44-01-PLAN.md -- Add synonyms/comment columns to SHOW VTabs and DESCRIBE metadata properties
- [x] 44-02-PLAN.md -- SHOW TERSE, IN SCHEMA/DATABASE scope filtering, SHOW COLUMNS IN SEMANTIC VIEW

### Phase 45: ALTER COMMENT + GET_DDL
**Goal**: Users can modify view-level comments after creation and reconstruct re-executable DDL from stored definitions
**Depends on**: Phase 44
**Requirements**: ALT-01, ALT-02, SHOW-07, SHOW-08
**Success Criteria** (what must be TRUE):
  1. User can ALTER SEMANTIC VIEW ... SET COMMENT = '...' to add or change the view-level comment, and the change is visible in subsequent SHOW/DESCRIBE
  2. User can ALTER SEMANTIC VIEW ... UNSET COMMENT to remove the view-level comment
  3. GET_DDL('SEMANTIC_VIEW', 'name') returns a syntactically valid CREATE OR REPLACE SEMANTIC VIEW statement
  4. GET_DDL output round-trips correctly: parsing the output and storing it produces a definition equivalent to the original
**Plans:** 2/2 plans complete
Plans:
- [x] 45-01-PLAN.md -- ALTER SET/UNSET COMMENT parser generalization, VTab implementation, and integration tests
- [x] 45-02-PLAN.md -- GET_DDL scalar function with DDL reconstruction and round-trip tests

### Phase 46: Wildcard Selection + Queryable FACTS
**Goal**: Users can query with table_alias.* wildcards for dimensions/metrics and can query facts at row level via the table function
**Depends on**: Phase 45
**Requirements**: WILD-01, WILD-02, WILD-03, FACT-01, FACT-02, FACT-03, FACT-04
**Success Criteria** (what must be TRUE):
  1. User can pass table_alias.* in dimensions or metrics parameter and it expands to all matching items scoped to that table alias
  2. Wildcard expansion excludes PRIVATE dimensions/metrics (only PUBLIC items included)
  3. User can query semantic_view('v', facts := ['f1', 'f2'], dimensions := ['d1']) and receive row-level unaggregated results
  4. Combining facts and metrics in the same query produces a clear blocking error
  5. Fact queries with dimensions require all referenced objects to follow the same logical table path
**Plans:** 2/2 plans complete
Plans:
- [x] 46-01-PLAN.md -- Wildcard expansion (table_alias.*), QueryRequest.facts field, mutual exclusion, VTab parameter registration
- [x] 46-02-PLAN.md -- Fact query expansion path, table path validation, type resolution, integration tests

### Phase 47: Semi-Additive Metrics
**Goal**: Users can declare and query snapshot-style metrics that aggregate correctly over non-additive dimensions
**Depends on**: Phase 46
**Requirements**: SEMI-01, SEMI-02, SEMI-03, SEMI-04, SEMI-05
**Success Criteria** (what must be TRUE):
  1. User can declare NON ADDITIVE BY (dimension [ASC|DESC]) on a metric in DDL and the view stores successfully
  2. Querying a semi-additive metric produces correct results via CTE-based ROW_NUMBER snapshot selection before aggregation
  3. A query mixing regular aggregate metrics and semi-additive metrics in the same request produces correct results for both
  4. Semi-additive metrics interact correctly with fan trap detection (no false positives or missed fan traps)
  5. Semi-additive metrics work correctly with multi-table JOINs and USING RELATIONSHIPS
**Plans:** 2/2 plans complete
Plans:
- [x] 47-01-PLAN.md -- Model types (NonAdditiveDim, SortOrder, NullsOrder), body parser NON ADDITIVE BY, render_ddl, DESCRIBE, validation
- [x] 47-02-PLAN.md -- CTE-based expansion pipeline (semi_additive.rs), fan trap skip, mixed metrics, integration tests

### Phase 48: Window Function Metrics
**Goal**: Users can declare and query window function metrics that produce non-aggregated results with partition-aware computation
**Depends on**: Phase 47
**Requirements**: WIN-01, WIN-02, WIN-03, WIN-04, WIN-05
**Success Criteria** (what must be TRUE):
  1. User can declare a window function metric with PARTITION BY EXCLUDING in DDL and the view stores successfully
  2. Querying a window function metric produces correct non-aggregated results (no GROUP BY in generated SQL)
  3. Attempting to mix window function metrics with aggregate metrics in the same query produces a clear blocking error
  4. Window function metrics are excluded from fan trap detection (no false errors)
  5. SHOW SEMANTIC DIMENSIONS FOR METRIC shows required=TRUE for dimensions in the window metric's partition specification
**Plans:** 2/2 plans complete
Plans:
- [x] 48-01-PLAN.md -- WindowSpec model types, OVER clause body parser, GET_DDL emission, DESCRIBE surfacing, serde backward compat
- [x] 48-02-PLAN.md -- CTE-based window expansion pipeline (window.rs), fan trap skip, mixing error, SHOW DIMS required=TRUE, integration tests

### Phase 49: Security & Correctness Hardening
**Goal**: Harden the extension against FFI panics, lock poisoning, and resource exhaustion from malformed definitions
**Depends on**: Phase 48
**Requirements**: SEC-01, SEC-02, SEC-03, SEC-04
**Success Criteria** (what must be TRUE):
  1. All FFI query-path entry points are wrapped in `catch_unwind` so Rust panics cannot unwind through C++ stack frames
  2. Poisoned RwLock/Mutex states are handled gracefully (error returned, not panic) in catalog and query paths
  3. Derived metric and fact resolution enforces a cycle detection check and a maximum nesting depth limit, returning a clear error on violation
  4. Unsafe pointer arithmetic in test helpers has bounds checks guarding against out-of-range row indices
**Plans:** 2/2 plans complete
Plans:
- [x] 49-01-PLAN.md -- Poisoned lock handling + test helper bounds checks (SEC-02, SEC-04)
- [x] 49-02-PLAN.md -- FFI catch_unwind wrappers + cycle/depth limits (SEC-01, SEC-03)

### Phase 50: Code Quality & Test Coverage
**Goal**: Improve test coverage for untested modules, reduce code duplication, and introduce domain types for stronger compile-time guarantees
**Depends on**: Phase 49
**Requirements**: QUAL-01, QUAL-02, QUAL-03, QUAL-04, QUAL-05, QUAL-06
**Success Criteria** (what must be TRUE):
  1. `expand/join_resolver.rs`, `expand/fan_trap.rs`, and `expand/facts.rs` each have unit tests covering normal paths and edge cases (empty inputs, circular graphs, missing references)
  2. Dimension/metric/fact resolution loops in `expand()` and `expand_facts()` are deduplicated into a shared generic helper
  3. `DimensionName` and `MetricName` newtypes replace bare `String` in `QueryRequest` and resolution code, with case-insensitive comparison consolidated in one place
  4. Named `NaGroup` struct replaces tuple `Vec<(Vec<String>, Vec<NonAdditiveDim>, Vec<usize>)>` in `semi_additive.rs`
  5. Dead `parse_constraint_columns()` in `model.rs` is removed
  6. `sql_gen.rs` tests use structural property assertions instead of exact string equality where appropriate

**Plans:** 2/2 plans complete
Plans:
- [x] 50-01-PLAN.md -- Unit tests for join_resolver, fan_trap, facts; sql_gen property assertions
- [x] 50-02-PLAN.md -- resolve_names deduplication, DimensionName/MetricName newtypes, NaGroup struct, dead code removal

## Progress

**Execution Order:**
Phases execute in numeric order: 43 -> 44 -> 45 -> 46 -> 47 -> 48 -> 49 -> 50

| Phase | Milestone | Plans Complete | Status | Completed |
|-------|-----------|----------------|--------|-----------|
| 43. Metadata Foundation | v0.6.0 | 2/2 | Complete    | 2026-04-10 |
| 44. SHOW/DESCRIBE Metadata Surface + Enhancements | v0.6.0 | 2/2 | Complete    | 2026-04-11 |
| 45. ALTER COMMENT + GET_DDL | v0.6.0 | 2/2 | Complete    | 2026-04-11 |
| 46. Wildcard Selection + Queryable FACTS | v0.6.0 | 2/2 | Complete    | 2026-04-12 |
| 47. Semi-Additive Metrics | v0.6.0 | 2/2 | Complete    | 2026-04-12 |
| 48. Window Function Metrics | v0.6.0 | 2/2 | Complete    | 2026-04-12 |
| 49. Security & Correctness Hardening | v0.6.0 | 2/2 | Complete    | 2026-04-14 |
| 50. Code Quality & Test Coverage | v0.6.0 | 2/2 | Complete    | 2026-04-14 |
