Dremio

Dremio

Dremio is a data lakehouse platform that provides a unified SQL analytics layer over data lakes, object storage (S3, ADLS, GCS), and databases. It enables direct querying without copying data, using Apache Arrow-based acceleration.

Cloud vs self-hosted

Dremio Cloud scopes work under projects—containers for catalog and SQL. The connection picks the project context; accounts with several projects may default to the first unless tools expose selection.

Self-hosted omits the project layer: catalog and SQL map directly to your instance. The bundled connection tells the tools which deployment style to use.

Data Model

• Projects (Cloud only): Top-level organizational unit. Each Cloud account has one or more projects.
• Catalog entries: The top-level objects within a project (or at the root for self-hosted). These include:
  • Sources: Connections to external data (S3 buckets, databases, file systems)
  • Spaces: Logical grouping areas for organizing datasets and views
  • Folders: Subdirectories within spaces or sources for hierarchical organization
  • Datasets: Tables, views, or files that can be queried via SQL
• Each catalog entry has an id, path (dot-separated hierarchy), type (CONTAINER or DATASET), and containerType (SOURCE, SPACE, FOLDER, or HOME).

Typical Workflows

1. Explore available data: List top-level catalog entries to see what sources, spaces, and folders are available.
2. Navigate deeper: Retrieve a specific catalog entry by ID or path to see its children and metadata.
3. Query data: Execute SQL queries against any dataset. Use the context parameter for unqualified table names, or use fully-qualified paths (e.g. SELECT * FROM mySource.mySchema.myTable).
4. Inspect results: SQL results include schema (column names and types) and data rows.

SQL Capabilities

Dremio supports full ANSI SQL with extensions for lakehouse operations:

• SELECT queries with JOINs, aggregations, window functions, CTEs
• DDL: CREATE TABLE, CREATE VIEW, ALTER, DROP
• DML: INSERT, MERGE, DELETE, UPDATE (on supported formats like Iceberg)
• Reflection management for query acceleration
• Nessie-based versioning (BRANCH, TAG, COMMIT references)

SQL runs asynchronously—jobs move through submitted → completed (or failed) before rows are materialized. Large result sets return in chunks (bounded row windows).

Gotchas

• Instance: Cloud vs self-hosted use different path roots; the workspace connection must match your deployment or discovery fails.
• Multi-project Cloud: When several projects exist, behavior may pin to a default project unless a tool asks for one explicitly.
• Network: Self-hosted clusters must be reachable from the environment executing the tools.
• Wide results: Pull large scans in sequential chunks rather than assuming a single mega-response.
• Catalog paths: Dot-separated segments; special characters in path segments need quoting per Dremio’s path rules.