Skip to content

dbt Mesh

Goal: Model the streaming data from the base project, with the end goal of consolidating both data sources to make a consolidated fact table.

You notice he base project is already modeling streaming data, which is pull data from the new streaming platform. Your team are responsible for the legacy data.

The problem is that the team who run the base project do not get the legacy data, meaning any analysis they do on the data will exclude any information from before

Exercise 1 - Query the new data using cross-project reference.

Query the available streaming data from the base project.

Hint: How to query the right data

Look at the model governance in the base project - particularly look for which streaming models you actually have access to.

Check the model fct_content_performance.sql and make sure you are comfortable with how the cross-project reference is being configured.

Use these bits of information to write a SQL select statement (this can be done in the analyses/ folder for now) to see the new streaming model.

Part 2 - Compare the new and legacy streaming data

Check the documentation and the output of your query from above. Write down the main differences are between the legacy and the streaming data, you will need to be aware of this so you can apply the correct modeling later.

To help with the direction of your investigate... answer the following questions:

  • Which model from the base project can you query? Why?
  • What is the grain of the legacy streaming data compared to the new streaming data?
  • The legacy system kept collecting data after switching in the migration period - how long was that done for? (Hint: check out the min/max of ping_date and watched_at in the legacy and new data)
  • Bonus: Why are the fct/dim models not available for use by your team? How will this impact your project development in the future?
Hint: Where is the data

Your project is MediaPulse - Analytics. You're responsible for legacy streaming.

The Base project is reponsible for the current streaming.

Check the dbt_project.yml, you will see which models are public to you - it is only the staging models. So these are the only models available to you.

Create a new model in the intermediate folder called int_new_streaming__watch_events.sql and query the new streaming data using.

    `select * from {{ ref('mediapulse_base', 'stg_streaming__usr_watch_events_log') }}`

Step 3 - Build an intermediate model

Build an intermediate model on top of the staged legacy streaming data so that you reshape the data according to the shape of the new streaming data.

Step 4 - Build a fct_all_streaming_events model

In the marts folder, create a new model that consolidates the legacy and new streaming data.

Note which columns will be empty due to these fields not being continued, or being new, in the new data. Use comments in the SQL code to explain this - this will help with the next step.

Hints: What to include/exclude
  • Make a decision on how to reconcile the overlapping user fields
  • Add an is_legacy flag field to mark which rows are from the legacy data - this will be helpful in the next step!

Step 5 - Write tests

Write the yaml file yourself for the documentation and the important tests on the model (this is a good real-world candidate for dbt Wizard - for this training, write it manually).

Add tests that are only true when on legacy or when on the new data. Use a where parameter to filter to legacy/new rows.

Extension 1 - Versioning

Add versioning to the models you are using with dbt Mesh / project dependency.

What happens when you've included versioning and the base project updates the models you are working with?