Cover
Title Page
Copyright
Acknowledgments
About the Author
About the Technical Editor
Introduction
1. Who Should Read This Book?
2. Interactive Online Learning Environment and Test Bank
3. DP-900 Exam Objectives
4. Domain 1: Describe Core Data Components
5. Domain 2: Describe How to Work with Relational Data on Azure
6. Domain 3: Describe How to Work with Nonrelational Data on Azure
7. Domain 4: Describe an Analytics Workload on Azure
8. Assessment Test
9. Answers to the Assessment Test
Chapter 1: Core Data Concepts
1. Describe Types of Core Data Workloads
2. Describe Data Analytics Core Concepts
3. Summary
4. Exam Essentials
5. Review Questions
Chapter 2: Relational Databases in Azure
1. Relational Database Features
2. Relational Database Offerings in Azure
3. Management Tasks for Relational Databases in Azure
4. Query Techniques for SQL
5. Summary
6. Exam Essentials
7. Review Questions
Chapter 3: Nonrelational Databases in Azure
1. Nonrelational Database Features
2. Azure Cosmos DB
3. Management Tasks for Azure Cosmos DB
4. Summary
5. Exam Essentials
6. Review Questions
Chapter 4: File, Object, and Data Lake Storage
1. File and Object Storage Features
2. Azure Storage
3. Management Tasks for Azure Storage
4. Summary
5. Exam Essentials
6. Review Questions
Chapter 5: Modern Data Warehouses in Azure
1. Analytical Workload Features
2. Modern Data Warehouse Components
3. End-to-End Analytics with Azure Synapse Analytics
4. Summary
5. Exam Essentials
6. Review Questions
Chapter 6: Reporting with Power BI
1. Power BI at a Glance
2. Summary
3. Exam Essentials
4. Review Questions
Appendix: Answers to the Review Questions
1. Chapter 1: Core Data Concepts
2. Chapter 2: Relational Databases in Azure
3. Chapter 3: Nonrelational Databases in Azure
4. Chapter 4: File, Object, and Data Lake Storage
5. Chapter 5: Modern Data Warehouses in Azure
6. Chapter 6: Reporting with Power BI
Index
End User License Agreement

List of Tables

Chapter 2
1. TABLE 2.1 Available Pay-As-You-Go SQL Server images
2. TABLE 2.2 Available bring your own license SQL Server images
3. TABLE 2.3 Azure SQL MI service tier characteristics
4. TABLE 2.4 DTU-based purchasing model service tier characteristics
5. TABLE 2.5 vCore-based purchasing model service tier characteristics
6. TABLE 2.6 Dedicated SQL pool service level objectives
7. TABLE 2.7 Azure Database for MySQL service tier resource options
8. TABLE 2.8 Common sqlcmd switches
9. TABLE 2.9 DDL commands
10. TABLE 2.10 Common SQL data types
11. TABLE 2.11 Common SQL constraints
12. TABLE 2.12 DML commands
Chapter 3
1. TABLE 3.1 Azure Cosmos DB API-specific names for containers
2. TABLE 3.2 Azure Cosmos DB API-specific names for items
3. TABLE 3.3 Azure Table storage vs. Azure Cosmos DB Table API
Chapter 4
1. TABLE 4.1 Storage service URL endpoint patterns
2. TABLE 4.2 Storage account redundancy options
3. TABLE 4.3 Blob and Directory ACL Permissions
4. TABLE 4.4 Supported AzCopy authorization methods
Chapter 5
1. TABLE 5.1 Standard and premium tier DBU prices

List of Illustrations

Chapter 1
1. FIGURE 1.1 Key-value store
2. FIGURE 1.2 Document database
3. FIGURE 1.3 Columnar database
4. FIGURE 1.4 Graph database
5. FIGURE 1.5 Structured data
6. FIGURE 1.6 JSON example
7. FIGURE 1.7 Common architecture for batch processing in Azure
8. FIGURE 1.8 Live stream processing
9. FIGURE 1.9 On-demand stream processing
10. FIGURE 1.10 Lambda architecture
11. FIGURE 1.11 ETL workflow
12. FIGURE 1.12 ADF control flow
13. FIGURE 1.13 Ordering data flow processing with a control flow
14. FIGURE 1.14 ADF mapping data flow
15. FIGURE 1.15 Azure Databricks and SQL stored procedure control flow
16. FIGURE 1.16 ELT workflow
17. FIGURE 1.17 Analytics Maturity Model
18. FIGURE 1.18 Table
19. FIGURE 1.19 Matrix
20. FIGURE 1.20 Column chart
21. FIGURE 1.21 Line chart
22. FIGURE 1.22 Pie chart
23. FIGURE 1.23 Scatter plot
24. FIGURE 1.24 Map
Chapter 2
1. FIGURE 2.1 OLTP ERD
2. FIGURE 2.2 Star schema
3. FIGURE 2.3 View definition
4. FIGURE 2.4 Azure SQL abstraction vs. administrative effort
5. FIGURE 2.5 Select a SQL virtual machine image.
6. FIGURE 2.6 Create a SQL virtual machine: Basics tab.
7. FIGURE 2.7 Create a SQL virtual machine: Networking tab.
8. FIGURE 2.8 Create a SQL virtual machine: Settings tab.
9. FIGURE 2.9 Create Azure SQL Database Managed Instance: Basics tab.
10. FIGURE 2.10 Create Azure SQL Database Managed Instance: Networking tab.
11. FIGURE 2.11 Configuring an Azure SQL Database
12. FIGURE 2.12 Create Azure SQL Database: Basics tab.
13. FIGURE 2.13 Create Azure SQL Database: Networking tab.
14. FIGURE 2.14 Scaling an Azure SQL MI
15. FIGURE 2.15 Create MySQL server: Basics tab.
16. FIGURE 2.16 Azure Cloud Shell icon
17. FIGURE 2.17 Adding an AAD Administrator
18. FIGURE 2.18 Connecting to a user database with SSMS
19. FIGURE 2.19 Script View as ALTER To statement
20. FIGURE 2.20 SSMS execution plan
21. FIGURE 2.21 Types of SQL joins
Chapter 3
1. FIGURE 3.1 Key-Value store
2. FIGURE 3.2 Document database
3. FIGURE 3.3 Columnar database
4. FIGURE 3.4 Graph database
5. FIGURE 3.5 Select Azure Cosmos DB API.
6. FIGURE 3.6 Create an Azure Cosmos DB Account: Basics tab.
7. FIGURE 3.7 Create an Azure Cosmos DB Account: Networking tab.
8. FIGURE 3.8 Create an Azure Cosmos DB Account: Backup Policy tab.
9. FIGURE 3.9 Azure Cosmos DB Data Explorer button
10. FIGURE 3.10 Azure Cosmos DB Data Explorer splash page
11. FIGURE 3.11 New Database
12. FIGURE 3.12 New Container
13. FIGURE 3.13 Adding a new write region replica
14. FIGURE 3.14 Updating the default consistency to bounded staleness
15. FIGURE 3.15 Azure Cosmos DB Explorer Open Full Screen icon
16. FIGURE 3.16 Azure Cosmos DB Explorer pop-up screen
Chapter 4
1. FIGURE 4.1 Create a Storage Account: Basics tab.
2. FIGURE 4.2 Create a Storage Account: Advanced tab security configurations.
3. FIGURE 4.3 Create a Storage Account: Advanced tab storage configurations.
4. FIGURE 4.4 Create a Storage Account: Networking tab.
5. FIGURE 4.5 Create a Storage Account: Data Protection tab.
6. FIGURE 4.6 File shares button
7. FIGURE 4.7 Create a New File Share button.
8. FIGURE 4.8 New file share
9. FIGURE 4.9 Connect button
10. FIGURE 4.10 Connect page
11. FIGURE 4.11 Containers button
12. FIGURE 4.12 Create a New Container button.
13. FIGURE 4.13 New Container
14. FIGURE 4.14 Upload button
15. FIGURE 4.15 Upload blob
16. FIGURE 4.16 Networking button
17. FIGURE 4.17 Virtual Network and Firewall Access
18. FIGURE 4.18 Exceptions and Networking Routing
19. FIGURE 4.19 Shared access signature configuration options
20. FIGURE 4.20 Configure Active Directory for Azure Files.
21. FIGURE 4.21 Manage ACLs in the Azure Portal.
22. FIGURE 4.22 How ADLS evaluates identity access
23. FIGURE 4.23 Connect to Azure Storage button
24. FIGURE 4.24 Storage account display
25. FIGURE 4.25 Upload Files pop-up page
Chapter 5
1. FIGURE 5.1 Batch processing example
2. FIGURE 5.2 On-demand stream processing example
3. FIGURE 5.3 Lambda architecture workflow
4. FIGURE 5.4 Apache Spark distributed architecture
5. FIGURE 5.5 Create an Azure Databricks workspace: Basics tab.
6. FIGURE 5.6 Create an Azure Databricks workspace: Networking tab.
7. FIGURE 5.7 Launch Workspace button
8. FIGURE 5.8 Azure Databricks home page
9. FIGURE 5.9 Azure Databricks workspace personas
10. FIGURE 5.10 Azure Databricks Workspace tab
11. FIGURE 5.11 Azure Databricks Compute page
12. FIGURE 5.12 Azure Databricks Jobs page
13. FIGURE 5.13 Azure Databricks Create Cluster page
14. FIGURE 5.14 Azure Databricks Create Notebook page
15. FIGURE 5.15 Creating a mount point with Azure AD credential passthrough
16. FIGURE 5.16 Create an ADF Instance: Basics tab.
17. FIGURE 5.17 Create an ADF Instance: Git configuration tab.
18. FIGURE 5.18 Azure Data Factory overview page
19. FIGURE 5.19 Azure Data Factory Studio home page
20. FIGURE 5.20 Azure Data Factory Studio Author page
21. FIGURE 5.21 Azure Data Factory Studio Monitor page
22. FIGURE 5.22 Azure Data Factory Studio Manage page
23. FIGURE 5.23 Creating a blank ADF pipeline
24. FIGURE 5.24 The ADF Pipeline Creation page
25. FIGURE 5.25 Copy Data Activity: General tab
26. FIGURE 5.26 New Dataset page
27. FIGURE 5.27 New linked service page: Azure SQL Database
28. FIGURE 5.28 Set properties page for a new dataset: Azure SQL Database
29. FIGURE 5.29 Copy Data Activity: Source tab
30. FIGURE 5.30 New linked service page: ADLS
31. FIGURE 5.31 Set properties page for a new dataset: ADLS
32. FIGURE 5.32 Using the Azure Data Factory Studio to edit a CSV dataset
33. FIGURE 5.33 Copy Data Activity: Mapping tab
34. FIGURE 5.34 Using the Publish all button to save the pipeline and datasets....
35. FIGURE 5.35 Create an Azure Synapse Analytics workspace: Basics tab.
36. FIGURE 5.36 Create an Azure Synapse Analytics workspace: Security tab.
37. FIGURE 5.37 Azure Synapse workspace overview page
38. FIGURE 5.38 Synapse Studio home page
39. FIGURE 5.39 Synapse Studio Data page
40. FIGURE 5.40 Synapse Studio Develop page
41. FIGURE 5.41 Synapse Studio Integrate page
42. FIGURE 5.42 Synapse Studio Monitor page
43. FIGURE 5.43 Synapse Studio Manage page
44. FIGURE 5.44 New dedicated SQL pool: Basics tab
45. FIGURE 5.45 New dedicated SQL pool: Additional settings tab
46. FIGURE 5.46 Pausing a dedicated SQL pool
47. FIGURE 5.47 Synapse Studio SQL script window
48. FIGURE 5.48 Choosing the Built-in SQL pool
49. FIGURE 5.49 Executing Serverless SQL Pool Queries in Synapse Studio
Chapter 6
1. FIGURE 6.1 A blank report in the Power BI Desktop Report view. You can switc...
2. FIGURE 6.2 Get Data page
3. FIGURE 6.3 SQL Server database connection page
4. FIGURE 6.4 Select the tables or views that will be imported into the Power B...
5. FIGURE 6.5 Power Query Editor
6. FIGURE 6.6 M Code can be viewed and edited through the Power Query Advanced ...
7. FIGURE 6.7 Power BI Desktop Model view
8. FIGURE 6.8 Power BI Desktop Data view
9. FIGURE 6.9 Power BI Desktop Report view on the Order Quantity Sold Per Item ...
10. FIGURE 6.10 Power BI Service Workspace home page
11. FIGURE 6.11 Power BI Service Report view
12. FIGURE 6.12 The pin icon on a report visual
13. FIGURE 6.13 Power BI dashboard
14. FIGURE 6.14 Power BI Q&A output
15. FIGURE 6.15 Power BI Report Builder Getting Started window
16. FIGURE 6.16 A blank report in Power BI Report Builder

Published by John Wiley & Sons, Inc., Hoboken, New Jersey.

Published simultaneously in Canada and the United Kingdom.

978-1-119-85583-5

978-1-119-85585-9 (ebk.)

978-1-119-85584-2 (ebk.)

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at www.wiley.com/go/permission.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware the Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic formats. For more information about Wiley products, visit our web site at www.wiley.com.

Library of Congress Control Number: 2021950194

Trademarks: WILEY, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and Azure are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book. MC Microsoft Certified Azure Data Fundamentals Study Guide is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation.

Cover design: Wiley

Acknowledgments

While I have been able to work on several exciting opportunities in my professional career at Microsoft, including delivering live presentations and working with some of the biggest brand name organizations the world, this was my first time tackling a technical book. This project was both intense and incredibly rewarding, as it allowed me to share what I believe are the fundamental skills anyone will need to start a successful career with the Microsoft data stack. However, this would not have been possible without the support from the following people.

First and foremost, I would like to thank my wife, Kaiya, for her love and support during the writing of this book. It is from her that I gather inspiration to be my best self every day. Thanks to my mom and dad for their unrelenting support and helping me make the most of every opportunity.

I would also like to thank my colleague Susanne Tedrick, author of WOMEN OF COLOR IN TECH: A Blueprint for Inspiring and Mentoring the Next Generation of Technology Innovators, (Wiley, 2020) for reaching out to me when this opportunity became available and to Kenyon Brown, the acquisitions editor, for helping me get it off the ground. Many thanks to Ayman El-Ghazali, the technical editor for this book and a mentor of mine throughout my time at Microsoft. Special thanks to Jon Flynn and Tash Tahir, two of my colleagues at Microsoft, for taking the time out of their busy schedule to review the content.

Finally, thank you to the entire team who made this book come together, including David Clark (project editor), Pete Gaughan (managing editor), Judy Flynn (copyeditor), and Barath Kumar Rajasekaran, who polished the rough content and made sure the project kept moving. Thanks also to all of the people who work behind the scenes with the production of this book.