Fabric Data Warehousing and Analytical T-SQL

Instructor: Alaa Hady · Language: English or Arabic · Suggested duration: 2 days · Level: Intermediate to Advanced (Level 200–300)

Objectives

• Architect and build an enterprise relational Data Warehouse on Fabric's distributed SQL engine.
• Write high-performance T-SQL for data ingestion (COPY INTO, cross-database queries), transformation (stored procedures, CTEs, window functions), and dimensional modeling.
• Implement Warehouse security (Row-Level Security, Dynamic Data Masking, column-level permissions) and source control.
• Connect downstream Direct Lake and DirectQuery semantic models seamlessly.

Outline and contents

1. Fabric Data Warehouse architecture: separation of storage (OneLake Delta Parquet) and distributed T-SQL compute
2. Lakehouse vs. Warehouse decision framework: choosing the right analytical store or combining both via cross-database queries
3. Schema design in Fabric Warehouse: schemas, tables, supported data types, constraints, and surrogate key generation (IDENTITY alternatives)
4. High-speed data ingestion: COPY INTO, Data Pipelines, Dataflows Gen2, and zero-copy cross-database queries over Lakehouse Shortcuts
5. Analytical T-SQL mastery: Common Table Expressions (CTEs), Window Functions (LAG, LEAD, ROW_NUMBER, SUM() OVER), MERGE/Upsert patterns, and Stored Procedures
6. Cloning tables (CREATE TABLE AS CLONE OF) for zero-copy development snapshots and point-in-time recovery
7. Enterprise SQL Security: Grant/Deny/Revoke object permissions, Column-Level Security, Row-Level Security (RLS predicates), and Dynamic Data Masking (DDM)
8. Query performance tuning: Query Insights views, monitoring long-running queries, statistics, and Delta file compaction
9. Visual Database Projects inside VS Code / Azure Data Studio and Git integration
10. Hands-on Lab: Developing a T-SQL stored-procedure driven Star Schema Warehouse with RLS and Dynamic Data Masking

Target audience

SQL developers, BI developers, analytics engineers, data warehouse architects, and DBA/data teams.

Expected outcomes

Participants can build, secure, and optimize a modern cloud Data Warehouse in Fabric using familiar T-SQL tools while writing natively to open OneLake Delta tables.