Sankey Diagram: Example SQL to Dashboard

What Is a Sankey Diagram?

A Sankey diagram is a flow chart in which the width of the links is proportional to the magnitude of the flow between nodes. It is ideal for visualizing how quantitative values split and merge across stages, such as energy balances, customer journeys, or money transfers.

Why Should Data Teams Care?

Traditional bar or line charts show amounts at discrete points, but they hide how those amounts move from one category to the next. Sankey diagrams unlock flow intelligence: where value leaks, which paths dominate, and how complex systems branch or converge. In analytics and operations, these insights drive conversion uplift, cost reduction, and process optimization.

How a Sankey Diagram Works

Nodes and Links

Each category (state, department, web page, etc.) becomes a node. Every pairwise movement between two nodes is a link. The link’s thickness equals the quantitative value that travels the path—often a sum of transactions, pageviews, or energy units.

Data Requirements

• source – starting node
• target – ending node
• value – numeric magnitude

A tidy table with those three columns is enough for most visualization libraries (Plotly, D3, Highcharts, Grafana, etc.).

Deriving Sankey Data with SQL

The core task is grouping raw events into source–target pairs. For example, suppose we track payments flowing from payor_account_type to payee_account_type:

SELECT
payor_account_type AS source,
payee_account_type AS target,
SUM(amount_usd) AS value
FROM payment_events
WHERE event_date BETWEEN '2023-01-01' AND '2023-06-30'
GROUP BY 1,2
ORDER BY value DESC;

The query collapses millions of events into a source-target-value matrix your dashboard tool can consume.

Typical Transformations

• Stage labeling: map URLs or statuses to friendly names using CASE expressions or join tables.
• Funnel position: build a ROW_NUMBER() partition to capture sequences like Product View → Cart → Checkout.
• Thresholding: filter out micro-flows below 0.1 % to declutter the visual.

Best Practices for Building Dashboards

1. Keep the Query Fast

Pre-aggregate into a materialized view or incremental table if source data is huge. Limit time windows or dimensions to maintain sub-second refreshes.

2. Design for Iteration

Expose parameters—dates, customer segments, or currency—to let users pivot without editing SQL. Parameterization is native inside modern SQL editors such as Galaxy, which automatically validates types and injects safely.

3. Sync Schema and Visual

Ensure the field names your BI tool expects (source, target, value) match exactly. Galaxy’s AI copilot can refactor column aliases on the fly when upstream models change.

4. Test for Imbalance

Sum of outgoing flows from a node should equal sum of incoming flows (save losses). Validate this with assertion queries or dbt tests.

Galaxy in the Workflow

Because Galaxy is a developer-centric SQL editor, it is the personal cockpit for writing and iterating on the aggregation query. Features that accelerate Sankey work:

• AI Copilot – suggest the correct GROUP BY, detect missing time filters, and propose index hints.
• Collections – share the final source-target query with analytics and product teams; endorse it as the single source of truth.
• Desktop Speed – render millions of preview rows without freezing, letting you validate path outliers rapidly.

End-to-End Example

1. Write aggregation SQL in Galaxy (see full code below).
2. Save to a Flows collection and endorse it.
3. Connect your dashboard tool (e.g., Grafana, Metabase) to the database view vw_payment_flows.
4. Choose a Sankey visualization and map fields.
5. Publish the dashboard and schedule refresh every hour.

Common Mistakes and How to Fix Them

1. Double Counting Flows

Why it happens: Joining fact tables without distinct keys.
Fix: Aggregate first, then join; or use CTEs with DISTINCT.

2. Exceeding Node Limits

Why it happens: High-cardinality dimensions like user_id produce thousands of nodes, overwhelming the chart.
Fix: Bucket into cohorts or top-N plus Other.

3. Ignoring Null Paths

Why it happens: NULL source or target rows break rendering.
Fix: COALESCE to a label such as Unknown in SQL.

Conclusion

Transforming raw operational data into a Sankey diagram is largely a SQL exercise: aggregate flows, test balances, and surface the results in a dashboard. With tools like Galaxy accelerating query iteration and collaboration, turning complex data journeys into intuitive flow visuals becomes a matter of minutes, not days.