Creating Interactive Notebooks

We also have some skill templates for creating notebooks on top of DDP data with marimo. The guides below are opinionated but hopefully useful as inspiration. You can also point your agent to the full repo.

# Creating an Interactive Notebook

DDP notebooks are marimo notebooks (`.py` files, not `.ipynb`). Marimo uses a DAG of cells
with automatic dependency resolution — cell order in the file doesn't affect execution order.
For the full style guide, see `notebooks/CLAUDE.md` in the repo.

## Required Boilerplate

Every notebook needs three things:

**1. File header** (top of file):
```python
import marimo

__generated_with = "unknown"
app = marimo.App(width="full")
```

**2. Setup cell** (can be placed at the bottom — marimo resolves deps automatically):
```python
@app.cell(hide_code=True)
def setup_pyoso():
    # This code sets up pyoso to be used as a database provider for this notebook
    # This code is autogenerated. Modification could lead to unexpected results :)
    import pyoso
    import marimo as mo
    pyoso_db_conn = pyoso.Client().dbapi_connection()
    return mo, pyoso_db_conn
```

**3. Entry point** (bottom of file):
```python
if __name__ == "__main__":
    app.run()
```

## Running & Validating

```bash
# Check structure (catches cycles, multiple definitions, import errors)
uv run marimo check notebooks/my-notebook.py

# Edit interactively in browser (cells don't auto-execute on open)
uv run marimo edit notebooks/my-notebook.py

# Run headless — executes all cells, useful for verifying end-to-end
uv run marimo run notebooks/my-notebook.py
```

Always run `marimo check` before committing. It catches problems that
won't surface until runtime.

## Cell Conventions

- **All cells** use `@app.cell(hide_code=True)` — no exceptions

- Use `mo.sql(query, engine=pyoso_db_conn)` for SQL queries

- Prefix throwaway variables with `_` to avoid "multiple definitions" errors

- Use `"""` for markdown strings, not `r"""`

- Named data for downstream cells should be descriptive: `df_top_ecosystems`

- Each cell must explicitly `return` all variables other cells need

- Declare dependencies via function args: `def _(mo, px, df_data):`

## Common Anti-Patterns

| Don't | Do Instead |
|-------|------------|
| `conn = pyoso.Client()` in random cells | Use `pyoso_db_conn` from `setup_pyoso` |
| `conn.to_pandas(sql)` | `mo.sql(sql, engine=pyoso_db_conn)` |
| Import `mo` in multiple cells | Get `mo` from `setup_pyoso` via function args |
| `from dotenv import load_dotenv` | Rely on `direnv` — keys load automatically |
| Reuse a variable name across cells | Prefix with `_` or give a unique name |
| `@app.cell` without `hide_code=True` | Always include `hide_code=True` |
| `r"""` raw strings for markdown | Use plain `"""` triple-quote strings |
| Circular references between cells | Restructure so data flows one direction |
| `SELECT *` in queries | List columns explicitly for clarity |
| Wide date ranges in live queries | Use `CURRENT_DATE - INTERVAL '30' DAY` or narrower |

## Notebook Structure (recommended order)

1. Title + intro

2. Overview section

3. Data lineage (`mo.mermaid()`)

4. Model previews (accordion with SQL snippets)

5. Live data exploration (stats + charts)

6. Sample queries (markdown + executed pairs)

7. Related models

8. Helper utilities cell

9. Imports cell (`plotly.express`, `pandas`)

10. Setup cell (`setup_pyoso`)

# Advanced Notebook Patterns

Deeper patterns for polished DDP notebooks. Assumes familiarity with the basics above.

## Caching Expensive Queries

Use `mo.persistent_cache` to avoid re-running slow queries during development.
The cache persists across sessions and is keyed by cell content — if the SQL changes,
the cache invalidates automatically.

```python
@app.cell(hide_code=True)
def _(mo, pyoso_db_conn):
    with mo.persistent_cache("my_query_data"):
        df_results = mo.sql(
            """
            SELECT ecosystem_name, day, total_devs
            FROM oso.stg_opendevdata__eco_mads
            WHERE day >= CURRENT_DATE - INTERVAL '365' DAY
            """,
            engine=pyoso_db_conn,
            output=False
        )
    return (df_results,)
```

**Tips:**

- Give each cache a unique, descriptive name

- Use `output=False` when you need the DataFrame for downstream processing rather than inline display

- Wrap the entire query block — the `with` block defines what gets cached

- Especially useful for queries that scan large date ranges or join multiple tables

## Plotly Chart Templates

All charts use `plotly_white` template with consistent axis styling.

### Bar Chart (horizontal)
```python
_fig = px.bar(
    df_data,
    x='value_column',
    y='label_column',
    orientation='h',
    text='value_column',
    color_discrete_sequence=['#4C78A8']
)
_fig.update_traces(
    texttemplate='%{text:,.0f}',
    textposition='outside'
)
_fig.update_layout(
    template='plotly_white',
    margin=dict(t=20, l=0, r=0, b=0),
    height=400,
    showlegend=False,
    yaxis=dict(categoryorder='total ascending')
)
```

### Line / Area Chart (time series)
```python
_fig = px.area(
    df_timeseries,
    x='day',
    y='value',
    color='series_name',
    color_discrete_sequence=['#4C78A8', '#F58518', '#72B7B2']
)
_fig.update_layout(
    template='plotly_white',
    margin=dict(t=20, l=0, r=0, b=0),
    height=400,
    hovermode='x unified',
    xaxis=dict(tickformat='%b %Y'),
    legend=dict(
        orientation='h', yanchor='bottom', y=1.02,
        xanchor='right', x=1, title_text=''
    )
)
```

### Shared Axis Styling
Apply to all charts:
```python
_fig.update_xaxes(title='', showgrid=False, linecolor='#000', linewidth=1)
_fig.update_yaxes(title='Y Label', showgrid=True, gridcolor='#E5E5E5',
                  linecolor='#000', linewidth=1)
```

### Rendering
Always render with the mode bar hidden:
```python
mo.ui.plotly(_fig, config={'displayModeBar': False})
```

### Color Palette

- Primary: `#4C78A8` (blue) — default for single-series

- Secondary: `#F58518` (orange) — second series

- Tertiary: `#72B7B2` (teal) — third series

- Area fill: 0.2 opacity (e.g., `rgba(76, 120, 168, 0.2)`)

## Stats Widgets

Place summary stats above charts using `mo.stat`:
```python
mo.vstack([
    mo.hstack([
        mo.stat(label="Active Devs", value=f"{dev_count:,}",
                bordered=True, caption="Last 30 days"),
        mo.stat(label="Total Repos", value=f"{repo_count:,}",
                bordered=True, caption="Recursive mapping"),
        mo.stat(label="Peak MAD", value=f"{peak:,}",
                bordered=True, caption=f"{peak_date:%b %Y}"),
    ], widths='equal', gap=1),
    mo.ui.plotly(_fig, config={'displayModeBar': False}),
])
```

**Rules:** Always `bordered=True`, always include `caption`, 3-4 stats per row.

## Table Formatting

Use `mo.ui.table` with formatting for large numeric IDs:
```python
# Format integer ID columns to avoid scientific notation
fmt = {
    c: '{:.0f}'
    for c in df.columns
    if df[c].dtype == 'int64' and ('_id' in c or c == 'id')
}
mo.ui.table(
    df,
    format_mapping=fmt,
    show_column_summaries=False,
    show_data_types=False
)
```

For read-only metric tables, disable selection and enable pagination:
```python
mo.ui.table(df_metrics, selection=None, pagination=True)
```

## Table Previews with Accordion

The `render_table_preview` helper wraps a model preview in an accordion
with row count, column count, and a SQL snippet:
```python
# In your helper utilities cell:
def render_table_preview(model_name):
    df = get_model_preview(model_name)
    sql_snippet = generate_sql_snippet(model_name, df)
    row_count = get_row_count(model_name)
    col_count = len(df.columns)
    title = f"{model_name} | {row_count:,.0f} rows, {col_count} cols"
    return mo.accordion({title: mo.vstack([sql_snippet, table])})

# Then in content cells:
render_table_preview('oso.stg_opendevdata__ecosystems')
```

## Data Lineage Diagrams

Use `mo.mermaid()` for showing model relationships:
```python
@app.cell(hide_code=True)
def _(mo):
    mo.mermaid("""
    graph TD
        A[source_model<br/>Raw data] --> B[int_model<br/>Cleaned]
        B --> C[final_model<br/>Ready to query]
    """)
    return
```