Table of Contents

Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Part I. Introduction to Analytics Engineering
1. Understanding the Role of an Analytics Engineer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
The Making of Analytics Engineering 3
What Exactly Does an Analytics Engineer Do? 4
The Big ETL-to-ELT (R)evolution 6
Analytics Engineer Versus Data Engineer Versus Data Analyst 7
A Day in the Life of an Analytics Engineer 9
What’s in the Analytics Engineer’s Toolbox? 10
What About dbt (Data Build Tool)? 11
Summary 12
2. Core Analytics Engineering: Data Architectures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
The Data Architecture Evolution 15
Relational Operational Database 15
Relational Data Warehouse 17
Data Lake 21
Data Lakehouse 22
Data Mesh 23
Event-Driven Architecture 25
Summary 28
3. Core Analytics Engineering: Data Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Conceptual Model 32
Logical Model 34
v
Physical Model 36
Online Transaction Processing (OLTP) Systems 38
Normalization 38
Normal Forms 39
Online Analytical Processing (OLAP) Systems 40
Denormalization 40
Dimensional Data Model 40
Star and Snowflake Schemas 42
Slowly Changing Dimensions 45
Different Types of Fact Tables 47
Summary 49
Part II. Working with Microsoft Fabric
4. Introduction to Microsoft Fabric. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Fundamental Fabric Concepts 54
Tenant 55
Capacity 55
Workspace 56
Fabric Components 58
Foundation Components 58
Workload Components 59
Fabric’s Cost Model 66
Reserved Monthly Capacity Pricing 66
Pay-as-You-Go Capacity Pricing 67
Storage Costs 67
User Licenses 67
Other Costs 67
Getting Started with Fabric 67
Following in Power BI’s Successful Footsteps 68
Summary 69
5. Data Ingestion and Data Access. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Sample Data for the Examples 72
Sample Architecture 72
Creating a Workspace 73
Setting Up Lakehouses and a Warehouse for Our Examples 74
Uploading CSV Files to Tables 76
Copy Job 77
When to Use Copy Job 78
Using Copy Job to Load Bronze_Lakehouse 78
Dataflows 80
When to Use Dataflows 80
vi | Table of Contents
Creating a Dataflow 80
Using Copilot in Dataflows 90
Scheduling a Dataflow 91
To Stage or Not to Stage 92
Fast Copy 95
Best Practices for Dataflows 95
Pipelines 96
When to Use Pipelines 96
Creating a Pipeline 97
Scheduling and Triggering a Pipeline 99
Activity Chaining: On Success, on Failure, on Completion 100
Using Copilot in Pipelines 104
Best Practices for Pipelines 106
Apache Airflow 107
When to Use Apache Airflow Jobs 107
Creating a Simple Airflow Job 107
Shortcuts 109
Mirroring 111
Summary 112
6. Working with the Lakehouse in Microsoft Fabric. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Understanding the Microsoft Fabric Lakehouse 113
The OneLake Foundation 113
Delta Lake: The Secret Sauce 114
The Two Faces of the Lakehouse 114
A Real-World Success Story 115
The Lakehouse Versus the Warehouse in Fabric 115
The Integration Advantage 116
Creating and Configuring Your First Lakehouse 116
Planning Your Lakehouse Structure 117
Step-by-Step Lakehouse Creation 117
The SQL Analytics Endpoint Magic 120
Common Pitfalls and How to Avoid Them 120
Configuration Checklist 121
Managing Bronze, Silver, and Gold Layers 122
Bronze Layer (Raw Zone) 122
Silver Layer 123
Gold Layer 123
Working with Delta Tables 123
Creating and Managing Delta Tables 124
Time Travel and Versioning 125
Schema Evolution and Enforcement 125
Optimization Techniques 126
The SQL Analytics Endpoint 129
Table of Contents | vii
T-SQL Capabilities and Limitations 130
Connecting Power BI to the Lakehouse 130
Cross-Database Queries 130
Advanced Patterns and Best Practices 131
Implementing the Medallion Design Pattern 131
Data Quality and Validation Strategies 144
Using Copilot in Lakehouse 146
Summary 151
7. Working with the Data Warehouse in Microsoft Fabric. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Getting Started with Warehouse Explorer 153
SQL Query Editor 154
Visual Query Editor 156
SQL Query in Notebook 157
Copilot for Data Warehouse 158
Example 1: Fixing Query Errors 158
Example 2: Explaining a Query 159
Example 3: SQL Code Completion 160
Example 4: Using the Chat Window 161
Other Ways to Connect to a Warehouse 161
Warehouse-Specific Capabilities 162
Time Travel with T-SQL 162
Zero-Copy Table Cloning 163
Cross-Database Querying 164
Data Warehouse Collation 165
Working with Keys and Constraints 165
Unique Identifiers 165
Primary Keys, Foreign Keys, and Unique Constraints 166
Loading Data into the Warehouse with SQL 167
CREATE TABLE AS (CTAS) 167
INSERT … SELECT 168
COPY INTO 168
OPENROWSET 168
Data Recovery and Point-in-Time Access 169
Restore Points 169
Warehouse Snapshots 170
Security and Monitoring 170
Summary 171
8. Working with Real-Time Intelligence in Microsoft Fabric. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
What Is Stream Processing? 173
Real-Time Hub 175
Eventstreams 176
The Eventhouse and the KQL Database 177
viii | Table of Contents
Eventhouse 177
KQL Database 179
OneLake Availability 181
Eventhouse Endpoint for Lakehouse and Warehouse 183
Query and Visualize Data in Real-Time Intelligence 186
KQL Queryset 186
Do I Need KQL If I Already Know SQL? 187
Real-Time Dashboards 189
Visualize Data with Power BI 193
Activator 194
Fabric IQ 195
Build Your First Ontology 198
Summary 210
9. Microsoft Fabric Decision Guide: When to Choose What. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Choosing an Analytical Engine 213
Data Volume 214
Supported Data Types 214
Supported Programming Languages 215
Supported Data Ingestion and Data Access Methods 216
Access Control 216
OneLake Interoperability 216
Scenario-Based Decision Guide 217
Mirrored Azure SQL Database Versus SQL Database 219
Scenario 1: Web Application with Operational Data 220
Scenario 2: Big Data Containing Sensitive Information 220
The SQL Database in Fabric Versus Fabric Warehouse 221
Scenario 1: Aggregating Big Data for Analytical Reports 221
Scenario 2: Near-Real-Time Operational Reporting with Enforced Database
Constraints 222
Direct Lake Versus Import Mode for Semantic Models 222
Scenario 1: Self-Service Business Intelligence with Power Query 223
Scenario 2: Near-Real-Time Reporting Requirement 224
Scenario 3: Resource-Consuming Data Refresh Process 224
Scenario 4: Using DAX Calculated Tables/Columns 224
Scenario 5: Using T-SQL Views 224
Scenario 6: Row-Level Security/Object-Level Security Enforced in the
Warehouse/SQL Analytics Endpoint of the Lakehouse 225
All Roads Lead to OneLake, but Which One Is the Right Road? 225
Dataflow Versus Notebook Versus Pipeline Versus Mirroring Versus Shortcut 226
Scenario 1: Ingesting Data As Is from an On-Premises Data Source 227
Scenario 2: Customizing the Data-Writing Process 227
Scenario 3: Transforming Data for the Serving Layer 228
To V-Order or Not to V-Order? 229
Table of Contents | ix
Summary 230
Part III. Working with Microsoft Power BI
10. Understanding Power BI Core Components. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Power BI Desktop 234
Power BI Storage Modes 236
Live Connection 237
Comparing Import, DirectQuery, Direct Lake, and Live Connection 237
The Languages of Power BI: Power Query, M, and DAX 238
The Power BI Service 238
Workspaces 239
Power BI Apps 240
Power BI Dashboards 241
Deployment Pipelines 243
Lineage and Impact Analysis 243
Governance 244
OneLake Catalog 244
Other Tools in the Power BI Ecosystem 245
Power BI Report Server 245
Power BI Report Builder 246
Power BI Mobile 246
Power BI Gateway 246
Copilot in Power BI 247
Power BI Cost Model 247
Workspace Type 247
User Licenses 250
Workspace Types and User License Requirements 251
Summary 252
11. Design and Build Semantic Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Getting Data 253
Connecting to a Lakehouse 254
Understanding Lakehouse Connection Options 255
Creating a Direct Lake Model 256
The Model View 258
Relationships 260
Cardinality 262
Cross-Filter Direction 264
Active or Inactive 265
Assuming Referential Integrity 267
Creating the Rest of the Relationships 268
Measures 270
x | Table of Contents
Hierarchies 271
Date Table 272
Column Properties 272
Opening the Model from the Fabric Portal 274
Creating a Direct Lake Model in the Fabric Portal 275
Creating a Simple Report from the Fabric Portal 277
Creating a Simple Report from Power BI Desktop 278
Import Models 279
DirectQuery Models 282
Direct Lake Fallback to DirectQuery 282
Summary 283
12. Enhance Semantic Models with DAX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Starting with Simple Measures (Aggregation Functions) 286
Basic Aggregation Functions 286
Aggregation Functions with an A Suffix 287
X Aggregation Functions (Iterators) 287
COUNTROWS and APPROXIMATEDISTINCTCOUNT 289
Understanding Context in DAX 289
Row Context 290
Filter Context 291
Introducing CALCULATE 293
Filter Modifier Functions 295
REMOVEFILTERS 296
ALL 297
ALLEXCEPT 298
KEEPFILTERS 299
Time Intelligence Functions 299
Year-to-Date (YTD) 300
Prior Year Comparisons 301
Percent Change Calculations 301
Relationship Navigation Functions 301
RELATED 302
RELATEDTABLE 302
USERELATIONSHIP 302
Table and Filter Functions 303
FILTER 303
VALUES 304
DISTINCT 305
SUMMARIZECOLUMNS 305
ADDCOLUMNS 306
Variables 306
Summary 308
Table of Contents | xi
13. Optimize Enterprise-Scale Semantic Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
What Makes a Model Enterprise-Scale 310
Memory Versus Compute, and Why Memory Is the Harder Problem 310
What’s Actually Using Your Memory: A Quick Tour of VertiPaq 311
Column-Level Optimizations: The Cheap Fixes First 313
Pattern 1: Remove What Nobody Is Using 313
Pattern 2: Reduce Cardinality 314
Pattern 3: Disable Unnecessary Attribute Hierarchies 316
Pattern 4: Optimize for Run-Length Encoding (RLE) 318
When Column-Level Fixes Aren’t Enough: Architectural Patterns 318
Pattern 5: User-Defined Aggregations 318
Pattern 6: Split the Model (the Master Model Pattern) 321
The Other Front: Refresh Memory 322
Pattern 7: Refresh Less Data with Partitioning 323
Query Folding: Make the Source Do the Work 324
Hybrid Tables: Incremental Refresh Plus DirectQuery 325
Pattern 8: Refresh Scale-Out 327
The Temporary Scale-Up Workaround 328
Direct Lake: Changing the Rules of the Game 328
Memory Residency States 329
Why the Delta Table Layout Matters 330
Update Patterns That Play Nicely (and Ones That Don’t) 331
Maintenance: Don’t Skip It! 332
Anti-Patterns: Things That Look Like Optimization but Aren’t 333
Summary 335
Part IV. Maintaining an Analytics Solution
14. Implement Security and Governance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
A Layered Access Security Model 339
Entra ID 340
Network Security 340
Inbound Protection 341
Managed Virtual Networks 349
Outbound Protection 349
Workspace Roles 354
Item Permissions 355
Data-Level Security 358
Data Encryption at Rest 359
Governance 359
Admin Portal 360
Microsoft Purview 360
OneLake Catalog 360
xii | Table of Contents
Admin Monitoring Workspace 360
Monitoring Hub 361
Summary 361
15. Manage the Analytics Development Lifecycle. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
Setting the Stage: Application Lifecycle Management, Continuous Integration,
Continuous Delivery, and What Fabric Actually Gives You 363
Git Integration: The Continuous-Integration Half of the Story 364
Connecting a Workspace and the Initial Synchronization 364
Supported Items 367
Branches: The Mental Model That Actually Works 367
Branching Out to a New Workspace and Selective Branching 367
Compare Code Changes: Finally, a Real Diff Experience 368
Folders in Git 369
Permissions: Who Can Do What 369
Deployment Pipelines: The Continuous Delivery Half 369
Stages, Pairing, and Autobinding 371
Full, Selective, and Backward Deployment 371
Deployment Rules: The Configuration Layer 372
The Permission Model 373
What Gets Copied and What Doesn’t 373
Variable Libraries: The Parameterization We’ve Been Waiting For 373
Creating a Variable Library 374
Value Sets: The Environment Switch 374
Consuming Variables in Other Items 375
The New Item Reference Variable Type 376
Variable Libraries Versus Deployment Rules: When to Use Which 376
Continuous Integration/Continuous Deployment Across the Data Engineering
Surface 377
Notebooks and Environments 377
Data Pipelines 377
Dataflow Gen2 377
User Data Functions: Git Integration, Deployment, and the Moving Parts 377
Other Item Types 379
The Fabric Command-Line Interface: Automation from Your Terminal 379
The Filesystem Mental Model 379
Installing and Authenticating 380
The fab deploy Command: Continuous Integration/Continuous Deployment in
One Command 381
Beyond Deployment 381
Putting It Together: A Reference Shape 381
Summary 383
Table of Contents | xiii
Part V. Putting It All Together
16. Semantic Link. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Visualize Dependencies in the Semantic Model 389
Optimize Semantic Models with Best Practices Analyzer Rules 390
Translate Semantic Models 391
Migrate Existing Semantic Models to Direct Lake 392
Augment the Gold Layer 393
Summary 395
17. Create Your Dream Fabric Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Project 1: A Logistics Group Goes from Spreadsheets to Fabric 397
Decision 1: The Silver Layer Should Be a Warehouse, Not a Lakehouse 398
Decision 2: Keep the Bronze Layer in a Lakehouse;
Just Put It Next to the Warehouse 399
Decision 3: Workspaces Organized by Domain 401
Decision 4: The Three-Capacity Model 405
Decision 5: Semantic Model Strategy—Who Builds, Who Certifies, Who Consumes 406
What We’d Do Differently If We Started Over 408
Project 2: A Sports Organization That Loves Data 408
What Is SL Benfica, and Why This Case Study? 408
Choosing Fabric and the Operating Model 409
Ingestion: Standardize the Outcome, Not Every Connector 410
Real-Time Intelligence for Operations, Commercial Moments, and Sport 411
Gold Autonomy and Governed Distribution 412
Capacity, Security, and Delivery at Enterprise Scale 413
Outcomes, Lessons, and What Comes Next 414
Synthesis: A Way of Arriving at the Right Shape 415
Question 1: Who Owns the Platform After We Leave? 415
Question 2: Where Does the Data Live Today, and Exactly How Do We Read It? 416
Question 3: What’s the Freshness Requirement, Honestly? 416
Question 4: What’s the Blast Radius of a Bad Change? 416
Question 5: What’s the Constraint That’s Not on the Slide? 417
What Gets Decided First, What Stays Reversible 417
A Closing Thought 418
Summary 418
Appendix: Going Above and Beyond with External Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
xiv | Table of Contents