Dremio Analytics

Dremio Analytics Query Skill

This skill teaches you how to query Dremio for business analytics, CRM data, and reporting.

Prerequisites

• Dremio MCP tools available (dremio_execute_dremio_query, dremio_execute_dremio_query_and_get_results)
• Authentication configured (login may be required via dremio_login)

Dremio Basics

Dremio is a data lakehouse query engine. Data is organized in:

• Spaces: Logical groupings (like databases)
• Folders: Organization within spaces
• Virtual Datasets (VDS): Curated views
• Physical Datasets (PDS): Raw source tables

Query Execution

Simple Query with Results

dremio_execute_dremio_query_and_get_results({
  sql: "SELECT * FROM my_space.my_table LIMIT 10",
  limit: 100,
  timeout: 60000  // 60 seconds
})

Async Query (Long Running)

// 1. Start query
const { jobId } = await dremio_execute_dremio_query({
  sql: "SELECT * FROM large_table"
})

// 2. Check status
dremio_check_job_status({ jobId })

// 3. Get results when complete
dremio_get_query_results({ jobId, limit: 100, offset: 0 })

Common Data Sources

HubSpot CRM Data

Billing Data

Common Query Patterns

1. List Recent Deals

SELECT 
  deal_name,
  deal_stage,
  amount,
  close_date,
  company_name
FROM hubspot.deals d
LEFT JOIN hubspot.companies c ON d.company_id = c.id
WHERE close_date >= CURRENT_DATE - INTERVAL '90' DAY
ORDER BY close_date DESC
LIMIT 50

2. Deal Pipeline Summary

SELECT 
  deal_stage,
  COUNT(*) as deal_count,
  SUM(amount) as total_value,
  AVG(amount) as avg_deal_size
FROM hubspot.deals
WHERE close_date >= CURRENT_DATE - INTERVAL '1' YEAR
GROUP BY deal_stage
ORDER BY total_value DESC

3. Top Customers by Revenue

SELECT 
  c.company_name,
  COUNT(d.id) as deal_count,
  SUM(d.amount) as total_revenue
FROM hubspot.companies c
JOIN hubspot.deals d ON c.id = d.company_id
WHERE d.deal_stage = 'closed_won'
GROUP BY c.company_name
ORDER BY total_revenue DESC
LIMIT 20

4. Contact Search

SELECT 
  first_name,
  last_name,
  email,
  company_name,
  job_title
FROM hubspot.contacts
WHERE 
  LOWER(email) LIKE '%@acme.com'
  OR LOWER(company_name) LIKE '%acme%'
LIMIT 50

5. Monthly Revenue Trend

SELECT 
  DATE_TRUNC('month', close_date) as month,
  COUNT(*) as deals_closed,
  SUM(amount) as revenue
FROM hubspot.deals
WHERE deal_stage = 'closed_won'
  AND close_date >= CURRENT_DATE - INTERVAL '12' MONTH
GROUP BY DATE_TRUNC('month', close_date)
ORDER BY month

6. Deal Conversion Rate

SELECT 
  deal_stage,
  COUNT(*) as count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as percentage
FROM hubspot.deals
WHERE created_date >= CURRENT_DATE - INTERVAL '1' YEAR
GROUP BY deal_stage
ORDER BY count DESC

SQL Syntax Notes

Dremio uses ANSI SQL with some specifics:

Date Functions

-- Current date/time
CURRENT_DATE
CURRENT_TIMESTAMP

-- Date arithmetic
date_column + INTERVAL '30' DAY
date_column - INTERVAL '1' MONTH

-- Date truncation
DATE_TRUNC('month', date_column)
DATE_TRUNC('year', date_column)

-- Formatting
TO_CHAR(date_column, 'YYYY-MM-DD')

String Functions

-- Case conversion
LOWER(column), UPPER(column)

-- Pattern matching
column LIKE '%pattern%'
column ILIKE '%pattern%'  -- case-insensitive

-- Concatenation
CONCAT(col1, ' ', col2)
col1 || ' ' || col2

Aggregations

COUNT(*), COUNT(DISTINCT column)
SUM(column), AVG(column)
MIN(column), MAX(column)

Window Functions

-- Running total
SUM(amount) OVER (ORDER BY date_column)

-- Row number
ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col)

-- Percentage of total
100.0 * amount / SUM(amount) OVER ()

Query Best Practices

1. Always use LIMIT: Prevent returning too much data

   SELECT * FROM table LIMIT 100
   

2. Filter early: Put WHERE clauses before JOINs when possible

3. Use column names: Avoid SELECT * in production queries

4. Index-friendly filters: Use equality (=) over LIKE when possible

5. Date ranges: Always filter by date for large tables

Troubleshooting

Query Timeout

• Increase timeout in query options
• Add more filters to reduce data volume
• Use async query pattern for long-running queries

Table Not Found

1. Check spelling (case-sensitive)
2. Verify full path: space.folder.table
3. List available tables:

   SELECT * FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_SCHEMA = 'your_space'
   

Column Not Found

List columns for a table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'your_table'

Permission Denied

• Verify login credentials
• Check space/table permissions with admin

Example Workflow

// 1. Login if needed
dremio_login({ username: "user", password: "pass" })

// 2. Explore available tables
dremio_execute_dremio_query_and_get_results({
  sql: "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES LIMIT 50"
})

// 3. Query specific data
dremio_execute_dremio_query_and_get_results({
  sql: `
    SELECT company_name, SUM(amount) as revenue
    FROM hubspot.deals
    WHERE deal_stage = 'closed_won'
    GROUP BY company_name
    ORDER BY revenue DESC
    LIMIT 10
  `
})