Skip to main content

SQL in Trino

Writing SQL in a Trino-Powered Environment​

Promethium lets you query data from multiple platforms — like Snowflake, Databricks, SQL Server, and more — using one SQL engine: Trino(./trino-intro).

This guide will help you write SQL that works well in this distributed, cross-platform environment.

Key Concepts​

1. Fully Qualified Table Names​

Because Trino connects to many platforms at once, you must refer to tables using their full path: catalog.schema.table

For example:

SELECT * FROM snowflake.sales.customers;
SELECT * FROM databricks.analytics.transactions;
ComponentMeaning
catalogThe name of the connected data platform (e.g. snowflake, databricks, postgresql)
schemaA schema / namespace within the catalog
tableThe table or view name

2. Cross-Platform Joins​

Trino can join data across completely different systems:

SELECT a.customer_id, a.name, b.amount
FROM snowflake.sales.customers a
JOIN databricks.transactions b
  ON a.customer_id = b.customer_id
WHERE b.amount > 100;

This is incredibly powerful — but keep in mind:

  • Performance depends on how much data needs to move across systems
  • Applying filters (WHERE, LIMIT) early helps performance
  • Not all functions are available or supported across every platform. Available functions and operators are documented by Trino.

3. Data Types and Compatibility​

Each data source has its own data types (e.g., VARCHAR, NUMBER, TIMESTAMP), but Trino maps them to a common set.

Some tips:

  • Use CAST() when working across systems with different formats
  • Be aware of timestamp and date formatting differences
  • Avoid platform-specific functions unless you’re only querying one source

💡 Mantra will automatically cater for a number of these nuances.

4. SQL Syntax Tips​

Trino follows ANSI SQL, so most standard SQL will work out of the box.

Here are a few supported features:

  • SELECT, JOIN, GROUP BY, ORDER BY, LIMIT
  • Common aggregate functions: SUM(), AVG(), COUNT(), MAX()
  • Window functions: ROW_NUMBER(), RANK(), LAG(), etc.
  • CTEs (Common Table Expressions):
WITH filtered AS (
  SELECT * FROM snowflake.sales.customers
  WHERE country = 'US'
)
SELECT COUNT(*) FROM filtered;

❗ Trino does not support platform-specific SQL dialects like T-SQL, PL/pgSQL, or stored procedures.

4. Use Aliases for Readability​

When working across catalogs or complex joins, use aliases to simplify queries:

SELECT a.id, b.date
FROM postgres.hr.employees a
JOIN snowflake.attendance.checkins b
  ON a.id = b.employee_id

5. Things to Avoid​

IssueWhy It Matters
Using non-standard SQL syntaxTrino supports ANSI SQL, not vendor-specific SQL
Referencing tables without catalogTrino needs fully qualified paths like catalog.schema.table
Complex joins without filtersCan slow down queries across large datasets and platforms
Unsupported functionsSome functions from Snowflake or Databricks may not work