# Dremio Analytics

> Query Dremio for CRM data, billing information, and business analytics. Use when retrieving HubSpot deals, customer data, or generating business reports from the data lakehouse.

- Skill: `shakudo-io/dremio-analytics` (Agent Skill)
- Install (CLI): `npx skillmds@latest add shakudo-io/dremio-analytics`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shakudo-io/dremio-analytics/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: MIT
- Author: Shakudo-io (https://skillmd.com/u/shakudo-io)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shakudo-io/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

```javascript
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)

```javascript
// 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

| Table Path | Description |
|------------|-------------|
| `hubspot.deals` | Sales deals/opportunities |
| `hubspot.companies` | Company records |
| `hubspot.contacts` | Contact records |
| `hubspot.deal_stages` | Deal pipeline stages |

### Billing Data

| Table Path | Description |
|------------|-------------|
| `billing.invoices` | Customer invoices |
| `billing.subscriptions` | Active subscriptions |
| `billing.usage` | Usage metrics |

## Common Query Patterns

### 1. List Recent Deals

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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
```sql
-- 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
```sql
-- Case conversion
LOWER(column), UPPER(column)

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

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

### Aggregations
```sql
COUNT(*), COUNT(DISTINCT column)
SUM(column), AVG(column)
MIN(column), MAX(column)
```

### Window Functions
```sql
-- 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
   ```sql
   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:
   ```sql
   SELECT * FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_SCHEMA = 'your_space'
   ```

### Column Not Found

List columns for a table:
```sql
SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'your_table'
```

### Permission Denied

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

## Example Workflow

```javascript
// 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
  `
})
```