Data Modelling and Visualization

Project mission

Transforming raw data loaded into a Data Warehouse into actionable Analytical Views, utilizing a dbt (data build tool) to ensure efficient data modeling and transformation. Additionally, the project includes the creation of a business intelligence dashboard to visualize the transformed data, providing valuable insights for decision-making.

Objectives

Data Transformation: Efficiently convert raw data into meaningful analytical views that are easy to interpret.
Automation: Leverage dbt to automate data transformations, ensuring consistency and reducing manual intervention.
Scalability: Design the dbt project to handle increasing volumes of data without compromising performance.
Visualization: Develop an interactive dashboard that provides clear, actionable insights from the transformed data.
Maintainability: Ensure the dbt project is easy to maintain, with clear documentation and modular components for future enhancements.

Steps to Implement

1. Data Import

Google Cloud Storage (GCS) served as the initial landing zone for raw data files in form of .csv files.

From GSC, the data was loaded into a structures environment, BigQuery, which is used to define schema and import data for querying.

2. Data Transformation (dbt)

Data Build Tool (dbt) was used to automate development of SQL-based transformations to clean the data and create models.

The data transformation process started with importing raw trip data for green and yellow taxis into staging tables (staging.green_trips_all and staging.yellow_trips_all). These tables were cleaned and standardized into refined staging tables (stg_green_trips and stg_yellow_trips).

A lookup table (taxi_zone_lookup) was used to create a dimension table (dim_zones), mapping zone IDs to readable names. These staging and dimension tables were then combined to form a comprehensive fact_trips table, capturing essential trip metrics.

Finally, a data mart (dm_monthly_zone_revenue) aggregated this data to provide monthly revenue insights by zone, enabling efficient analysis and reporting.

3. Visualization (Looker Studio)

A Looker Studio report was used to provide insights through an interactive dashboard.

This effort was critcal to enable stakeholders to explore data visually, uncover trends, and make informed decisions.

Report Summary

New York Taxi Trips Dashboard

Explore the New York Taxi Trips Dashboard

Dive deeper into the data with my interactive dashboard. Explore detailed visualizations and insights on taxi trip patterns and popular zones. Click below to access the full dashboard:

View Dashboard

Tools and Technologies

SQL

dbt

BigQuery

Google Cloud Storage

Looker Studio

Page updated

Google Sites

Report abuse