---
phase: 54-materialization-model-ddl
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - src/model.rs
  - src/body_parser.rs
  - src/parse.rs
  - src/render_ddl.rs
  - tests/yaml_proptest.rs
  - test/sql/phase54_materializations.test
autonomous: true
requirements: [MAT-01, MAT-06, MAT-07]

must_haves:
  truths:
    - "MATERIALIZATIONS clause in SQL DDL accepts named materializations with TABLE, DIMENSIONS, and METRICS sub-clauses"
    - "MATERIALIZATIONS section in YAML definitions produces the same internal representation as the SQL DDL clause"
    - "Materialization metadata persists across DuckDB restarts (stored and loaded with backward compatibility for pre-v0.7.0 views)"
    - "Define-time validation ensures materialization dimensions and metrics reference declared names in the semantic view"
    - "GET_DDL round-trip preserves materializations (render -> parse -> render produces identical output)"
    - "Proptest verifies Materialization JSON/YAML round-trip for arbitrary inputs"
  artifacts:
    - path: "src/model.rs"
      provides: "Materialization struct with serde derives, materializations field on SemanticViewDefinition"
      contains: "pub struct Materialization"
    - path: "src/body_parser.rs"
      provides: "MATERIALIZATIONS clause parsing in body parser"
      contains: "parse_materializations_clause"
    - path: "src/parse.rs"
      provides: "materializations field wired into rewrite_ddl_keyword_body"
      contains: "keyword_body.materializations"
    - path: "src/render_ddl.rs"
      provides: "DDL reconstruction for MATERIALIZATIONS clause"
      contains: "emit_materializations"
    - path: "tests/yaml_proptest.rs"
      provides: "Proptest with Materialization strategy and materializations in arb_definition"
      contains: "arb_materialization"
    - path: "test/sql/phase54_materializations.test"
      provides: "Integration tests for MATERIALIZATIONS DDL, persistence, round-trip"
      min_lines: 50
  key_links:
    - from: "src/body_parser.rs"
      to: "src/model.rs"
      via: "use crate::model::Materialization"
      pattern: "Materialization"
    - from: "src/parse.rs"
      to: "src/body_parser.rs"
      via: "KeywordBody.materializations consumed in rewrite_ddl_keyword_body"
      pattern: "keyword_body\\.materializations"
    - from: "src/render_ddl.rs"
      to: "src/model.rs"
      via: "reads def.materializations to emit DDL clause"
      pattern: "def\\.materializations"
    - from: "tests/yaml_proptest.rs"
      to: "src/model.rs"
      via: "uses Materialization in arb_definition strategy"
      pattern: "Materialization"
---

<objective>
Add the MATERIALIZATIONS clause to the semantic view data model, SQL DDL body parser, DDL reconstruction, and YAML support.

Purpose: Users can declare materializations as part of a semantic view definition, enabling the routing engine (Phase 55) to redirect queries to pre-aggregated tables. This phase is the model-and-parser foundation -- no query-time routing logic.

Output: Materialization struct in model.rs, MATERIALIZATIONS clause parsing in body_parser.rs, wiring in parse.rs, DDL reconstruction in render_ddl.rs, define-time validation, YAML support (automatic via serde), backward-compatible persistence, proptest coverage, and comprehensive tests (unit + proptest + sqllogictest).
</objective>

<execution_context>
@$HOME/.claude/get-shit-done/workflows/execute-plan.md
@$HOME/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/54-materialization-model-ddl/54-RESEARCH.md
@.planning/phases/54-materialization-model-ddl/54-VALIDATION.md

@src/model.rs
@src/body_parser.rs
@src/parse.rs
@src/render_ddl.rs
@src/util.rs

<interfaces>
<!-- Key types and contracts the executor needs. Extracted from codebase. -->

From src/model.rs (line 342):
```rust
pub struct SemanticViewDefinition {
    pub base_table: String,
    pub tables: Vec<TableRef>,
    pub dimensions: Vec<Dimension>,
    pub metrics: Vec<Metric>,
    pub joins: Vec<Join>,
    pub facts: Vec<Fact>,
    pub column_type_names: Vec<String>,
    pub column_types_inferred: Vec<u32>,
    pub created_on: Option<String>,
    pub database_name: Option<String>,
    pub schema_name: Option<String>,
    pub comment: Option<String>,
}
```

From src/body_parser.rs (line 37):
```rust
pub struct KeywordBody {
    pub tables: Vec<TableRef>,
    pub relationships: Vec<Join>,
    pub facts: Vec<Fact>,
    pub dimensions: Vec<Dimension>,
    pub metrics: Vec<Metric>,
}
```

From src/body_parser.rs (line 54):
```rust
const CLAUSE_KEYWORDS: &[&str] = &["tables", "relationships", "facts", "dimensions", "metrics"];
const CLAUSE_ORDER: &[&str] = &["tables", "relationships", "facts", "dimensions", "metrics"];
```

From src/parse.rs (line 1096-1130):
```rust
fn rewrite_ddl_keyword_body(...) -> Result<Option<String>, ParseError> {
    let mut keyword_body = parse_keyword_body(body_text, body_offset)?;
    infer_cardinality(&keyword_body.tables, &mut keyword_body.relationships)?;
    let base_table = keyword_body.tables.first().map(|t| t.table.clone()).unwrap_or_default();
    let def = crate::model::SemanticViewDefinition {
        base_table,
        tables: keyword_body.tables,
        dimensions: keyword_body.dimensions,
        metrics: keyword_body.metrics,
        joins: keyword_body.relationships,
        facts: keyword_body.facts,
        column_type_names: vec![],
        column_types_inferred: vec![],
        created_on: None,
        database_name: None,
        schema_name: None,
        comment: view_comment,
    };
    // ... serialize to JSON ...
}
```

From src/render_ddl.rs (line 256-291):
```rust
pub fn render_create_ddl(name: &str, def: &SemanticViewDefinition) -> Result<String, String> {
    // ... emits TABLES, RELATIONSHIPS, FACTS, DIMENSIONS, METRICS ...
    // MATERIALIZATIONS clause needs to be added after METRICS
}
```

From src/util.rs (line 12):
```rust
pub fn suggest_closest(name: &str, available: &[String]) -> Option<String>;
```

From src/body_parser.rs (line 80):
```rust
pub(crate) fn split_at_depth0_commas(body: &str) -> Vec<(usize, &str)>;
```

From tests/yaml_proptest.rs (existing proptest file):
```rust
// Existing strategy functions: arb_name, arb_expr, arb_table_ref, arb_dimension,
// arb_metric, arb_fact, arb_join, arb_definition
// arb_definition constructs SemanticViewDefinition and needs updating to include materializations
// Uses: use semantic_views::model::{...};
// proptest! block tests yaml_json_roundtrip_equivalence
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Materialization struct, body parser clause, parse.rs wiring, render_ddl reconstruction, proptest</name>
  <files>src/model.rs, src/body_parser.rs, src/parse.rs, src/render_ddl.rs, tests/yaml_proptest.rs</files>
  <read_first>
    src/model.rs
    src/body_parser.rs
    src/parse.rs
    src/render_ddl.rs
    src/util.rs
    tests/yaml_proptest.rs
    .planning/phases/54-materialization-model-ddl/54-RESEARCH.md
  </read_first>
  <behavior>
    - Test: Materialization struct serializes/deserializes JSON round-trip
    - Test: Materialization struct serializes/deserializes YAML round-trip (MAT-06)
    - Test: SemanticViewDefinition with materializations JSON round-trip preserves all fields
    - Test: SemanticViewDefinition without materializations field deserializes to empty vec (backward compat, MAT-07)
    - Test: Empty materializations vec is omitted from serialized JSON (skip_serializing_if)
    - Test: YAML and JSON with materializations produce identical SemanticViewDefinition structs (MAT-06)
    - Test: parse_materializations_clause parses single materialization entry
    - Test: parse_materializations_clause parses multiple comma-separated entries
    - Test: parse_materializations_clause handles DIMENSIONS-only materialization (no METRICS)
    - Test: parse_materializations_clause handles METRICS-only materialization (no DIMENSIONS)
    - Test: parse_materializations_clause rejects entry with neither DIMENSIONS nor METRICS
    - Test: parse_keyword_body recognizes MATERIALIZATIONS clause after METRICS
    - Test: parse_keyword_body rejects MATERIALIZATIONS before METRICS (ordering validation)
    - Test: parse_keyword_body validates materialization dimension names reference declared dimensions (with "Did you mean?" suggestion)
    - Test: parse_keyword_body validates materialization metric names reference declared metrics (with "Did you mean?" suggestion)
    - Test: parse_keyword_body rejects duplicate materialization names
    - Test: render_create_ddl emits MATERIALIZATIONS clause when present
    - Test: render_create_ddl omits MATERIALIZATIONS clause when empty
    - Test: DDL round-trip (render -> parse -> render) preserves materializations
    - Proptest: Arbitrary Materialization structs survive JSON serialize -> deserialize round-trip
    - Proptest: Arbitrary SemanticViewDefinition (with materializations) JSON/YAML round-trip produces identical structs
  </behavior>
  <action>
    **1. Add Materialization struct to model.rs (after the `Fact` struct, around line 233):**

    ```rust
    /// A named materialization declaration mapping a pre-aggregated table
    /// to the dimensions and metrics it covers.
    ///
    /// At define time, only the dimension/metric name references are validated
    /// (must match declared names). The TABLE is not validated for existence
    /// (it may be created later by external tools like dbt).
    #[derive(Debug, Clone, Default, PartialEq, Serialize, Deserialize)]
    #[cfg_attr(feature = "arbitrary", derive(arbitrary::Arbitrary))]
    pub struct Materialization {
        /// User-assigned name for this materialization (e.g., "daily_revenue_by_region").
        pub name: String,
        /// Fully qualified table name of the pre-aggregated table
        /// (e.g., "catalog.schema.daily_revenue_agg").
        pub table: String,
        /// Dimension names covered by this materialization.
        /// Must be a subset of the semantic view's declared dimensions.
        pub dimensions: Vec<String>,
        /// Metric names covered by this materialization.
        /// Must be a subset of the semantic view's declared metrics.
        pub metrics: Vec<String>,
    }
    ```

    **2. Add materializations field to SemanticViewDefinition (after `facts` field, before `column_type_names`):**

    ```rust
    /// Named materializations mapping pre-aggregated tables to covered dims/metrics.
    /// Old stored JSON without this field deserializes with empty Vec.
    /// Not serialized when empty to preserve backward-compatible JSON.
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub materializations: Vec<Materialization>,
    ```

    **3. Update body_parser.rs:**

    a. Add `Materialization` to the import from `crate::model` (line 7-10).

    b. Add `"materializations"` as the LAST entry in both `CLAUSE_KEYWORDS` (line 54) and `CLAUSE_ORDER` (line 59) arrays. After:
    ```rust
    const CLAUSE_KEYWORDS: &[&str] = &["tables", "relationships", "facts", "dimensions", "metrics", "materializations"];
    const CLAUSE_ORDER: &[&str] = &["tables", "relationships", "facts", "dimensions", "metrics", "materializations"];
    ```

    c. Update the ordering error message in `find_clause_bounds` (around line 282) to include MATERIALIZATIONS:
    ```rust
    "Clause '{kw_upper}' appears out of order; clauses must appear as: TABLES, RELATIONSHIPS (optional), FACTS (optional), DIMENSIONS (optional), METRICS (optional), MATERIALIZATIONS (optional).",
    ```

    d. Add `materializations: Vec<Materialization>` field to `KeywordBody` struct (line 37-43).

    e. Add `"materializations"` match arm in `parse_keyword_body` (around line 341-348, in the `for bound in &bounds` loop):
    ```rust
    "materializations" => {
        // parsed below after dimensions/metrics are available
    }
    ```
    Store the bound content/offset in temporary variables for post-loop processing (similar to how metrics are collected in metrics_raw then validated after the loop).

    Alternatively, since MATERIALIZATIONS is always parsed last (after DIMENSIONS and METRICS are already fully parsed), parse it inline but use the already-constructed `dimensions` and `metrics` vecs for validation.

    f. Implement `parse_materializations_clause(body: &str, base_offset: usize) -> Result<Vec<Materialization>, ParseError>`:
    - Use `split_at_depth0_commas(body)` to split top-level materialization entries.
    - For each entry, parse `name AS (...)` pattern:
      - Extract name before `AS` (case-insensitive).
      - Extract sub-body inside the parenthesized block after `AS`.
      - Within sub-body, scan for TABLE, DIMENSIONS, METRICS sub-keywords.
      - TABLE sub-clause: everything between TABLE keyword and the next comma or sub-keyword. Trim whitespace.
      - DIMENSIONS sub-clause: extract parenthesized list, split by comma. Each item is a dimension name (trimmed).
      - METRICS sub-clause: extract parenthesized list, split by comma. Each item is a metric name (trimmed).
    - Validate: at least one of DIMENSIONS or METRICS must be non-empty. Error: "Materialization 'name': must specify at least one of DIMENSIONS or METRICS."

    g. Add validation in `parse_keyword_body` after the existing window metric validation (after line 538), before the final `Ok(KeywordBody {...})`:

    - **Duplicate name check:** Iterate materializations, case-insensitive name comparison. Error: "Duplicate materialization name 'X'."
    - **Dimension reference check:** For each materialization, for each dimension name, verify it exists in `dimensions` vec (case-insensitive). Use `crate::util::suggest_closest` for "Did you mean?" Error format: "Materialization 'name': dimension 'X' not found in semantic view dimensions. Did you mean 'Y'?"
    - **Metric reference check:** For each materialization, for each metric name, verify it exists in `metrics` vec (case-insensitive). Use `crate::util::suggest_closest` for "Did you mean?" Error format: "Materialization 'name': metric 'X' not found in semantic view metrics. Did you mean 'Y'?"

    h. Return `materializations` in the `KeywordBody` struct at the end of `parse_keyword_body`.

    **4. Update parse.rs (rewrite_ddl_keyword_body, around line 1117-1130):**

    Add `materializations: keyword_body.materializations` to the `SemanticViewDefinition` construction:
    ```rust
    let def = crate::model::SemanticViewDefinition {
        base_table,
        tables: keyword_body.tables,
        dimensions: keyword_body.dimensions,
        metrics: keyword_body.metrics,
        joins: keyword_body.relationships,
        facts: keyword_body.facts,
        materializations: keyword_body.materializations,  // <-- ADD THIS
        column_type_names: vec![],
        column_types_inferred: vec![],
        created_on: None,
        database_name: None,
        schema_name: None,
        comment: view_comment,
    };
    ```

    **5. Update render_ddl.rs:**

    a. Add `emit_materializations` function (after `emit_metrics`, before `render_create_ddl`):
    ```rust
    /// Emit MATERIALIZATIONS clause entries.
    fn emit_materializations(out: &mut String, def: &SemanticViewDefinition) {
        out.push_str("MATERIALIZATIONS (\n");
        for (i, mat) in def.materializations.iter().enumerate() {
            out.push_str("    ");
            out.push_str(&mat.name);
            out.push_str(" AS (\n");
            out.push_str("        TABLE ");
            out.push_str(&mat.table);
            if !mat.dimensions.is_empty() || !mat.metrics.is_empty() {
                out.push_str(",\n");
            }
            if !mat.dimensions.is_empty() {
                out.push_str("        DIMENSIONS (");
                out.push_str(&mat.dimensions.join(", "));
                out.push(')');
                if !mat.metrics.is_empty() {
                    out.push(',');
                }
                out.push('\n');
            }
            if !mat.metrics.is_empty() {
                out.push_str("        METRICS (");
                out.push_str(&mat.metrics.join(", "));
                out.push_str(")\n");
            }
            out.push_str("    )");
            if i + 1 < def.materializations.len() {
                out.push(',');
            }
            out.push('\n');
        }
        out.push_str(")\n");
    }
    ```

    b. Add the materializations call in `render_create_ddl` after the metrics emission (after line 289, before `Ok(out)`):
    ```rust
    if !def.materializations.is_empty() {
        emit_materializations(&mut out, def);
    }
    ```

    **6. Add unit tests:**

    - In `model.rs` `#[cfg(test)] mod tests`: Add `mod phase54_materialization_tests` with tests for:
      - Materialization struct JSON round-trip
      - SemanticViewDefinition with materializations round-trip
      - Old JSON without materializations field deserializes to empty vec (backward compat)
      - Empty materializations vec omitted from JSON (skip_serializing_if)
      - YAML round-trip for materializations
      - YAML and JSON produce identical structs with materializations

    - In `body_parser.rs` test module: Add tests for:
      - parse_materializations_clause single entry
      - parse_materializations_clause multiple entries
      - parse_materializations_clause DIMENSIONS-only entry
      - parse_materializations_clause METRICS-only entry
      - parse_materializations_clause rejects empty (no DIMS, no METRICS)
      - parse_keyword_body with MATERIALIZATIONS clause
      - MATERIALIZATIONS before METRICS rejected (ordering)
      - Materialization dimension reference validation with suggestion
      - Materialization metric reference validation with suggestion
      - Duplicate materialization name rejected

    - In `render_ddl.rs` test module: Add tests for:
      - render_create_ddl emits MATERIALIZATIONS clause
      - render_create_ddl omits MATERIALIZATIONS when empty
      - DDL round-trip (render -> parse -> render) preserves materializations

    **7. Add proptest for Materialization in tests/yaml_proptest.rs:**

    Read `tests/yaml_proptest.rs` first. This file contains the existing `arb_definition()` strategy and `yaml_json_roundtrip_equivalence` proptest. Update it to include Materialization:

    a. Add `Materialization` to the import from `semantic_views::model`:
    ```rust
    use semantic_views::model::{
        AccessModifier, Cardinality, Dimension, Fact, Join, JoinColumn, Materialization, Metric,
        NonAdditiveDim, NullsOrder, SemanticViewDefinition, SortOrder, TableRef, WindowOrderBy,
        WindowSpec,
    };
    ```

    b. Add an `arb_materialization()` strategy function (after `arb_join`, before `arb_definition`):
    ```rust
    fn arb_materialization() -> impl Strategy<Value = Materialization> {
        (
            arb_name(),
            arb_name(),
            proptest::collection::vec(arb_name(), 0..=3),
            proptest::collection::vec(arb_name(), 0..=3),
        )
            .prop_map(|(name, table, dimensions, metrics)| Materialization {
                name,
                table,
                dimensions,
                metrics,
            })
    }
    ```

    c. Update `arb_definition()` to include materializations. Add `proptest::collection::vec(arb_materialization(), 0..=2)` to the tuple and wire it into the `SemanticViewDefinition` construction:
    ```rust
    fn arb_definition() -> impl Strategy<Value = SemanticViewDefinition> {
        (
            arb_name(),
            proptest::collection::vec(arb_table_ref(), 0..=2),
            proptest::collection::vec(arb_dimension(), 1..=3),
            proptest::collection::vec(arb_metric(), 1..=3),
            proptest::collection::vec(arb_join(), 0..=2),
            proptest::collection::vec(arb_fact(), 0..=2),
            proptest::option::of("[a-z ]{1,30}"),
            proptest::collection::vec(arb_materialization(), 0..=2),
        )
            .prop_map(
                |(base_table, tables, dimensions, metrics, joins, facts, comment, materializations)| {
                    SemanticViewDefinition {
                        base_table,
                        tables,
                        dimensions,
                        metrics,
                        joins,
                        facts,
                        materializations,
                        column_type_names: vec![],
                        column_types_inferred: vec![],
                        created_on: None,
                        database_name: None,
                        schema_name: None,
                        comment,
                    }
                },
            )
    }
    ```

    The existing `yaml_json_roundtrip_equivalence` proptest will automatically exercise Materialization round-trips because it uses `arb_definition()` -- no changes needed to the proptest! block itself.

    d. Add a standalone Materialization-specific proptest in the same `proptest!` block:
    ```rust
    #[test]
    fn materialization_json_roundtrip(mat in arb_materialization()) {
        let json_str = serde_json::to_string(&mat).expect("JSON serialize");
        let roundtripped: Materialization = serde_json::from_str(&json_str).expect("JSON deserialize");
        prop_assert_eq!(mat, roundtripped);
    }
    ```

    **Important implementation notes:**
    - The sub-clause parser for MATERIALIZATIONS entries must handle 3 levels of nesting: clause body -> entry sub-body (after `AS (`) -> DIMENSIONS/METRICS lists (parenthesized). Use `split_at_depth0_commas` for the top-level entry splitting. For the inner sub-body, implement a simple keyword scanner that finds TABLE, DIMENSIONS, METRICS positions and extracts content between them.
    - The TABLE sub-clause value is the text between the TABLE keyword and the next comma or DIMENSIONS/METRICS keyword. Accept any string (no format validation -- query-time validation in Phase 55).
    - Case-insensitive keyword matching throughout (`.eq_ignore_ascii_case`).
  </action>
  <verify>
    <automated>cargo test materialization -- --nocapture 2>&1 | tail -20</automated>
  </verify>
  <acceptance_criteria>
    - src/model.rs contains `pub struct Materialization` with fields: name, table, dimensions, metrics
    - src/model.rs contains `pub materializations: Vec<Materialization>` on SemanticViewDefinition with `#[serde(default, skip_serializing_if = "Vec::is_empty")]`
    - src/model.rs Materialization has derives: Debug, Clone, Default, PartialEq, Serialize, Deserialize, and cfg_attr arbitrary::Arbitrary
    - src/body_parser.rs CLAUSE_KEYWORDS array contains "materializations" as last entry
    - src/body_parser.rs CLAUSE_ORDER array contains "materializations" as last entry
    - src/body_parser.rs contains `pub(crate) fn parse_materializations_clause`
    - src/body_parser.rs KeywordBody struct has `pub materializations: Vec<Materialization>` field
    - src/body_parser.rs contains validation for duplicate materialization names
    - src/body_parser.rs contains validation for materialization dimension references with suggest_closest
    - src/body_parser.rs contains validation for materialization metric references with suggest_closest
    - src/parse.rs rewrite_ddl_keyword_body contains `materializations: keyword_body.materializations`
    - src/render_ddl.rs contains `fn emit_materializations`
    - src/render_ddl.rs render_create_ddl calls emit_materializations when non-empty
    - tests/yaml_proptest.rs contains `arb_materialization` strategy function
    - tests/yaml_proptest.rs `arb_definition` includes materializations field
    - tests/yaml_proptest.rs contains `materialization_json_roundtrip` proptest
    - `cargo test materialization` passes all tests including proptests (exit code 0)
    - `cargo test` passes (all existing tests still green)
  </acceptance_criteria>
  <done>
    Materialization struct exists in model.rs with serde derives. MATERIALIZATIONS clause parses in body_parser.rs. parse.rs wires materializations through to SemanticViewDefinition. render_ddl.rs reconstructs MATERIALIZATIONS clause. Define-time validation catches bad dim/metric references with suggestions. Proptest coverage: arb_materialization strategy added to tests/yaml_proptest.rs, arb_definition updated to include materializations, standalone materialization_json_roundtrip proptest added, existing yaml_json_roundtrip_equivalence proptest automatically covers Materialization via updated arb_definition. All unit tests and proptests pass.
  </done>
</task>

<task type="auto">
  <name>Task 2: sqllogictest integration tests for MATERIALIZATIONS DDL and persistence</name>
  <files>test/sql/phase54_materializations.test</files>
  <read_first>
    test/sql/phase52_yaml_ddl.test
    test/sql/phase45_get_ddl.test
    test/sql/phase42_persistence.test
    src/model.rs
    src/body_parser.rs
    src/render_ddl.rs
  </read_first>
  <action>
    Create `test/sql/phase54_materializations.test` with sqllogictest integration tests covering the full DDL pipeline for MATERIALIZATIONS. This requires a fresh `just build` before running.

    **Test sections:**

    **Section 1: Basic MATERIALIZATIONS clause parsing and querying**
    ```sql
    # Load extension
    require semantic_views

    # Create a base table for testing
    statement ok
    CREATE TABLE orders (id INTEGER, region VARCHAR, amount DECIMAL(10,2), order_date DATE);

    # Create a pre-aggregated table (materialization target)
    statement ok
    CREATE TABLE daily_revenue_agg (region VARCHAR, revenue DECIMAL(10,2), order_count BIGINT);

    # Create semantic view with single materialization
    statement ok
    CREATE SEMANTIC VIEW revenue_view AS
    TABLES (
        o AS orders PRIMARY KEY (id)
    )
    DIMENSIONS (
        o.region AS o.region
    )
    METRICS (
        o.revenue AS SUM(o.amount),
        o.order_count AS COUNT(*)
    )
    MATERIALIZATIONS (
        daily_rev AS (
            TABLE daily_revenue_agg,
            DIMENSIONS (region),
            METRICS (revenue, order_count)
        )
    )

    # Verify the view is queryable (materializations don't affect query behavior yet -- that's Phase 55)
    query I
    SELECT count(*) FROM semantic_view('revenue_view', dimensions := ['region'], metrics := ['revenue'])
    ----
    0
    ```

    **Section 2: Multiple materializations**
    ```sql
    statement ok
    CREATE TABLE monthly_revenue_agg (region VARCHAR, revenue DECIMAL(10,2));

    statement ok
    CREATE OR REPLACE SEMANTIC VIEW revenue_view AS
    TABLES (
        o AS orders PRIMARY KEY (id)
    )
    DIMENSIONS (
        o.region AS o.region
    )
    METRICS (
        o.revenue AS SUM(o.amount),
        o.order_count AS COUNT(*)
    )
    MATERIALIZATIONS (
        daily_rev AS (
            TABLE daily_revenue_agg,
            DIMENSIONS (region),
            METRICS (revenue, order_count)
        ),
        monthly_rev AS (
            TABLE monthly_revenue_agg,
            DIMENSIONS (region),
            METRICS (revenue)
        )
    )
    ```

    **Section 3: GET_DDL round-trip with materializations**
    ```sql
    # GET_DDL should include MATERIALIZATIONS clause
    query I
    SELECT GET_DDL('SEMANTIC_VIEW', 'revenue_view') LIKE '%MATERIALIZATIONS%'
    ----
    true

    query I
    SELECT GET_DDL('SEMANTIC_VIEW', 'revenue_view') LIKE '%daily_rev%'
    ----
    true

    query I
    SELECT GET_DDL('SEMANTIC_VIEW', 'revenue_view') LIKE '%monthly_rev%'
    ----
    true
    ```

    **Section 4: View without materializations (backward compat)**
    ```sql
    statement ok
    CREATE SEMANTIC VIEW simple_view AS
    TABLES (
        o AS orders PRIMARY KEY (id)
    )
    DIMENSIONS (
        o.region AS o.region
    )
    METRICS (
        o.revenue AS SUM(o.amount)
    )

    # GET_DDL should NOT include MATERIALIZATIONS for view without them
    query I
    SELECT GET_DDL('SEMANTIC_VIEW', 'simple_view') LIKE '%MATERIALIZATIONS%'
    ----
    false
    ```

    **Section 5: Error cases -- validation**
    ```sql
    # Materialization referencing non-existent dimension
    statement error
    CREATE SEMANTIC VIEW bad_dim_view AS
    TABLES (o AS orders PRIMARY KEY (id))
    DIMENSIONS (o.region AS o.region)
    METRICS (o.revenue AS SUM(o.amount))
    MATERIALIZATIONS (
        bad_mat AS (
            TABLE some_table,
            DIMENSIONS (nonexistent_dim),
            METRICS (revenue)
        )
    )
    ----
    dimension 'nonexistent_dim' not found

    # Materialization referencing non-existent metric
    statement error
    CREATE SEMANTIC VIEW bad_met_view AS
    TABLES (o AS orders PRIMARY KEY (id))
    DIMENSIONS (o.region AS o.region)
    METRICS (o.revenue AS SUM(o.amount))
    MATERIALIZATIONS (
        bad_mat AS (
            TABLE some_table,
            DIMENSIONS (region),
            METRICS (nonexistent_metric)
        )
    )
    ----
    metric 'nonexistent_metric' not found

    # Materialization with neither DIMENSIONS nor METRICS
    statement error
    CREATE SEMANTIC VIEW empty_mat_view AS
    TABLES (o AS orders PRIMARY KEY (id))
    DIMENSIONS (o.region AS o.region)
    METRICS (o.revenue AS SUM(o.amount))
    MATERIALIZATIONS (
        empty_mat AS (
            TABLE some_table
        )
    )
    ----
    must specify at least one of DIMENSIONS or METRICS

    # Duplicate materialization names
    statement error
    CREATE SEMANTIC VIEW dup_mat_view AS
    TABLES (o AS orders PRIMARY KEY (id))
    DIMENSIONS (o.region AS o.region)
    METRICS (o.revenue AS SUM(o.amount))
    MATERIALIZATIONS (
        same_name AS (
            TABLE table1,
            DIMENSIONS (region)
        ),
        same_name AS (
            TABLE table2,
            METRICS (revenue)
        )
    )
    ----
    Duplicate materialization name
    ```

    **Section 6: MATERIALIZATIONS clause ordering**
    ```sql
    # MATERIALIZATIONS before METRICS should fail
    statement error
    CREATE SEMANTIC VIEW order_err_view AS
    TABLES (o AS orders PRIMARY KEY (id))
    DIMENSIONS (o.region AS o.region)
    MATERIALIZATIONS (
        bad AS (TABLE t, DIMENSIONS (region))
    )
    METRICS (o.revenue AS SUM(o.amount))
    ----
    out of order
    ```

    **Section 7: DIMENSIONS-only and METRICS-only materializations**
    ```sql
    statement ok
    CREATE SEMANTIC VIEW partial_mat_view AS
    TABLES (o AS orders PRIMARY KEY (id))
    DIMENSIONS (o.region AS o.region)
    METRICS (o.revenue AS SUM(o.amount))
    MATERIALIZATIONS (
        dims_only AS (
            TABLE t1,
            DIMENSIONS (region)
        ),
        mets_only AS (
            TABLE t2,
            METRICS (revenue)
        )
    )
    ```

    **Section 8: YAML with materializations (via FROM YAML)**
    ```sql
    statement ok
    CREATE OR REPLACE SEMANTIC VIEW yaml_mat_view FROM YAML $$
    base_table: orders
    tables:
      - alias: o
        table: orders
        pk_columns:
          - id
    dimensions:
      - name: region
        expr: o.region
        source_table: o
    metrics:
      - name: revenue
        expr: SUM(o.amount)
        source_table: o
    materializations:
      - name: daily_rev
        table: daily_revenue_agg
        dimensions:
          - region
        metrics:
          - revenue
    $$

    # Verify YAML-created view with materializations has GET_DDL output including materializations
    query I
    SELECT GET_DDL('SEMANTIC_VIEW', 'yaml_mat_view') LIKE '%MATERIALIZATIONS%'
    ----
    true

    query I
    SELECT GET_DDL('SEMANTIC_VIEW', 'yaml_mat_view') LIKE '%daily_rev%'
    ----
    true
    ```

    **Section 9: Qualified table names in materialization TABLE clause**
    ```sql
    statement ok
    CREATE OR REPLACE SEMANTIC VIEW qual_mat_view AS
    TABLES (o AS orders PRIMARY KEY (id))
    DIMENSIONS (o.region AS o.region)
    METRICS (o.revenue AS SUM(o.amount))
    MATERIALIZATIONS (
        qual_mat AS (
            TABLE catalog.schema.daily_revenue_agg,
            DIMENSIONS (region),
            METRICS (revenue)
        )
    )

    query I
    SELECT GET_DDL('SEMANTIC_VIEW', 'qual_mat_view') LIKE '%catalog.schema.daily_revenue_agg%'
    ----
    true
    ```

    **Section 10: Cleanup**
    ```sql
    statement ok
    DROP SEMANTIC VIEW IF EXISTS revenue_view

    statement ok
    DROP SEMANTIC VIEW IF EXISTS simple_view

    statement ok
    DROP SEMANTIC VIEW IF EXISTS partial_mat_view

    statement ok
    DROP SEMANTIC VIEW IF EXISTS yaml_mat_view

    statement ok
    DROP SEMANTIC VIEW IF EXISTS qual_mat_view
    ```

    Adapt the exact error message substrings to match what the body parser actually produces (the error messages in Section 5 may need minor adjustments based on the exact format from Task 1's implementation). The key is that each `statement error` block has a `----` line followed by a substring that must appear in the error message.
  </action>
  <verify>
    <automated>just build && just test-sql 2>&1 | tail -20</automated>
  </verify>
  <acceptance_criteria>
    - test/sql/phase54_materializations.test exists with at least 50 lines
    - test/sql/phase54_materializations.test contains `require semantic_views`
    - test/sql/phase54_materializations.test contains at least 4 `statement error` blocks testing validation
    - test/sql/phase54_materializations.test contains `FROM YAML` test for YAML materializations
    - test/sql/phase54_materializations.test contains GET_DDL round-trip checks
    - test/sql/phase54_materializations.test contains qualified table name test (catalog.schema.table)
    - `just build && just test-sql` passes (exit code 0)
    - `just test-all` passes (full quality gate)
  </acceptance_criteria>
  <done>
    sqllogictest integration tests cover: basic MATERIALIZATIONS DDL, multiple materializations, GET_DDL round-trip, backward compatibility (no materializations), validation errors (bad dim/metric refs, empty mat, duplicates, ordering), DIMENSIONS-only and METRICS-only materializations, YAML materializations, and qualified table names. Full test suite passes.
  </done>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| User DDL input -> body parser | Untrusted SQL/YAML input parsed into Materialization structs |
| Materialization table name -> stored JSON | User-provided table name stored as data, not interpolated into SQL at define time |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-54-01 | Tampering | Materialization table name in stored JSON | accept | Table name stored as JSON string data via serde. NOT interpolated into any SQL at define time. At query time (Phase 55), the table name will be quoted via DuckDB's identifier quoting when constructing the routing SQL. No define-time SQL injection vector exists. |
| T-54-02 | Denial of Service | Oversized MATERIALIZATIONS clause | mitigate | Existing body parser depth tracking (`split_at_depth0_commas` parenthesis depth counter) limits nesting. YAML path has YAML_SIZE_CAP (1 MiB) applied before parsing. SQL DDL is bounded by DuckDB's statement size limits. No additional mitigation needed beyond existing controls. |
| T-54-03 | Information Disclosure | Materialization table names in GET_DDL | accept | GET_DDL exposes stored definition metadata by design. Materialization table names are not secrets -- they reference tables the user explicitly declared. Same trust model as existing table names in TABLES clause. |
</threat_model>

<verification>
1. `cargo test materialization` -- all Materialization-related unit tests and proptests pass
2. `cargo test` -- all existing tests still green (no regressions)
3. `just build && just test-sql` -- sqllogictest integration tests pass
4. `just test-all` -- full quality gate (Rust + sqllogictest + DuckLake CI)
</verification>

<success_criteria>
1. `MATERIALIZATIONS` clause accepted in SQL DDL with named entries containing TABLE, DIMENSIONS, METRICS sub-clauses
2. `materializations` field in YAML definitions produces identical internal representation as SQL DDL
3. Materialization metadata persists via JSON serialization with backward compatibility (old JSON without field loads to empty vec)
4. Define-time validation rejects bad dim/metric references with "Did you mean?" suggestions
5. GET_DDL round-trip preserves materializations
6. Proptest coverage: Materialization JSON round-trip and SemanticViewDefinition JSON/YAML round-trip with materializations
7. `just test-all` passes
</success_criteria>

<output>
After completion, create `.planning/phases/54-materialization-model-ddl/54-01-SUMMARY.md`
</output>
