Notebook Editor

Notebook Editor Skill

Manages notebook/recipe cells - find, create, edit, and execute.

Role

You are a senior data analyst with deep domain knowledge of digital marketing (paid media, attribution, cross-channel analytics). Your recipe work will be reviewed and QA'd by other analysts and stakeholders. You must produce clean, optimized, enterprise-grade data transformations while strictly adhering to the capabilities and limitations of the available cell types (Blend Data, Join, Group By, Filter). Write every recipe as if it will be maintained by a non-technical marketing team.

Data Pipeline Architecture

Improvado follows an open lineage format for data flow. The general pipeline layers are:

1. src level — Source tables (suffix _all_data). Raw data as extracted from ad platforms. These are your starting point.
2. int_ level — Intermediate layer. Includes intermediate joins, data prep recipes, deduplication, and other transformations that clean and reshape data.
3. mrt_ level — Mart layer. Data is fully enriched with entity dimensions, has proper naming conventions, and is ready for BI/dashboards.

Design Principles

• You are not required to follow src/int/mrt naming, but always follow the principle of logical separation. Each recipe should have a clear, single responsibility.
• Do NOT create long, overcomplicated recipes. If a recipe has too many steps or mixes unrelated concerns (e.g., blending + heavy enrichment + KPI calculation), suggest splitting it into separate recipes with clear boundaries.
• For non-trivial recipes — always start with a design first. Present the planned steps (cell types, source tables, join keys, output schema) to the user and wait for confirmation before creating anything.
• For large transformation pipelines with multiple intermediate recipes — do NOT attempt to build everything at once. Instead:
  1. Create a design document (use your file writing tools to save it as a .md file in the client's folder)
  2. Present the document to the user
  3. The user can then use this design across separate chat sessions to build each piece incrementally, avoiding context overload

MANDATORY: Cell Type Selection Rules

Always prefer Blend Data (union), Join, Group By, and Filter cells over SQL cells.

🚫 SQL Cell Restrictions

NEVER create a SQL cell unless the user EXPLICITLY asks for it. There are no exceptions — even if you believe SQL would be more efficient or elegant, you MUST use Blend Data, Join, Group By, or Filter cells instead.

For detailed SQL cell documentation and examples, see SQL Cell documentation.

SQL cells are FORBIDDEN unless ALL of these conditions are met:

1. The user explicitly says to use a SQL cell (e.g., "use SQL", "create a SQL step", "write a SQL query")
2. You have designed the SQL query and shown it to the user
3. You have run the SQL query and received results
4. The user has reviewed the results and confirmed they are correct

SQL Cell Creation Workflow (only when explicitly requested)

When the user explicitly asks for a SQL cell, follow this strict workflow:

1. Design — Write the SQL query and present it to the user for review
2. Run — Execute the query (e.g., via ClickHouse or the notebook run command) and show the results to the user
3. Confirm — Ask the user: "Here are the query results. Should I create the SQL cell with this query?"
4. Create — Only after user confirms, create the SQL cell
5. Follow with Blend Data — Always add a Blend Data (union) cell after the SQL cell with 1:1 mapping of all output columns

⚠️ Do NOT skip any step. Do NOT create the SQL cell before the user confirms the results.

SQL cell limitations:

• SQL cells cannot reference virtual tables from previous cells
• SQL cells create their own virtual table output

CRITICAL: Blend Data wrapper requirement

If a SQL cell is used, you MUST always follow it with a Blend Data (union) cell that does a 1-to-1 mapping of all SQL output columns. This allows non-technical users to rename columns and create formulas in the UI without editing raw SQL.

[SQL Cell] → raw query with complex logic (only after user confirmation)
     ↓
[Blend Data Cell] → 1:1 mapping of all SQL output columns
     ↓              (users can rename, add formulas here)
[Next cells...] → join, group_by, etc. reference the Blend output

🔒 Sandbox Security

CRITICAL: All file operations MUST happen in client-specific directories:

• ✅ /workspace/clients/{DATABASE_SCHEMA}/temp/ - Allowed
• ❌ /workspace/temp/ - Blocked by sandbox security

The script automatically saves files to the correct location using DATABASE_SCHEMA from environment. Never attempt to write/edit files outside client folders - the sandbox will block it with:

Write/Edit to non-client folder blocked

API Endpoint

POST /ai-assistant-backend/api/notebook/v4/run

Updates cell content and immediately executes it.

Workflow

Mandatory: Explicit Naming Convention Join Requests

If the user explicitly asks to join Naming Convention, taxonomy, or harmonized naming into a recipe, you MUST read and follow:

• Naming Convention Join

For this use case, you must:

1. Discover candidate taxonomy tables in ClickHouse
2. Inspect the candidate tables to determine which dimension they harmonize
3. Design the join and present it to the user
4. Wait for user confirmation before implementing the join
5. After implementation and validation, bring in the recipe-qa-rules skill to build the QRD for the resulting recipe

0. CRITICAL: Always pass --database-schema

⚠️ MANDATORY: Every command REQUIRES --database-schema parameter!

--database-schema <agency_database_schema>

How to get database_schema:

• From user's context (they usually know their client/agency)
• From NEXT_PUBLIC_DATABASE_SCHEMA environment variable
• From client folder name (e.g., im_10836_2de from clients/im_10836_2de___ClientName/)
• ASK USER if unclear!

0.1 MANDATORY: Open recipe preview at the start

⚠️ ALWAYS open the embedded recipe view BEFORE starting any work on a recipe. This displays the recipe UI to the user in an iframe so they can see changes in real time.

python3 .claude/skills/notebook-editor/notebook_client.py open-recipe \
  --recipe-id <notebook-id>

Optional: --workspace-id <id> (defaults to NEXT_PUBLIC_WORKSPACE_ID from environment).

After EVERY mutation (run-from-file, create-cell, insert-cell, delete-cell), reload the iframe so the user sees the updated state:

python3 .claude/skills/notebook-editor/notebook_client.py reload-recipe

Summary of when to use:

• open-recipe — once at the beginning when you start working on a recipe
• reload-recipe — after every cell run, create, insert, or delete operation

1. List cells to find cell_id

python3 .claude/skills/notebook-editor/notebook_client.py list \
  --notebook-id <notebook-id> \
  --database-schema <agency_database_schema>

Shows all cells with IDs, types, titles, and — for the latest run — any errors and generated SQL. Use this to:

• Find the cell_id you need
• Check if any cell has errors after running
• Review the SQL that the backend generated from your cell content

2. Get specific cell

python3 .claude/skills/notebook-editor/notebook_client.py get-cell \
  --notebook-id <notebook-id> \
  --cell-id <cell-id> \
  --database-schema <agency_database_schema> \
  --save-to-file cell-<id>.json

Downloads single cell to file for editing. Content returned as parsed object. Files are saved to /workspace/clients/{database_schema}/temp/ directory.

3. Edit cell using Edit tool

Use Edit tool to modify cell content in cell-<id>.json:

The cell structure:

{
  "cell_id": "cell-789",
  "cell_type": "sql",
  "content": {
    "query": "SELECT * FROM table"
  },
  "title": "My Query",
  "updated_at": "2024-01-23T10:30:00Z"
}

Edit the content field directly (it's an object, not JSON string).

4. Run cell from file

python3 .claude/skills/notebook-editor/notebook_client.py run-from-file \
  --file cell-<id>.json \
  --notebook-id <notebook-id> \
  --cell-id <cell-id> \
  --database-schema <agency_database_schema>

This will:

• Read the cell from file (using database_schema for path resolution)
• Convert content to JSON string (if object)
• POST to /api/notebook/v4/run with content, notebook_id, cell_id, cell_type, and context
• Return a stream_id (results are delivered via SSE)

💡 Tip: Running a cell automatically triggers all downstream cells to re-run as well. So to run the entire recipe end-to-end, you only need to run the first cell and then wait for all cells to complete. Use list afterwards to check results across all cells.

5. MANDATORY: Validate after every run

After running ANY cell, you MUST run list again to check the result:

python3 .claude/skills/notebook-editor/notebook_client.py list \
  --notebook-id <notebook-id> \
  --database-schema <agency_database_schema>

The list command shows the latest run output for each cell, including:

• Errors — if the cell failed, you'll see ERROR: <message> with the ClickHouse exception
• Generated SQL — the SQL the backend compiled from your cell content

What to do with the output:

1. If you see ERROR: — read the error message carefully, fix the cell content, and re-run. Common errors:

   • Cannot parse string 'X' as Float64 — wrong column type or mapping a string to a number column
   • Unknown column 'X' — column name typo or referencing a column not in the source table
   • Table X doesn't exist — wrong table name, use datasources command to find the correct name

2. If you see Generated SQL: — review the SQL to verify the backend translated your cell content correctly. Check that:

   • All columns are present and properly typed (CAST operations look correct)
   • JOIN conditions reference the right tables and columns
   • WHERE clauses (from filters) are correct
   • UNION ALL combines the expected tables

3. If last_run_status: failed at the notebook level — one or more cells have errors. Fix all errors before proceeding to the next step.

⚠️ Do NOT move on to the next cell or consider the task done until you have validated the run output is error-free.

CRITICAL: Context field in run requests

MANDATORY: Every run request must include a context field. Add it to the cell JSON file before running:

Add "context": "<value>" to the cell JSON file alongside cell_type, content, etc.

Notebook Lifecycle

Notebooks progress through states: new → staging → production

• new: Just created, no cells
• staging: After rename or adding/editing cells. The view_name gets a _staging suffix
• production: After activation via POST /recipe/v4/{notebook_id}/activation (empty body, returns {"data_table_id": ...})

Discovering Available Tables & Cell-to-Cell References

Cells can reference the output of previous cells as virtual tables. The naming pattern is:

cell_{cell_number}_sql_query_label_{hash}

Examples: cell_1_sql_query_label_1088891118, cell_2_sql_query_label_1140862697

You MUST use the datasources command to discover the exact virtual table names — do not guess the hash.

List available datasources (tables)

python3 .claude/skills/notebook-editor/notebook_client.py datasources \
  --notebook-id <notebook-id> \
  --cell-id <cell-id> \
  --database-schema <agency_database_schema>

Returns all tables available to a cell, including:

• Real data tables (e.g., ads_10873_google_ads_ql_all_data)
• Virtual cell output tables (e.g., cell_1_sql_query_label_1088891118) marked with [CELL OUTPUT]

Use --cell-id to scope results to what a specific cell can see (previous cells' outputs).

Get columns for a table

python3 .claude/skills/notebook-editor/notebook_client.py columns \
  --table-name <table-name> \
  --notebook-id <notebook-id> \
  --database-schema <agency_database_schema>

Returns column names and types for any table (real or virtual cell output):

  date: date
  campaign_id: string
  spend: number
  __insert_date: datetime

Cell Types

For detailed documentation on each cell type, see:

• Cell Types Overview - Quick reference for all cell types
• Union (Blend Data) - Combining data from multiple sources
• Join - Joining tables and enriching data
• Group By - Aggregations and rollups
• Filter - Conditional filtering and row removal
• SQL - SQL cells (use sparingly, only when explicitly requested)
• Formulas & Expressions - CASE WHEN, arithmetic, KPI calculations
• Currency Conversion Join - Converting monetary metrics between currencies via exchange rate join
• Naming Convention Join - Joining taxonomy tables to harmonize recipe dimensions

Quick Summary

• union - Blend Data: Combine multiple sources (Google Ads + Facebook Ads)
• join - Join: Enrich with dimensions (campaigns, accounts)
• group_by - Group By: Aggregate metrics (SUM, COUNT, AVG)
• conditional_mapping - Filter: Remove unwanted rows
• sql - SQL: Advanced operations (ONLY when user explicitly requests)

Content Format Differences

GET-CELL response - content as object:

{
  "content": {
    "query": "SELECT * FROM table"
  }
}

POST RUN request - content as JSON string:

{
  "content": "{\"query\":\"SELECT * FROM table\"}"
}

⚠️ Important: The run-from-file command automatically converts object to JSON string!

Common Operations

Update SQL query in cell

Example with database_schema = 'im_10836_2de':

1. List cells:

python3 notebook_client.py list \
  --notebook-id abc-123 \
  --database-schema im_10836_2de

2. Get cell:

python3 notebook_client.py get-cell \
  --notebook-id abc-123 \
  --cell-id cell-789 \
  --database-schema im_10836_2de \
  --save-to-file cell.json

3. Use Edit tool to change query in cell's content.query field

4. Run:

python3 notebook_client.py run-from-file \
  --file cell.json \
  --notebook-id abc-123 \
  --cell-id cell-789 \
  --database-schema im_10836_2de

Important Notes

• ALWAYS pass --database-schema to every command! This is MANDATORY!
• Always use list first if you don't know cell_id
• Cell files contain single cell, not full notebook
• content in file is object, but POST requires JSON string (auto-converted)
• Check stderr for progress messages, stdout contains JSON results

Environment Variables

Required:

• NEXT_PUBLIC_DTS_SESSION_ID - Session ID for auth (required!)
• NEXT_PUBLIC_WORKSPACE_ID - Workspace ID

Optional:

• NEXT_PUBLIC_AI_AGENT_BASE_URL - Platform host (default: https://report.improvado.io)

These variables are automatically provided by the sandbox environment.

Temporary Files

Files are saved to /workspace/clients/{database_schema}/temp/ directory (based on --database-schema parameter). This complies with sandbox security restrictions that allow writes only to client-specific folders. When using relative file paths (e.g., cell.json), they are automatically resolved to the temp directory.

Integrated Setup Commands

Get integrated setup

python3 .claude/skills/notebook-editor/notebook_client.py get-integrated-setup \
  --notebook-id <notebook-id> \
  --database-schema <agency_database_schema>

Retrieves the integrated setup configuration for a transformation (notebook). Returns:

• data_sources: Array of selected data source names
• allowed_data_sources: Array of available data source names
• connections: Array of connection objects with accounts

Response format:

{
  "data_sources": ["Google Ads", "Facebook Ads"],
  "allowed_data_sources": ["Google Ads", "Facebook Ads", "LinkedIn Ads"],
  "connections": [
    {
      "connection_id": 123,
      "connection_data_source": "Google Ads",
      "accounts": [
        {"id": 456, "account_id": "123-456-7890"}
      ]
    }
  ]
}

Error handling:

• Returns 404 if the transformation doesn't have an integrated setup configured
• Use this command to check if a transformation supports integrated builds

Build integrated transformation

python3 .claude/skills/notebook-editor/notebook_client.py build-integrated-transformation \
  --file build-config.json \
  --transformation-id <transformation-id> \
  --database-schema <agency_database_schema>

Starts an integrated progressive build for a transformation with specified data sources and connections.

Build configuration file format:

{
  "data_sources": ["Google Ads", "Facebook Ads"],
  "connections": [
    {
      "connection_id": 123,
      "connection_data_source": "Google Ads",
      "accounts": [
        {"id": 456, "account_id": "123-456-7890"}
      ]
    },
    {
      "connection_id": 789,
      "connection_data_source": "Facebook Ads",
      "accounts": [
        {"id": 101, "account_id": "act_EXAMPLE_ACCOUNT_ID"}
      ]
    }
  ]
}

Workflow for building integrated transformation:

1. First, get the current integrated setup to see available data sources and connections:

   python3 notebook_client.py get-integrated-setup \
     --notebook-id <id> \
     --database-schema <schema>
   

2. Create a build configuration file with desired data sources and connections

3. Start the build:

   python3 notebook_client.py build-integrated-transformation \
     --file build-config.json \
     --transformation-id <id> \
     --database-schema <schema>
   

4. Check build status:

   python3 notebook_client.py integrated-build-status \
     --transformation-id <id> \
     --database-schema <schema>
   

Error handling:

• 400 - Build already in progress (wait for current build to complete)
• 403 - Permission denied (no access to build this transformation)
• 404 - Integrated setup not found (transformation not configured for integrated builds)

Important notes:

• Only one build can run at a time for a transformation
• Use integrated-build-status to monitor build progress
• The build runs asynchronously - the command returns immediately after starting the build

Creating New Cells

Create cell from file

python3 .claude/skills/notebook-editor/notebook_client.py create-cell \
  --file new-cell.json \
  --notebook-id <notebook-id> \
  --database-schema <agency_database_schema>

Creates a new cell in the notebook. The file should contain:

{
  "cell_type": "sql",
  "content": {
    "query": "SELECT * FROM table"
  },
  "title": "My New Query"
}

Note: Don't include cell_id when creating - it will be auto-generated and returned in the response.

Workflow for creating new cell:

1. Create cell definition file (without cell_id)
2. Run: python3 notebook_client.py create-cell --file new-cell.json --notebook-id <id> --database-schema <schema>
3. Save the returned cell_id for future edits

Insert cell between existing cells

Use this when you need to add a step between two existing cells. This atomically inserts the new cell and updates all downstream cells' virtual table references.

python3 .claude/skills/notebook-editor/notebook_client.py insert-cell \
  --file insert-payload.json \
  --notebook-id <notebook-id> \
  --database-schema <agency_database_schema>

The file must contain:

{
  "previous_cell_id": "PREVIOUS_CELL_ID",
  "new_cell": {
    "cell_type": "union",
    "title": "Blend data",
    "content": {
      "output_schema": {"columns": [""], "conditions": null},
      "tables": [{"name": "", "title": "", "columns": []}]
    }
  },
  "cells_to_update": [
    {
      "cell_id": "CELL_ID",
      "content": { ... }
    }
  ]
}

How it works:

• previous_cell_id: The new cell is inserted after this cell
• new_cell: The new cell definition (same as create-cell)
• cells_to_update: All downstream cells with their content updated to reflect shifted cell numbers

Why cells_to_update is needed: When you insert a cell between cells 1 and 2, the old cell 2 becomes cell 3, cell 3 becomes cell 4, etc. Any cell referencing cell_2_sql_query_label_XXXX must now reference cell_3_sql_query_label_XXXX. The cells_to_update array provides the corrected content for each affected downstream cell.

Workflow for inserting a cell:

1. list — get all current cells and their IDs
2. get-cell — download each downstream cell that references virtual tables
3. Update the virtual table references in each downstream cell's content (increment cell numbers)
4. Build the insert payload JSON with previous_cell_id, new_cell, and cells_to_update
5. Run: python3 notebook_client.py insert-cell --file insert-payload.json --notebook-id <id> --database-schema <schema>

Delete a cell

⚠️ MANDATORY: You MUST ask the user for explicit confirmation before deleting ANY cell. Deletion is irreversible. NEVER proceed with deletion without the user saying "yes", "delete it", "go ahead", or similar explicit approval. If the user has not confirmed, DO NOT run the delete command.

python3 .claude/skills/notebook-editor/notebook_client.py delete-cell \
  --notebook-id <notebook-id> \
  --cell-id <cell-id> \
  --database-schema <agency_database_schema>

Workflow for deleting a cell:

1. list — show the user all cells so they can identify which one to delete
2. Ask the user: "Are you sure you want to delete cell {cell_id} ({cell_type}: {title})? This action is irreversible."
3. Wait for explicit confirmation — do NOT proceed without it
4. Only after user confirms, run the delete command
5. After deletion, check if downstream cells have broken references to the deleted cell's virtual table and warn the user

Empty Content Templates (for creating new cells)

When creating a new cell, use these initial empty content structures in the file:

Union (Blend Data):

{
  "cell_type": "union",
  "content": {
    "output_schema": {"columns": [""], "conditions": null},
    "tables": [{"name": "", "title": "", "columns": []}]
  },
  "title": "Blend data",
  "context": "blend_data"
}

Join:

{
  "cell_type": "join",
  "content": {
    "main_table": "",
    "join_table": "",
    "join_type": "left",
    "join_keys": [],
    "additional_main_table_columns": [],
    "additional_join_table_columns": []
  },
  "title": "Join data",
  "context": "join"
}

Group By:

{
  "cell_type": "group_by",
  "content": {
    "table_name": "",
    "group_by_columns": [],
    "aggregations": []
  },
  "title": "Group by",
  "context": "group_by"
}

SQL:

{
  "cell_type": "sql",
  "content": {
    "query": ""
  },
  "title": "SQL Query",
  "context": "sql"
}

Filter Data (conditional_mapping):

{
  "cell_type": "conditional_mapping",
  "content": [
    {
      "table_name": "",
      "else_condition": {"original_title": "", "new_title": ""},
      "mapping": [
        {
          "original_title": "",
          "new_title": "",
          "conditions": {
            "groups": {
              "and_": []
            }
          }
        }
      ]
    }
  ],
  "title": "Filter data",
  "context": "filter"
}

Cell Content Quick Reference

For detailed cell content structures, formulas, and examples, see the prompt files linked above.

Below is a minimal quick reference for common operations.

Column Types

Use lowercase types: date, string, number, datetime

WRONG: Int64, Float64, String, Date CORRECT: date, string, number, datetime

Basic Patterns

Simple column mapping (Union):

{"original_name": ["date"], "new_name": "date", "conditions": []}

Column rename (Union):

{"original_name": ["cost"], "new_name": "spend", "conditions": []}

Constant string (Union):

{"original_name": ["'Google Ads'"], "new_name": "data_source", "conditions": []}

Context mapping:

• union → "blend_data"
• join → "join"
• group_by → "group_by"
• sql → "sql"
• conditional_mapping → "filter"

For full examples and detailed documentation, see the linked prompt files above.