How to Install DBT and Set Up a Project, Create Your First dbt Model

 

###### Snowflake DDL  - at the end #########

Hello Data Folks, 

Today we’ll see how to install DBT, set up your first project, and create a dbt model.

So, without further ado, let's dive right in and get started!

 

Python is a prerequisite for using dbt, so make sure to download it from python.org and install it on your system.

Please select this checkbox during the installation process. This’ll automatically add the Python installation directory to your system's Path variable.

To verify that Python is working correctly, open the command prompt and use the "python --version" command.

Looking good!

 

Let's now download and install Visual Studio Code, the most powerful and widely used IDE in the industry.

After installing VS Code, please proceed to install the Python and dbt extensions one after another.

As a best practice, please choose extensions with high downloads and ratings.

 

Please open the terminal, and cd to the path where you want to setup your first project.

Create a Python virtual environment.

Activate the virtual environment created.

 

Let's install dbt with the “pip install dbt hyphen data platform” command.

For instance, if you are using Snowflake, run “pip install dbt-snowflake”.

 

Before we initialize our first project, we should create a dot dbt folder in the user home directory.

You can either do it over here, with this make directory command.

Or navigate to the home directory using %userprofile%, and create the folder.

This is the default folder where dbt will create and maintain the profile dot yamel, one of the important dbt configuration files, where database connection details and user credentials are stored.

 

We are ready to initialize our first dbt project using the “dbt init” command.

This command creates the necessary folder structure for dbt.

and generates the dbt_project dot yamel file, which contains project-specific configurations.

it also creates the profiles dot yamel file, which stores the database connection details.

 

Please follow the onscreen instructions and respond to the prompts that appear.

 

Now, let's navigate to the project that was just created.

The file we want to focus on is the dbt_project dot yamel. It contains important information such as the project name, version, and most notably, the target database profile.

Additionally, you’ll find the profiles dot yamel file in your home directory. This file stores the database connection details, which you supplied during the dbt init process.

 

At this point, it’s a good idea to verify the connectivity between dbt and your data platform using the “dbt debug” command.

Great, connection works as expected.

 

Next, we’ll move forward to creating our first dbt model.

In the context of dbt, a model refers to a SQL query or a group of SQL queries that are designed to carry out a particular transformation task on your data platform.

 

This data model represents an “order management system”, a project in the retail domain.

We’ll explore more about this business and data model in our upcoming sessions. However, for now, our focus will be on the customers and orders.

Using these two tables, let's attempt to identify customers who have placed a larger number of orders.

 

As mentioned earlier, dbt models predominantly consist of SQL statements.

So, let's begin by writing a SELECT query that fulfills our requirement.

We could join the "customers" and "orders" tables, and aggregate the results using the GROUP BY clause.

 

It works! However, dbt makes extensive use of Common Table Expressions or CTEs, for improved readability and modularity; so, you must understand how to write SQL queries using CTEs.

CTEs act as temporary tables that are accessible only within the same query and are discarded after the query execution. To use a CTE, simply prefix “with “CTE name” as” to your SELECT statement.

Using CTEs provides an abstraction of the underlying complex logic, resulting in a more readable and modular query.

Let's create our first model called "customerorders.sql" with the query that we’ve just framed.

Please save the model once you are done.

Now, we are ready to execute our first model.

Issue the "dbt run" command in the terminal. This command references the "dbt-project dot yamel" file, and "profile dot yamel" file for project and connection details respectively, and executes the models in the project.

If the execution is successful, you’ll see that a view with the same name as the model file has been created.

Let's verify the object created in Snowflake.

All working well as expected; the business users can now effortlessly utilize their preferred reporting tools like PowerBI to access this data.

 

Before we wrap up, there is one final point worth mentioning.

The default materialization for dbt models is a view, but it can be configured or changed to the table, either at the "dbt-project dot yamel" file or at the model file itself.

Let’s rerun the project after this change.

As expected, the model has been successfully created as a table this time.

That's all for today, in our next video, we'll take a deep dive into dbt models, create dependencies between them, and much more.

So please stay tuned for more exiting videos, and don't forget to like – subscribe - and share your comments. Thanks for watching!

CREATE DATABASE SLEEKMART_OMS;

CREATE SCHEMA L1_LANDING;

CREATE SCHEMA L2_PROCESSING;

CREATE SCHEMA L3_CONSUMPTION;

 

USE database SLEEKMART_OMS;

USE SCHEMA L1_LANDING;

CREATE TABLE IF NOT EXISTS Dates (

Date DATE NOT NULL,

Day VARCHAR(3) NULL,

Month VARCHAR(10) NULL,

Year VARCHAR(4) NULL,

Quarter INT NULL,

DayOfWeek VARCHAR(10) NULL,

WeekOfYear INT NULL,

Updated_at TIMESTAMP NULL,

PRIMARY KEY (Date));

CREATE TABLE IF NOT EXISTS customers (

CustomerID VARCHAR(10),

FirstName VARCHAR(50),

LastName VARCHAR(50),

Email VARCHAR(100),

Phone VARCHAR(100),

Address VARCHAR(100),

City VARCHAR(50),

State VARCHAR(2),

ZipCode VARCHAR(10),

Updated_at TIMESTAMP);

CREATE TABLE IF NOT EXISTS Employees (

EmployeeID INT NOT NULL,

FirstName VARCHAR(100) NULL,

LastName VARCHAR(100) NULL,

Email VARCHAR(200) NULL,

JobTitle VARCHAR(100) NULL,

HireDate DATE NULL,

ManagerID INT NULL,

Address VARCHAR(200) NULL,

City VARCHAR(50) NULL,

State VARCHAR(50) NULL,

ZipCode VARCHAR(10) NULL,

Updated_at TIMESTAMP NULL,

PRIMARY KEY (EmployeeID));

CREATE TABLE IF NOT EXISTS Stores (

StoreID INT NOT NULL,

StoreName VARCHAR(100) NULL,

Address VARCHAR(200) NULL,

City VARCHAR(50) NULL,

State VARCHAR(50) NULL,

ZipCode VARCHAR(10) NULL,

Email VARCHAR(200) NULL,

Phone VARCHAR(50) NULL,

Updated_at TIMESTAMP NULL,

PRIMARY KEY (StoreID));

CREATE TABLE IF NOT EXISTS Suppliers (

SupplierID INT NOT NULL,

SupplierName VARCHAR(100) NULL,

ContactPerson VARCHAR(100) NULL,

Email VARCHAR(200) NULL,

Phone VARCHAR(50) NULL,

Address VARCHAR(50) NULL,

City VARCHAR(50) NULL,

State VARCHAR(10) NULL,

ZipCode VARCHAR(10) NULL,

Updated_at TIMESTAMP NULL,

PRIMARY KEY (SupplierID));

CREATE TABLE IF NOT EXISTS Products (

ProductID INT NOT NULL,

Name VARCHAR(100) NULL,

Category VARCHAR(100) NULL,

RetailPrice DECIMAL(10,2) NULL,

SupplierPrice DECIMAL(10,2) NULL,

SupplierID INT NULL,

Updated_at TIMESTAMP NULL,

PRIMARY KEY (ProductID));

CREATE TABLE IF NOT EXISTS OrderItems (

OrderItemID INT NOT NULL,

OrderID INT NULL,

ProductID INT NULL,

Quantity INT NULL,

UnitPrice DECIMAL(10,2) NULL,

Updated_at TIMESTAMP NULL,

PRIMARY KEY (OrderItemID));

CREATE TABLE IF NOT EXISTS Orders (

OrderID INT NOT NULL,

OrderDate DATE NULL,

CustomerID INT NULL,

EmployeeID INT NULL,

StoreID INT NULL,

Status VARCHAR(10) NULL,

Updated_at TIMESTAMP NULL,

PRIMARY KEY (OrderID));