Dialect System¶

Summary¶

This chapter explains the DialectSpec system — a registry-driven mechanism for handling backend-specific connection construction and operation routing. It covers the DialectSpec overview, the dialect registry and its name-key lookup, connection builders that produce backend-specific connection parameters, operation hooks for customizing behavior, and per-dialect configuration. Concrete dialect implementations for SQLite, DuckDB, PostgreSQL, and Snowflake demonstrate the extensibility of the system.

DialectSpec Overview¶

The DialectSpec is a frozen dataclass that encapsulates everything mountainash-data needs to know about a specific database dialect. Rather than scattering dialect-specific logic across dozens of subclasses (the library's original approach involved 13 separate connection class files), the DialectSpec system consolidates all per-dialect information into data-driven configuration objects stored in a single registry.

Each DialectSpec captures five categories of information about a dialect: its Ibis backend name, the connection mode it uses, the URI scheme for connection strings, the callable that builds connections, and optional capability hooks for dialect-specific operations like index management.

@dataclass(frozen=True)
class DialectSpec:
    """Per-dialect configuration and capability hooks."""
    ibis_backend_name: str
    connection_mode: str
    connection_string_scheme: str
    connection_builder: t.Optional[ConnectionBuilder] = None
    get_index_exists_sql: t.Optional[GetIndexExistsSql] = None
    get_list_indexes_sql: t.Optional[GetListIndexesSql] = None
    extras: t.Mapping[str, t.Any] = field(default_factory=dict)

The frozen nature of DialectSpec means that once created, a dialect's configuration cannot be modified at runtime. This immutability ensures that dialect behavior remains predictable and thread-safe throughout the application lifecycle.

Diagram: DialectSpec Anatomy¶

DialectSpec Anatomy

Type: infographic sim-id: dialectspec-anatomy
Library: vis-network
Status: Specified

An interactive card visualization showing a DialectSpec instance as a structured form with labeled fields. Each field is a clickable region that expands to show its type, purpose, and an example value. The card initially shows the DuckDB DialectSpec with ibis_backend_name="duckdb", connection_mode="connection_string", connection_string_scheme="duckdb://", and populated connection_builder and index hooks. A dropdown at the top allows switching between different dialect cards (SQLite, PostgreSQL, Snowflake, BigQuery) to compare their configurations. Learning objective: Remember the components of a DialectSpec (Bloom: Remember). Controls: dropdown to switch dialects, click fields for detail expansion. Colors: Teal for required fields, MediumPurple for optional hooks, Gold for extras.

Dialect Registry¶

The dialect registry is the DIALECTS dictionary defined in backends.ibis.dialects._registry. It maps string keys (dialect names) to DialectSpec instances, providing a single lookup point for all dialect-related configuration. When IbisBackend.__init__() receives a dialect name, it looks up the corresponding DialectSpec in this registry.

The registry currently contains 12 entries covering the major SQL backends supported through Ibis.

DIALECTS: dict[str, DialectSpec] = {
    "sqlite":     DialectSpec(ibis_backend_name="sqlite",     ...),
    "duckdb":     DialectSpec(ibis_backend_name="duckdb",     ...),
    "motherduck": DialectSpec(ibis_backend_name="duckdb",     ...),
    "postgres":   DialectSpec(ibis_backend_name="postgres",   ...),
    "mysql":      DialectSpec(ibis_backend_name="mysql",      ...),
    "mssql":      DialectSpec(ibis_backend_name="mssql",      ...),
    "oracle":     DialectSpec(ibis_backend_name="oracle",     ...),
    "snowflake":  DialectSpec(ibis_backend_name="snowflake",  ...),
    "bigquery":   DialectSpec(ibis_backend_name="bigquery",   ...),
    "redshift":   DialectSpec(ibis_backend_name="postgres",   ...),
    "trino":      DialectSpec(ibis_backend_name="trino",      ...),
    "pyspark":    DialectSpec(ibis_backend_name="pyspark",    ...),
}

Notice that the dialect name key and the ibis_backend_name are not always the same. For example, "motherduck" maps to ibis_backend_name="duckdb" because MotherDuck uses DuckDB's protocol internally. Similarly, "redshift" maps to ibis_backend_name="postgres" because Redshift uses the PostgreSQL wire protocol.

Adding a new dialect to mountainash-data requires only creating a new DialectSpec entry and a connection builder function. No class hierarchies need to be modified, and no existing code needs to change.

Dialect Name Key¶

The dialect name key is the string identifier used to look up a dialect in the registry. These keys serve as the user-facing API for selecting a backend: when creating an IbisBackend, the dialect parameter must match one of the registered keys.

Keys are designed to be intuitive and match common usage. The choice of "postgres" rather than "postgresql" follows the same convention used by Ibis and most connection string URI schemes.

Dialect Key	Ibis Backend Name	URI Scheme
`"sqlite"`	`"sqlite"`	`sqlite://`
`"duckdb"`	`"duckdb"`	`duckdb://`
`"motherduck"`	`"duckdb"`	`duckdb://md:`
`"postgres"`	`"postgres"`	`postgres://`
`"snowflake"`	`"snowflake"`	`snowflake://`
`"bigquery"`	`"bigquery"`	`bigquery://`
`"redshift"`	`"postgres"`	`postgres://`
`"trino"`	`"trino"`	`trino://`

The registry lookup is case-sensitive. Requesting "PostgreSQL" or "POSTGRES" raises a KeyError, and the error message includes the sorted list of available dialect names to guide the user toward the correct key.

Connection Builder¶

A connection builder is a callable (typically a module-level function) that accepts keyword arguments and returns a live Ibis backend connection object. Each dialect has its own connection builder that handles the dialect-specific logic for constructing connection strings, setting authentication parameters, and invoking the appropriate Ibis connect method.

The connection builder signature is flexible by design. It accepts **config keyword arguments, which are the same kwargs passed to IbisBackend.__init__(). Each builder extracts the parameters it needs and ignores the rest, forwarding any unrecognized kwargs to the underlying Ibis connection.

Connection builders handle three distinct connection modes.

CONNECTION_STRING: The builder constructs a URI-style connection string (e.g., postgres://user:pass@host:port/db) and passes it to ibis.connect().
KWARGS: The builder passes individual keyword arguments directly to a backend-specific connect method (used by BigQuery).
HYBRID: The builder constructs a connection string and also passes additional keyword arguments alongside it (used by Snowflake and Trino).

# CONNECTION_STRING mode (PostgreSQL)
def _build_postgres_connection(**config):
    import ibis
    host = config.get("host", "localhost")
    port = config.get("port", 5432)
    conn_str = f"postgres://{user}:{password}@{host}:{port}/{database}"
    return ibis.connect(conn_str)

# KWARGS mode (BigQuery)
def _build_bigquery_connection(**config):
    import ibis.backends.bigquery as bq
    return bq.connect(project_id=config.get("project_id"), ...)

# HYBRID mode (Snowflake)
def _build_snowflake_connection(**config):
    import ibis
    conn_str = f"snowflake://{user}:{password}@{account}"
    return ibis.connect(conn_str, warehouse=warehouse, role=role)

Operation Hooks¶

Operation hooks are optional callable attributes on DialectSpec that provide dialect-specific SQL generation for operations that vary across database engines. Currently, mountainash-data defines two hook types for index management: get_index_exists_sql and get_list_indexes_sql.

These hooks are necessary because there is no standard SQL syntax for querying index metadata. Each database engine stores index information in different system tables and requires different query patterns.

The hook type signatures are defined as type aliases in the registry module:

GetIndexExistsSql = t.Callable[[str, str, t.Optional[str]], str]
# (index_name, table_name, database) -> SQL string

GetListIndexesSql = t.Callable[[str, t.Optional[str]], str]
# (table_name, database) -> SQL string

Dialects that do not support index management (or where it has not yet been implemented) leave these hooks as None. The operations layer checks for None before attempting to use a hook and raises NotImplementedError for unsupported operations.

Diagram: Operation Hooks Dispatch¶

Operation Hooks Dispatch

Type: workflow sim-id: operation-hooks-dispatch
Library: vis-network
Status: Specified

A dispatch flow diagram showing how an index_exists() call resolves to dialect-specific SQL. The flow starts with a "BaseIbisOperations.index_exists()" node, which branches to a "DialectSpec hook lookup" node. From there, three paths diverge: DuckDB path leading to "SELECT COUNT() FROM duckdb_indexes()" SQL, SQLite path leading to "SELECT COUNT() FROM sqlite_master" SQL, and PostgreSQL path leading to a "None (not implemented)" dead end. Each SQL output is shown in a code block node. Clicking a path highlights the full resolution chain. Learning objective: Apply knowledge of hook dispatch to predict SQL output for a given dialect (Bloom: Apply). Controls: click paths to highlight, hover for SQL details. Colors: Teal for entry point, DarkGreen for DuckDB, SteelBlue for SQLite, Crimson for unimplemented.

Per Dialect Configuration¶

Per dialect configuration refers to the practice of storing all dialect-specific behavior within the DialectSpec rather than in separate subclasses. Each entry in the DIALECTS registry is a complete, self-contained configuration for its dialect, including connection mode, URI scheme, connection builder, and capability hooks.

This data-driven approach replaced an earlier architecture where each dialect had its own class file (e.g., sqlite_ibis_connection.py, duckdb_ibis_connection.py, postgres_ibis_connection.py). The refactoring consolidated 13 separate files into a single registry module, reducing code duplication and making it easier to compare dialects side by side.

The extras field on DialectSpec provides a general-purpose extension mechanism. Backend-specific configuration that does not fit the standard fields can be stored here without modifying the DialectSpec dataclass definition. This is useful for experimental features or dialect-specific tuning parameters.

SQLite Dialect¶

The SQLite dialect represents the simplest connection model in mountainash-data. SQLite is an embedded database that stores data in a single file (or in memory), requiring no server process, no authentication, and no network configuration.

The SQLite connection builder accepts a database parameter specifying the file path or the special string ":memory:" for an in-memory database. It delegates to ibis.sqlite.connect() rather than using the generic ibis.connect() with a URI, because Ibis's SQLite backend expects a direct file path.

def _build_sqlite_connection(**config):
    import ibis
    database = config.get("database", ":memory:")
    return ibis.sqlite.connect(database)

SQLite provides both index management hooks (sqlite_get_index_exists_sql and sqlite_get_list_indexes_sql), which query the sqlite_master system table. Note that SQLite's index queries ignore the database parameter because SQLite does not support cross-database queries.

The DialectSpec for SQLite uses connection_mode="connection_string" and connection_string_scheme="sqlite://".

DuckDB Dialect¶

The DuckDB dialect supports both file-based and in-memory databases, similar to SQLite, but with significantly richer analytical capabilities including columnar storage, vectorized execution, and direct Parquet file querying.

The DuckDB connection builder handles two modes: in-memory (when no database path is provided) and file-based (when a path is given). It also supports a read_only parameter that defaults to False for in-memory databases.

def _build_duckdb_connection(**config):
    import ibis
    database = config.get("database", None)
    read_only = config.get("read_only", False)
    if database is None:
        connection_string = "duckdb://"
        kwargs = {"read_only": False}
    else:
        connection_string = f"duckdb://{database}"
        kwargs = {"read_only": read_only}
    return ibis.connect(connection_string, **kwargs)

DuckDB provides index management hooks that query the duckdb_indexes() system function, which returns index metadata including names, definitions, and uniqueness flags.

MotherDuck shares DuckDB's engine

The MotherDuck dialect is a cloud-hosted variant of DuckDB. Its ibis_backend_name is "duckdb" and it uses the same index management SQL. The key difference is the connection string scheme (duckdb://md:) and the addition of a token parameter for authentication.

PostgreSQL Dialect¶

The PostgreSQL dialect represents the server-based connection model. Unlike SQLite and DuckDB, PostgreSQL requires network connectivity, authentication credentials, and typically a host, port, username, password, and database name.

The connection builder supports two input modes: a pre-built connection string or individual parameters that are assembled into a connection string.

def _build_postgres_connection(**config):
    import ibis
    connection_string = config.get("connection_string", None)
    if connection_string is not None:
        return ibis.connect(connection_string)

    host = config.get("host", "localhost")
    port = config.get("port", 5432)
    user = config.get("user", config.get("username", None))
    password = config.get("password", None)
    database = config.get("database", None)

    conn_str = f"postgres://{user}:{password}@{host}:{port}/{database}"
    return ibis.connect(conn_str)

The PostgreSQL dialect does not currently provide index management hooks, as PostgreSQL's pg_indexes system catalog requires different query patterns than the DuckDB family. The Redshift dialect reuses the PostgreSQL connection builder entirely, since Redshift speaks the PostgreSQL wire protocol.

Snowflake Dialect¶

The Snowflake dialect demonstrates the HYBRID connection mode, where both a connection string and additional keyword arguments are required. Snowflake connections require an account identifier, user credentials, and warehouse/role configuration that do not fit cleanly into a URI-only format.

The connection builder constructs a snowflake:// connection string from the core parameters (user, password, account, database, schema) and passes warehouse and role as separate keyword arguments.

def _build_snowflake_connection(**config):
    import ibis
    user = config.get("user", config.get("username", None))
    password = config.get("password", None)
    account = config.get("account", None)
    # ... build connection string ...
    kwargs = {}
    if warehouse:
        kwargs["warehouse"] = warehouse
    if role:
        kwargs["role"] = role
    return ibis.connect(conn_str, **kwargs)

The Snowflake dialect does not provide index management hooks because Snowflake, as a cloud data warehouse, uses automatic micro-partitioning rather than user-defined indexes.

Diagram: Dialect Comparison Matrix¶

Dialect Comparison Matrix

Type: chart sim-id: dialect-comparison-matrix
Library: Chart.js
Status: Specified

An interactive comparison matrix (heatmap style) with dialect names on the Y axis and features on the X axis. Features include: connection_mode (STRING/KWARGS/HYBRID), has_index_hooks (boolean), requires_auth (boolean), supports_in_memory (boolean), and ibis_backend_name. Cells are color-coded: green for supported/available, red for not supported, blue for special handling. Clicking a row highlights that dialect's full configuration. Clicking a column sorts dialects by that feature. A search/filter bar at the top allows filtering to specific dialects. Learning objective: Evaluate which dialects support specific capabilities (Bloom: Evaluate). Controls: click row/column to highlight/sort, search bar for filtering. Colors: DarkGreen for supported, Crimson for unsupported, SteelBlue for special.

Key Takeaways¶

DialectSpec is a frozen dataclass that consolidates all dialect-specific configuration (connection mode, URI scheme, builder, hooks) into a single data-driven object.
The dialect registry (DIALECTS dictionary) maps string keys to DialectSpec instances, enabling lookup-based dialect selection without class hierarchies.
Dialect name keys are the user-facing API for backend selection, designed to match common conventions (e.g., "postgres" not "postgresql").
Connection builders are callables that handle three connection modes: CONNECTION_STRING (URI-based), KWARGS (parameter-based), and HYBRID (both).
Operation hooks provide dialect-specific SQL generation for operations like index management, with None values indicating unsupported capabilities.
The data-driven approach replaced 13 separate dialect class files with a single registry module, reducing duplication and improving maintainability.
SQLite and DuckDB represent embedded/local dialects with index hook support; PostgreSQL represents the server-based model; Snowflake demonstrates the HYBRID connection mode for cloud warehouses.
Adding a new dialect requires only a connection builder function and a DialectSpec entry in the registry.