meal-inc/bonnard-metabase-migrate

Migrate from Metabase to Bonnard

This skill guides you through analyzing an existing Metabase instance and building a semantic layer that replicates its most important metrics. Walk through each phase in order, confirming progress before moving on.

Phase 1: Connect to Metabase

Set up a connection to the Metabase instance:

bon metabase connect

This prompts for the Metabase URL and API key. The API key should be created in Metabase under Admin > Settings > Authentication > API Keys. An admin-level key gives the richest analysis (permissions, schema access).

Phase 2: Analyze the Instance

Generate an intelligence report that maps the entire Metabase instance:

bon metabase analyze

This writes a report to .bon/metabase-analysis.md. Read it carefully — it drives every decision in the remaining phases.

How to interpret each section

| Report Section | What It Tells You | Action | |----------------|-------------------|--------| | Most Referenced Tables | Tables used most in SQL queries | Create cubes for these first — they are the core of the data model | | Top Cards by Activity | Most-viewed questions/models | analytical cards (GROUP BY + aggregation) map to measures; lookup cards indicate key filter dimensions; display cards can be skipped | | Common Filter Variables | Template vars ({{var}}) used across 3+ cards | These must be dimensions on relevant cubes | | Foreign Key Relationships | FK links between tables | Define joins between cubes using these relationships | | Collection Structure | How users organize content by business area | Map each top-level collection to a view (one view per business domain) | | Dashboard Parameters | Shared filters across dashboards | The most important shared dimensions — ensure they exist on relevant cubes | | Table Inventory | Field counts and classification per table | Field classification (dims/measures/time) guides each cube definition; tables with 0 refs can be deprioritized | | Schema Access | Which schemas non-admin groups can query | Focus on user-facing schemas — skip admin-only/staging schemas |

Phase 3: Connect the Data Warehouse

Add a datasource pointing to the same database that Metabase queries. The database connection details can often be found in Metabase under Admin > Databases, or in the analysis report header.

# Non-interactive (preferred for agents)
bon datasource add --name my_warehouse --type postgres \
  --host db.example.com --port 5432 --database mydb --schema public \
  --user myuser --password mypassword

# Import from dbt if available
bon datasource add --from-dbt

# Interactive setup (in user's terminal)
bon datasource add

Supported types: postgres, redshift, snowflake, bigquery, databricks, duckdb.

The connection will be tested automatically during bon deploy.

Phase 4: Explore Key Tables

Before writing cubes, drill into the most important tables and cards identified in Phase 2. Use the explore commands to understand field types and existing SQL patterns:

# View table fields with type classification
bon metabase explore table <id>

# View card SQL and columns
bon metabase explore card <id>

# View schemas and tables in a database
bon metabase explore database <id>

# View cards in a collection
bon metabase explore collection <id>

How explore output maps to cube definitions

| Explore Field | Cube Mapping | |---------------|-------------| | Field class pk | Set primary_key: true on dimension | | Field class fk | Join candidate — note the target table | | Field class time | Dimension with type: time | | Field class measure | Measure candidate — check card SQL for aggregation type | | Field class dim | Dimension with type: string or type: number |

How card SQL maps to measures

Look at the SQL in analytical cards to determine measure types:

| Card SQL Pattern | Cube Measure | |-----------------|-------------| | SUM(amount) | type: sum, sql: amount | | COUNT(*) | type: count | | COUNT(DISTINCT user_id) | type: count_distinct, sql: user_id | | AVG(price) | type: avg, sql: price | | MIN(date) / MAX(date) | type: min / type: max, sql: date |

Use bon docs cubes.measures.types for all 12 measure types.

Phase 5: Build Cubes

Create cubes for the most-referenced tables (from Phase 2). Start with the highest-referenced table and work down. Create one file per cube in bonnard/cubes/.

For each cube:

1. Set sql_table to the full schema.table path
2. Set data_source to the datasource name from Phase 3
3. Add a primary_key dimension — must be unique. If no column is naturally unique, use sql with ROW_NUMBER() instead of sql_table and add a synthetic key
4. Add time dimensions for date/datetime columns
5. Add measures based on card SQL patterns (Phase 4)
6. Add filtered measures for every card with a WHERE clause beyond date range — e.g., "active offers" (status != cancelled). This is the #1 way to match dashboard numbers.
7. Add dimensions for columns used as filters (template vars from Phase 2)
8. Add description to every measure and dimension — descriptions should say what's included and excluded. Dimension descriptions should include example values for categorical fields.

Example — bonnard/cubes/orders.yaml:

cubes:
  - name: orders
    sql_table: public.orders
    data_source: my_warehouse
    description: Order transactions

    measures:
      - name: count
        type: count
        description: Total number of orders

      - name: total_revenue
        type: sum
        sql: amount
        description: Sum of order amounts

    dimensions:
      - name: id
        type: number
        sql: id
        primary_key: true

      - name: created_at
        type: time
        sql: created_at
        description: Order creation timestamp

      - name: status
        type: string
        sql: status
        description: Order status (pending, completed, cancelled)

Adding joins

Use FK relationships from the analysis report to define joins between cubes:

    joins:
      - name: customers
        sql: "{CUBE}.customer_id = {customers.id}"
        relationship: many_to_one

Use bon docs cubes.joins for the full reference.

Phase 6: Build Views

Map Metabase collections to views. Each top-level collection (business domain) from the analysis report becomes a view that composes the relevant cubes. Name views by what they answer (e.g., sales_pipeline), not by what table they wrap (e.g., orders_view).

Create one file per view in bonnard/views/.

The view description is critical — it's how AI agents decide which view to query. It should answer: what's in here, when to use it, and when to use something else instead. Cross-reference related views to prevent agents from picking the wrong one.

Example — bonnard/views/sales_analytics.yaml:

views:
  - name: sales_analytics
    description: >-
      Sales team view — order revenue, counts, and status breakdowns with
      customer region. Default view for revenue and order questions. Use the
      status dimension (values: pending, completed, cancelled) to filter.
      For customer-level analysis, use customer_insights instead.
    cubes:
      - join_path: orders
        includes:
          - count
          - total_revenue
          - created_at
          - status

      - join_path: orders.customers
        prefix: true
        includes:
          - name
          - region

Use bon docs views for the full reference. See /bonnard-design-guide for principles on view naming, descriptions, and structure.

Phase 7: Validate and Deploy

Validate the semantic layer:

bon validate

Fix any errors. Common issues:

• Missing primary_key dimension
• Unknown measure/dimension types
• Undefined cube referenced in a view join path
• Missing data_source

Then deploy:

bon login
bon deploy -m "Migrate semantic layer from Metabase"

Phase 8: Verify

Compare results from the semantic layer against Metabase card outputs. Pick 3-5 important analytical cards from the analysis report and run equivalent queries:

# Run a semantic layer query
bon query '{"measures": ["sales_analytics.total_revenue"], "dimensions": ["sales_analytics.status"]}'

# SQL format
bon query --sql "SELECT status, MEASURE(total_revenue) FROM sales_analytics GROUP BY 1"

Compare the numbers with the corresponding Metabase card. If they don't match:

• Check the SQL in the card (bon metabase explore card <id>) for filters or transformations
• Ensure the measure type matches the aggregation (SUM vs COUNT vs AVG)
• Check for WHERE clauses that should be filtered measures (not segments)

Test with natural language too. Set up MCP (bon mcp) and ask an agent the same questions your Metabase dashboards answer. Check whether it picks the right view and measure — most failures are description problems, not data problems. See /bonnard-design-guide Principle 6.

Next Steps

After the core migration is working:

• Iterate on descriptions — test with real questions via MCP, fix agent mistakes by improving view descriptions and adding filtered measures (/bonnard-design-guide)
• Add remaining tables as cubes (work down the reference count list)
• Build audience-centric views that combine multiple cubes — match how Metabase collections organize data by business domain
• Add calculated measures for complex card SQL (bon docs cubes.measures.calculated)
• Set up MCP for AI agent access (bon mcp)
• Review and iterate with bon deployments and bon diff <id>