In this guide, we will walk through how to setup dbt Core in the cloud with Databricks. After you finish this guide, you will have the sample data provided uploaded to Databricks and run your first dbt command in the cloud.

Although the steps in this guide will specifically utilize Databricks, the steps can be modified slightly to work with any database that dbt supports. We also have guides made specifically for BigQuery, Redshift, and Snowflake.

If you would rather watch a video version of this guide, feel free to head over to YouTube. Let's jump right in!

1. From the Databricks homepage, navigate to the left sidebar and select data. This will open up the sidebar to look at the data currently stored in Databricks.
2. Click Create Table. This will take you to the Create New Table page.
3. Under Files, click the box to browse your files and select spi_matches_latest.csv.

4. Click Create Table with UI
5. Select Cluster to preview table. Click Preview Table. This will give you the ability to specify options for the table.

6. Under Table Name, enter stg_football_matches.
7. Under Create in Database, feel free to change to any database you like. We will use the default for simplicity's sake.
8. Under File Type, make sure CSV is selected.
9. Under Column Delimiter, make sure , is in the box.
10. Check the box for First row is header.
11. Check the box for Infer schema.
12. Click Create Table. This will redirect to the table that you just created:

13. Click Data on the left sidebar to open the data sidebar. Click Create Table.
14. Repeat steps 3-12 with the following changes:
    1. Under Files, click the box to browse your files and select spi_global_rankings.csv.
    2. Under Table Name, enter stg_football_rankings.
15. After repeating those steps with the indicated changes, your resulting page should look like this:

dbt Core Part 2 - Setting Up dbt on Github

Fork dbt Setup from GitHub

1. Fork this repository. The repository contains the beginning state of a dbt project.
2. Clone the repository locally on your computer.
3. Open dbt_project.yml in your text editor.

dbt Project File Setup

1. Change the project name to soccer_538.
2. Change the profile to soccer_538.
3. Change model name to soccer_538.
4. Under the soccer_538 model, add a staging and marts folder that are both materialized as views.
5. Save your changes.

Profile Setup

1. Open profiles.yml.
2. Update the file to this and change the schema to use your name and the http path.:

soccer_538:
    target: dev
    outputs:
        dev:
          type: databricks
          schema: dbt_jack_sparrow
          host: "{{ env_var('databricks_host') }}"
          http_path: /sql/your/http/path
          token: "{{ env_var('databricks_token') }}"

3. Create a new file in your root directory of your dbt project called execute_dbt.py.
4. Paste this code block for the content of execute_dbt.py:

import subprocess
import os
import json

dbt_command = os.environ.get('dbt_command', 'dbt run')

subprocess.run(['sh', '-c', dbt_command], check=True)

5. Commit and push your changes to Github.

Now that we have our sample data and dbt processes setup, we need to write our example models for the dbt job to run.

dbt Models

1. Navigate into the models folder in your text editor. There should be a subfolder under models called example. Delete that subfolder and create a new folder called 538_football.
2. Create two subfolders inside 538_football called staging and marts.

3. Inside the staging folder, create a file called stg_football_matches.sql.
4. Paste the following code into that file:
   select * from default.stg_football_matches
5. Inside the staging folder, create a file called stg_football_rankings.sql
6. Paste the following code into that file:
   select * from default.stg_football_rankings
7. In the staging folder, add a file called schema.yml.
8. In this file, paste the following information:

version: 2

models:
  - name: stg_football_matches
    description: Table from 538 that displays football matches and predictions about each match.

  - name: stg_football_rankings
    description: Table from 538 that displays a teams ranking worldwide

9. In the marts folder, create a file called mart_football_information.sql.
10. Paste the following code into that file:

with
  qryMatches as (
    SELECT * FROM {{ ref('stg_football_matches') }} where league = 'Barclays Premier League'
    ),
  qryRankings as (
    SELECT * FROM {{ ref('stg_football_rankings') }} where league = 'Barclays Premier League'
  ),

  qryFinal as (
    select
      qryMatches.season,
      qryMatches.date,
      qryMatches.league,
      qryMatches.team1,
      qryMatches.team2,
      team_one.rank as team1_rank,
      team_two.rank as team2_rank
    from
      qryMatches join
      qryRankings as team_one on
        (qryMatches.team1 = team_one.name) join
      qryRankings as team_two on
        (qryMatches.team2 = team_two.name)
  )

select * from qryFinal

11. In the marts folder, add a file called schema.yml
12. In this file, paste the following:

version: 2

models:
  - name: mart_football_information
    description: Table that displays football matches along with each team's world ranking.

13. Save the changes.
14. Push a commit to Github.

We are ready to move into Shipyard to run our process. First, you will need to create a developer account.

dbt Core Part 3 - Setting Up dbt on Shipyard

Create Developer Shipyard Account

1. Navigate to Shipyard's sign-up page here.

2. Sign up with your email address and organization name.
3. Connect to your Github account by following this guide. After connecting your Github account, you'll be ready to create your first Blueprint.

Creating dbt Core Blueprint

1. On the sidebar of Shipyard's website, click Blueprints.
2. Click Add Blueprint on the top right of your page.
3. Select Python.
4. Under Blueprint variables, click Add Variable.
5. Under display name, enter dbt CLI Command.
6. Under reference name, enter dbt_command.
7. Under default value, enter dbt run.
8. Click the check box for required
9. Under placeholder, enter Enter the command for dbt.
10. Click Next
11. Click Git.

12. Select the repository where your dbt files sit.
13. Click the source that you want the files pulled from. Generally main or master.
14. Under file to run, enter execute_dbt.py.
15. Under Git Clone Location, select the option for Unpack into Current Working Directory.
16. Click Next Step on the bottom right of the screen.
17. Next to Environment Variable, click the plus sign to add an environment variable.

Add Environment Variables

The environment variables that need to be added will vary based on the cloud database that you use. Please click your database below to continue:

Python Packages

1. Click the plus sign next to Python Packages.
2. In the Name field, enter dbt-databicks. In the version field, enter ==1.0.0.
3. Click Next.

Blueprint Settings

1. Under Blueprint Name, enter dbt - Execute CLI Command.
2. Under synopsis, enter This Blueprint runs a dbt core command.
3. Click Save.
4. In the top right of your screen, click Use this Blueprint. This will take you over to the Fleet Builder and prompt you to select a project.

Build dbt Core Fleet

1. On the Select a Project prompt, click the drop down menu to expand it and select Create a New Project.
2. Under project name, enter dbt Core Testing.
3. Under timezone, enter your timezone.

4. Click Create Project.
5. Select dbt Core Testing and click Select Project. This will create a new Fleet in the project. The Fleet Builder will now visible with one Vessel located inside of the Fleet.
6. Click on the Vessel in the Fleet Builder and you will see the settings for the Vessel pop up on the left of your screen.

7. Under Vessel Name, enter dbt Core CLI Command.
8. Under dbt CLI Command, enter dbt debug.
9. Click the gear on the sidebar to open Fleet Settings.

10. Under Fleet Name, enter dbt Core.
11. Click Save & Finish on the bottom right of your screen.
12. This should take you to a page showing that your Fleet was created successfully.

13. Click Run Your Fleet. This will take you over to the Fleet Log.

14. You can click on the bar to get the output from your run.

If you scroll to the top of the output, you will see that the environment variables that were put in during the Blueprint creation process are hidden from the user.

If dbt debug succeeds, we are ready to move into part three of the guide. If it fails, please go back to the steps above and make sure everything is setup correctly. Feel free to send an Intercom message to us at anytime using the widget on the bottom right of the Shipyard screen.

In the meantime, please consider subscribing to our weekly newsletter, "All Hands on Data." You'll get insights, POVs, and inside knowledge piped directly into your inbox. See you there!