The Modern Data Warehouse in Azure: Building with Speed and Agility on Microsoft’s Cloud Platform

Description

Build a modern data warehouse on Microsoft's Azure Platform that is flexible, adaptable, and fast—fast to snap together, reconfigure, and fast at delivering results to drive good decision making in your business. Gone are the days when data warehousing projects were lumbering dinosaur-style projects that took forever, drained budgets, and produced business intelligence (BI) just in time to tell you what to do 10 years ago. This book will show you how to assemble a data warehouse solution like a jigsaw puzzle by connecting specific Azure technologies that address your own needs and bring value to your business. You will see how to implement a range of architectural patterns using batches, events, and streams for both data lake technology and SQL databases. You will discover how to manage metadata and automation to accelerate the development of your warehouse while establishing resilience at every level. And you will know how to feed downstream analytic solutions such as Power BI and Azure Analysis Services to empower data-driven decision making that drives your business forward toward a pattern of success. This book teaches you how to employ the Azure platform in a strategy to dramatically improve implementation speed and flexibility of data warehousing systems. You will know how to make correct decisions in design, architecture, and infrastructure such as choosing which type of SQL engine (from at least three options) best meets the needs of your organization. You also will learn about ETL/ELT structure and the vast number of accelerators and patterns that can be used to aid implementation and ensure resilience. Data warehouse developers and architects will find this book a tremendous resource for moving their skills into the future through cloud-based implementations. What You Will Learn Choose the appropriate Azure SQL engine for implementing a given data warehouse Develop smart, reusable ETL/ELT processes that are resilient and easily maintained Automate mundane development tasks through tools such as PowerShell Ensure consistency of data by creating and enforcing data contracts Explore streaming and event-driven architectures for data ingestion Create advanced staging layers using Azure Data Lake Gen 2 to feed your data warehouse Who This Book Is For Data warehouse or ETL/ELT developers who wish to implement a data warehouse project in the Azure cloud, and developers currently working in on-premise environments who want to move to the cloud, and for developers with Azure experience looking to tighten up their implementation and consolidate their knowledge Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Chapter 1: The Rise of the Modern Data Warehouse Getting Started Multi-region Support Resource Groups and Tagging Azure Security Tools of the Trade Glossary of Terms Naming Conventions Chapter 2: The SQL Engine The Four Vs Azure Synapse Analytics Understanding Distributions The First Problem ROUND ROBIN Distribution HASH Distribution The Distribution Column How to Check if You Have the Right Column REPLICATED Distribution Resource Management Resource Classes Static Resource Classes Dynamic Resource Classes Pausing and Resuming the Warehouse Workload Management PolyBase Azure SQL Database The Cloud-Based OLTP Engine The Benefits of Azure SQL Database Improved Concurrency Trickle-Fed Data Warehouses Managing Slowly Changing Dimensions Intelligent Query Processing and Tuning Automatic Tuning Adaptive Query Processing Batch Mode Memory Grant Feedback Adaptive Joins Interleaved Execution Hyperscale The Hyperscale Architecture Accelerated Disaster Recovery Azure SQL Deployment Options Azure SQL Database Managed Instances Azure SQL Database Elastic Pools Azure SQL Database V-Core Tiers Azure Synapse Analytics vs. Azure SQL Database The Right Type of Data The Size of the Data The Frequency of the Data The Availability of the Data The Integration of Data Chapter 3: The Integration Engine Introduction to Azure Data Factory The Data Factory Building Blocks Linked Services Integration Runtimes Self-Hosted Integration Runtime Azure SSIS Integration Runtime Triggers Datasets Pipelines and Activities Activity Types External Compute Activities Internal Activities Iteration and Conditional Activities Web Activities Output Constraints Implementing Azure Data Factory Security in Azure Data Factory Using the Managed Service Identity Source Control of Azure Data Factory Templates Solution Structure Getting Started with Azure Data Factory Create Linked Services Creating Datasets Creating Pipelines Debugging Your Pipelines Monitoring Your Pipelines Parameter-Driven Pipelines Getting Started with Parameters Using the Lookup Activity Getting Started with the Lookup Activity Additional Azure Data Factory Elements Additional Invocation Methods Mapping Data Flows Multiple Inputs and Outputs Schema Modifier Row Modifier Execute Mapping Data Flows Azure Data Factory Processing Patterns Linear Pipelines Parent-Child Processing Iterative Parent-Child Processing Dynamic Column Mappings Partitioning Datasets Chapter 4: The Ingestion Architecture Layers of Curation The Raw Layer The Clean Layer The Transformed Layer Understanding Ingestion Architecture Batch Ingestion The Risks and Opportunities of Batch Ingestion The ETL Window The ETL Anti-window Failure Investigation and Troubleshooting The Batch Ingestion Tools Batch Ingestion for Azure Synapse Analytics Create External Table As Select (CETAS) Event Ingestion The Risks and Opportunities of Event-Based Ingestion Implementing Event Ingestion Decoupled Processing Listening for Events Queuing Events Event Ingestion for Azure Synapse Analytics Event Ingestion for Azure SQL Database Stream Ingestion The Risks and Opportunities of Stream Ingestion Implementing Stream Ingestion Stream Ingestion with Azure Event Hub’s and Stream Analytics Jobs Stream Ingestion for Azure Blob Storage Stream Ingestion for Azure SQL Database The Lambda Architecture Blending Streams and Batches The Serving Layer Assessing the Approach Chapter 5: The Role of the Data Lake The Modern Enterprise and Its Data Lake Azure Data Lake Technology Azure Data Lake Gen 1 Azure Blob Storage Azure Data Lake Gen 2 Planning the Enterprise Data Lake Storing Raw Data Storing Cleaned Data Storing Transformed Data Facilitating Experimentation Implementing the Enterprise Data Lake Security Configuration in Azure Data Lake Applying Security in Azure Data Lake Gen 2 Implementing a Raw Directory Partitioning Choosing a File Format Implementing a Clean Directory Cleaning Within a Database Cleaning Within a Data Lake Cleaning Within Azure Data Factory Implementing a Transformed Directory Example Polyglot Architectures Example One Example Two Example Three Example Four Chapter 6: The Role of the Data Contract What Is a Data Contract? Working with Data Contracts Designing Data Contracts Generating Data Contracts Validating Data Contacts Storing Data Contracts Modifying Data Contracts Integrating Data Contracts Fetching Metadata Fetching Orchestration Metadata Utilizing Orchestration Metadata Fetching Entity Metadata Utilizing Entity Metadata Code Generation Getting Started with Code Generation Harmonizing Schema Evolution Chapter 7: Logging, Auditing, and Resilience Logging the Data Movement Process Basic Logging Requirements Where to Store Your Logs Events to Be Logged Extended Logging Capabilities Aggregating Your Logs Auditing the Data Movement Process Basic Auditing Requirements Auditing Data Volumes Auditing Processing Times Storing High Watermarks Incorporating Resilience into the Data Movement Process Basic Resiliency Using Metadata for Troubleshooting Creating Alerts Using Azure Data Factory Alert Rules Creating Custom Alerts from Azure Data Factory Extending Resiliency Utilizing Data Factory Fault Tolerance Checking File Structure Using Data Factory Creating Alerts from Skipped Rows Monitoring the Data Movement Process Chapter 8: Using Scripting and Automation The Power of PowerShell Commonly Used Scripts Code Generation Invoke Data Factory Pipeline Recurse Data Lake Structures Chapter 9: Beyond the Modern Data Warehouse Microsoft Power BI Working with Power BI Building a Power BI Report Publish Report to Power BI Service Azure Analysis Services The Basics of Azure Analysis Services Analysis Services as a Semantic Layer Analysis Services Security Model The Vertipaq Engine Creating an Analysis Services Project Create Analysis Objects Create a Calculated Column Create a Measure Create a KPI Create a Hierarchy Create a Perspective Creating Roles (RBAC) Deploy Analysis Services to Azure Processing an Azure Analysis Services Model Azure Cosmos DB The Cosmos DB Architecture Horizontal Partitioning Resource Units Consistency Write Data to Azure Cosmos DB Index