| name | Dremio SQL Reference |
| description | Teaches an AI agent the Dremio SQL dialect — unique syntax, Iceberg DML, reflections DDL, versioned queries, RBAC commands, and links to the full SQL reference docs. |
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:
| Feature | Dremio Syntax | Common Mistake |
|---|
| Table paths use dots | "my-catalog".schema.table | Forgetting double quotes around names with hyphens/special chars |
| Versioned queries | SELECT * FROM t AT BRANCH main | Using non-existent FOR SYSTEM_TIME instead of AT |
| Time travel | AT SNAPSHOT '...' or AT TIMESTAMP '...' | Using Snowflake-style BEFORE(TIMESTAMP => ...) |
| Iceberg DML | INSERT, UPDATE, DELETE, MERGE on Iceberg tables | Trying DML on non-Iceberg sources |
| COPY INTO | COPY INTO table FROM '@source/path' | Incorrect path format or file format spec |
| Reflections | ALTER TABLE ... CREATE ... REFLECTION | Using non-existent CREATE REFLECTION command |
| UDFs | CREATE FUNCTION name(x INT) RETURNS INT RETURN SELECT x + 1 | Using AS $$ ... $$ syntax from PostgreSQL |
| Column masking | ALTER TABLE ... SET MASKING POLICY ... | Column-level security has unique Dremio syntax |
| Path quoting | Double quotes: "my-source"."my.schema"."table" | Using backticks (MySQL) or square brackets (SQL Server) |
SQL Command Quick Reference
DDL — Tables & Views
CREATE TABLE catalog.schema.my_table (
id INT,
name VARCHAR,
created_at TIMESTAMP
) PARTITION BY (MONTH(created_at));
CREATE TABLE catalog.schema.summary AS
SELECT region, SUM(amount) AS total FROM sales GROUP BY region;
CREATE VIEW space.my_view AS
SELECT * FROM catalog.schema.my_table WHERE active = true;
ALTER TABLE catalog.schema.my_table ADD COLUMNS (email VARCHAR);
ALTER TABLE catalog.schema.my_table DROP COLUMN email;
DROP TABLE catalog.schema.my_table;
DROP VIEW space.my_view;
DML — Iceberg Tables
INSERT INTO catalog.schema.my_table VALUES (1, 'Alice', CURRENT_TIMESTAMP);
INSERT INTO catalog.schema.my_table SELECT * FROM staging_table;
UPDATE catalog.schema.my_table SET name = 'Bob' WHERE id = 1;
DELETE FROM catalog.schema.my_table WHERE id = 1;
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 TABLE catalog.schema.my_table;
COPY INTO — Bulk Ingestion
COPY INTO catalog.schema.my_table
FROM '@my_s3_source/data/files/'
FILE_FORMAT 'parquet';
COPY INTO catalog.schema.my_table
FROM '@my_s3_source/data/'
FILE_FORMAT 'csv'
(RECORD_DELIMITER '\n', FIELD_DELIMITER ',', SKIP_FIRST_LINE);
Table Maintenance
OPTIMIZE TABLE catalog.schema.my_table;
VACUUM TABLE catalog.schema.my_table EXPIRE SNAPSHOTS OLDER_THAN '2024-01-01 00:00:00';
ROLLBACK TABLE catalog.schema.my_table TO SNAPSHOT 'snapshot_id_here';
ANALYZE TABLE catalog.schema.my_table COMPUTE STATISTICS;
Versioned Queries (Nessie/Arctic)
SELECT * FROM catalog.schema.my_table AT BRANCH main;
SELECT * FROM catalog.schema.my_table AT TAG v1_release;
SELECT * FROM catalog.schema.my_table AT SNAPSHOT '1234567890';
SELECT * FROM catalog.schema.my_table AT TIMESTAMP '2024-06-15 12:00:00';
Reflections (Query Acceleration)
ALTER TABLE catalog.schema.my_table
CREATE RAW REFLECTION my_raw_ref
USING DISPLAY (col1, col2, col3);
ALTER TABLE catalog.schema.my_table
CREATE AGGREGATE REFLECTION my_agg_ref
USING DIMENSIONS (region) MEASURES (amount (SUM, COUNT));
ALTER TABLE catalog.schema.my_table DROP REFLECTION my_raw_ref;
RBAC — Grants & Privileges
GRANT SELECT ON catalog.schema.my_table TO ROLE analysts;
GRANT ALL ON FOLDER "Analytics" TO ROLE data_engineers;
REVOKE SELECT ON catalog.schema.my_table FROM ROLE analysts;
CREATE ROLE data_viewers;
GRANT ROLE data_viewers TO USER "john@example.com";
User-Defined Functions (UDFs)
CREATE FUNCTION double_it(x INT)
RETURNS INT
RETURN SELECT x * 2;
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 FUNCTION double_it;
Row-Access & Column-Masking Policies
CREATE FUNCTION region_filter(region_col VARCHAR)
RETURNS BOOLEAN
RETURN SELECT region_col = QUERY_USER();
ALTER TABLE catalog.schema.my_table
SET ROW ACCESS POLICY region_filter(region);
CREATE FUNCTION mask_email(email_col VARCHAR)
RETURNS VARCHAR
RETURN SELECT CASE WHEN QUERY_USER() = 'admin' THEN email_col ELSE '***' END;
ALTER TABLE catalog.schema.my_table
SET COLUMN MASKING POLICY mask_email(email);
PIPE (Continuous Ingestion)
CREATE PIPE my_pipe AS
COPY INTO catalog.schema.target
FROM '@my_s3_source/streaming/'
FILE_FORMAT 'json';
ALTER PIPE my_pipe DEDUPE_LOOKBACK_PERIOD 7;
DESCRIBE PIPE my_pipe;
DROP PIPE my_pipe;
Utility
USE catalog.schema;
SET QUEUE "high_priority";
WITH recent AS (
SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT customer_id, SUM(amount) FROM recent GROUP BY customer_id;
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.
