The focus of this guide is to showcase the ease of using dbt (Data Build Tool) with Hydra Cloud. What is being presented is supplier-related performance metrics to an organization’s procurement officer.
For demonstration, the data and the KPIs were chosen from the generic supplier contract management process shown here.
Synthetic data is being used for this guide, and the metrics are the following.
Configure your Hydra instance and have the connection details ready. Fetch them from your Hydra Cloud connection page as shown below.
Log into your workstation.
Open the terminal and configure and verify dbt-core installation using the following steps.
Navigate to your project directory and initialize your dbt project using the following steps.
On the terminal, navigate to the project directory and review the sub-directories and files.
Using a terminal, navigate to your workstation users directory and locate the hidden .dbt directory.
Navigate inside the .dbt directory and open the profiles.yml in your favorite editor.
Fetch your Hydra Cloud credentials and connection details: https://dashboard.hydra.so/
Under tag outputs add a tag: hydra and change the target tag value to hydra. Input your hydra connection details as values.
Open the terminal and navigate inside the project directory containing the dbt_project.yml file, and execute the dbt debug command as shown below.
The “OK connection ok” response confirms that the dbt-core is able to connect to your Hydra Cloud instance.
Congratulations! You have successfully established the initial connection between dbt and Hydra Cloud.
Open your favorite SQL management tool and connect to the Hydra Cloud. As shown in the workflow here, import the following source tables into the Hydra Cloud.
Review the data types for the date columns in all the source tables.
Open your favorite code editor and navigate to the “demohydra” directory where you have your dbt files.
Create a directory under models and name it to “scm”.
Now we create our first data warehouse table using the source tables.
Under models, create your schema.yml file and write the following table creation code.
We shall be using the schema.yml file to create all the tables needed in the Hydra Cloud.
In the models directory, create the file “quarters.sql” and write the sql code as shown below.
After creating the table definition under schema.yml and the models file, go ahead and execute the dbt run command in your project directory.
Open your favorite SQL management tool and connect to the Hydra Cloud.
As shown below, review that the models table has been successfully created in your Hydra Cloud.
Congratulations! You have now created and populated your first Hydra Cloud models table using dbt.
As shown in this workflow, repeat the previous steps for table column definitions inside the schema.yml file and create models under the demohydra/models/scm directory for the following tables and models.