Building a Modern Data Warehouse - Data Engineering Bootcamp

Design scalable data architectures by comparing data warehouse, data lake, data lakehouse, and data mesh, then apply medallion architecture with bronze, silver, and gold layers for a modern data warehouse.

Create a git repository named SQL data warehouse project to store and track code. Add a readme and MIT license, and structure folders like datasets, documents, scripts, and tests.

Create a new SQL Server data warehouse database, switch to the master, then switch to the data warehouse and create bronze, silver, and gold schemas.

Build bronze-layer tables in a data warehouse via full loads, truncating and inserting data with no transformations or data modeling, guided by source metadata and a strict naming convention.

Map data sources and flows with a data flow diagram to show data lineage from CRM and ERP to bronze, then commit bronze scripts and a load procedure.

Apply data quality checks on order date, shipping date and due date, convert integer dates to real dates, and enforce business rules for sales, quantity, and price.

Create or alter a silver layer stored procedure to load the entire silver layer with robust error handling, timing metrics, and consistent messaging, mirroring the bronze layer and ETL standards.

Extend the data flow to the silver layer, visualize lineage from bronze to silver (and toward gold), and document ETL scripts, server layer DDLs, and quality checks in the repository.

Identify business objects from source systems and design the gold layer through data integration. Validate the data model, rename columns for clarity, and document with a data dictionary and git.

Build the gold dimension for customers using a star schema, integrating crm and erp data from the silver layer with joins, dedup checks, and a surrogate key in a view.

Create a data catalog to document your data products, describing tables, columns, data types, and relationships to save time and clarify the gold layer for business users and analysts.

Explore the database structure by running queries and using information_schema to inspect tables, views, columns, and metadata; practice discovering dimensions, measures, dates, and rankings for a data warehouse project.

Explore how to compute key business metrics using SQL aggregate functions (sum, average, count) to reveal total sales, total quantity, average price, and orders, and build a consolidated measures report.

explore magnitude analysis by aggregating a measure by a dimension to reveal insights, such as revenue by category, customers by country, and average costs by products, with sorting and comparisons.

Master advanced SQL techniques to perform analytics projects, track changes over time, segment customers and products, and generate two reports using window functions and CTE queries.

Compare current sales to targets such as the average and previous year using window functions to measure performance and flag above or below trends in yearly product sales.

Apply part-to-whole analysis to identify how each category contributes to total sales, calculate percentages with window functions and CTEs, and reveal top-performing and underperforming segments for data-driven strategy.

Segment data by converting a measure into a new category with case when statements, then aggregate another measure by this category to reveal cost ranges and customer segments.

Document all datasets, documentation, and scripts in a git repository with clear comments and formatting to share your sql data warehousing and analytics reports.