Supported SQL Operations

Comprehensive reference of DDL and DML statements supported for data transformations

Overview

This page documents write operations supported via the Data Transformations connector. These operations enable you to create, manage, and manipulate tables and views in your private namespace on Dune.

Both read and write operations use the same Trino endpoint (trino.api.dune.com), but write operations require the transformations=true session property.

Standard read operations (SELECT, JOIN, WHERE, etc.) are documented in the Query Engine section and work without any special session properties.

While dbt is the recommended tool for data transformations, these SQL operations work with any Trino-compatible client including Hex, Jupyter notebooks, SQLMesh, or direct Trino connections.

All write operations require the transformations=true session property to be set. Without this property, DDL and DML statements will be rejected.

Prerequisites

• Dune Enterprise account with Data Transformations enabled
• Valid Dune API key with write permissions
• Queries must target the dune catalog

DDL Operations

Schema Management

Create and manage schemas within your namespace.

CREATE SCHEMA

CREATE SCHEMA [IF NOT EXISTS] schema_name

Create a new schema in your team namespace.

Examples:

CREATE SCHEMA IF NOT EXISTS your_team;
CREATE SCHEMA IF NOT EXISTS your_team__tmp_dev;

Access control: You can only create schemas matching your team handle or temporary schemas with the pattern {team}__tmp_*.

SHOW SCHEMAS

SHOW SCHEMAS [FROM catalog]

List all schemas you have access to.

Example:

SHOW SCHEMAS FROM dune;

Returns: All public schemas plus your own schemas.

Query Information Schema

SELECT ... FROM dune.information_schema.schemata

Get detailed schema metadata using information schema queries.

Example:

SELECT schema_name, catalog_name
FROM dune.information_schema.schemata
WHERE schema_name LIKE 'your_team%'
ORDER BY schema_name;

Table Management

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name (column_definitions)

Create a new table with explicit column definitions.

Example:

CREATE TABLE IF NOT EXISTS your_team.users (
    user_id BIGINT,
    address VARBINARY,
    created_at TIMESTAMP,
    total_volume DOUBLE
);

Supported column types: All DuneSQL data types including BIGINT, DOUBLE, VARCHAR, VARBINARY, TIMESTAMP, BOOLEAN, ARRAY, MAP, and custom types like UINT256 and INT256.

To control whether a table is publicly queryable, see Table Visibility.

CREATE TABLE AS SELECT (CTAS)

CREATE TABLE table_name AS query

Create a table from a query result.

Example:

CREATE TABLE your_team.daily_volumes AS
SELECT
    DATE_TRUNC('day', block_time) as day,
    COUNT(*) as tx_count,
    SUM(value) as total_volume
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '7' DAY
GROUP BY 1;

CREATE OR REPLACE TABLE

CREATE OR REPLACE TABLE table_name AS query

Create or replace an existing table atomically.

Example:

CREATE OR REPLACE TABLE your_team.latest_prices AS
SELECT
    contract_address,
    symbol,
    price_usd,
    MAX(minute) as last_updated
FROM prices.usd
WHERE minute >= NOW() - INTERVAL '1' HOUR
GROUP BY 1, 2, 3;

Note: This completely replaces the table. For incremental updates, use MERGE instead. To control table visibility (public/private), see Table Visibility.

DROP TABLE

DROP TABLE [IF EXISTS] table_name

Delete a table and its data.

Examples:

DROP TABLE IF EXISTS your_team.old_analysis;
DROP TABLE IF EXISTS your_team__tmp_pr123.test_model;

Access control: You can only drop tables in your own namespaces.

SHOW TABLES

SHOW TABLES [FROM schema]

List all tables in a schema.

Examples:

SHOW TABLES FROM your_team;
SHOW TABLES FROM your_team__tmp_;

SHOW CREATE TABLE

SHOW CREATE TABLE table_name

View the DDL for a table.

Example:

SHOW CREATE TABLE your_team.users;

Returns: The full CREATE TABLE statement that can recreate the table, including any table properties like visibility settings.

Table Visibility

ALTER TABLE ... SET PROPERTIES and all other write statements must be run through a Trino-compatible client (CLI, dbt, Hex, Python trino package, etc.) connected to trino.api.dune.com with transformations=true enabled at connection time. They cannot be executed from the Dune web app — attempting to do so returns Access Denied: Cannot set system session property transformations. See Running write statements via the Trino CLI for a working example.

Tables created via Data Transformations are private by default — only your team can query them. You can make a table publicly queryable by anyone on Dune by setting the dune.public property.

Table visibility only applies to tables, not views.

Set visibility when creating a table:

-- Create a public table with explicit columns
CREATE TABLE your_team.public_metrics (
    metric_name VARCHAR,
    metric_value DOUBLE,
    updated_at TIMESTAMP
)
WITH (
    extra_properties = map_from_entries(ARRAY[
        ROW('dune.public', 'true')
    ])
);

-- Create a public table from a query
CREATE TABLE your_team.public_metrics
WITH (
    extra_properties = map_from_entries(ARRAY[
        ROW('dune.public', 'true')
    ])
) AS
SELECT
    DATE_TRUNC('day', block_time) as day,
    COUNT(*) as tx_count
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '7' DAY
GROUP BY 1;

Set visibility with CREATE OR REPLACE TABLE:

CREATE OR REPLACE TABLE your_team.public_metrics
WITH (
    extra_properties = map_from_entries(ARRAY[
        ROW('dune.public', 'true')
    ])
) AS
SELECT
    DATE_TRUNC('day', block_time) as day,
    COUNT(*) as tx_count
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '7' DAY
GROUP BY 1;

When using CREATE OR REPLACE TABLE:

• If the table already exists and you omit dune.public, the existing visibility setting is preserved.
• If the table does not exist and you omit dune.public, it defaults to private.

Change visibility on an existing table:

-- Make a table public
ALTER TABLE dune.your_team.my_table
SET PROPERTIES extra_properties = map_from_entries(ARRAY[
    ROW('dune.public', 'true')
]);

-- Make a table private again
ALTER TABLE dune.your_team.my_table
SET PROPERTIES extra_properties = map_from_entries(ARRAY[
    ROW('dune.public', 'false')
]);

Check the current visibility setting:

-- Method 1: View the full CREATE TABLE statement
SHOW CREATE TABLE dune.your_team.my_table;

-- Method 2: Query table properties directly
SELECT * FROM dune.your_team."my_table$properties";

Public tables are queryable by anyone on Dune and appear in the data explorer under community data.

View Management

CREATE OR REPLACE VIEW

CREATE OR REPLACE VIEW view_name AS query

Create or update a view definition.

Example:

CREATE OR REPLACE VIEW your_team.active_traders AS
SELECT
    trader_address,
    COUNT(*) as trade_count,
    SUM(amount_usd) as total_volume
FROM your_team.dex_trades
WHERE block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1
HAVING COUNT(*) >= 10;

Benefits: Views don’t store data, so they don’t consume storage credits. They’re computed on-the-fly when queried.

DROP VIEW

DROP VIEW [IF EXISTS] view_name

Delete a view definition.

Example:

DROP VIEW IF EXISTS your_team.old_view;

SHOW CREATE VIEW

SHOW CREATE VIEW view_name

View the SQL definition of a view.

Example:

SHOW CREATE VIEW your_team.active_traders;

Column Operations

SHOW COLUMNS

SHOW COLUMNS FROM table_name

List all columns in a table.

Example:

SHOW COLUMNS FROM your_team.users;

DESCRIBE

DESCRIBE table_name

Get detailed column information including names, types, and metadata.

Example:

DESCRIBE your_team.users;

Returns: Column names, data types, nullability, and additional metadata.

Query Information Schema for Columns

SELECT ... FROM dune.information_schema.columns

Get detailed column metadata using information schema queries.

Example:

SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    is_nullable
FROM dune.information_schema.columns
WHERE table_schema = 'your_team'
    AND table_name = 'users'
ORDER BY ordinal_position;

DML Operations

Insert Operations

INSERT INTO with VALUES

INSERT INTO table_name (columns) VALUES (values)

Insert specific rows into a table.

Example:

INSERT INTO your_team.config (key, value, updated_at)
VALUES
    ('max_slippage', '0.5', NOW()),
    ('min_liquidity', '1000', NOW());

INSERT INTO with SELECT

INSERT INTO table_name SELECT ...

Insert query results into a table.

Example:

INSERT INTO your_team.daily_aggregates
SELECT
    DATE_TRUNC('day', block_time) as day,
    protocol,
    COUNT(*) as transaction_count,
    SUM(amount_usd) as volume_usd
FROM your_team.decoded_events
WHERE block_time >= CURRENT_DATE
GROUP BY 1, 2;

Use case: Append-only incremental models, historical snapshots, event logging.

Update Operations

MERGE INTO

MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...

Upsert data - update existing rows and insert new ones in a single operation.

Example:

MERGE INTO your_team.user_stats t
USING (
    SELECT
        user_address,
        COUNT(*) as trade_count,
        SUM(volume_usd) as total_volume,
        MAX(block_time) as last_trade
    FROM ethereum.dex_trades
    WHERE block_time >= CURRENT_DATE - INTERVAL '1' DAY
    GROUP BY 1
) s
ON t.user_address = s.user_address
WHEN MATCHED THEN
    UPDATE SET
        trade_count = t.trade_count + s.trade_count,
        total_volume = t.total_volume + s.total_volume,
        last_trade = GREATEST(t.last_trade, s.last_trade)
WHEN NOT MATCHED THEN
    INSERT (user_address, trade_count, total_volume, last_trade)
    VALUES (s.user_address, s.trade_count, s.total_volume, s.last_trade);

Use case: Incremental models that update existing records and insert new ones (dbt merge strategy).

Performance: More efficient than delete+insert for partial updates of large tables.

Delete Operations

DELETE FROM

DELETE FROM table_name WHERE condition

Delete rows matching a condition.

Example:

DELETE FROM your_team.staging_data
WHERE created_at < NOW() - INTERVAL '7' DAY;

TRUNCATE TABLE

TRUNCATE TABLE table_name

Remove all rows from a table efficiently.

Example:

TRUNCATE TABLE your_team.temp_calculations;

Performance: TRUNCATE is faster than DELETE FROM table for removing all rows as it doesn’t scan individual rows.

Maintenance Operations

These operations optimize table performance and manage metadata. They consume credits based on compute and data written.

OPTIMIZE

ALTER TABLE table_name EXECUTE OPTIMIZE

Compact small files and optimize table layout for better query performance.

Example:

ALTER TABLE your_team.large_dataset EXECUTE OPTIMIZE;

When to use:

• After many small writes
• Before running complex queries
• Periodically for frequently-updated tables

Effect: Combines small data files into larger ones, improving query performance by reducing file overhead.

VACUUM

ALTER TABLE table_name EXECUTE VACUUM

Remove old data files and reclaim storage space.

Example:

ALTER TABLE your_team.historical_data EXECUTE VACUUM;

When to use:

• After major updates or deletes
• To reclaim storage space
• Based on your retention policies

Effect: Removes old file versions that are no longer needed, reducing storage costs.

VACUUM is most effective when run after OPTIMIZE, as it can remove the redundant small files replaced during compaction.

Automating Maintenance in dbt

Use dbt post-hooks to automate maintenance:

models:
  your_project:
    large_model:
      +post-hook:
        - "ALTER TABLE {{ this }} EXECUTE OPTIMIZE"
        - "ALTER TABLE {{ this }} EXECUTE VACUUM"

Datashare Operations

These table procedures register a table for Datashare sync and manage its lifecycle.

These operations require Datashare to be enabled and configured for your team. They are intended for tables in your production namespace, not temporary dev or PR schemas.

Register Or Trigger A Datashare Sync

ALTER TABLE table_name EXECUTE datashare(...)

Register a table for Datashare and trigger a sync to your configured target.

Example:

ALTER TABLE dune.your_team.daily_metrics EXECUTE datashare(
    time_column => 'block_date',
    unique_key_columns => ARRAY['block_number', 'block_date'],
    time_start => CAST(current_date - interval '1' day AS VARCHAR),
    time_end => CAST(current_date + interval '1' day AS VARCHAR),
    full_refresh => false
);

Arguments:

• time_column: Column used to define the sync window
• unique_key_columns: Columns that uniquely identify a row
• time_start: SQL expression for the start of the sync window
• time_end: SQL expression for the end of the sync window
• full_refresh: Whether the sync should rebuild the full exported table

Keep the window expressions aligned with the time_column granularity. For example, date columns should use date-based window expressions.

Remove A Table From Datashare

ALTER TABLE table_name EXECUTE delete_datashare

Stop syncing a table to Datashare.

Example:

ALTER TABLE dune.your_team.daily_metrics EXECUTE delete_datashare;

Monitor Datashare State

Use the Datashare system tables to inspect registrations and sync history:

SELECT *
FROM dune.datashare.table_syncs
WHERE source_schema = 'your_team';

SELECT *
FROM dune.datashare.table_sync_runs
WHERE source_schema = 'your_team'
ORDER BY created_at DESC;

For dbt-specific setup, see dbt to Datashare.

Information Schema Queries

Query metadata about your tables and schemas.

List All Tables

SELECT
    table_schema,
    table_name,
    table_type
FROM dune.information_schema.tables
WHERE table_schema = 'your_team'
ORDER BY table_name;

Find Tables by Pattern

SELECT
    table_schema,
    table_name
FROM dune.information_schema.tables
WHERE table_schema LIKE 'your_team%'
    AND table_name LIKE 'user_%'
ORDER BY table_schema, table_name;

Get Table Column Details

SELECT
    column_name,
    data_type,
    is_nullable,
    ordinal_position
FROM dune.information_schema.columns
WHERE table_schema = 'your_team'
    AND table_name = 'users'
ORDER BY ordinal_position;

Check Table Existence

SELECT COUNT(*) > 0 as table_exists
FROM dune.information_schema.tables
WHERE table_schema = 'your_team'
    AND table_name = 'my_model';

Permissions & Access Control

What You Can Do

Read Permissions

• All public Dune datasets
• Your team’s private uploaded data
• Your transformation tables and views

Write Permissions

• Create schemas: {team_name} and {team_name}__tmp_*
• Create tables/views in your schemas only
• Insert, update, delete data in your tables only
• Drop your own tables and views
• Set table visibility (public/private) on tables you own

What You Cannot Do

• Write to public schemas (e.g., ethereum, prices, etc.)
• Write to other teams’ namespaces
• Modify tables you don’t own

Schema Naming Rules

Valid namespace patterns:

• your_team - Production schema
• your_team__tmp_ - Default development schema
• your_team__tmp_alice - Personal development schema
• your_team__tmp_pr123 - CI/CD schema for PR #123

Invalid patterns:

• eth__tmp_ - Cannot use reserved prefixes
• another_team - Cannot use other team names
• public__tmp_ - Cannot use system schemas

Session Properties

Required Property

SET SESSION transformations = true;

Purpose: Enables write operations. Without this, all DDL/DML statements will be rejected.

How to set:

• dbt: Add to profiles.yml under session_properties
• Python: Set in connection parameters
• Trino CLI: Pass --session=transformations=true at connection time. See Running write statements via the Trino CLI.
• Other Trino clients (Hex, Python trino, DBeaver, etc.): Set transformations=true in the client’s session properties at connection time.

Running write statements via the Trino CLI

Write statements (DDL, DML, ALTER TABLE ... SET PROPERTIES) cannot be executed from the Dune web app. They must be run through a Trino-compatible client with the transformations=true session property set at connection time.

Here is a working example using the Trino CLI to make a table public:

# Set the API key as an environment variable (do not pass it inline)
export TRINO_PASSWORD=<your-dune-api-key>

trino \
  --server https://trino.api.dune.com \
  --user dune \
  --password \
  --session=transformations=true \
  --execute="ALTER TABLE dune.your_team.your_table SET PROPERTIES extra_properties = map_from_entries(ARRAY[ROW('dune.public','true')])"

The --password flag does not accept the API key as a value. It tells the CLI to prompt for one interactively. To pass the API key non-interactively, set the TRINO_PASSWORD environment variable as shown above.

The --session value must not contain whitespace. Writing --session='transformations=true ' (with a trailing space) will fail with Session property value contains spaces or is not ASCII. Use --session=transformations=true exactly.

For dbt, set the session property in profiles.yml under session_properties — see the dbt Getting Started guide.

Optional Properties

Join Distribution Type

Optimize join performance:

SET SESSION join_distribution_type = 'PARTITIONED';

Options:

• AUTOMATIC (default) - Let Trino choose
• PARTITIONED - Repartition both tables
• BROADCAST - Broadcast smaller table

When to use: For complex joins on large tables.

Common Patterns

Incremental Model Pattern (Merge)

-- Check if target table exists
CREATE TABLE IF NOT EXISTS your_team.user_daily_stats (
    date DATE,
    user_address VARBINARY,
    tx_count BIGINT,
    volume_usd DOUBLE,
    PRIMARY KEY (date, user_address)
);

-- Merge new data
MERGE INTO your_team.user_daily_stats t
USING (
    SELECT
        DATE_TRUNC('day', block_time) as date,
        "from" as user_address,
        COUNT(*) as tx_count,
        SUM(value) as volume_usd
    FROM ethereum.transactions
    WHERE block_time >= CURRENT_DATE - INTERVAL '1' DAY
    GROUP BY 1, 2
) s
ON t.date = s.date AND t.user_address = s.user_address
WHEN MATCHED THEN
    UPDATE SET
        tx_count = s.tx_count,
        volume_usd = s.volume_usd
WHEN NOT MATCHED THEN
    INSERT (date, user_address, tx_count, volume_usd)
    VALUES (s.date, s.user_address, s.tx_count, s.volume_usd);

Delete+Insert Pattern

-- Delete existing data for update period
DELETE FROM your_team.hourly_metrics
WHERE hour >= DATE_TRUNC('hour', NOW() - INTERVAL '24' HOUR);

-- Insert fresh data
INSERT INTO your_team.hourly_metrics
SELECT
    DATE_TRUNC('hour', block_time) as hour,
    protocol,
    COUNT(*) as event_count,
    SUM(amount_usd) as total_volume
FROM your_team.decoded_events
WHERE block_time >= DATE_TRUNC('hour', NOW() - INTERVAL '24' HOUR)
GROUP BY 1, 2;

Append-Only Pattern with Deduplication

-- Insert new records only
INSERT INTO your_team.event_log
SELECT DISTINCT
    evt_tx_hash,
    evt_block_time,
    evt_block_number,
    user_address,
    action_type
FROM ethereum.decoded_events
WHERE evt_block_time >= (
    SELECT COALESCE(MAX(evt_block_time), TIMESTAMP '2020-01-01')
    FROM your_team.event_log
)
AND NOT EXISTS (
    SELECT 1
    FROM your_team.event_log existing
    WHERE existing.evt_tx_hash = ethereum.decoded_events.evt_tx_hash
);

Staging Table Pattern

-- Create staging table
CREATE TABLE your_team__tmp_.staging_new_users AS
SELECT
    user_id,
    address,
    first_seen,
    tx_count
FROM your_source.raw_users
WHERE created_at >= CURRENT_DATE;

-- Validate staging data
SELECT
    COUNT(*) as total_rows,
    COUNT(DISTINCT user_id) as unique_users,
    COUNT(*) FILTER (WHERE address IS NULL) as null_addresses
FROM your_team__tmp_.staging_new_users;

-- Promote to production
INSERT INTO your_team.users
SELECT * FROM your_team__tmp_.staging_new_users;

-- Clean up staging
DROP TABLE your_team__tmp_.staging_new_users;

Best Practices

Use Appropriate Incremental Strategy

• Merge: When you need to update existing rows (e.g., daily aggregations)
• Delete+Insert: When recomputing entire partitions (e.g., hourly metrics)
• Append: For immutable event logs

Optimize Write Operations

• Batch inserts instead of single-row inserts
• Use CTAS for bulk table creation
• Partition large tables by date for efficient updates

Manage Table Lifecycle

• Drop temporary tables in __tmp_ schemas regularly
• Use views for derived data that doesn’t need persistence
• Run OPTIMIZE periodically on frequently-updated tables

Monitor Credit Usage

• Track write operations in large tables
• Use incremental models to minimize data written

Limitations & Considerations

No ALTER TABLE for Schema Changes

You cannot alter table structure (add, rename, or drop columns) directly. Instead, recreate the table:

-- Create new table with desired schema
CREATE TABLE your_team.users_new AS
SELECT
    user_id,
    address,
    created_at,
    -- Add new columns or modify types
    CAST(total_volume AS DECIMAL(38, 2)) as total_volume_precise
FROM your_team.users;

-- Drop old table
DROP TABLE your_team.users;

-- Rename new table (via re-create)
CREATE TABLE your_team.users AS
SELECT * FROM your_team.users_new;

DROP TABLE your_team.users_new;

ALTER TABLE ... SET PROPERTIES is supported for changing table properties like visibility. See Table Visibility.

No SCHEMA DROP

Empty schemas are automatically managed. You cannot explicitly drop schemas.

Transaction Behavior

Each statement is auto-committed. There is no multi-statement transaction support.

Concurrent Writes

Writing to the same table from multiple processes simultaneously may cause conflicts. Use:

• Separate staging tables
• Partitioned writes
• Sequential execution

Troubleshooting

”Access Denied” Errors

Problem: Cannot create table in schema.

Solution: Verify you’re using the correct team namespace and have Data Transformations enabled.

”Session Property Required” or “Cannot set system session property transformations” Error

Problem: Write operation rejected, or SET SESSION transformations = true returns Access Denied.

Solution: Write statements cannot be run from the Dune web app. Connect via a Trino-compatible client (Trino CLI, dbt, Hex, Python trino, etc.) and set transformations=true at connection time, not as a runtime SET SESSION statement. See Running write statements via the Trino CLI for a working example.

Slow MERGE Operations

Problem: MERGE takes too long on large tables.

Solution:

• Add filters to reduce rows matched
• Run OPTIMIZE before MERGE
• Consider delete+insert for full partition updates

Table Not Visible in Dune App

Problem: Created table doesn’t appear in queries.

Solution: Remember to use the dune. catalog prefix:

SELECT * FROM dune.your_team.your_table;

Examples

Complete examples and templates are available in the dune-dbt-template repository.

See the /models/templates/ directory for:

• View model example
• Table model example
• Merge incremental model
• Delete+insert incremental model
• Append incremental model with deduplication

Related Documentation

• dbt Connector
• Trino Connector
• Query Engine Overview
• DuneSQL Functions & Operators
• Rate Limits
• Billing