---
phase: 14-ducklake-integration-test-refresh-and-ci-job
plan: "01"
type: execute
wave: 1
depends_on: []
files_modified:
  - test/integration/test_ducklake_helpers.py
  - test/integration/test_ducklake_ci.py
autonomous: true
requirements: [DUCKLAKE-CI]

must_haves:
  truths:
    - "test_ducklake_helpers.py exists with shared extension-load and DuckLake-attach helpers"
    - "test_ducklake_ci.py runs without any jaffle-shop data files — all data is generated inline"
    - "CI test covers all 6 test cases: define view, query with dimension, global aggregate, explain, typed BIGINT output, time dimension with known dates"
    - "Typed output assertion: order_count metric returns Python int (BIGINT), not str"
    - "Time dimension assertion: ordered_at truncated to day returns datetime.date values matching known inserted dates"
  artifacts:
    - path: "test/integration/test_ducklake_helpers.py"
      provides: "Shared helpers: get_ext_dir, load_extension, attach_ducklake, teardown"
    - path: "test/integration/test_ducklake_ci.py"
      provides: "CI integration test with inline synthetic data"
  key_links:
    - from: "test_ducklake_ci.py"
      to: "test_ducklake_helpers.py"
      via: "import from test_ducklake_helpers"
      pattern: "from test_ducklake_helpers import"
    - from: "test_ducklake_ci.py"
      to: "create_semantic_view (v0.2.0 API)"
      via: "con.execute STRUCT/LIST call"
      pattern: "create_semantic_view"
    - from: "test_ducklake_ci.py"
      to: "semantic_view (v0.2.0 API)"
      via: "con.execute table function"
      pattern: "semantic_view"
---

<objective>
Create the shared helpers module and the new CI integration test with inline synthetic data.

Purpose: The CI test is the net-new deliverable of Phase 14 — a self-contained test that runs in GitHub Actions without any jaffle-shop data downloads. The helpers module reduces duplication between CI and local tests.
Output: test_ducklake_helpers.py (shared boilerplate) + test_ducklake_ci.py (CI test, 6 test cases).
</objective>

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

<context>
@.planning/phases/14-ducklake-integration-test-refresh-and-ci-job/14-CONTEXT.md
@.planning/phases/14-ducklake-integration-test-refresh-and-ci-job/14-RESEARCH.md
@test/integration/test_ducklake.py

<interfaces>
<!-- v0.2.0 API confirmed in src/lib.rs and test/sql/phase4_query.test -->

create_semantic_view 6-argument STRUCT/LIST signature:
```sql
SELECT create_semantic_view(
    'view_name',                                              -- arg 0: name VARCHAR
    [{'alias': 'o', 'table': 'schema.table_name'}],          -- arg 1: tables LIST(STRUCT)
    [],                                                       -- arg 2: relationships LIST(STRUCT)
    [{'name': 'col', 'expr': 'col', 'source_table': 'o'}],   -- arg 3: dimensions LIST(STRUCT)
    [{'name': 'ts', 'expr': 'ts_col', 'granularity': 'day'}],-- arg 4: time_dimensions LIST(STRUCT)
    [{'name': 'cnt', 'expr': 'count(*)', 'source_table': 'o'}] -- arg 5: metrics LIST(STRUCT)
)
```

Query:
```sql
SELECT * FROM semantic_view('view_name', dimensions := ['col'], metrics := ['cnt'])
```

Explain:
```sql
SELECT * FROM explain_semantic_view('view_name', dimensions := ['col'], metrics := ['cnt'])
```

Cleanup:
```sql
SELECT drop_semantic_view('view_name')
```

Extension path: Project ROOT / "build" / "debug" / "semantic_views.duckdb_extension"
CI extension path: set via SEMANTIC_VIEWS_EXTENSION_PATH env var (target/debug/ for cargo builds)

DuckLake ATTACH:
```sql
ATTACH 'ducklake:/path/to/file.ducklake' AS alias (DATA_PATH '/path/to/data/')
```
Note: DATA_PATH must end with trailing slash.

Jaffle-shop raw_orders schema (from test_ducklake.py comments):
  id INTEGER, customer VARCHAR, ordered_at DATE/TIMESTAMP,
  store_id INTEGER, subtotal INTEGER, tax_paid INTEGER, order_total INTEGER
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Create test_ducklake_helpers.py — shared extension load and DuckLake attach boilerplate</name>
  <files>test/integration/test_ducklake_helpers.py</files>
  <action>
Create `test/integration/test_ducklake_helpers.py` as a PEP 723 uv script with `dependencies = ["duckdb"]`.

The module provides four helper functions used by both the CI test and the updated local test:

1. `get_project_root() -> Path` — resolves to the project root (3 levels up from the helpers file: test/integration/test_ducklake_helpers.py → project root).

2. `get_ext_dir() -> str` — returns `str(get_project_root() / "test" / "data" / "duckdb_extensions")`, creating the directory if it doesn't exist.

3. `get_extension_path() -> Path` — reads `SEMANTIC_VIEWS_EXTENSION_PATH` env var first. If set, return `Path(os.environ["SEMANTIC_VIEWS_EXTENSION_PATH"])`. If not set, fall back to `get_project_root() / "build" / "debug" / "semantic_views.duckdb_extension"`.

4. `load_extension(con, extension_path: Path) -> None` — executes:
   ```python
   con.execute(f"INSTALL '{extension_path}'")
   con.execute("LOAD semantic_views")
   con.execute("LOAD ducklake")
   ```

5. `attach_ducklake(con, ducklake_file: str, data_dir: str, alias: str = 'jaffle') -> None` — executes:
   ```python
   con.execute(f"ATTACH 'ducklake:{ducklake_file}' AS {alias} (DATA_PATH '{data_dir}')")
   ```
   Note: data_dir must end with `/` — callers are responsible for ensuring this.

The helpers module is NOT a test runner itself — it has no main() or if __name__ block. It is a pure library module imported by the test files.

Use standard Python: `import os`, `from pathlib import Path`.
  </action>
  <verify>
    <automated>python3 -c "import sys; sys.path.insert(0, 'test/integration'); from test_ducklake_helpers import get_project_root, get_ext_dir, get_extension_path, load_extension, attach_ducklake; print('imports OK')"</automated>
  </verify>
  <done>test_ducklake_helpers.py exists with 5 exported functions; python3 import succeeds without errors.</done>
</task>

<task type="auto">
  <name>Task 2: Create test_ducklake_ci.py — CI integration test with 6 test cases and inline synthetic data</name>
  <files>test/integration/test_ducklake_ci.py</files>
  <action>
Create `test/integration/test_ducklake_ci.py` as a PEP 723 uv script:
```python
# /// script
# dependencies = ["duckdb"]
# requires-python = ">=3.9"
# ///
```

The test creates DuckLake data entirely in a temp directory, runs 6 test cases, and cleans up. No jaffle-shop files needed.

**Imports and constants:**
```python
import os
import sys
import tempfile
import datetime
from pathlib import Path

# Add test/integration to path for helpers import
sys.path.insert(0, str(Path(__file__).resolve().parent))
from test_ducklake_helpers import get_ext_dir, get_extension_path, load_extension, attach_ducklake
```

**`setup_synthetic_ducklake(ext_dir: str) -> tuple[str, str, str, str]`:**

Creates a DuckLake catalog in a temp directory with known synthetic data. Returns `(tmpdir, catalog_path, ducklake_file, data_dir)`.

1. `tmpdir = tempfile.mkdtemp()`
2. `ducklake_file = os.path.join(tmpdir, "test.ducklake")`
3. `data_dir = os.path.join(tmpdir, "data") + "/"` (trailing slash required)
4. `os.makedirs(data_dir, exist_ok=True)`
5. `catalog_path = os.path.join(tmpdir, "catalog.duckdb")`
6. Create a connection to catalog_path with config `{"allow_unsigned_extensions": "true", "extension_directory": ext_dir}` using `duckdb.connect()`.
7. Execute:
   ```python
   con.execute("INSTALL ducklake")
   con.execute("LOAD ducklake")
   con.execute(f"ATTACH 'ducklake:{ducklake_file}' AS jaffle (DATA_PATH '{data_dir}')")
   ```
8. Create table with known schema (mirrors jaffle-shop raw_orders):
   ```sql
   CREATE TABLE jaffle.raw_orders (
       id INTEGER, customer VARCHAR, ordered_at DATE,
       store_id INTEGER, subtotal INTEGER, tax_paid INTEGER, order_total INTEGER
   )
   ```
9. Insert 5 rows with known values (use these exact values for deterministic assertions):
   ```sql
   INSERT INTO jaffle.raw_orders VALUES
       (1, 'alice',   '2024-01-15', 1, 1000, 80,  1080),
       (2, 'bob',     '2024-01-15', 2, 2000, 160, 2160),
       (3, 'alice',   '2024-02-10', 1,  500,  40,  540),
       (4, 'charlie', '2024-02-10', 2, 1500, 120, 1620),
       (5, 'bob',     '2024-03-05', 1,  800,  64,  864)
   ```
10. Close the catalog connection.
11. Return `(tmpdir, catalog_path, ducklake_file, data_dir)`.

**`run_tests()` function:**

1. Call `ext_dir = get_ext_dir()` and `ext_path = get_extension_path()`.
2. Print extension path and DuckDB version.
3. Call `setup_synthetic_ducklake(ext_dir)` to get `(tmpdir, catalog_path, ducklake_file, data_dir)`.
4. Connect to `catalog_path` with `{"allow_unsigned_extensions": "true", "extension_directory": ext_dir}` using `duckdb.connect()`.
5. Call `load_extension(con, ext_path)`.
6. Call `attach_ducklake(con, ducklake_file, data_dir, alias='jaffle')`.
7. Verify `raw_orders` is accessible (basic sanity check).

Run 6 test cases, tracking `passed` and `failed`:

**Test 1: Define semantic view over DuckLake table**
```python
con.execute("""
    SELECT create_semantic_view(
        'ci_orders',
        [{'alias': 'o', 'table': 'jaffle.raw_orders'}],
        [],
        [{'name': 'store_id', 'expr': 'store_id', 'source_table': 'o'},
         {'name': 'customer', 'expr': 'customer', 'source_table': 'o'}],
        [{'name': 'ordered_at', 'expr': 'ordered_at', 'granularity': 'day'}],
        [{'name': 'order_count', 'expr': 'count(*)', 'source_table': 'o'},
         {'name': 'total_revenue', 'expr': 'sum(order_total)', 'source_table': 'o'}]
    )
""")
```
Assert no exception. PASS if succeeds.

**Test 2: Query with dimension (store_id)**
```python
result = con.execute("""
    SELECT * FROM semantic_view(
        'ci_orders',
        dimensions := ['store_id'],
        metrics := ['order_count']
    )
""").fetchall()
```
Assert `len(result) > 0`. Assert `len({row[0] for row in result}) >= 2` (at least 2 distinct store IDs).

**Test 3: Global aggregate**
```python
result = con.execute("""
    SELECT * FROM semantic_view('ci_orders', metrics := ['order_count', 'total_revenue'])
""").fetchall()
```
Assert `len(result) == 1`. Assert `result[0][0] == 5` (exactly 5 rows inserted). Assert `result[0][1]` > 0 (total_revenue positive — it's DECIMAL so may be string, just check truthy).

**Test 4: Explain output**
```python
result = con.execute("""
    SELECT * FROM explain_semantic_view(
        'ci_orders',
        dimensions := ['store_id'],
        metrics := ['order_count']
    )
""").fetchall()
lines = [row[0] for row in result]
explain_text = "\n".join(lines)
```
Assert `"ci_orders" in explain_text`. Assert `"raw_orders" in explain_text`.

**Test 5: Typed BIGINT output**
```python
result = con.execute("""
    SELECT * FROM semantic_view('ci_orders', metrics := ['order_count'])
""").fetchall()
```
Assert `isinstance(result[0][0], int)`, message: `f"Expected int (BIGINT) for order_count, got {type(result[0][0])}: {result[0][0]}"`.
(count(*) is BIGINT after Phase 12/13 typed output).

**Test 6: Time dimension with known date values**
```python
result = con.execute("""
    SELECT * FROM semantic_view(
        'ci_orders',
        dimensions := ['ordered_at'],
        metrics := ['order_count']
    )
""").fetchall()
```
Assert all `row[0]` values are `datetime.date` instances: `all(isinstance(row[0], datetime.date) for row in result)`.
Extract dates: `dates = {row[0] for row in result}`.
Assert `datetime.date(2024, 1, 15) in dates` (truncated day, 2 rows).
Assert `datetime.date(2024, 2, 10) in dates` (truncated day, 2 rows).
Assert `datetime.date(2024, 3, 5) in dates` (truncated day, 1 row).
Assert `len(result) == 3` (3 distinct days).

**Cleanup:** In a `finally` block:
```python
try:
    con.execute("SELECT drop_semantic_view('ci_orders')")
except Exception:
    pass
con.close()
import shutil
shutil.rmtree(tmpdir, ignore_errors=True)
```

**Summary and exit:** Print `f"Results: {passed} passed, {failed} failed"`. If `failed > 0`: print "FAILED"; `sys.exit(1)`. Else: print "ALL PASSED".

**`main()` and `if __name__ == "__main__"` block** — just calls `run_tests()`. No `check_prerequisites()` — the CI test generates all its own data.
  </action>
  <verify>
    <automated>python3 -c "import ast, sys; ast.parse(open('test/integration/test_ducklake_ci.py').read()); print('syntax OK')"</automated>
  </verify>
  <done>test_ducklake_ci.py exists, passes Python syntax check, imports from test_ducklake_helpers, uses only v0.2.0 API (create_semantic_view, semantic_view, explain_semantic_view, drop_semantic_view), has 6 test cases with typed output and time dimension assertions.</done>
</task>

</tasks>

<verification>
- `test/integration/test_ducklake_helpers.py` exists and imports without error
- `test/integration/test_ducklake_ci.py` exists and passes syntax check
- No call to `define_semantic_view` or `semantic_query` anywhere in either new file
- `test_ducklake_ci.py` imports from `test_ducklake_helpers`
- `test_ducklake_ci.py` has exactly 6 test cases
- Typed output assertion uses `isinstance(result[0][0], int)` for BIGINT
- Time dimension assertions check for `datetime.date` type and known date values
- Synthetic data: 5 rows, 3 distinct dates (2024-01-15 x2, 2024-02-10 x2, 2024-03-05 x1)
</verification>

<success_criteria>
Two new files committed. The CI test is self-contained: no downloads, no fixture files, all data inline. Uses v0.2.0 API exclusively. Runs via `uv run test/integration/test_ducklake_ci.py` after extension is built.
</success_criteria>

<output>
After completion, create `.planning/phases/14-ducklake-integration-test-refresh-and-ci-job/14-01-SUMMARY.md`
</output>
