Building a Modern Data Warehouse - Data Engineering Bootcamp

Analyze project requirements to design a modern SQL Server data warehouse that consolidates ERP and CRM CSV data for analytics and reporting, ensuring data quality and an analytics data model.

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.

Define the bronze, silver, and gold layers, where bronze stores raw data for traceability, silver standardizes data with basic transformations, and gold delivers business-ready insights via views.

Design a modern data warehouse with a source layer, a consume layer, and bronze–silver–gold within the warehouse to transform csv inputs from crm and erp into business-ready insights.

Define a three-layer data warehouse build with bronze, silver, and gold epics, and outline tasks such as analyzing, coding, testing, documenting, and committing to git, plus establishing project naming conventions.

Define a strict naming convention for the data warehouse, using snake_case and avoiding reserved words. Apply bronze, silver, gold layer rules with prefixes for tables and columns, plus surrogate keys.

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.

Learn to load csv data into the data warehouse using bulk insert, including file path, header skip, and comma delimiter, into bronze tables, with full load via truncate and insert.

Build a stored procedure to load the bronze layer in a data warehouse, with structured begin and end blocks, logging messages, and per-table duration timing for robust etl.

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.

Derive category id from the product key, normalize product line values, and fix start/end dates with lead; cast to date and load into silver with data quality checks.

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.

Build the dimension products by integrating crm and erp data to capture current product information, ensure uniqueness, and create a surrogate key while publishing the gold dimension products 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.

Extend the data flow to the gold layer, establish data lineage from bronze through silver to gold, and finalize the git repo with gold views and quality checks.

Create and populate a data warehouse database from datasets using three methods: SQL scripts, import flat files, or restore backups. Work with the customer, products, and sales tables.

Learn to split data into dimensions and measures by checking numeric values and whether aggregation makes sense, enabling effective grouping and analysis.

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.

Identify unique values across dimensions using distinct queries to reveal country distribution, product categories, subcategories, and product names, unveiling data granularity and dimension relationships for analysis.

Explore date boundaries with min and max to find earliest and latest dates, compute ranges using datediff, and analyze order and birth dates for time-span insights.

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.

Analyze how a measure evolves over time by aggregating with a date dimension to reveal trends and seasonality, using SQL with year and month groupings, date trunk, and formatting.

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.

Create a SQL-based customer report by building a dataset with joins and CTEs, compute recency, segment into VIP, regular, and new, define age groups, and publish as gold-layer view report_customers.

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