# Requirements Archive: v0.6.0 Snowflake SQL DDL Parity

**Archived:** 2026-04-17
**Status:** SHIPPED

For current requirements, see `.planning/REQUIREMENTS.md`.

---

# Requirements: DuckDB Semantic Views

**Defined:** 2026-04-09
**Core Value:** A DuckDB user can define a semantic view once and query it with any combination of dimensions and metrics, without writing GROUP BY or JOIN logic by hand

## v0.6.0 Requirements

Requirements for Snowflake SQL DDL Parity milestone. Each maps to roadmap phases.

### Metadata

- [ ] **META-01**: User can add COMMENT = '...' to a semantic view in CREATE DDL
- [ ] **META-02**: User can add COMMENT = '...' to individual tables, dimensions, metrics, and facts in DDL
- [ ] **META-03**: User can add WITH SYNONYMS = ('alias1', 'alias2') to tables, dimensions, metrics, and facts
- [ ] **META-04**: User can mark facts and metrics as PRIVATE or PUBLIC (default PUBLIC)
- [ ] **META-05**: PRIVATE facts/metrics are hidden from query results but usable in derived metric/dimension expressions
- [ ] **META-06**: All metadata fields (COMMENT, SYNONYMS, PRIVATE/PUBLIC) persist across restarts with backward-compatible JSON deserialization
- [ ] **META-07**: Pre-v0.6.0 stored views load without error (all new fields default to empty/PUBLIC)

### ALTER DDL

- [x] **ALT-01**: User can ALTER SEMANTIC VIEW ... SET COMMENT = '...' to add/change view-level comment
- [x] **ALT-02**: User can ALTER SEMANTIC VIEW ... UNSET COMMENT to remove view-level comment

### Semi-Additive Metrics

- [x] **SEMI-01**: User can declare NON ADDITIVE BY (dimension [ASC|DESC]) on a metric in DDL
- [x] **SEMI-02**: Semi-additive metrics use CTE-based snapshot selection (ROW_NUMBER) before aggregation at query time
- [x] **SEMI-03**: Queries mixing regular and semi-additive metrics produce correct results
- [x] **SEMI-04**: Semi-additive metrics interact correctly with fan trap detection
- [x] **SEMI-05**: Semi-additive metrics work with multi-table JOINs and USING RELATIONSHIPS

### Window Function Metrics

- [ ] **WIN-01**: User can declare a window function metric with PARTITION BY EXCLUDING in DDL
- [x] **WIN-02**: Window function metrics produce correct non-aggregated results at query time
- [x] **WIN-03**: Queries cannot mix window function metrics with aggregate metrics (blocking error)
- [x] **WIN-04**: Window function metrics are excluded from fan trap detection
- [x] **WIN-05**: SHOW SEMANTIC DIMENSIONS FOR METRIC shows required=TRUE for window metric partition dimensions

### Queryable FACTS

- [x] **FACT-01**: User can query facts via semantic_view('v', facts := ['f1', 'f2'], dimensions := ['d1'])
- [x] **FACT-02**: Fact queries return row-level (unaggregated) results
- [x] **FACT-03**: Facts and metrics cannot be combined in the same query (blocking error)
- [x] **FACT-04**: Fact queries with dimensions require all referenced objects from the same logical table path

### Wildcard Selection

- [x] **WILD-01**: User can use table_alias.* to select all dimensions scoped to that table
- [x] **WILD-02**: User can use table_alias.* to select all metrics scoped to that table
- [x] **WILD-03**: Wildcard expansion respects PRIVATE visibility (PRIVATE items excluded)

### Introspection

- [ ] **SHOW-01**: SHOW SEMANTIC VIEWS/DIMENSIONS/METRICS/FACTS include synonyms and comment columns
- [ ] **SHOW-02**: SHOW SEMANTIC VIEWS IN SCHEMA schema_name filters by schema
- [ ] **SHOW-03**: SHOW SEMANTIC VIEWS IN DATABASE db_name filters by database
- [ ] **SHOW-04**: SHOW TERSE SEMANTIC VIEWS returns reduced column set
- [ ] **SHOW-05**: SHOW COLUMNS IN SEMANTIC VIEW returns unified dims+facts+metrics with kind column
- [ ] **SHOW-06**: DESCRIBE SEMANTIC VIEW includes COMMENT, SYNONYMS, and ACCESS_MODIFIER properties
- [ ] **SHOW-07**: GET_DDL('SEMANTIC_VIEW', 'name') returns a re-executable CREATE OR REPLACE statement
- [ ] **SHOW-08**: GET_DDL output round-trips correctly (parse → store → GET_DDL → parse produces equivalent definition)

## Future Requirements

Deferred to future release. Tracked but not in current roadmap.

### Query Interface

- **QIFC-01**: Direct SQL query interface (SELECT AGG(metric) FROM sv GROUP BY dim)
- **QIFC-02**: SEMANTIC_VIEW() clause syntax (SEMANTIC_VIEW(v DIMENSIONS d1 METRICS m1))

### Advanced Relationships

- **REL-01**: ASOF temporal relationships for time-series joins
- **REL-02**: Range join constraints (BETWEEN ... AND ... EXCLUSIVE)

### Materialization

- **MAT-01**: Pre-aggregation / materialization selection
- **MAT-02**: Cross-view optimization (sharing materialized tables)

## Out of Scope

Explicitly excluded. Documented to prevent scope creep.

| Feature | Reason |
|---------|--------|
| Direct SQL query interface | Requires parser-level integration beyond current hook model |
| SEMANTIC_VIEW() clause syntax | Same parser limitation as direct SQL |
| YAML definition format | SQL DDL first; YAML is a future path |
| Fan trap auto-deduplication | Changes query semantics; detection + blocking is the 80/20 |
| Aggregate facts (COUNT in FACTS) | Blurs row-level boundary; aggregation belongs in METRICS |
| Cortex AI integration | Snowflake-specific (AI_SQL_GENERATION, AI_QUESTION_CATEGORIZATION) |
| COPY GRANTS | No DuckDB RBAC equivalent |
| Column-level security | Beyond extension scope; DuckDB handles access control |
| Custom query engine | DuckDB is the engine; extension is a preprocessor only |

## Traceability

Which phases cover which requirements. Updated during roadmap creation.

| Requirement | Phase | Status |
|-------------|-------|--------|
| META-01 | Phase 43 | Pending |
| META-02 | Phase 43 | Pending |
| META-03 | Phase 43 | Pending |
| META-04 | Phase 43 | Pending |
| META-05 | Phase 43 | Pending |
| META-06 | Phase 43 | Pending |
| META-07 | Phase 43 | Pending |
| ALT-01 | Phase 45 | Complete |
| ALT-02 | Phase 45 | Complete |
| SEMI-01 | Phase 47 | Complete |
| SEMI-02 | Phase 47 | Complete |
| SEMI-03 | Phase 47 | Complete |
| SEMI-04 | Phase 47 | Complete |
| SEMI-05 | Phase 47 | Complete |
| WIN-01 | Phase 48 | Pending |
| WIN-02 | Phase 48 | Complete |
| WIN-03 | Phase 48 | Complete |
| WIN-04 | Phase 48 | Complete |
| WIN-05 | Phase 48 | Complete |
| FACT-01 | Phase 46 | Complete |
| FACT-02 | Phase 46 | Complete |
| FACT-03 | Phase 46 | Complete |
| FACT-04 | Phase 46 | Complete |
| WILD-01 | Phase 46 | Complete |
| WILD-02 | Phase 46 | Complete |
| WILD-03 | Phase 46 | Complete |
| SHOW-01 | Phase 44 | Pending |
| SHOW-02 | Phase 44 | Pending |
| SHOW-03 | Phase 44 | Pending |
| SHOW-04 | Phase 44 | Pending |
| SHOW-05 | Phase 44 | Pending |
| SHOW-06 | Phase 44 | Pending |
| SHOW-07 | Phase 45 | Pending |
| SHOW-08 | Phase 45 | Pending |

**Coverage:**
- v0.6.0 requirements: 34 total
- Mapped to phases: 34
- Unmapped: 0

---
*Requirements defined: 2026-04-09*
*Last updated: 2026-04-09 after roadmap creation*
