Quick tip: Using dbt with SingleStoreDB
Tags: singlestoredb, dbt
Published: 2022-10-03 15:37:08

## Abstract
This short article will show how to set up [dbt](https://www.getdbt.com/) for use with SingleStoreDB. We'll also quickly apply a [dbt example](https://github.com/dbt-labs/jaffle_shop) to test the [SingleStore dbt adapter](https://github.com/memsql/dbt-singlestore).

## Install dbt
First, we'll install `dbt-core`, as follows:

```shell
pip install dbt-core==1.1
```

It may be necessary to use `sudo` if there are insufficient privileges to complete the installation.

Next, we'll install `dbt-singlestore`, as follows:

```shell
pip install dbt-singlestore
```

We can now check the installation:

```shell
dbt --version
```

The output should be similar to the following:

```
Core:
  - installed: 1.1.0
  - latest:    1.2.1 - Update available!

Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - singlestore: 1.1.2 - Up to date!
```

We are ready to work with dbt.

## Create a SingleStoreDB Cloud account

To use dbt with SingleStoreDB Cloud, we'll first create a free account from the [portal](https://portal.singlestore.com/). After we have logged in to the portal, we need to check that we can see the option to **Create a new workspace group** in the left-hand navigation pane, as shown in Figure 1.

![Figure 1. Create a new workspace group.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7xi4iq04dmglu9yvwi0y.png)

<center>Figure 1. Create a new workspace group.</center>

In the centre of the webpage, we should also see the option to **Create Workspace**, as shown in Figure 2.

![Figure 2. Create Workspace.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7vy486ykdsnbzrfuxq6a.png)

<center>Figure 2. Create Workspace.</center>

We'll click the **Create Workspace** button. On the next page, we'll be presented with three options:

1. **Workspace Group Name**. We'll call our group **dbt Demo Group**.
2. **Cloud Provider**. We have the option to use AWS, GCP or Azure. In this article, we'll use **AWS**.
3. **Region**. A drop-down menu provides a list of supported regions. We can choose a region or use the default.

At the bottom of the webpage, we'll click **Next**.

On the next page, we'll be presented with three options:

1. **Workspace Details**. We'll call our workspace **dbt-demo**.
2. **Size**. S-00 will be fine for initial testing.
3. **Confirm Workspace Group**. We should check that the **dbt Demo Group** is selected in the drop-down menu.

At the bottom of the page, we'll click **Create Workspace**.

After a short time, the Workspace Group should be successfully created and available.

Under the Overview tab (Figure 3),

![Figure 3. Overview.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cndb84302ngg8czjw8t9.png)

<center>Figure 3. Overview.</center>

if we scroll down, we'll see our **dbt-demo** workspace. Using the **Connect** pulldown, we'll select **Connect Directly** to obtain the information we need to connect to SingleStoreDB Cloud using various clients. We'll make a note of the host.

In Figure 3, we can also see **Access** and **Firewall** tabs. We'll change these as follows:

- **Access**. We'll create a password using the **Generate Strong Password** button and save the password in a safe place.
- **Firewall**. For initial setup and testing, we'll allow **Access from anywhere**. We can change this later.

From the left-hand navigation pane, we'll select the **SQL Editor** and create a new database, as follows:

```sql
CREATE DATABASE dbt_demo;
```

## Create SingleStoreDB dbt profile
We'll now create a `profiles.yml` file and save it in the `~/.dbt` directory. Here are the details we need:

```yml
jaffle_shop:
  outputs:
    dev:
      type: singlestore
      host: <TO DO>
      port: 3306
      user: admin
      password: <TO DO>
      database: dbt_demo
      schema: dbt_demo
      threads: 1
  target: dev
```

We'll replace the `<TO DO>` for **host** and **password** with the values from our SingleStoreDB Cloud account.

## Testing the dbt project

First, we'll clone the following repository:

```shell
git clone https://github.com/dbt-labs/jaffle_shop
```
We'll now change to `jaffle_Shop`:

```shell
cd jaffle_shop
```

Next, we'll check our profile, as follows:

```shell
dbt debug
```

The output should be:

```
All checks passed!
```

We'll now run the following commands, in the order shown, one by one:

```shell
dbt seed

dbt run

dbt test

dbt docs generate

dbt docs serve
```

Each command should successfully complete.

## Summary

Through a quick example, we have seen how dbt can be used with SingleStoreDB. If you are already a dbt professional user, then using your dbt skills with SingleStoreDB will be a breeze.