dbt

dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.

dbt is the T in ELT. Organize, cleanse, denormalize, filter, rename, and pre-aggregate the raw data in your warehouse so that it's ready for analysis.

dbt-singlestore

dbt-singlestore enables dbt to work with SingleStore. For more information on using dbt with SingleStore, consult the docs.

Installation

dbt-singlestore is available on PyPI. To install the latest version via pip, run:

pip install dbt-singlestore

Configuring your profile

A sample profile can be found in sample_profiles.yml. It is a standard profile for SQL data sources in dbt. In SingleStore, like in MySQL, database and schema denote the same concept. In dbt, and also in a number of DBMS (e.g. Postgres) schema has a different meaning (a namespace within a database). Therefore, schema must be specified in dbt profile, but it won't affect database objects by default. However, it can be used as a prefix for table names, as outlined in dbt docs for SingleStore profile.

Supported Features

• Custom schemas can be defined in a project models, but they can only serve as a modification to the prefix of the corresponding table names.

• SSL connection would be automatically established by dbt-singlestore for SingleStore users created with 'Require SSL' flag.

SingleStore-specific table options

Reference tables

SingleStore supports REFERENCE tables, which are replicated across the cluster and are useful for small/dimension tables that are frequently joined.

To create a REFERENCE table from a dbt model, set reference=true on a table materialization:

{{
    config(
        materialized='table',
        reference=true,
    )
}}

select ...

When reference=true (default false), the adapter generates CREATE REFERENCE TABLE ... rather than a regular CREATE TABLE ...

Rowstore reference tables

If you want a rowstore reference table, set storage_type='rowstore':

{{
    config(
        materialized='table',
        reference=true,
        storage_type='rowstore',
    )
}}

select ...

This maps to CREATE ROWSTORE REFERENCE TABLE ...

Restrictions / validation

To match SingleStore semantics, dbt-singlestore enforces:

• No shard_key with reference=true — reference tables don’t use sharding; compilation fails if both are set.

• No temporary=true with reference=true — SingleStore does not support temporary reference tables; compilation fails if both are set.

Testing and supported versions

Default dbt tests and jaffle_shop project are used to check the adapter functionality. Development overview has a section "Run tests" which contains instructions on running the tests. Currently, the tests have been successfully run for the following product versions:

To use this adapter, SingleStore must be upgraded to the version 8.5 or newer.

Contributors

We thank Doug Beatty who build an adapter for mysql which has been used to build an initial version of this adapter.

Contribute

• Want to help us build dbt-singlestore? Check out the Contributing Guide.