create-chart

Create a new saved chart in a space. Requires table name, metric query, and chart configuration.

⚠️ CRITICAL: Chart configuration structure must be precise or the chart will break!

═══════════════════════════════════════════════════════════════════ COMPLETE WORKING EXAMPLE - Line Chart with Count Distinct Metric: ═══════════════════════════════════════════════════════════════════

metricQuery: { "exploreName": "my_table", "dimensions": ["my_table_date_day"], "metrics": [], "filters": { "dimensions": { "id": "root", "and": [ { "id": "filter_1", "target": {"fieldId": "my_table_country"}, "operator": "equals", "values": ["US"] }, { "id": "filter_2", "target": {"fieldId": "my_table_date_day"}, "values": [30], "operator": "inThePast", "required": false, "settings": { "completed": false, "unitOfTime": "days" } } ] } }, "sorts": [{"fieldId": "my_table_date_day", "descending": true}], "limit": 500, "tableCalculations": [], "additionalMetrics": [ { "name": "dau", "label": "Daily Active Users", "description": "Count of unique users", "type": "count_distinct", "sql": "${TABLE}.user_id", "table": "my_table", "baseDimensionName": "user_id", "formatOptions": {"type": "default", "separator": "default"} } ] }

chartConfig: { "type": "cartesian", "config": { "layout": { "xField": "my_table_date_day", "yField": ["my_table_dau"], "flipAxes": false }, "eChartsConfig": { "xAxis": [{"name": "Date"}], "yAxis": [{"name": "DAU"}], "series": [ { "type": "line", "encode": { "xRef": {"field": "my_table_date_day"}, "yRef": {"field": "my_table_dau"} }, "yAxisIndex": 0 } ] } } }

pivotConfig (optional): { "columns": ["my_table_country"] }

═══════════════════════════════════════════════════════════════════ EXAMPLE WITH CUSTOM DIMENSIONS - Stacked/Segmented Charts: ═══════════════════════════════════════════════════════════════════

Use Case: Create grouped/segmented visualizations by categorizing data into meaningful buckets (e.g., Top N + "Other" pattern, status groupings, etc.)

metricQuery: { "exploreName": "your_table", "dimensions": ["your_table_date_day", "category_dimension"], "metrics": [], "filters": { "dimensions": { "id": "root", "and": [ { "id": "filter_1", "target": {"fieldId": "your_table_date_day"}, "values": [30], "operator": "inThePast", "required": false, "settings": {"completed": false, "unitOfTime": "days"} } ] } }, "sorts": [{"fieldId": "your_table_date_day", "descending": true}], "limit": 500, "additionalMetrics": [ { "name": "unique_count", "label": "Unique Count", "type": "count_distinct", "sql": "${TABLE}.identifier_column", "table": "your_table", "baseDimensionName": "identifier_column" } ], "customDimensions": [ { "id": "category_dimension", "name": "Category Dimension", "type": "sql", "table": "your_table", "sql": "CASE\n WHEN raw_field = 'value1' THEN 'Category A'\n WHEN raw_field = 'value2' THEN 'Category B'\n WHEN raw_field IN ('value3', 'value4') THEN 'Category C'\n ELSE 'Other'\n END", "dimensionType": "string" } ] }

chartConfig: { "type": "cartesian", "config": { "layout": { "xField": "your_table_date_day", "yField": ["your_table_unique_count"], "flipAxes": false }, "eChartsConfig": { "xAxis": [{"name": "Date"}], "yAxis": [{"name": "Count"}], "series": [ { "type": "bar", "stack": "your_table_unique_count", "encode": { "xRef": {"field": "your_table_date_day"}, "yRef": {"field": "your_table_unique_count"} }, "yAxisIndex": 0 } ] } } }

pivotConfig: { "columns": ["category_dimension"] }

Key Pattern: The custom dimension "category_dimension" is:

1. Defined in customDimensions with SQL CASE logic

2. Added to dimensions array for grouping

3. Used in pivotConfig.columns to create separate stacks/segments per category Result: One stacked segment per CASE branch, visualizing data by category over time

═══════════════════════════════════════════════════════════════════ KEY RULES (MUST FOLLOW): ═══════════════════════════════════════════════════════════════════

1. additionalMetrics naming:

   • Metrics are referenced as: "{table}_{metricName}"

   • Example: table="my_table", name="dau" → "my_table_dau"

2. series.encode MUST use objects (NOT strings): ✅ CORRECT: "xRef": {"field": "my_table_date_day"} ❌ WRONG: "xRef": "my_table_date_day"

3. eChartsConfig.series is required:

   • Must have at least one series object

   • Each series MUST have: type, encode.xRef, encode.yRef

4. Metric types for additionalMetrics:

   • "count_distinct": COUNT(DISTINCT field)

   • "count": COUNT(*)

   • "sum": SUM(field)

   • "avg": AVG(field)

   • "min": MIN(field)

   • "max": MAX(field)

5. Filter operators and structures:

   Simple filters:

   • "equals": {"operator": "equals", "values": ["US"]}

   • "notEquals": {"operator": "notEquals", "values": ["US"]}

   • "contains": {"operator": "contains", "values": ["search_term"]}

   • "notNull": {"operator": "notNull"}

   • "isNull": {"operator": "isNull"}

   Time-based filters (CRITICAL - note the structure): The "inThePast" operator requires specific structure: { "id": "filter_1", "target": {"fieldId": "table_date_field"}, "values": [30], # ← Number goes HERE in values array "operator": "inThePast", "required": false, "settings": { "completed": false, # ← Must be FALSE (not true) "unitOfTime": "days" # Options: "days", "weeks", "months", "years" } }

   ⚠️ Common mistakes to AVOID: ❌ WRONG: "settings": {"number": 30} → Number does NOT go in settings ❌ WRONG: "completed": true → Must be false ✅ CORRECT: "values": [30] + "completed": false

6. Pivot configuration:

   • Use pivotConfig to split series by dimension values

   • Example: {"columns": ["my_table_country"]} creates one line per country

   • This enables grouping/segmentation in charts

7. Custom Dimensions (customDimensions):

   • Create calculated dimensions using SQL expressions (CASE, CONCAT, etc.)

   • Custom dimensions can be used in dimensions array, pivots, and filters

   • Each custom dimension requires: id, name, type, table, sql, dimensionType

   Structure: { "id": "custom_dim_id", # Unique identifier to reference in dimensions/pivots "name": "Custom Dimension Name", # Display name shown in UI "type": "sql", # Always "sql" for custom dimensions "table": "base_table", # Base table name (matches exploreName) "sql": "CASE WHEN ... THEN ... ELSE ... END", # SQL expression "dimensionType": "string" # Data type: "string", "number", "date", etc. }

   Common Patterns:

   a) Top N + "Other" grouping (reduce cardinality): { "id": "top_items_group", "sql": "CASE WHEN item_name = 'TopItem1' THEN 'TopItem1' WHEN item_name = 'TopItem2' THEN 'TopItem2' WHEN item_name IN ('TopItem3', 'TopItem4') THEN 'TopItem3/4' ELSE 'Other' END", "dimensionType": "string" }

   b) Status/Category mapping: { "id": "status_group", "sql": "CASE WHEN status IN ('active', 'pending') THEN 'Active' WHEN status IN ('completed', 'archived') THEN 'Completed' ELSE 'Other' END", "dimensionType": "string" }

   c) Numeric bucketing: { "id": "value_bucket", "sql": "CASE WHEN amount < 10 THEN 'Small' WHEN amount < 100 THEN 'Medium' ELSE 'Large' END", "dimensionType": "string" }

   Usage in metricQuery:

   • Add to customDimensions array: "customDimensions": [...]

   • Reference by id in dimensions: "dimensions": ["table_date", "custom_dim_id"]

   • Use in pivots: "pivotConfig": {"columns": ["custom_dim_id"]}

   • Filter on custom dimensions just like regular dimensions

   Benefits:

   • Reduce high-cardinality dimensions to manageable segments

   • Apply business logic without modifying base tables

   • Create "Top N + Other" patterns for cleaner visualizations

   • Categorize raw values into meaningful groups

   With Pivots - Creating Segmented Charts: When a custom dimension is used in BOTH dimensions array AND pivotConfig.columns, Lightdash creates one separate series/segment per unique value from the CASE statement. Example: 5 CASE branches = 5 stacked segments in the chart.

═══════════════════════════════════════════════════════════════════ CHART TYPES: ═══════════════════════════════════════════════════════════════════

Line Chart: series[].type = "line" Bar Chart: series[].type = "bar" Area Chart: series[].type = "line" + series[].areaStyle = {} Stacked Area: series[].type = "line" + series[].areaStyle = {} + series[].stack = "stack_name"

═══════════════════════════════════════════════════════════════════ VALIDATION: ═══════════════════════════════════════════════════════════════════

The server will automatically:

• Validate chart config structure (xRef/yRef objects)

• Validate field references match metricQuery

• Auto-generate tableConfig.columnOrder

• Add additionalMetrics to metrics array for proper display

If validation fails, you'll get a detailed error message.