Imported from developer-advocacy-dremio/dremio-skills-bundle (dremio-sql-skill/SKILL.md). Install upstream with npx skills add developer-advocacy-dremio/dremio-skills-bundle --skill dremio-sql-skill. Copyright stays with the author.

Dremio SQL Skill

This skill equips you to write correct SQL for Dremio. Dremio uses a SQL dialect built on Apache Calcite with significant extensions for Apache Iceberg tables, versioned catalogs, reflections, RBAC, and PIPE ingestion. Many of these features have syntax that differs from standard ANSI SQL or other engines like Snowflake, BigQuery, or PostgreSQL.

Always read the linked documentation page for exact syntax before writing a query.

Key Dialect Differences

Things that catch agents off guard when writing SQL for Dremio:

SQL Command Quick Reference

DDL — Tables & Views

-- Create an Iceberg table
CREATE TABLE catalog.schema.my_table (
  id INT,
  name VARCHAR,
  created_at TIMESTAMP
) PARTITION BY (MONTH(created_at));

-- Create table from query
CREATE TABLE catalog.schema.summary AS
SELECT region, SUM(amount) AS total FROM sales GROUP BY region;

-- Create a view
CREATE VIEW space.my_view AS
SELECT * FROM catalog.schema.my_table WHERE active = true;

-- Alter table (add/drop columns, change partition)
ALTER TABLE catalog.schema.my_table ADD COLUMNS (email VARCHAR);
ALTER TABLE catalog.schema.my_table DROP COLUMN email;

-- Drop
DROP TABLE catalog.schema.my_table;
DROP VIEW space.my_view;

DML — Iceberg Tables

-- Insert
INSERT INTO catalog.schema.my_table VALUES (1, 'Alice', CURRENT_TIMESTAMP);
INSERT INTO catalog.schema.my_table SELECT * FROM staging_table;

-- Update
UPDATE catalog.schema.my_table SET name = 'Bob' WHERE id = 1;

-- Delete
DELETE FROM catalog.schema.my_table WHERE id = 1;

-- Merge (upsert)
MERGE INTO catalog.schema.target AS t
USING catalog.schema.source AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

-- Truncate
TRUNCATE TABLE catalog.schema.my_table;

COPY INTO — Bulk Ingestion

-- Load from object storage into an Iceberg table
COPY INTO catalog.schema.my_table
FROM '@my_s3_source/data/files/'
FILE_FORMAT 'parquet';

-- CSV with options
COPY INTO catalog.schema.my_table
FROM '@my_s3_source/data/'
FILE_FORMAT 'csv'
(RECORD_DELIMITER '\n', FIELD_DELIMITER ',', SKIP_FIRST_LINE);

Table Maintenance

-- Compact small files (Iceberg)
OPTIMIZE TABLE catalog.schema.my_table;

-- Remove old snapshots and orphaned files
VACUUM TABLE catalog.schema.my_table EXPIRE SNAPSHOTS OLDER_THAN '2024-01-01 00:00:00';

-- Rollback to prior snapshot
ROLLBACK TABLE catalog.schema.my_table TO SNAPSHOT 'snapshot_id_here';

-- Analyze table statistics
ANALYZE TABLE catalog.schema.my_table COMPUTE STATISTICS;

Versioned Queries (Nessie/Arctic)

-- Query at a specific branch
SELECT * FROM catalog.schema.my_table AT BRANCH main;

-- Query at a tag
SELECT * FROM catalog.schema.my_table AT TAG v1_release;

-- Query at a snapshot ID
SELECT * FROM catalog.schema.my_table AT SNAPSHOT '1234567890';

-- Time travel
SELECT * FROM catalog.schema.my_table AT TIMESTAMP '2024-06-15 12:00:00';

Reflections (Query Acceleration)

-- Create a raw reflection
ALTER TABLE catalog.schema.my_table
  CREATE RAW REFLECTION my_raw_ref
  USING DISPLAY (col1, col2, col3);

-- Create an aggregation reflection
ALTER TABLE catalog.schema.my_table
  CREATE AGGREGATE REFLECTION my_agg_ref
  USING DIMENSIONS (region) MEASURES (amount (SUM, COUNT));

-- Drop a reflection
ALTER TABLE catalog.schema.my_table DROP REFLECTION my_raw_ref;

RBAC — Grants & Privileges

-- Grant SELECT on a dataset
GRANT SELECT ON catalog.schema.my_table TO ROLE analysts;

-- Grant on a space/folder
GRANT ALL ON FOLDER "Analytics" TO ROLE data_engineers;

-- Revoke
REVOKE SELECT ON catalog.schema.my_table FROM ROLE analysts;

-- Create a role
CREATE ROLE data_viewers;

-- Grant role to user
GRANT ROLE data_viewers TO USER "john@example.com";

User-Defined Functions (UDFs)

-- Create a scalar UDF
CREATE FUNCTION double_it(x INT)
  RETURNS INT
  RETURN SELECT x * 2;

-- Create a tabular UDF
CREATE FUNCTION recent_orders(days INT)
  RETURNS TABLE(id INT, amount DOUBLE)
  RETURN SELECT id, amount FROM orders
    WHERE order_date > CURRENT_DATE - CAST(days AS INTERVAL DAY);

-- Drop
DROP FUNCTION double_it;

Row-Access & Column-Masking Policies

-- Create a row-access policy function
CREATE FUNCTION region_filter(region_col VARCHAR)
  RETURNS BOOLEAN
  RETURN SELECT region_col = QUERY_USER();

-- Apply row-access policy
ALTER TABLE catalog.schema.my_table
  SET ROW ACCESS POLICY region_filter(region);

-- Create a masking policy function
CREATE FUNCTION mask_email(email_col VARCHAR)
  RETURNS VARCHAR
  RETURN SELECT CASE WHEN QUERY_USER() = 'admin' THEN email_col ELSE '***' END;

-- Apply masking policy
ALTER TABLE catalog.schema.my_table
  SET COLUMN MASKING POLICY mask_email(email);

PIPE (Continuous Ingestion)

-- Create a pipe for continuous ingestion
CREATE PIPE my_pipe AS
  COPY INTO catalog.schema.target
  FROM '@my_s3_source/streaming/'
  FILE_FORMAT 'json';

-- Manage pipes
ALTER PIPE my_pipe DEDUPE_LOOKBACK_PERIOD 7;
DESCRIBE PIPE my_pipe;
DROP PIPE my_pipe;

Utility

-- Set context (default schema)
USE catalog.schema;

-- Set query queue
SET QUEUE "high_priority";

-- Common table expressions
WITH recent AS (
  SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT customer_id, SUM(amount) FROM recent GROUP BY customer_id;

-- Show table properties (Iceberg metadata)
SHOW TBLPROPERTIES catalog.schema.my_table;

SQL Documentation Index

When you need the exact syntax, parameters, or examples for a specific command or function, read the relevant page using your URL-reading tools.

SQL Commands — General

SQL Commands — Iceberg Tables

SQL Functions

Other SQL Reference

Tips

• Always double-quote identifiers with hyphens, dots, or special characters: "my-catalog"."my.schema"
• DML (INSERT, UPDATE, DELETE, MERGE) only works on Iceberg tables, not views or non-Iceberg sources.
• COPY INTO is the preferred way to bulk-load files (Parquet, CSV, JSON) from object storage into Iceberg tables.
• OPTIMIZE compacts small files; VACUUM removes expired snapshots. Both are essential for Iceberg table maintenance.
• Versioned queries (AT BRANCH, AT TAG, AT SNAPSHOT) only work with Nessie/Arctic catalogs.
• Reflections are created via ALTER TABLE ... CREATE ... REFLECTION, not a standalone CREATE REFLECTION.
• QUERY_USER() is a special function that returns the current user — useful in row-access and masking policies.
• When unsure about exact syntax, always read the specific doc page from the index above.