MCA Microsoft Certified Associate Azure Data Engineer Study Guide

ABOUT THE AUTHOR Benjamin Perkins is currently employed at Microsoft in Munich, Germany, as a Senior Escalation Engineer on the Azure team. He is a C# programming expert and cloud engineer who has been working professionally in the IT industry for almost three decades. His roles in IT have spanned the entire spectrum including programmer, system architect, technical support engineer, team leader, and mid-level management. While employed at Hewlett-Packard and Compaq Computer Corporation, he received numerous awards, degrees, and certifications.

• Introduction xxviiPart I Azure Data Engineer Certification and Azure Products 1Chapter 1 Gaining the Azure Data Engineer Associate Certification 3The Journey to Certification 7How to Pass Exam DP- 203 8Understanding the Exam Expectations and Requirements 9Use Azure Daily 17Read Azure Articles to Stay Current 17Have an Understanding of All Azure Products 20Azure Product Name Recognition 21Azure Data Analytics 23Azure Synapse Analytics 23Azure Databricks 26Azure HDInsight 28Azure Analysis Services 30Azure Data Factory 31Azure Event Hubs 33Azure Stream Analytics 34Other Products 35Azure Storage Products 36Azure Data Lake Storage 37Azure Storage 40Other Products 42Azure Databases 43Azure Cosmos DB 43Azure SQL Server Products 46Additional Azure Databases 46Other Products 47Azure Security 48Azure Active Directory 48Role- Based Access Control 51Attribute- Based Access Control 53Azure Key Vault 53Other Products 55Azure Networking 56Virtual Networks 56Other Products 59Azure Compute 59Azure Virtual Machines 59Azure Virtual Machine Scale Sets 60Azure App Service Web Apps 60Azure Functions 60Azure Batch 60Azure Management and Governance 60Azure Monitor 61Azure Purview 61Azure Policy 62Azure Blueprints (Preview) 62Azure Lighthouse 62Azure Cost Management and Billing 62Other Products 63Summary 64Exam Essentials 64Review Questions 66Chapter 2 CREATE DATABASE dbName; GO 69The Brainjammer 70A Historical Look at Data 71Variety 73Velocity 74Volume 74Data Locations 74Data File Formats 75Data Structures, Types, and Concepts 83Data Structures 83Data Types and Management 92Data Concepts 95Data Programming and Querying for Data Engineers 125Data Programming 126Querying Data 143Understanding Big Data Processing 169Big Data Stages 169Etl, Elt, Eltl 174Analytics Types 175Big Data Layers 176Summary 177Exam Essentials 177Review Questions 179Part II Design and Implement Data Storage 181Chapter 3 Data Sources and Ingestion 183Where Does Data Come From? 185Design a Data Storage Structure 189Design an Azure Data Lake Solution 190Recommended File Types for Storage 198Recommended File Types for Analytical Queries 199Design for Efficient Querying 200Design for Data Pruning 203Design a Folder Structure That Represents the Levels of Data Transformation 203Design a Distribution Strategy 205Design a Data Archiving Solution 206Design a Partition Strategy 207Design a Partition Strategy for Files 209Design a Partition Strategy for Analytical Workloads 210Design a Partition Strategy for Efficiency and Performance 211Design a Partition Strategy for Azure Synapse Analytics 211Identify When Partitioning Is Needed in Azure Data Lake Storage Gen 2 212Design the Serving/Data Exploration Layer 213Design Star Schemas 214Design Slowly Changing Dimensions 215Design a Dimensional Hierarchy 219Design a Solution for Temporal Data 220Design for Incremental Loading 222Design Analytical Stores 223Design Metastores in Azure Synapse Analytics and Azure Databricks 224The Ingestion of Data into a Pipeline 228Azure Synapse Analytics 228Azure Data Factory 268Azure Databricks 275Event Hubs and IoT Hub 301Azure Stream Analytics 303Apache Kafka for HDInsight 314Migrating and Moving Data 316Summary 317Exam Essentials 317Review Questions 319Chapter 4 The Storage of Data 321Implement Physical Data Storage Structures 322Implement Compression 322Implement Partitioning 325Implement Sharding 328Implement Different Table Geometries with Azure Synapse Analytics Pools 329Implement Data Redundancy 331Implement Distributions 341Implement Data Archiving 342Azure Synapse Analytics Develop Hub 346Implement Logical Data Structures 360Build a Temporal Data Solution 361Build a Slowly Changing Dimension 365Build a Logical Folder Structure 368Build External Tables 369Implement File and Folder Structures for Efficient Querying and Data Pruning 372Implement a Partition Strategy 375Implement a Partition Strategy for Files 376Implement a Partition Strategy for Analytical Workloads 377Implement a Partition Strategy for Streaming Workloads 378Implement a Partition Strategy for Azure Synapse Analytics 378Design and Implement the Data Exploration Layer 379Deliver Data in a Relational Star Schema 379Deliver Data in Parquet Files 385Maintain Metadata 386Implement a Dimensional Hierarchy 386Create and Execute Queries by Using a Compute Solution That Leverages SQL Serverless and Spark Cluster 388Recommend Azure Synapse Analytics Database Templates 389Implement Azure Synapse Analytics Database Templates 389Additional Data Storage Topics 390Storing Raw Data in Azure Databricks for Transformation 390Storing Data Using Azure HDInsight 392Storing Prepared, Trained, and Modeled Data 393Summary 394Exam Essentials 395Review Questions 396Part III Develop Data Processing 399Chapter 5 Transform, Manage, and Prepare Data 401Chapter 6 Ingest and Transform Data 402Transform Data Using Azure Synapse Pipelines 404Transform Data Using Azure Data Factory 410Transform Data Using Apache Spark 414Transform Data Using Transact- SQL 429Transform Data Using Stream Analytics 431Cleanse Data 433Split Data 435Shred JSON 439Encode and Decode Data 445Configure Error Handling for the Transformation 450Normalize and Denormalize Values 451Transform Data by Using Scala 461Perform Exploratory Data Analysis 463Transformation and Data Management Concepts 473Transformation 473Data Management 480Azure Databricks 481Data Modeling and Usage 485Data Modeling with Machine Learning 486Usage 494Summary 500Exam Essentials 500Review Questions 502Create and Manage Batch Processing and Pipelines 505Design and Develop a Batch Processing Solution 507Design a Batch Processing Solution 510Develop Batch Processing Solutions 512Create Data Pipelines 538Handle Duplicate Data 560Handle Missing Data 569Handle Late- Arriving Data 571Upsert Data 572Configure the Batch Size 578Configure Batch Retention 581Design and Develop Slowly Changing Dimensions 582Design and Implement Incremental Data Loads 583Integrate Jupyter/IPython Notebooks into a Data Pipeline 590Chapter 7 Revert Data to a Previous State 591Handle Security and Compliance Requirements 592Design and Create Tests for Data Pipelines 593Scale Resources 593Design and Configure Exception Handling 593Debug Spark Jobs Using the Spark UI 594Implement Azure Synapse Link and Query the Replicated Data 594Use PolyBase to Load Data to a SQL Pool 595Read from and Write to a Delta Table 595Manage Batches and Pipelines 596Trigger Batches 597Schedule Data Pipelines 597Validate Batch Loads 598Implement Version Control for Pipeline Artifacts 604Manage Data Pipelines 607Manage Spark Jobs in a Pipeline 609Handle Failed Batch Loads 610Summary 610Exam Essentials 611Review Questions 612Design and Implement a Data Stream Processing Solution 615Develop a Stream Processing Solution 617Design a Stream Processing Solution 618Create a Stream Processing Solution 630Process Time Series Data 657Design and Create Windowed Aggregates 658Process Data Within One Partition 661Process Data Across Partitions 663Upsert Data 665Handle Schema Drift 674Configure Checkpoints/Watermarking During Processing 680Replay Archived Stream Data 685Design and Create Tests for Data Pipelines 688Monitor for Performance and Functional Regressions 689Optimize Pipelines for Analytical or Transactional Purposes 689Scale Resources 690Design and Configure Exception Handling 691Handle Interruptions 694Ingest and Transform Data 694Transform Data Using Azure Stream Analytics 694Monitor Data Storage and Data Processing 695Monitor Stream Processing 695Summary 695Exam Essentials 696Review Questions 697Part IV Secure, Monitor, and Optimize Data Storage and Data Processing 699Chapter 8 Keeping Data Safe and Secure 701Design Security for Data Policies and Standards 702Design a Data Auditing Strategy 711Design a Data Retention Policy 716Design for Data Privacy 717Design to Purge Data Based on Business Requirements 719Design Data Encryption for Data at Rest and in Transit 719Design Row- Level and Column- Level Security 722Design a Data Masking Strategy 723Design Access Control for Azure Data Lake Storage Gen 2 724Implement Data Security 730Implement a Data Auditing Strategy 731Manage Sensitive Information 739Implement a Data Retention Policy 745Encrypt Data at Rest and in Motion 748Implement Row- Level and Column- Level Security 749Implement Data Masking 753Manage Identities, Keys, and Secrets Across Different Data Platform Technologies 755Implement Access Control for Azure Data Lake Storage Gen 2 765Implement Secure Endpoints (Private and Public) 772Implement Resource Tokens in Azure Databricks 778Load a DataFrame with Sensitive Information 779Write Encrypted Data to Tables or Parquet Files 780Develop a Batch Processing Solution 781Handle Security and Compliance Requirements 782Design and Implement the Data Exploration Layer 784Browse and Search Metadata in Microsoft Purview Data Catalog 784Push New or Updated Data Lineage to Microsoft Purview 785Summary 786Exam Essentials 787Review Questions 789Chapter 9 Monitoring Azure Data Storage and Processing 791Monitoring Data Storage and Data Processing 793Implement Logging Used by Azure Monitor 793Configure Monitoring Services 799Understand Custom Logging Options 821Measure Query Performance 822Monitor Data Pipeline Performance 823Monitor Cluster Performance 824Measure Performance of Data Movement 824Interpret Azure Monitor Metrics and Logs 825Monitor and Update Statistics about Data Across a System 828Schedule and Monitor Pipeline Tests 830Interpret a Spark Directed Acyclic Graph 830Monitor Stream Processing 832Implement a Pipeline Alert Strategy 832Develop a Batch Processing Solution 832Design and Create Tests for Data Pipelines 832Develop a Stream Processing Solution 837Monitor for Performance and Functional Regressions 837Design and Create Tests for Data Pipelines 838Azure Monitoring Overview 841Azure Batch 841Azure Key Vault 842Azure SQL 843Summary 844Exam Essentials 844Review Questions 846Chapter 10 Troubleshoot Data Storage Processing 849Optimize and Troubleshoot Data Storage and Data Processing 851Optimize Resource Management 854Compact Small Files 857Handle Skew in Data 859Handle Data Spill 860Find Shuffling in a Pipeline 862Tune Shuffle Partitions 864Tune Queries by Using Indexers 869Tune Queries by Using Cache 876Optimize Pipelines for Analytical or Transactional Purposes 877Optimize Pipeline for Descriptive versus Analytical Workloads 886Troubleshoot a Failed Spark Job 888Troubleshoot a Failed Pipeline Run 890Rewrite User- Defined Functions 899Design and Develop a Batch Processing Solution 901Design and Configure Exception Handling 902Debug Spark Jobs by Using the Spark UI 902Scale Resources 902Monitor Batches and Pipelines 904Handle Failed Batch Loads 904Design and Develop a Stream Processing Solution 905Optimize Pipelines for Analytical or Transactional Purposes 905Handle Interruptions 906Scale Resources 908Summary 909Exam Essentials 910Review Questions 912Appendix Answers to Review Questions 915Chapter 1: Gaining the Azure Data Engineer Associate Certification 916Chapter 2: CREATE DATABASE dbName; GO 916Chapter 3: Data Sources and Ingestion 917Chapter 4: The Storage of Data 918Chapter 5: Transform, Manage, and Prepare Data 918Chapter 6. Create and Manage Batch Processing and Pipelines 919Chapter 7: Design and Implement a Data Stream Processing Solution 920Chapter 8: Keeping Data Safe and Secure 921Chapter 9: Monitoring Azure Data Storage and Processing 921Chapter 10: Troubleshoot Data Storage Processing 922Index 925