Data Lakehouse using Snowflake – YouTube Trending Dataset
PS – These projects are from year 2022 – year 2023. Restored these post from old hosting service.
1. Introduction
The project focusses on ingesting YouTube trending data from Microsoft Azure Blob Storage to create a data lakehouse in Snowflake. This data is further merged, cleaned and stored to answer some critical business questions.
2. Datasets
Two types of files were ingested: the YouTube Trending Dataset and the YouTube Categories Dataset — 11 files each, one per country (Brazil, Canada, Germany, France, Great Britain, India, Japan, Korea, Mexico, Russia, United States).
2.1 YouTube Trending Dataset. 11 CSV files with daily records for top trending videos per country from 2020-08-12 to 2022-01-28. Attributes include video_id, title, publishedAt, channelId, channelTitle, categoryId, trending_date, view_count, likes, dislikes, comment_count, comments_disabled.
2.2 YouTube Categories Dataset. 11 unstructured JSON files with 29 categories each (category_id, category_title); category ids can differ per country.
3. Architecture
Azure Blob Storage holds the CSV and JSON files, later ingested into Snowflake for processing.
3.1 Microsoft Azure Blob Storage. Container bde-at-1 in storage account rajatsinghuts stores all JSON and CSV files.
3.2 Snowflake. Snowflake’s Data Cloud (SaaS) was used for CRUD operations on the blob data, including extracting information from JSON files into tables.
Transformation steps in Snowflake: Staging (external tables over blob storage), Data Merging (table_youtube_trending ⨝ table_youtube_category on country + category_id), Data Cleaning (duplicate rows removed from table_youtube_final), Data Warehousing (final table stored for use).
3.3 Consumer Systems. The processed database can feed business applications and visualisation tools.
4. Database Details and Data Dictionary
| Type | Name |
| Database | BDE_AT_1 |
| Storage integration | AZURE_BDE_AT_1 |
| Stage | STAGE_BDE_AT_1 |
| File format | FILE_FORMAT_CSV |
| External table | EX_TABLE_YOUTUBE_TRENDING |
| External table | EX_TABLE_YOUTUBE_CATEGORY |
| Table | TABLE_YOUTUBE_TRENDING |
| Table | TABLE_YOUTUBE_CATEGORY |
| Table | TABLE_YOUTUBE_FINAL |
| Table | TABLE_YOUTUBE_DUPLICATES |
5. SQL Queries
Business Question. For each country, which category_title has the most distinct videos and what is its percentage (2 decimals) out of the total distinct videos of that country? Order by category_title and country.
select d_by_country.country, category_title, total_category_video, total_country_video, total_category_video/total_country_video*100 as percentage from (select country, count(distinct title) as total_country_video from table_youtube_final group by country) d_by_country inner join (select * from (select country, category_title, total_category_video, rank() over(partition by country order by total_category_video desc) as rk from (select country, category_title, count(distinct title) as total_category_video from table_youtube_final group by country, category_title)) where rk = 1) d_by_category on d_by_country.country = d_by_category.country order by category_title, country;
Explanation. The first part counts distinct video titles by country; the second counts by country and category, using rank() partitioned by country to keep the top category (nested, since Snowflake disallows WHERE on window functions directly). The two parts are joined on country.
6. Business Problem
If launching a new YouTube channel (excluding Music and Entertainment), which category would top the trends? By sum of views: Music (739.9B), Entertainment (519.5B), Gaming (195.8B), People & Blogs (187.4B) lead. But to avoid regional bias, count appearances in each country’s TOP 2: People & Blogs (9), Gaming (8), Sports (3), Comedy (2). Conclusion: create in the People & Blogs category.
7. References
Snowflake. n.d. Snowflake Documentation. https://docs.snowflake.com/en/user-guide/intro-key-concepts.html