Applied Data Modeling Using Snowflake and Power BI

Design a conceptual data model that captures four entities, namely customers, orders, products, and payments, and their relationships using entity relationship diagrams to prepare for normalization and logical design.

Identify and relate entities using entity relationship diagrams to structure data before implementation, detailing 1-to-1, 1-to-many, and many-to-many connections, bridging table, and normalization for data integrity.

Explore data normalization and first normal form (1NF) to reduce redundancy, ensure data integrity, and optimize storage in cloud warehouses like Snowflake, laying groundwork for later 2NF and 3NF.

Learn second and third normal form by transforming a denormalized orders table with an order items bridge to remove partial dependencies and ensure values depend on the primary key.

Construct a logical model for a dataset with customers, orders, products, and payments by defining attributes, primary keys, foreign keys, and relationships, then apply normalization.

Learn how to implement the logical model as a physical schema in Snowflake or MySQL, selecting data types, primary keys, and constraints for performance and integrity.

Explore dimensional modeling for data warehouses by dividing data into fact and dimension tables to support fast querying and aggregations in analytical reporting.

Explore the star schema, a simple data warehousing model with a central fact table and surrounding dimension tables, linking enrollments to students, courses, and dates via foreign keys.

Explore the snowflake schema as a normalized extension of the star schema, splitting dimensions into subdimensions to reduce redundancy, while noting increased joins and overall complexity.

Apply hands-on data modeling to a mountain bike sales dataset by identifying entities and relationships, choosing a star or snowflake schema, and building a physical model in Snowflake.

Develop a conceptual design for website sales data by outlining tables: product, sales territory, orders, and shipping, with key attributes and a plan for a snowflake schema.

Normalize to a snowflake schema with orders and order details as fact tables, and build dimension tables for sales territory, country, shipping carrier, and product subcategory for clear relationships.

Define and visualize entity relationships among fact orders, fact order details, products, and related tables using one-to-many and one-to-one connections, with primary and foreign keys.

build a refined model diagram for a snowflake/star schema by generating dimension and fact tables, adding a dim date table, and validating primary and foreign keys, data types, and relationships.

Sign up for the 30-day free Snowflake trial, create your account with an email, choose Azure and SQL, activate via email, then prepare to load data.

Navigate the Snowflake home page to explore the free trial, projects, notebooks, streamlit apps, and the data area with databases, schemas, and tables.

Load raw website sales into Snowflake by creating a staging database (StG), loading a CSV into the web sales raw table, and confirming 60,000 rows were inserted.

Create the physical Snowflake tables by building a new sales db and web orders schema, using Claude-generated SQL and enforcing primary and foreign keys.

Populate the date dimension table from the raw web sales data using Snowflake SQL, including a CTE to surface unique dates, with semester, fiscal quarter, and weekend flags.

Populate the country and region dimension tables in Snowflake by extracting distinct countries and regions from web sales data, assigning unique IDs, and validating the inserts.

Populate the shipping carrier and product dimension tables by inserting IDs and names. Join the product subcategory to retrieve IDs for the product table and ensure uniqueness.

Populate the shipping dimension by adding the sales order number, adjust date formats, and join shipping to the fact tables to fully populate the orders and order details tables.

Test the Snowflake data model by using Claude to generate and run sql queries that verify table structures, counts, duplicates, and foreign key relationships in the sales web order schema.

Run analysis queries on the sales web orders dataset to identify top selling products, monthly sales trends, and regional performance via joined territory and country data.

Install Power BI desktop and the Snowflake ODBC driver, create a system DSN, test the connection, and load Snowflake tables into Power BI via ODBC.

Explore validating a Snowflake data model in Power BI by connecting shipping, orders, and dimension tables, creating active relationships, and testing with visuals to spot missing links.

Learn data vault modeling, a flexible approach for historical data using hubs, links, and satellites. See how hubs capture core concepts, links relate them, and satellites hold load dates.

Load the airline CSV in Power BI, transform data with Power Query, and create the first dimension table (passenger) in a star schema with airport and country dimensions.

Create a nationality ID as a foreign key to link the passenger and nationality dimensions, deduplicate nationalities, and set a 100 starting index via a Power Query merge.

Duplicate the airline dataset to build the country and airport dimensions, include country name and code, deduplicate, and add country IDs from 1000 and airport IDs from 2000.

Build continent, pilot, and flight status dimension tables in power bi, deduplicate records, assign IDs, and prep for the final fact table and date table.

Learn to build a Power BI fact table with foreign keys to dimensions, merge queries to map IDs, and create a date dimension using DAX for robust data modeling.

Build and validate a Power BI star schema by defining and activating relationships between fact flights and dimensions, testing with visuals and confirming data flow.

Design a Power BI dashboard from the star schema data model to visualize flight status, top nationalities, and date-based trends with interactive filters and slicers.

Analyze the Airbnb data set for your capstone, identify host and listing details, location, pricing, reviews, and languages, then develop conceptual, logical, and physical models in Snowflake.

Walks through solving a capstone project by modeling a raw Airbnb dataset in a star schema on Snowflake, detailing host language bridge, location, property, and date dimensions.

Master the foundations of conceptual, logical, and physical data modeling, design ER diagrams, apply normalization, and explore dimensional modeling with star and snowflake schemas, culminating in Power BI reporting.