Week 4 - Analytics Engineering

Goal:

• Transforming the data loaded in DWH to Analytical Views
• Understand 4 aspects of the analysis

Sample project:

• booking_dbt project
• ny_taxi_trips_dbt project

Analysis Methods

graph TD
%% Add a transparent text node as a watermark
style Watermark fill:none,stroke:none
Watermark[Created by: LongBui]

    A[Analysis Aspects] --> B[Descriptive Analysis]
    A --> C[Diagnostic Analysis]
    A --> D[Predictive Analysis]
    A --> E[Prescriptive Analysis]
    B --> B1[Summarize Data]
    B --> B2[Identify Patterns]
    C --> C1[Identify Causes]
    C --> C2[Determine Relationships]
    D --> D1[Forecast Future Trends]
    D --> D2[Predict Outcomes]
    E --> E1[Provide Recommendations]
    E --> E2[Optimize Strategies]

Thinking Process of Analytics

You can follow this step:

Step 1 : Understand The Current State

As everything happens, we need to ask question Why and How to ensure we understand correctly.

Step 2: Setting Clear Object

Per the understanding, we set the goal for our actions, it’s ok for the short-term goal, we only need to target to road to the goal.

Step 3: Segmenting the Audience

Substrate the gold by the Subject of things, in this example, we segment the Audience; by this action, we can have the smaller set of gold with measurable metrics and have the NUMBER of count.

Step 4: Personalizing Marketing Campaigns

With this strategy, we obviously can set and run the right campaign for right people.

Step 5: Enhancing User Experience

Collecting the metrics to optimize the UI/UX as well as value from the product (Application or Knowledge Base).

Step 6: Analyzing Data and Metric

Defining and setting up the metrics, KPI, tooling, reporting for tracking the user bahaviour like Daily Active User (aka DAU) or MAU or In-app, Channel, Top products, top page, user Click map, etc.

Step 7: Iterating and Optimizing

Finally, repeat your action, revise the process, factor and enhance the product continuously.

Road to Analysis

graph TD
%% Add a transparent text node as a watermark
style Watermark fill:none,stroke:none
Watermark[Created by: LongBui]
    subgraph A[Understanding Current State]
        A1[Assess Marketing Performance]
        A2[Analyze User Activity]
        A3[Identify KPIs]
    end

    subgraph B[Setting Objectives]
        B1[Define Sales Targets]
        B2[Set Engagement Metrics]
        B3[Align with Business Goals]
    end

    subgraph C[Segmenting Audience]
        C1[Identify Segments]
        C2[Create Personas]
        C3[Analyze Behavior]
    end

    subgraph D[Personalizing Campaigns]
        D1[Develop Campaigns]
        D2[Personalize Messaging]
        D3[Test and Optimize]
    end

    subgraph E[Enhancing UX]
        E1[Improve UI/UX]
        E2[Optimize Performance]
        E3[Collect Feedback]
    end

    subgraph F[Analyzing Data]
        F1[Monitor Metrics]
        F2[Cohort Analysis]
        F3[Generate Insights]
    end

    subgraph G[Iterating and Optimizing]
        G1[Adjust Strategies]
        G2[Test and Learn]
        G3[Scale Success]
    end

    A --> B --> C --> D --> E --> F --> G

Prerequisites

Follow the instruction at booking_dbt

Once you get done, check the harder project in the following below“

We will build a project using dbt and a running data warehouse. By this stage of the course you should have already:

• A running warehouse (Snowflake or Local Postgres)
• A set of running pipelines ingesting the project dataset (week 3 completed): Taxi Rides NY dataset
  • Yellow taxi data - Years 2024
  • Green taxi data - Years 2024
  • fhv data - Year 2024

dbt Setting

Setting up dbt for using Snowflake (Preferred)

• You will need to create a dbt cloud account using this link

• Connect to your warehouse following these instructions.

• More detailed instructions in dbt_cloud_setup.md

Optionally setting up dbt on local using Docker Compose

Setting up dbt for using Postgres locally (Optional)

As an Option to the cloud, that require to have a postgres database, you will be able to run the project installing dbt locally.

Make sure you have ~/.dbt on your local machine

your_project_profile:
  outputs:
    dev:
      dbname: database
      host: localhost
      password: postgres
      port: 5432
      schema: public
      type: postgres
      user: postgres
  target: dev

Introduction to analytics engineering

• What is analytics engineering?
• ETL vs ELT
• Data modeling concepts (fact and dim tables)

What is dbt?

Intro to dbt Introduction

Starting a dbt project

• Starting a new project with dbt init
• dbt cloud setup
• Integration with Github repository
• Snowflake Connection Configuration

Development of dbt models

• Anatomy of a dbt model: written code vs compiled Sources
• Materialisations: table, view, incremental, ephemeral
• Seeds, sources and ref
• Jinja and Macros
• Packages
• Variables
• Macros

Testing and documenting dbt models

• Tests
• Documentation

Deploying a dbt project

• Deployment: development the data model
• Commit the change to your github repository: git add . && git commit -m "Model Created" && git push origin main
• dbt cloud: scheduler, sources and hosted documentation

Integrate dbt with Airflow

Visualizing the transformed data

• Using Google data studio - Looker (Cloud)

• Using Metabase (local installation)

What make you better ?

• Data modeling: normalize and de-normalize
• Data structure: prefer to work with NoSQL data
• How data movement is handled: CDC, SCD, RCD
• Analyze data, getting actionable information