Data Engineering from Design to Implementation

Explore how an erd maps a database structure with tables for houses, rooms, and occupants, detailing columns, data types, and one-to-many relationships via crow's feet notation.

Dimensional modelling structures a data warehouse with a flat design, using a fact table of metrics and dimension tables like room and occupant for fast querying and reporting.

Explore the star schema, a flat data structure optimized for information delivery in data warehouses, centering analysis on a fact table with dimensions and system generated primary keys.

Participate in the data design exercise by analyzing provided data to create an information package, identify dimensions and the fact table with its attributes, and build a star schema.

Explore the fundamentals of databases, an electronic information collection organized into tables with columns and rows, and learn data types like integer, numeric, boolean, and text, using Postgres.

Create a database and a product list table in PostgreSQL, with product_code as the integer primary key, and load csv data into it using copy, then query with select.

Master the three-part structure of select statements: specify columns, choose source tables, and apply where filters. Learn to order, limit, group, and join data across tables.

Learn how to write basic select statements and filter data with where, in, not in, and like. Rename columns, order results, limit rows, and format prices for display.

Download the two course resources: the cell statuses and the cells grip data sees, to begin the exercises and apply them.

Design and load raw sales data into a unified database structure, joining customer, product, store, and purchase date dimensions to consolidate separate files and enable reporting.

Create a product sales information package by identifying dimensions such as customers, products, stores, regions, and sales reps, with facts like quantity and price, and explore flat versus snowflake designs.

Create a star schema from the product sales information package by linking a fact table to customers, products, stores, regions, and sales dimensions in Postgres.

Load temp tables using the prepared scripts, keeping the naming convention for team sales and team reps, and import data from your csv into dimensions and facts in Postgres.

populate dimension tables in a star schema by inserting distinct customer and product data, using system-generated keys, and split full names into first name and surname with substring.

Create a comprehensive view by joining fact and dimension tables, enabling descriptive customer, product, and region data for reporting with Power BI.

Load data into Power BI from a Postgres database by importing a database view for reporting. Connect to localhost and product sales database, use Get Data, and import the view.

Create Power BI dashboards from imported data, applying filters and slices, and refresh data before publishing online. Add calculated columns with DAX and drill down by region and store.