Data Metabase

Metabase

Automate Metabase via API: reports (cards), dashboards, and chart settings.

Quick Start

Inputs (env vars)

• METABASE_URL (e.g., https://metabase.example.com)
• Preferred: METABASE_API_KEY
• Fallback: METABASE_USERNAME + METABASE_PASSWORD

Sanity checks

python3 frameworks/shared-skills/skills/data-metabase/scripts/metabase_api.py health
python3 frameworks/shared-skills/skills/data-metabase/scripts/metabase_api.py whoami

Live API documentation

Your Metabase instance serves OpenAPI docs at /api/docs (e.g., https://metabase.example.com/api/docs). Use this to discover version-specific endpoints and request shapes.

Workflow

1. Confirm API availability (GET /api/util/health). 2. Authenticate with an API key (preferred) or a short-lived session (fallback). 3. Discover IDs (prefer discovery over hardcoding across environments):

• collection_id for where to save
• database id for dataset_query
• source-table / field ids if using MBQL

4. Create/update a card:

• Prefer native SQL for stable automation.
• Set display + visualization_settings explicitly.

5. Create/update a dashboard and add cards with consistent layout. 6. Validate by running/exporting results.

Key Concepts

• UI "Question" == API card
• Chart configuration lives on the card as display + visualization_settings
• Most viz keys are easiest to manage by copying from an existing card JSON, then editing

Guardrails

• Prefer Metabase "serialization" (Pro/Enterprise) for bulk, cross-environment migrations; use direct API for incremental upserts.
• Do not hardcode numeric IDs across environments when you can discover them or use serialization/entity IDs.
• Never commit METABASE_API_KEY, passwords, or session tokens.
• Prefer a dedicated, least-privileged automation account and collection.

References (read only as needed)

Scripts

scripts/metabase_api.py is a small, dependency-free helper to test auth and upsert cards.

Examples:

# Print authenticated user (tries API key, then session)
python3 frameworks/shared-skills/skills/data-metabase/scripts/metabase_api.py whoami

# Export an existing card JSON (use as a template for visualization_settings)
python3 frameworks/shared-skills/skills/data-metabase/scripts/metabase_api.py export-card --id 123 --out card.json

# Export an existing dashboard JSON (use as a template for layout)
python3 frameworks/shared-skills/skills/data-metabase/scripts/metabase_api.py export-dashboard --id 5 --out dashboard.json

# Create/update a card from a JSON spec (see references/reports-cards.md)
python3 frameworks/shared-skills/skills/data-metabase/scripts/metabase_api.py upsert-card --spec card-spec.json

# Create/update a dashboard from a JSON spec (base fields only)
python3 frameworks/shared-skills/skills/data-metabase/scripts/metabase_api.py upsert-dashboard --spec dashboard-spec.json

Fact-Checking

• Use web search/web fetch to verify current external facts, versions, pricing, deadlines, regulations, or platform behavior before final answers.
• Prefer primary sources; report source links and dates for volatile information.
• If web access is unavailable, state the limitation and mark guidance as unverified.

Metabase API Authentication

Goal: programmatic access to Metabase with an API key (preferred) and safe fallbacks.

Inputs

• METABASE_URL: Base URL, e.g. https://metabase.example.com (no trailing / preferred)
• Preferred: METABASE_API_KEY
• Fallback: METABASE_USERNAME + METABASE_PASSWORD

Strategy: confirm auth by calling a cheap endpoint

Use an endpoint that requires auth and returns the current principal:

• GET /api/user/current (commonly available)

If the request returns HTTP 200, your auth method is accepted.

API key authentication

Metabase API key auth has multiple variants across versions/editions. If you are not sure which header your instance expects, try these in order (and keep the one that returns 200 from GET /api/user/current):

1. X-API-KEY: <key> 2. Authorization: Bearer <key>

If both fail with 401/403:

• Confirm API keys are enabled in your Metabase instance.
• Check the Metabase admin UI for an "API keys" page and regenerate a key.
• Fall back to session auth if allowed (below).

Session authentication (fallback)

If your environment permits a service username/password (not recommended for long-lived automation), create a session:

• POST /api/session with JSON body:
• {"username":"...","password":"..."}

Use the response id as the session token in subsequent requests:

• X-Metabase-Session: <id>

Session lifetime and caching

• Sessions are valid for 14 days by default
• Configure via env var: MAX_SESSION_AGE (value in minutes)
• Cache the session token and reuse until it expires
• Logins are rate-limited; avoid creating new sessions per request

Handling 401 errors (auto-retry pattern)

When the API returns 401 (Unauthorized), your session may have expired. Implement auto-retry:

def request_with_retry(method, path, headers, body=None):
    status, payload, raw = _request(method, path, headers, body)
    if status == 401:
        # Refresh auth and retry once
        _, new_headers = _pick_auth_headers()
        status, payload, raw = _request(method, path, new_headers, body)
    return status, payload, raw

This pattern handles:

• Expired sessions
• Rotated API keys
• Temporary auth failures

Safety notes

• Never commit METABASE_API_KEY, passwords, or session tokens to the repository.
• Prefer a least-privileged service user and a dedicated collection for automation-managed assets.

Charts and Visualization Settings (Metabase)

Metabase stores chart configuration on the card:

• display: the visualization type (table/line/bar/pie/etc.)
• visualization_settings: an object with visualization-specific keys

Recommended approach: copy, then edit

Metabase visualization keys change over time and depend on chart type. The most reliable way to automate chart settings is:

1. Create the chart in the UI. 2. Export the card JSON (GET /api/card/:id). 3. Reuse and modify the exported display and visualization_settings as your template.

Practical patterns

Pattern: enforce consistent naming and formatting

• Keep name consistent (stable identifiers help cross-environment diffs).
• Keep display explicit (do not rely on defaults).
• Keep visualization_settings minimal (only keys you need).

Pattern: keep a "golden card" per chart type

Create one card per visualization type (line/bar/table/pie) configured exactly as desired. Export each as a template and reuse its visualization_settings for future cards.

Dashboards in Metabase API

Create and manage dashboards programmatically, including card placement and layout.

Contents

• Core endpoints
• Create a dashboard
• Add a card to a dashboard
• Update card positions
• Add a text card
• Common layout patterns
• Workflow: replicate dashboards across environments

Core endpoints

Create a dashboard

curl -X POST "$METABASE_URL/api/dashboard" \
  -H "X-API-KEY: $METABASE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Sales Overview",
    "description": "Key sales metrics",
    "collection_id": 10
  }'

Response includes the new dashboard id.

Add a card to a dashboard

Use POST /api/dashboard/:id/cards with placement properties:

curl -X POST "$METABASE_URL/api/dashboard/5/cards" \
  -H "X-API-KEY: $METABASE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "cardId": 123,
    "row": 0,
    "col": 0,
    "sizeX": 6,
    "sizeY": 4
  }'

Card placement properties

Grid system: Metabase uses an 18-column grid. Cards have minimum dimensions (typically 2x2 or 3x3 depending on version).

Update card positions

Use PUT /api/dashboard/:id/cards with an array of card updates:

curl -X PUT "$METABASE_URL/api/dashboard/5/cards" \
  -H "X-API-KEY: $METABASE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "cards": [
      {"id": 101, "row": 0, "col": 0, "sizeX": 9, "sizeY": 4},
      {"id": 102, "row": 0, "col": 9, "sizeX": 9, "sizeY": 4},
      {"id": 103, "row": 4, "col": 0, "sizeX": 18, "sizeY": 6}
    ]
  }'

Note: The id in the cards array is the dashcard_id (dashboard-card relationship ID), not the card/question ID. Get this from the dashboard GET response.

Add a text card

Text cards have cardId: null and use visualization_settings for content:

curl -X POST "$METABASE_URL/api/dashboard/5/cards" \
  -H "X-API-KEY: $METABASE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "cardId": null,
    "row": 0,
    "col": 0,
    "sizeX": 18,
    "sizeY": 2,
    "visualization_settings": {
      "text": "## Sales Dashboard\nUpdated daily at 6am UTC",
      "virtual_card": {"display": "text"}
    }
  }'

Common layout patterns

Two-column layout

+------------------+------------------+
|   Card A (9x4)   |   Card B (9x4)   |   row 0
+------------------+------------------+
|           Card C (18x6)             |   row 4
+-------------------------------------+

JSON for this layout:

{
  "cards": [
    {"id": 101, "row": 0, "col": 0, "sizeX": 9, "sizeY": 4},
    {"id": 102, "row": 0, "col": 9, "sizeX": 9, "sizeY": 4},
    {"id": 103, "row": 4, "col": 0, "sizeX": 18, "sizeY": 6}
  ]
}

Header + KPIs + chart

+-------------------------------------+
|        Text Header (18x2)           |   row 0
+--------+--------+--------+----------+
| KPI 1  | KPI 2  | KPI 3  | KPI 4    |   row 2
+--------+--------+--------+----------+
|           Main Chart (18x8)         |   row 6
+-------------------------------------+

Workflow: replicate dashboards across environments

1. Export source dashboard: GET /api/dashboard/:id 2. Extract card IDs and layout from dashcards array 3. Create cards in target (or use serialization for Pro/Enterprise) 4. Create dashboard in target: POST /api/dashboard 5. Add cards with same layout: POST /api/dashboard/:id/cards

Pro/Enterprise: Use serialization for full dashboard export/import with Entity IDs.

Metabase Embedding and Integration

Purpose: Operational guide for embedding Metabase dashboards and questions into external applications — public links, signed embedding (JWT), interactive embedding (SDK), theming, security, and troubleshooting. Freshness anchor: Q1 2026.

---

Decision Tree: Choosing an Embedding Approach

START: How will users access the embedded content?
│
├─ Public / no authentication needed
│   └─ Public sharing links
│       - Zero setup, URL-based
│       - No parameter filtering
│       - WARNING: anyone with URL can access
│
├─ Authenticated users, read-only dashboards
│   └─ Signed embedding (JWT)
│       - Server-side token generation
│       - Parameter locking for row-level security
│       - Iframe-based, limited interactivity
│
├─ Authenticated users, full interactivity
│   └─ Interactive embedding (SDK)
│       - Full Metabase experience in your app
│       - Drill-down, filtering, custom questions
│       - Requires Metabase Pro/Enterprise
│
└─ Programmatic data access (no UI)
    └─ Metabase API
        - REST API for query results
        - JSON responses for custom rendering

---

Quick Reference: Embedding Comparison (2026)

---

Public Sharing Links

Setup

Admin → Settings → Public Sharing → Enable

Usage

• Use when: Data is truly public, no sensitivity
• URL pattern: https://metabase.company.com/public/dashboard/{uuid}
• Parameters: Append ?param_name=value to URL

Security Checklist

• [ ] Only enable for genuinely public dashboards
• [ ] Review shared items regularly (Admin → Sharing)
• [ ] Disable public sharing globally if not needed
• [ ] No PII or sensitive data in public dashboards
• [ ] Consider rate limiting via reverse proxy

---

Signed Embedding (JWT)

How It Works

1. User visits your app
2. Your server generates JWT with:
   - Dashboard/question ID
   - Locked parameters (e.g., customer_id)
   - Expiration time
3. Frontend loads iframe with signed URL
4. Metabase validates JWT and renders content

Backend Setup

# Python — JWT token generation
import jwt
import time

METABASE_SECRET_KEY = "your-embedding-secret-key"  # from Admin → Embedding

def generate_embed_url(dashboard_id: int, params: dict) -> str:
    """Generate signed embedding URL for a dashboard."""
    payload = {
        "resource": {"dashboard": dashboard_id},
        "params": params,
        "exp": int(time.time()) + (10 * 60)  # 10 minute expiration
    }

    token = jwt.encode(payload, METABASE_SECRET_KEY, algorithm="HS256")
    return f"https://metabase.company.com/embed/dashboard/{token}"


# Example: embed dashboard 42 locked to customer_id=123
url = generate_embed_url(
    dashboard_id=42,
    params={"customer_id": 123}
)

// Node.js — JWT token generation
const jwt = require("jsonwebtoken");

const METABASE_SECRET_KEY = process.env.METABASE_EMBED_SECRET;

function generateEmbedUrl(dashboardId, params) {
  const payload = {
    resource: { dashboard: dashboardId },
    params: params,
    exp: Math.round(Date.now() / 1000) + 10 * 60, // 10 min
  };

  const token = jwt.sign(payload, METABASE_SECRET_KEY);
  return `https://metabase.company.com/embed/dashboard/${token}`;
}

// Example
const url = generateEmbedUrl(42, { customer_id: 123 });

Frontend Integration

<!-- Iframe embedding -->
<iframe
  src="{{ embed_url }}"
  frameborder="0"
  width="100%"
  height="800"
  allowtransparency="true"
  loading="lazy"
></iframe>

Parameter Control

JWT Payload Examples

// Locked customer_id, editable date range
{
  "resource": {"dashboard": 42},
  "params": {
    "customer_id": 123,
    "date_range": null
  },
  "exp": 1707580800
}

// Multiple locked parameters
{
  "resource": {"dashboard": 42},
  "params": {
    "customer_id": 123,
    "region": "US",
    "plan_type": "enterprise"
  },
  "exp": 1707580800
}

// Question (not dashboard) embedding
{
  "resource": {"question": 99},
  "params": {},
  "exp": 1707580800
}

---

Interactive Embedding (SDK)

Prerequisites

• Metabase Pro or Enterprise license
• SSO configured (SAML, JWT, or OIDC)

React SDK Setup

npm install @metabase/embedding-sdk-react

// MetabaseProvider.jsx
import { MetabaseProvider } from "@metabase/embedding-sdk-react";

const config = {
  metabaseInstanceUrl: "https://metabase.company.com",
  authProviderUri: "/api/metabase/auth", // your auth endpoint
};

const theme = {
  colors: {
    brand: "#4C51BF",
    "text-primary": "#1A202C",
    "text-secondary": "#718096",
    background: "#FFFFFF",
    "background-hover": "#F7FAFC",
  },
  fontSize: "14px",
  fontFamily: "Inter, sans-serif",
};

function App() {
  return (
    <MetabaseProvider config={config} theme={theme}>
      <YourApp />
    </MetabaseProvider>
  );
}

Key SDK Components

• InteractiveDashboard — Full dashboard with filters, drill-down
• StaticQuestion — Single chart/visualization (read-only)
• Props: dashboardId, initialParameterValues, withDownloads, hiddenParameters

Auth Endpoint Pattern

• Your server verifies app authentication
• Creates JWT with email, first_name, last_name, groups (maps to Metabase groups)
• Returns { id: token } to SDK
• SDK uses token to authenticate with Metabase instance

---

Theme and Appearance Customization

CSS Variables (Signed Embedding)

/* Custom styles for iframe embedding */
:root {
  --mb-color-brand: #4C51BF;
  --mb-color-brand-light: #EBF4FF;
  --mb-color-text-primary: #1A202C;
  --mb-color-text-secondary: #718096;
  --mb-color-bg-white: #FFFFFF;
  --mb-color-bg-light: #F7FAFC;
  --mb-font-family: "Inter", sans-serif;
}

URL Parameters for Appearance

# Example: dark mode, no title, no borders
/embed/dashboard/{token}#theme=night&titled=false&bordered=false

---

SSO Integration for Embedded Analytics

SSO Flow for Interactive Embedding

1. User logs into your app (your SSO)
2. Your app calls your auth endpoint
3. Auth endpoint creates Metabase JWT with user info + groups
4. SDK uses JWT to authenticate with Metabase
5. Metabase maps JWT groups to Metabase permission groups
6. User sees only data their group permits

Metabase JWT SSO Configuration

Admin → Settings → Authentication → JWT
├─ JWT Identity Provider URI: https://yourapp.com/api/metabase/auth
├─ String used by the JWT signing key: [shared secret]
├─ User attribute → Email: email
├─ User attribute → First Name: first_name
├─ User attribute → Last Name: last_name
└─ User attribute → Groups: groups

Group Mapping

---

Troubleshooting Common Embedding Issues

Debug Checklist

• [ ] Verify embedding secret matches between app and Metabase
• [ ] Check JWT payload with jwt.io (do NOT paste production secrets)
• [ ] Confirm parameter names match filter slugs in dashboard URL
• [ ] Test in incognito mode (rule out cached auth issues)
• [ ] Check browser console for CORS/CSP errors
• [ ] Verify authorized origins include your domain
• [ ] Test with a simple dashboard first before complex ones

---

Anti-Patterns

---

Cross-References

• permissions-collections.md — Permission model that governs embedded content
• native-query-patterns.md — SQL patterns used in embedded questions
• security-access-patterns.md — Security layers for data underlying embedded dashboards

---

Last updated: 2026-02-10 | Next review: 2026-05-10

Metabase Native Query Patterns

Purpose: Operational reference for writing effective SQL in Metabase — variables, field filters, template tags, SQL snippets, caching, performance tuning, and common gotchas. Freshness anchor: Q1 2026.

---

Quick Reference: Template Tag Types

---

Variables (Text, Number, Date)

Basic Variable Syntax

-- Text variable: user types a string
SELECT *
FROM orders
WHERE status = {{status}}

-- Number variable: user types a number
SELECT *
FROM orders
WHERE customer_id = {{customer_id}}

-- Date variable: user selects a date
SELECT *
FROM orders
WHERE created_at >= {{start_date}}
  AND created_at < {{end_date}}

Variable Configuration

Click variable tag in editor sidebar:
├── Variable type: Text | Number | Date
├── Filter widget type: dropdown, search, date picker
├── Required: yes/no
├── Default value: optional
└── Label: human-readable name

Optional Variables (Handle NULL)

-- Optional filter: include WHERE only when value provided
SELECT *
FROM orders
WHERE 1=1
  [[AND status = {{status}}]]
  [[AND region = {{region}}]]
  [[AND created_at >= {{start_date}}]]

-- The [[ ]] brackets make the clause optional
-- If user leaves filter empty, clause is excluded entirely

Variable Gotchas

---

Field Filters

What Makes Field Filters Special

• Generates an entire WHERE clause (not just a value)
• Connects to Metabase's filter widget (date pickers, dropdown lists)
• Supports "between", "is", "is not", relative dates
• Maps to a specific database column

Field Filter Syntax

-- Field filter replaces entire WHERE condition
SELECT
  DATE_TRUNC('day', created_at) AS order_date,
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue
FROM orders
WHERE {{created_at_filter}}
GROUP BY 1
ORDER BY 1

Field Filter Configuration

Click the variable tag → Set type to "Field Filter"
├── Database: your_database
├── Table: orders
├── Column: created_at
└── Widget type: Date (auto-selected for date columns)

Field Filter on Non-Date Columns

-- Field filter on a category column
SELECT *
FROM orders
WHERE {{status_filter}}

-- Configure: Field Filter → orders → status
-- Widget: dropdown with auto-populated values

Field Filter Limitations

Field Filter in CTE Workaround

-- WRONG: field filter in CTE (will fail)
-- WITH filtered AS (
--   SELECT * FROM orders WHERE {{date_filter}}
-- )

-- CORRECT: filter in final query or use subquery
SELECT
  o.order_id,
  o.amount,
  c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE {{date_filter}}  -- field filter on orders.created_at

---

SQL Snippets

Creating Snippets

Native query editor → Snippet icon (or type {{snippet:)
→ Create new snippet
→ Name: "active_orders_cte"
→ Content: (SQL below)

Snippet Examples

-- Snippet: "active_orders_cte"
active_orders AS (
  SELECT *
  FROM orders
  WHERE status IN ('completed', 'processing')
    AND is_test = false
    AND created_at >= '2025-01-01'
)

-- Usage in any question:
WITH {{snippet: active_orders_cte}}
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS order_count
FROM active_orders
GROUP BY 1

-- Snippet: "standard_exclusions"
AND is_test = false AND is_internal = false AND status != 'cancelled'
-- Usage: SELECT * FROM orders WHERE created_at >= {{start_date}} {{snippet: standard_exclusions}}

-- Snippet: "revenue_calculation"
SUM(CASE WHEN status = 'completed' THEN amount - COALESCE(refund_amount, 0) ELSE 0 END)
-- Usage: SELECT region, {{snippet: revenue_calculation}} AS net_revenue FROM orders GROUP BY 1

Snippet Best Practices

---

Saved Question Reuse (Card References)

Syntax

-- Reference a saved question by ID
SELECT
  region,
  COUNT(*) AS customer_count
FROM {{#42}}  -- saved question #42 as subquery
GROUP BY 1

-- Metabase wraps saved question as:
-- (SELECT ... FROM ... WHERE ...) AS question_42

Use Cases

• Use when: Reusing a complex filtered dataset across multiple analyses
• Use when: Non-SQL users built a question in the GUI that SQL users want to extend
• Avoid when: Performance-critical queries (adds subquery overhead)

Gotchas with Card References

---

Result Caching Configuration

Cache Settings

Admin → Settings → Caching
├── Saved question cache duration: 300 seconds (default)
├── Cache strategy: TTL | Schedule | Adaptive
├── Minimum query duration to cache: 1000ms
└── Max cache entry size: 100MB (default)

Per-Question Cache Override

Question → Info → Caching
├── Use default: inherits global setting
├── Custom TTL: set specific duration
├── Schedule: cache refreshes at specific times
└── Don't cache: always run fresh

Cache Strategy Decision

Cache Monitoring

-- Check cache hit rate (Metabase application database)
SELECT
  DATE_TRUNC('day', started_at) AS day,
  COUNT(*) AS total_queries,
  COUNT(CASE WHEN cache_hit THEN 1 END) AS cache_hits,
  ROUND(100.0 * COUNT(CASE WHEN cache_hit THEN 1 END) / COUNT(*), 1) AS hit_rate_pct
FROM query_execution
WHERE started_at > NOW() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1;

---

Query Performance in Metabase

Performance Checklist

• [ ] Check query_execution table for slow queries (>10s)
• [ ] Add database indexes for columns used in field filters
• [ ] Use materialized views for complex aggregations
• [ ] Enable caching for dashboards viewed frequently
• [ ] Limit result rows (Metabase caps at 2000 for display, 1M for download)
• [ ] Avoid SELECT * — select only needed columns
• [ ] Use date range filters to limit scanned data

Identifying Slow Queries

-- Top 10 slowest queries in last 7 days (Metabase application DB)
SELECT card_id, card_name,
  ROUND(AVG(running_time) / 1000.0, 2) AS avg_seconds,
  COUNT(*) AS execution_count
FROM query_execution
WHERE started_at > NOW() - INTERVAL '7 days' AND card_id IS NOT NULL
GROUP BY card_id, card_name ORDER BY avg_seconds DESC LIMIT 10;

---

Common Metabase SQL Patterns

Pattern: Date Spine with Metrics

• Generate date spine with generate_series (PG) or recursive CTE (MySQL)
• LEFT JOIN daily aggregations onto spine
• COALESCE nulls to 0 for days with no data

Pattern: Running Total

• Use window function: SUM(SUM(amount)) OVER (ORDER BY month) for cumulative
• Combine with DATE_TRUNC for monthly/weekly granularity

Pattern: Top N with "Other"

WITH ranked AS (
  SELECT category, SUM(amount) AS revenue,
    ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rn
  FROM orders WHERE {{created_at_filter}} GROUP BY 1
)
SELECT CASE WHEN rn <= 10 THEN category ELSE 'Other' END AS category,
  SUM(revenue) AS revenue
FROM ranked GROUP BY 1 ORDER BY SUM(revenue) DESC

---

Common Gotchas

---

Anti-Patterns

---

Cross-References

• permissions-collections.md — Who can run native queries
• embedding-integration.md — Parameter passing in embedded queries
• partition-strategies.md — Table partitioning for query performance
• monitoring-alerting-patterns.md — Database monitoring for query performance

---

Last updated: 2026-02-10 | Next review: 2026-05-10

Metabase Permissions and Collections

Purpose: Operational guide for configuring Metabase permissions — group-based access control, collection hierarchy, data sandboxing, database/table permissions, API automation, and multi-team organization. Freshness anchor: Q1 2026.

---

Decision Tree: Permission Architecture

START: How many distinct access levels do you need?
│
├─ 1-2 (e.g., admin + viewer)
│   └─ Simple group model
│       - Administrators group (built-in)
│       - All Users group with curated access
│
├─ 3-5 (e.g., admin, analyst, viewer, per-department)
│   └─ Group-per-role model
│       - One Metabase group per access level
│       - Collection hierarchy mirrors org structure
│
├─ 5-20 (e.g., per-customer or per-team sandboxing)
│   └─ Sandboxed groups model
│       - Data sandbox for row/column filtering
│       - Per-tenant groups with sandboxed permissions
│       - Requires Metabase Pro/Enterprise
│
└─ 20+ or dynamic
    └─ API-managed groups
        - Automated group creation via Metabase API
        - SSO group sync (SAML/JWT attributes)
        - Requires Metabase Pro/Enterprise

---

Quick Reference: Permission Levels

Data Permissions (Database/Schema/Table)

Collection Permissions

Native Query Permissions

---

Group-Based Permission Model

Core Concept

• Every user belongs to one or more groups
• Permissions are assigned to groups, never to individual users
• The All Users group sets the baseline (most restrictive)
• Additional groups add permissions (additive model)

Recommended Group Structure

Setup via Admin UI

Admin → People → Groups
1. Create group
2. Add members (or sync from SSO)

Admin → Permissions → Data
1. Select group
2. Set database/schema/table access level

Admin → Permissions → Collections
1. Select collection
2. Set group permission (Curate / View / No access)

---

Collection Hierarchy

Design Principles

• Collections are like folders — nest for organization
• Permission inheritance flows down (child inherits parent unless overridden)
• Keep hierarchy shallow (max 3-4 levels)
• Use naming conventions for discoverability

Recommended Structure

Our Analytics (root)
├── Public Dashboards/          (All Users: View)
│   ├── Company KPIs
│   └── Product Metrics
│
├── Marketing/                  (Marketing Team: Curate, Others: No access)
│   ├── Campaigns/
│   ├── Attribution/
│   └── [WIP]/                  (Working drafts, same permissions)
│
├── Finance/                    (Finance Team: Curate, Exec: View)
│   ├── Revenue/
│   ├── Forecasting/
│   └── Board Reporting/
│
├── Analytics Team/             (Data Analysts: Curate, Others: No access)
│   ├── Explorations/
│   ├── Data Quality/
│   └── Templates/
│
├── Customer Portals/           (No access for internal; per-customer groups)
│   ├── ACME Corp/              (External:ACME: View)
│   ├── Globex Inc/             (External:Globex: View)
│   └── Initech/                (External:Initech: View)
│
└── Archive/                    (Admins only)

Collection Permission Override

Parent collection: "Finance" — Finance Team: Curate
  └── Child collection: "Board Reporting" — Finance Team: View (override)
      Exec group: View

Effect: Finance team can curate most finance content
        but only view (not edit) board reporting dashboards

---

Data Sandboxing

What It Does

• Filters rows and/or hides columns based on user's group
• User sees full dashboard but data is scoped to their permissions
• Requires Metabase Pro/Enterprise

Row-Level Sandboxing

Admin → Permissions → Data → [Database] → [Table]
Select: "Sandboxed" for the group

Filter type: "Filter by a column in the table"
Column: customer_id
User attribute: customer_id (from SSO attributes)

How It Works

User logs in via SSO with attribute: customer_id = "acme-123"
  ↓
Metabase appends WHERE customer_id = 'acme-123' to all queries
  ↓
Dashboard shows only ACME data
  ↓
User cannot override or see the filter

Column-Level Sandboxing

Admin → Permissions → Data → [Database] → [Table]
Select: "Sandboxed" for the group

Filter type: "Use a saved question to limit data"
Question: pre-built question that excludes sensitive columns

Sandbox Configuration Checklist

• [ ] SSO configured with user attributes (customer_id, org_id, etc.)
• [ ] Sandbox attribute mapped in Admin → People → Group → Attribute
• [ ] Test with multiple user attributes to verify filtering
• [ ] Verify sandbox applies to native SQL queries (it does NOT by default)
• [ ] For native SQL sandbox, disable native query for sandboxed groups
• [ ] Test drill-down paths — sandbox must persist across drill-throughs

Sandbox Limitations

---

Database-Level vs Table-Level Permissions

When to Use Each

Granular Table Permissions

Admin → Permissions → Data → [Database]
Select "Granular" for group

Table: fct_orders → Unrestricted
Table: fct_payments → Unrestricted
Table: dim_customers → Sandboxed (filter by region)
Table: stg_* → Block (staging tables hidden)
Table: raw_* → Block (raw tables hidden)

---

Admin API for Permission Automation

List Groups

# GET all groups
curl -s -H "x-api-key: ${MB_API_KEY}" \
  https://metabase.company.com/api/permissions/group | jq '.'

Create Group

# POST new group
curl -s -X POST \
  -H "x-api-key: ${MB_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"name": "External: NewClient"}' \
  https://metabase.company.com/api/permissions/group

Set Data Permissions

# PUT data permissions for a group
curl -s -X PUT \
  -H "x-api-key: ${MB_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
    "groups": {
      "7": {
        "1": {
          "data": {
            "schemas": {
              "analytics": {
                "fct_orders": {"query": "all", "read": "all"},
                "dim_customers": {"query": "none", "read": "none"}
              }
            }
          }
        }
      }
    }
  }' \
  https://metabase.company.com/api/permissions/graph

Set Collection Permissions

# PUT collection permissions
curl -s -X PUT \
  -H "x-api-key: ${MB_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
    "groups": {
      "7": {
        "15": "read",
        "16": "none"
      }
    }
  }' \
  https://metabase.company.com/api/collection/graph

Automated Customer Onboarding

• Script flow: create group (POST /api/permissions/group), create collection (POST /api/collection), set permissions via graph API, set sandbox
• Automate via SSO group sync for scale

---

Multi-Team Organization Patterns

Pattern 1: Department-Based

• Use when: Teams have distinct data domains
• One group per department
• Collections mirror department structure
• Shared "Public" collection for cross-team dashboards

Pattern 2: Role-Based

• Use when: Access correlates with role, not department
• Groups: Admin, Analyst, Viewer, External
• Collections organized by topic, not team
• Role determines depth of access

Pattern 3: Hybrid (Department + Role)

• Use when: Both department and role matter
• User belongs to department group AND role group
• Department group controls data access (which tables)
• Role group controls capability (SQL vs GUI vs view-only)

---

Audit Logging

What Metabase Logs

Audit Queries (Application Database)

• Who accessed what: JOIN view_log with core_user, filter by timestamp
• Most active users: GROUP BY core_user.email on query_execution, count queries
• Failed logins: login_history WHERE session_id IS NULL (failed attempts have no session)

---

Anti-Patterns

---

Cross-References

• embedding-integration.md — Permissions model for embedded dashboards
• native-query-patterns.md — SQL access governed by permission groups
• security-access-patterns.md — Data lake security underlying Metabase permissions

---

Last updated: 2026-02-10 | Next review: 2026-05-10

Reports in Metabase API (Cards)

In Metabase, the UI "Question" is a card in the API.

Contents

• Core endpoints (most common)
• Card payload shape (practical subset)
• Query types
• Native SQL card example
• Editing workflow (recommended)
• Query Builder (MBQL) card example
• Parameters and safe query templating
• ID discovery cheatsheet
• Executing queries and exporting results

Core endpoints (most common)

• Create a card: POST /api/card
• Update a card: PUT /api/card/:id
• Read a card: GET /api/card/:id

Dashboards are separate objects:

• Create a dashboard: POST /api/dashboard
• Read/update a dashboard: GET|PUT /api/dashboard/:id

Note: Exact endpoints and response shapes can vary by Metabase version. Prefer to confirm on your instance by exporting an existing card via GET /api/card/:id and editing that JSON.

Card payload shape (practical subset)

When creating/updating a card, these fields cover most automation cases:

• name (string, REQUIRED)
• description (string, optional)
• collection_id (int, where to store the card)
• display (string, chart type; examples: table, bar, line, pie)
• visualization_settings (object; see references/charts-settings.md)
• dataset_query (object, REQUIRED)

Query types

Metabase supports two query types in dataset_query:

---

Native SQL card example

This is the most stable automation pattern: you control SQL directly.

dataset_query skeleton:

{
  "database": 2,
  "type": "native",
  "native": {
    "query": "select date_trunc('day', created_at) as day, count(*) as signups from users group by 1 order by 1"
  }
}

Minimal create payload:

{
  "name": "Daily signups",
  "collection_id": 10,
  "display": "line",
  "dataset_query": {
    "database": 2,
    "type": "native",
    "native": {
      "query": "select date_trunc('day', created_at) as day, count(*) as signups from users group by 1 order by 1"
    }
  },
  "visualization_settings": {}
}

Editing workflow (recommended)

1. Build a report in the Metabase UI until it looks correct. 2. Export it via GET /api/card/:id. 3. Treat that JSON as the source-of-truth template. 4. Apply small, targeted edits:

• SQL text
• collection_id
• display
• visualization_settings

5. PUT the updated JSON back to PUT /api/card/:id.

This approach avoids guessing version-specific defaults and visualization keys.

---

Query Builder (MBQL) card example

For questions created via the UI query builder, Metabase uses MBQL (Metabase Query Language), a JSON-based format.

dataset_query skeleton for MBQL:

{
  "database": 2,
  "type": "query",
  "query": {
    "source-table": 5,
    "aggregation": [["count"]],
    "breakout": [["field", 12, {"temporal-unit": "day"}]]
  }
}

Minimal create payload (MBQL):

{
  "name": "Daily order count",
  "collection_id": 10,
  "display": "line",
  "dataset_query": {
    "database": 2,
    "type": "query",
    "query": {
      "source-table": 5,
      "aggregation": [["count"]],
      "breakout": [["field", 12, {"temporal-unit": "day"}]]
    }
  },
  "visualization_settings": {}
}

Tip: Build a question in the Metabase UI, then use browser DevTools (Network tab) to inspect the request payload. This shows the exact MBQL structure for your query.

Converting MBQL to native SQL

Use POST /api/dataset/native to convert an MBQL query to native SQL:

curl -X POST "$METABASE_URL/api/dataset/native" \
  -H "X-API-KEY: $METABASE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query": {"source-table": 5, "aggregation": [["count"]]}}'

---

Parameters and safe query templating

Avoid string-interpolating untrusted input into SQL. If you need runtime parameters:

1. Build the question in the Metabase UI with filters/variables. 2. Export the card JSON (GET /api/card/:id). 3. Reuse the exported dataset_query (including any parameter/template-tag structures) as your template.

Metabase query templating shapes vary by version and by whether the question is SQL vs MBQL. Export-first is the most reliable way to keep parameters compatible with your instance.

ID discovery cheatsheet

Prefer discovery over hardcoding numeric IDs across environments.

Common endpoints (version/edition dependent):

• Collections: GET /api/collection, GET /api/collection/tree
• Databases: GET /api/database
• Tables and fields: GET /api/database/:id/metadata, GET /api/table, GET /api/field

Tip: When in doubt, use browser DevTools (Network tab) while saving a question in the UI to see the exact payload and IDs your instance uses.

Executing queries and exporting results

Use POST /api/dataset to run a query and get results:

curl -X POST "$METABASE_URL/api/dataset" \
  -H "X-API-KEY: $METABASE_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "database": 2,
    "type": "native",
    "native": {"query": "SELECT COUNT(*) FROM orders"}
  }'

Export formats via POST /api/card/:id/query/:format: