Data Build Tool Challenge - Data Modeling

Lab Steps

lock
Data Build Tool Challenge - Basics
lock
Data Build Tool Challenge - Data Modeling
lock
Data Build Tool Challenge - Data Testing
Need help? Contact our support team

Here you can find the instructions for this specific Lab Step.

If you are ready for a real environment experience please start the Lab. Keep in mind that you'll need to start from the first step.

In this step, you will complete tasks relevant to data modeling with dbt. Continue to the Validation Steps tab to perform these tasks.

Validation checks
3Checks
Adding a dbt Seed

Your manager asked you whether there is the possibility to add CSV and map them as tables through dbt. One of the great features of dbt is seeds.

Because you previously mapped employees source, a valuable seed would be a mapping between USA state abbreviations to full names.

Create a seed named usa_states.csv whose content can be found here.

Data build tool (dbt)
Defining the dbt Intermediate Model

Your manager wants to see how data modeling and transformations can be applied using dbt. Because you want to let the manager sees the potentials offered by dbt when multiple transformations need to be performed, you have to create an ephemeral model into an intermediate folder within the models folder.

Create an intermediate model named int_orders_with_products.sql that represents the orders along with the number of unique products related.

Notes

  • The field that represents the number of products needs to be named products_number
Data build tool (dbt)
Defining the dbt Mart Models

It's time to create the final mart models. You want to follow the dimensional modeling techniques and want to create both a dimension and a fact table.

Create the following dbt models into a marts folder within the models folder:

  • f_orders_more_two_products.sql - A fact table representing the orders that have more than two products related
  • d_usa_employees.sql - A dimension table representing employees living in USA

Notes

  • Start from the intermediate model you previously created to build the f_orders_more_two_products model
  • Insert also the extended state name in the d_usa_employees model. You need to perform a join from the stg_employees model to the usa_states seed.
  • Remember to materialize both the models as table.
Data build tool (dbt)