Data Warehouse & T-SQL

DW001: Schemas & Tables- Create schemas (raw, transformed, analytics) and base tables with T-SQL for a new Fabric Data Warehouse.
DW002: Adding New Data- Insert and load data using INSERT and COPY INTO; compare SQL-based ingestion vs. pipeline methods.
DW003: SQL Drills 1 (SELECT) -Practice SELECT, TOP, and ORDER BY to explore and filter raw data efficiently.
DW004: SQL Drills 2 (WHERE) -Use WHERE, AND, IN, and wildcards for flexible filtering and string pattern searches.
DW005: SQL Drills 3 (Adding Columns)- Add constant, calculated, and conditional columns with CASE and aliases for dynamic logic.
DW006: SQL Drills 5 (JOINs)- Apply GROUP BY, COUNT, and HAVING to create summary aggregations for analysis.
DW007: Join contacts and lookup tables with LEFT JOIN, diagnose nulls, and insert missing reference data.
DW008: Subqueries- Build nested queries with subqueries for advanced filtering and dynamic data extraction.
DW009: CTE- Re-write subqueries as Common Table Expressions (CTEs) for readability and maintainability.
DW010: Insert, Update, Delete -Used DML commands to add, modify, and remove records; introduced transactions and rollback concepts.
DW011: CTAS, Alter, Drop, Truncate- Create and modify tables using CTAS, ALTER TABLE, and understand DROP vs. TRUNCATE.
DW012: Views- Build SQL Views (transformed.v_contacts) to store transformation logic for reuse.
DW013: Stored Procedures- Create parameterized stored procedures for querying and data manipulation in pipelines.
DW014: IT/ELSE logic- Implement conditional IF/ELSE logic to replicate MERGE-style UPSERT operations.
DW015: Mini-Project: watermarks- Develop a metadata.watermark table and stored procs for incremental pipeline tracking.
DW016: DM Concepts 1 (Database Systems)1- OLTP vs. OLAP systems & normalization (1NF–3NF) translates into denormalized analytics
DW017: DM Concepts 2 (Kimball Methodology)- Introduced Fact and Dimension tables and the Star Schema approach for BI and Fabric.
DW018: CONFX Intro & Setup- Stage 12 raw CSVs in Bronze Lakehouse and define the Green (conformed) target layer.
DW019: CONFX Sessions Table- Build a conformed Sessions table combining Vegas and Stockholm data with unified structure.
DW020: CONFX Ticket Sales Table- Model Ticket Sales as a Fact table linking offers, customers, and calculated totals.
DW021: CONFX Date Table- Generate a dynamic Date Dimension with stored procedure p_generate_date_dim.
DW022: CONFX Attendance Table- Combine multiple attendance sources into a unified Fact table with normalized structure.
DW023: Connecting to SSMS- Connect Fabric Data Warehouse to SSMS and compare query development environments.
DW024: DM Concepts 3 (Keys)- Explore Primary, Foreign, Natural, and Surrogate keys; understand Fabric constraint handling.
DW025: DM Concepts 4 (Slowly Changing Dimensions)- Introduced SCD Type 1 & 2 designs for tracking historical changes to dimension data.
DW026: CONFX SCD Type 1 (Detect Changes)- Detect changes between Bronze and Green layers to identify inserts, updates, and deletes.
DW027: CONFX SCD Type 1 (Apply Changes)- Write idempotent stored procedures for SCD Type 1 merge logic and incremental updates.
DW028: CONFX SCD Type 2- Implement SCD Type 2 table with valid_from, valid_to, and is_current for history tracking.