MCA Microsoft Certified Associate Azure Data Engineer Study Guide
Description
In the MCA Microsoft Certified Associate Azure Data Engineer Study Guide: Exam DP-203, accomplished data engineer and tech educator Benjamin Perkins delivers a hands-on, practical guide to preparing for the challenging Azure Data Engineer certification and for a new career in an exciting and growing field of tech.
In the book, you'll explore all the objectives covered on the DP-203 exam while learning the job roles and responsibilities of a newly minted Azure data engineer. From integrating, transforming, and consolidating data from various structured and unstructured data systems into a structure that is suitable for building analytics solutions, you'll get up to speed quickly and efficiently with Sybex's easy-to-use study aids and tools.
This Study Guide also offers:
* Career-ready advice for anyone hoping to ace their first data engineering job interview and excel in their first day in the field
* Indispensable tips and tricks to familiarize yourself with the DP-203 exam structure and help reduce test anxiety
* Complimentary access to Sybex's expansive online study tools, accessible across multiple devices, and offering access to hundreds of bonus practice questions, electronic flashcards, and a searchable, digital glossary of key terms
A one-of-a-kind study aid designed to help you get straight to the crucial material you need to succeed on the exam and on the job, the MCA Microsoft Certified Associate Azure Data Engineer Study Guide: Exam DP-203 belongs on the bookshelves of anyone hoping to increase their data analytics skills, advance their data engineering career with an in-demand certification, or hoping to make a career change into a popular new area of tech.
More details
Other editions
Additional editions
Person
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.
Content
Introduction xxvii
Part I Azure Data Engineer Certification and Azure Products 1
Chapter 1 Gaining the Azure Data Engineer Associate Certification 3
The Journey to Certification 7
How to Pass Exam DP- 203 8
Understanding the Exam Expectations and Requirements 9
Use Azure Daily 17
Read Azure Articles to Stay Current 17
Have an Understanding of All Azure Products 20
Azure Product Name Recognition 21
Azure Data Analytics 23
Azure Synapse Analytics 23
Azure Databricks 26
Azure HDInsight 28
Azure Analysis Services 30
Azure Data Factory 31
Azure Event Hubs 33
Azure Stream Analytics 34
Other Products 35
Azure Storage Products 36
Azure Data Lake Storage 37
Azure Storage 40
Other Products 42
Azure Databases 43
Azure Cosmos DB 43
Azure SQL Server Products 46
Additional Azure Databases 46
Other Products 47
Azure Security 48
Azure Active Directory 48
Role- Based Access Control 51
Attribute- Based Access Control 53
Azure Key Vault 53
Other Products 55
Azure Networking 56
Virtual Networks 56
Other Products 59
Azure Compute 59
Azure Virtual Machines 59
Azure Virtual Machine Scale Sets 60
Azure App Service Web Apps 60
Azure Functions 60
Azure Batch 60
Azure Management and Governance 60
Azure Monitor 61
Azure Purview 61
Azure Policy 62
Azure Blueprints (Preview) 62
Azure Lighthouse 62
Azure Cost Management and Billing 62
Other Products 63
Summary 64
Exam Essentials 64
Review Questions 66
Chapter 2 CREATE DATABASE dbName; GO 69
The Brainjammer 70
A Historical Look at Data 71
Variety 73
Velocity 74
Volume 74
Data Locations 74
Data File Formats 75
Data Structures, Types, and Concepts 83
Data Structures 83
Data Types and Management 92
Data Concepts 95
Data Programming and Querying for Data Engineers 125
Data Programming 126
Querying Data 143
Understanding Big Data Processing 169
Big Data Stages 169
Etl, Elt, Eltl 174
Analytics Types 175
Big Data Layers 176
Summary 177
Exam Essentials 177
Review Questions 179
Part II Design and Implement Data Storage 181
Chapter 3 Data Sources and Ingestion 183
Where Does Data Come From? 185
Design a Data Storage Structure 189
Design an Azure Data Lake Solution 190
Recommended File Types for Storage 198
Recommended File Types for Analytical Queries 199
Design for Efficient Querying 200
Design for Data Pruning 203
Design a Folder Structure That Represents the Levels of Data Transformation 203
Design a Distribution Strategy 205
Design a Data Archiving Solution 206
Design a Partition Strategy 207
Design a Partition Strategy for Files 209
Design a Partition Strategy for Analytical Workloads 210
Design a Partition Strategy for Efficiency and Performance 211
Design a Partition Strategy for Azure Synapse Analytics 211
Identify When Partitioning Is Needed in Azure Data Lake Storage Gen 2 212
Design the Serving/Data Exploration Layer 213
Design Star Schemas 214
Design Slowly Changing Dimensions 215
Design a Dimensional Hierarchy 219
Design a Solution for Temporal Data 220
Design for Incremental Loading 222
Design Analytical Stores 223
Design Metastores in Azure Synapse Analytics and Azure Databricks 224
The Ingestion of Data into a Pipeline 228
Azure Synapse Analytics 228
Azure Data Factory 268
Azure Databricks 275
Event Hubs and IoT Hub 301
Azure Stream Analytics 303
Apache Kafka for HDInsight 314
Migrating and Moving Data 316
Summary 317
Exam Essentials 317
Review Questions 319
Chapter 4 The Storage of Data 321
Implement Physical Data Storage Structures 322
Implement Compression 322
Implement Partitioning 325
Implement Sharding 328
Implement Different Table Geometries with Azure Synapse Analytics Pools 329
Implement Data Redundancy 331
Implement Distributions 341
Implement Data Archiving 342
Azure Synapse Analytics Develop Hub 346
Implement Logical Data Structures 360
Build a Temporal Data Solution 361
Build a Slowly Changing Dimension 365
Build a Logical Folder Structure 368
Build External Tables 369
Implement File and Folder Structures for Efficient Querying and Data Pruning 372
Implement a Partition Strategy 375
Implement a Partition Strategy for Files 376
Implement a Partition Strategy for Analytical Workloads 377
Implement a Partition Strategy for Streaming Workloads 378
Implement a Partition Strategy for Azure Synapse Analytics 378
Design and Implement the Data Exploration Layer 379
Deliver Data in a Relational Star Schema 379
Deliver Data in Parquet Files 385
Maintain Metadata 386
Implement a Dimensional Hierarchy 386
Create and Execute Queries by Using a Compute Solution That Leverages SQL Serverless and Spark Cluster 388
Recommend Azure Synapse Analytics Database Templates 389
Implement Azure Synapse Analytics Database Templates 389
Additional Data Storage Topics 390
Storing Raw Data in Azure Databricks for Transformation 390
Storing Data Using Azure HDInsight 392
Storing Prepared, Trained, and Modeled Data 393
Summary 394
Exam Essentials 395
Review Questions 396
Part III Develop Data Processing 399
Chapter 5 Transform, Manage, and Prepare Data 401
Chapter 6 Ingest and Transform Data 402
Transform Data Using Azure Synapse Pipelines 404
Transform Data Using Azure Data Factory 410
Transform Data Using Apache Spark 414
Transform Data Using Transact- SQL 429
Transform Data Using Stream Analytics 431
Cleanse Data 433
Split Data 435
Shred JSON 439
Encode and Decode Data 445
Configure Error Handling for the Transformation 450
Normalize and Denormalize Values 451
Transform Data by Using Scala 461
Perform Exploratory Data Analysis 463
Transformation and Data Management Concepts 473
Transformation 473
Data Management 480
Azure Databricks 481
Data Modeling and Usage 485
Data Modeling with Machine Learning 486
Usage 494
Summary 500
Exam Essentials 500
Review Questions 502
Create and Manage Batch Processing and Pipelines 505
Design and Develop a Batch Processing Solution 507
Design a Batch Processing Solution 510
Develop Batch Processing Solutions 512
Create Data Pipelines 538
Handle Duplicate Data 560
Handle Missing Data 569
Handle Late- Arriving Data 571
Upsert Data 572
Configure the Batch Size 578
Configure Batch Retention 581
Design and Develop Slowly Changing Dimensions 582
Design and Implement Incremental Data Loads 583
Integrate Jupyter/IPython Notebooks into a Data Pipeline 590
Chapter 7 Revert Data to a Previous State 591
Handle Security and Compliance Requirements 592
Design and Create Tests for Data Pipelines 593
Scale Resources 593
Design and Configure Exception Handling 593
Debug Spark Jobs Using the Spark UI 594
Implement Azure Synapse Link and Query the Replicated Data 594
Use PolyBase to Load Data to a SQL Pool 595
Read from and Write to a Delta Table 595
Manage Batches and Pipelines 596
Trigger Batches 597
Schedule Data Pipelines 597
Validate Batch Loads 598
Implement Version Control for Pipeline Artifacts 604
Manage Data Pipelines 607
Manage Spark Jobs in a Pipeline 609
Handle Failed Batch Loads 610
Summary 610
Exam Essentials 611
Review Questions 612
Design and Implement a Data Stream Processing Solution 615
Develop a Stream Processing Solution 617
Design a Stream Processing Solution 618
Create a Stream Processing Solution 630
Process Time Series Data 657
Design and Create Windowed Aggregates 658
Process Data Within One Partition 661
Process Data Across Partitions 663
Upsert Data 665
Handle Schema Drift 674
Configure Checkpoints/Watermarking During Processing 680
Replay Archived Stream Data 685
Design and Create Tests for Data Pipelines 688
Monitor for Performance and Functional Regressions 689
Optimize Pipelines for Analytical or Transactional Purposes 689
Scale Resources 690
Design and Configure Exception Handling 691
Handle Interruptions 694
Ingest and Transform Data 694
Transform Data Using Azure Stream Analytics 694
Monitor Data Storage and Data Processing 695
Monitor Stream Processing 695
Summary 695
Exam Essentials 696
Review Questions 697
Part IV Secure, Monitor, and Optimize Data Storage and Data Processing 699
Chapter 8 Keeping Data Safe and Secure 701
Design Security for Data Policies and Standards 702
Design a Data Auditing Strategy 711
Design a Data Retention Policy 716
Design for Data Privacy 717
Design to Purge Data Based on Business Requirements 719
Design Data Encryption for Data at Rest and in Transit 719
Design Row- Level and Column- Level Security 722
Design a Data Masking Strategy 723
Design Access Control for Azure Data Lake Storage Gen 2 724
Implement Data Security 730
Implement a Data Auditing Strategy 731
Manage Sensitive Information 739
Implement a Data Retention Policy 745
Encrypt Data at Rest and in Motion 748
Implement Row- Level and Column- Level Security 749
Implement Data Masking 753
Manage Identities, Keys, and Secrets Across Different Data Platform Technologies 755
Implement Access Control for Azure Data Lake Storage Gen 2 765
Implement Secure Endpoints (Private and Public) 772
Implement Resource Tokens in Azure Databricks 778
Load a DataFrame with Sensitive Information 779
Write Encrypted Data to Tables or Parquet Files 780
Develop a Batch Processing Solution 781
Handle Security and Compliance Requirements 782
Design and Implement the Data Exploration Layer 784
Browse and Search Metadata in Microsoft Purview Data Catalog 784
Push New or Updated Data Lineage to Microsoft Purview 785
Summary 786
Exam Essentials 787
Review Questions 789
Chapter 9 Monitoring Azure Data Storage and Processing 791
Monitoring Data Storage and Data Processing 793
Implement Logging Used by Azure Monitor 793
Configure Monitoring Services 799
Understand Custom Logging Options 821
Measure Query Performance 822
Monitor Data Pipeline Performance 823
Monitor Cluster Performance 824
Measure Performance of Data Movement 824
Interpret Azure Monitor Metrics and Logs 825
Monitor and Update Statistics about Data Across a System 828
Schedule and Monitor Pipeline Tests 830
Interpret a Spark Directed Acyclic Graph 830
Monitor Stream Processing 832
Implement a Pipeline Alert Strategy 832
Develop a Batch Processing Solution 832
Design and Create Tests for Data Pipelines 832
Develop a Stream Processing Solution 837
Monitor for Performance and Functional Regressions 837
Design and Create Tests for Data Pipelines 838
Azure Monitoring Overview 841
Azure Batch 841
Azure Key Vault 842
Azure SQL 843
Summary 844
Exam Essentials 844
Review Questions 846
Chapter 10 Troubleshoot Data Storage Processing 849
Optimize and Troubleshoot Data Storage and Data Processing 851
Optimize Resource Management 854
Compact Small Files 857
Handle Skew in Data 859
Handle Data Spill 860
Find Shuffling in a Pipeline 862
Tune Shuffle Partitions 864
Tune Queries by Using Indexers 869
Tune Queries by Using Cache 876
Optimize Pipelines for Analytical or Transactional Purposes 877
Optimize Pipeline for Descriptive versus Analytical Workloads 886
Troubleshoot a Failed Spark Job 888
Troubleshoot a Failed Pipeline Run 890
Rewrite User- Defined Functions 899
Design and Develop a Batch Processing Solution 901
Design and Configure Exception Handling 902
Debug Spark Jobs by Using the Spark UI 902
Scale Resources 902
Monitor Batches and Pipelines 904
Handle Failed Batch Loads 904
Design and Develop a Stream Processing Solution 905
Optimize Pipelines for Analytical or Transactional Purposes 905
Handle Interruptions 906
Scale Resources 908
Summary 909
Exam Essentials 910
Review Questions 912
Appendix Answers to Review Questions 915
Chapter 1: Gaining the Azure Data Engineer Associate Certification 916
Chapter 2: CREATE DATABASE dbName; GO 916
Chapter 3: Data Sources and Ingestion 917
Chapter 4: The Storage of Data 918
Chapter 5: Transform, Manage, and Prepare Data 918
Chapter 6. Create and Manage Batch Processing and Pipelines 919
Chapter 7: Design and Implement a Data Stream Processing Solution 920
Chapter 8: Keeping Data Safe and Secure 921
Chapter 9: Monitoring Azure Data Storage and Processing 921
Chapter 10: Troubleshoot Data Storage Processing 922 Index 925
Introduction
A long time ago, I was sitting at my desk happily coding my Active Server Page (ASP) and COM component, when someone approached me and asked if I knew anything about databases. Without even a pause, I answered a confident yes, most people in IT know "something" about databases, right? Well, it turned out that a big project was starting, and they needed someone to create and manage a database. I acquired a server, installed a relational database management system (RDMBS), and executed CREATE DATABASE dbName; GO. And the rest is history. I like to call that out because these days, most of the data storage architecture already exists when you start the job. You must learn what someone else created. You experience problems but do not know why, because a lot happened before you started.
The new emerging technology called big data is providing a rare opportunity, kind of like the one I had. The opportunity is to build and/or be involved in creating an IT data analytics solution from the beginning. Being the person or the team who builds the framework and foundation of what could become a system that shapes the future of a company is career-altering. The experience is a differentiator that stays with you for the rest of your career, as it has in mine. But it could also be a catastrophe for numerous reasons, such as not being able to scale, being too hard to make changes, and not being reliable.
I must admit that during the early stages I re-created that database numerous times, dropping and re-creating tables, removing primary and foreign keys, changing indexes, reconfiguring triggers, and tuning stored procedures. I knew this was necessary in numerous scenarios because I made all the configurations myself and felt it was easier to start over than it was to troubleshoot further or make a questionable configuration that could have harsh consequences later. As the project matured, it became much more difficult and impactful to make changes; the design could no longer be changed; it was go time, do or die, succeed or fail.
Building the foundation of a project is great, though it can be a bit overwhelming, and at times you might feel you are guessing or simply bulldozing full speed ahead. Those feelings are normal, and some can end up defining you, destroying you, or making you stronger. The data analytics system you design must be able to scale, change, and provide dependable data on request; anything else will ultimately fail. This book can help you learn about scalability, flexibility, and reliability, which are areas that ultimately determine the success of a data analytics solution.
Who This Book Is For
This book is for anyone who wants to learn about Microsoft Azure products and features and, ultimately, to attain the Azure Data Engineer Associate certification. This book is not intended for absolute beginners, although beginners may gain some greater insights into Azure and how to consume and configure its products and services. Gaining the Azure Data Engineer Associate certification means that you can design, develop, implement, monitor, and optimize data solutions using the following:
- Azure Synapse Analytics
- Azure Data Factory (ADF)
- Azure Databricks
- Azure Data Lake Storage (ADLS)
- Azure Storage
- Azure SQL
- Azure Cosmos DB
- Apache Spark
- HDInsight
- Azure Stream Analytics
- Azure Event Hubs / IoT Hub
- Azure Batch
In addition understanding these Azure data products, you need to have some additional knowledge and experience with the following and many other related and dependent Azure products:
- Microsoft Purview
- Azure Virtual Machines
- Azure Functions
- Azure App Service
- Azure DevOps
- Azure Key Vault
- Azure Policy
- Azure Active Directory
- Azure Managed Identity
- Azure Monitor
- Azure Migrate
- Azure Virtual Network
That is a very broad range of topics, and the number of possible scenarios in which to apply them is equally as great. This book will provide insights into each of those topics, plus numerous others, but you are expected to have some basic, preexisting experience with them.
What This Book Covers
This book covers everything you need to know to greatly increase the probability of passing the Azure Data Engineer Associate exam. Of most importance, however, this book will help you design, implement, and support the ingestion, preparation, analysis, and presentation of data. Which of those two scenarios is most important to you? Hopefully both, because that is the goal and purpose of this book. You will learn about many Azure products and features, including Azure Synapse Analytics, Azure SQL, Azure Databricks, Azure Data Factory, Azure security, Azure networking, Azure compute, Azure datastores and storage, Azure messaging services, Azure migration tools, Azure monitoring tools and Azure recovery tools. In addition to learning about what these products and features are and do, you will perform some real-world exercises to implement and use many of them.
How This Book Is Structured
Good design really is everything. Unless you plan before doing it, it is highly probable that the result will not quite measure up to the expectations. In many instances, even with good planning and thought, the result might not measure up or even be successful. There are many priorities and areas to be concerned with when planning a big project. The same is true when you are migrating existing on-premises workloads to Azure or creating new applications and infrastructure directly on Azure. In both scenarios, security, networking, compute, and data storage all come into focus. The chapters in this book are provided in the order of priority. When planning your migration or deployments, make sure to include each of the phases. The order in which those IT components are analyzed, designed, and implemented is important and is the reason the book is constructed in this way.
- Part I: Azure Data Engineer Certification and Azure Products
- Part II: Design and Implement Data Storage
- Part III: Develop Data Processing
- Part IV: Secure, Monitor, and Optimize Data Storage and Data Processing
Security is by far the most important point of concentration. The network needs to be secured before you place your workloads into it. Then your data, compliance and governance, messaging concepts, development concepts, initial application deployment, and updates cannot be ignored or missed. Once your application is deployed, its lifecycle is really just the beginning. Monitoring it and having a failover and disaster recovery plan designed and tested are necessities for production IT solutions.
Following the design pattern laid out by the chapter flow will help you become a great Azure Data Engineer. Note that when you take the Data Engineering on Microsoft Azure exam, you sign a nondisclosure agreement (NDA) stating that you will not discuss the questions or any of the content of the exam. That is very important so that the integrity and value of the credential you gain when passing the exam maintains its prestige. This book will help you learn the skills and gain the experience an Azure Data Engineer should have. By learning and exercising the techniques in this book, your probability of passing the exam will be greatly increased. The point is, the book is geared towards building your experience and skill set on the Azure platform, which will then enable you to gain the certification.
Chapter 1: Gaining the Azure Data Engineer Associate Certification Chapter 1 provides an overview of what it takes to attain the Azure Data Engineer Associate certification. It begins with a quick overview of all Microsoft data-related certifications, such as the Azure Data Scientist and Azure Data Analytics certifications. This way you get good insights into the specific expectations regarding the Azure Data Engineer Associate certification. Comparing different certifications helps you gain a more precise set of Azure Data Engineer skills. You might also consider taking the AZ-900 exam, which is an introduction to Azure certification, and/or the DP-900 exam, which is an introduction to the Azure data service products. Both certifications will benefit your learning progressions in this field, but they are optional and not required for the DP-203 exam. You will also find some tips on how to pass the DP-203 exam, followed by an introduction to many Azure products and features. Although the DP-203 exam focuses on data services such as Azure Synapse Analytics, Azure Data Factory, and Azure Databricks, you must have some knowledge of other Azure products, including Azure Active Directory, Azure Key Vault, Azure Managed Identity, and Azure Stream Analytics, to name a few.
Chapter 2: CREATE DATABASE dbName; GO The first few chapters of a book are usually meant to get the reader into the right mindset so that the content in the succeeding chapters can be digested more easily. In this case, however, buckle your seatbelt and get ready to hit the ground running, because there is no time to waste. Chapter 2 briefly introduces the world of data but then dives right into some rather sophisticated data structures, types, and concepts an Azure Data Engineer Associate must know. To some extent, the discussion on those topics should be a refresher for an experienced data engineer who intends on taking the Azure Data...
