Metabase best practices

​

Avoid full table scans

• Bad example: Full table scan
  SELECT *
  FROM events;
  
• Good example: Filter early
  SELECT user_id, event_type
  FROM events
  WHERE event_type = 'purchase';
  

​

Use date filters

WHERE event_date >= current_date - interval '30' day

​

Prefer aggregated queries

• Bad example: Returning raw rows
  SELECT user_id, amount
  FROM transactions
  WHERE transaction_date >= current_date - interval '7' day;
  
• Good example: Summary metric
  SELECT
      date(transaction_date) AS day,
      sum(amount) AS total_revenue
  FROM transactions
  WHERE transaction_date >= current_date - interval '7' day
  GROUP BY 1
  ORDER BY 1;
  

​

Use partitions

• Bad example: Filtering on a non-partition column.
  SELECT *
  FROM events
  WHERE user_id = 123;
  
• Good example: Filter using the partition column.
  SELECT * FROM events
  WHERE event_date = current_date
  AND user_id = 123;
  

​

Limit returned data

• Bad example: Returning millions of rows.
  SELECT *
  FROM page_views
  WHERE view_date >= current_date - interval '90' day;
  
• Good example: Return only needed fields, or aggregated data.
  SELECT user_id, page_url, view_time
  FROM page_views
  WHERE view_time >= current_date - interval '7' day
  LIMIT 5000;
  

​

Additional resources

• To get an overview of Metabase, refer to the Metabase in NexusOne page.
• To learn practical ways to use Metabase in the NexusOne environment, refer to the Metabase hands-on examples page.
• For more details about Metabase, refer to the Metabase official documentation.
• If you are using the NexusOne portal to visualize your dataset, refer to the Discover page.