Build Efficient Metabase Queries and Dashboards

This skill covers Metabase Question Builder expertise - creating efficient queries, optimizing dashboard performance, and implementing advanced analytics workflows using both the GUI query builder and custom SQL. Core principles: start simple and build complexity incrementally, put performance first (query execution time and database load), design user-centric questions that answer specific business needs, build for reusability, and validate data integrity (nulls, duplicates).

Query-builder best practices cover filtering strategy (relative date ranges like "Past 30 days" over absolute dates for dynamic dashboards, filtering at the database level rather than post-aggregation, strategic AND/OR combination to avoid empty result sets, and "Is not empty" filters for null handling) and aggregation optimization, contrasting an efficient DATE_TRUNC/COUNT DISTINCT/GROUP BY 1 pattern against an expensive unindexed string-concatenation GROUP BY.

Advanced SQL patterns cover window functions for running totals and month-over-month growth:

-- Running totals and growth calculations
SELECT 
  month,
  monthly_revenue,
  SUM(monthly_revenue) OVER (ORDER BY month) as cumulative_revenue,
  LAG(monthly_revenue, 1) OVER (ORDER BY month) as prev_month_revenue,
  ROUND(
    (monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY month)) 
    / LAG(monthly_revenue, 1) OVER (ORDER BY month) * 100, 2
  ) as mom_growth_pct
FROM monthly_revenue_summary
ORDER BY month

and a customer-retention cohort-analysis pattern using a CTE to bucket customers by their first-order month and compute months-since-first-order. Dashboard design follows a four-part KPI structure (summary cards for key metrics, trend charts for time series, dimensional breakdowns, and drill-down tables), with reusable date-range and multi-select filter parameters using Metabase's templating syntax.

Performance optimization covers LIMIT in exploratory queries, database views for complex frequently-used joins, indexing WHERE/JOIN columns, and using COUNT(DISTINCT) sparingly on large datasets. Caching strategy contrasts a cache-friendly query (stable historical time window) against a cache-unfriendly one (a rolling NOW() - INTERVAL window that changes every query). Question types covered: metrics questions (single-number KPI cards with meaningful formatting and period comparisons), trend analysis (line charts for time series, bar charts for categorical comparisons, appropriate time granularity), and distribution analysis (a bucketed histogram query grouping order value into ranges).

Error handling and validation is shown via a data-quality-check query using UNION ALL to surface multiple named issues (missing customer IDs, negative revenue) over a recent time window. Tips for success: test across different date ranges, document complex SQL logic with comments, use consistent naming for filters and fields, validate critical metrics against source systems, design for mobile view, set up Metabase Pulse alerts, and version-control iterations of complex questions.

Use it when building or optimizing Metabase questions and dashboards - writing efficient aggregation SQL, designing cache-friendly queries, building cohort or window-function analyses, or structuring a KPI dashboard. It is not a general SQL-performance-tuning guide beyond the Metabase context, and it does not cover Metabase administration such as permissions, embedding, or data source setup.

Given a business question - for example, monthly revenue growth by cohort - it produces optimized SQL (window functions, CTEs, or aggregation), a recommended chart/question type, and guidance on making the query cache-friendly and dashboard-appropriate.

Works with Metabase's GUI query builder and native SQL editor, including its templating syntax for reusable filter parameters (date-range and multi-select loops) and its Pulse alerting feature.

Data analysts and BI engineers building or optimizing Metabase questions and dashboards.