DP-200: Implementing an Azure Data Solution

DP-200: Implementing an Azure Data Solution is part of the requirements for the Microsoft Certified: Azure Data Engineer Associate

Exam requirements

The official exam document are published here: https://docs.microsoft.com/en-us/learn/certifications/exams/dp-200

Exam preparation

Books covering the exam

Video training for the exam

Microsoft Partner Network

• DP-200: Implementing an Azure Data Solution
  https://partner.microsoft.com/en-us/training/assets/collection/dp-200-implementing-an-azure-data-solution#/
• DP-201: Designing an Azure Data Solution
  https://partner.microsoft.com/en-us/training/assets/collection/dp-201-designing-an-azure-data-solution#/

PluralSight

• Microsoft Azure Data Engineer (DP-200)
  https://app.pluralsight.com/paths/certificate/microsoft-azure-data-engineer-dp-200
• Azure Data Solution (DP-201)
  https://app.pluralsight.com/paths/certificate/azure-data-solution-dp-201

Microsoft Ignite (free)

• Microsoft Ignite Session Catalog
  https://myignite.techcommunity.microsoft.com/sessions

SQLBits (free)

• XVIII (2019) Conference
  https://sqlbits.com/content/Event18?type=3
  • Select Stars: A SQL DBAs Introduction to Azure Cosmos DB
    https://sqlbits.com/Sessions/Event18/Select_Stars_A_SQL_DBAs_Introduction_to_Azure_Cosmos_DB
  • Data Lake Design Patterns
    https://sqlbits.com/Sessions/Event18/Data_Lake_Design_Patterns
  • Data Integration with Azure Cosmos DB, Data Factory and Databricks
    https://sqlbits.com/Sessions/Event18/Data_Integration_with_Azure_Cosmos_DB_Data_Factory_and_Databricks
  • Modeling data and best practices for Azure Cosmos DBs SQL API
    https://sqlbits.com/Sessions/Event18/Modeling_data_and_best_practices_for_Azure_Cosmos_DBs_SQL_API
  • Understanding Gremlin and Azure Cosmos DBs Graph API for SQL Professionals
    https://sqlbits.com/Sessions/Event18/Understanding_Gremlin_and_Azure_Cosmos_DBs_Graph_API_for_SQL_Professionals
  • Azure Managed Instances – Your Bridge to the Cloud
    https://sqlbits.com/Sessions/Event18/Azure_Managed_Instances_Your_Bridge_to_the_Cloud2
  • Making Managed Instance Perform, a dive into storage.
    https://sqlbits.com/Sessions/Event18/Introduction_to_Managed_Instances
  • Managed Instances – Deep Dive
    https://sqlbits.com/Sessions/Event18/Managed_Instances_Deep_Dive
  • Build hybrid data platform with Azure SQL Database and SQL Server
    https://sqlbits.com/Sessions/Event18/Build_hybrid_data_platform_with_Azure_SQL_Database_and_SQL_Server
  • Hyperscale for Azure SQL DB
    https://sqlbits.com/Sessions/Event18/Hyperscale_for_Azure_SQL_DB
  • Modernize on-prem SQL Servers to Azure using Azure Database Migration Service
    https://sqlbits.com/Sessions/Event18/Modernize_on_premises_SQL_Servers_reaching_End_of_support_to_Azure_using_Azure_Database_Migration_Service
  • What’s new in SQL Server and Azure SQL Database Security
    https://sqlbits.com/Sessions/Event18/Whats_new_in_SQL_Server_and_Azure_SQL_Database_Security
  • Inside SQL Server 2019 Polybase
    https://sqlbits.com/Sessions/Event18/Inside_SQL_Server_2019_Polybase
  • Azure Data Factory v2 with Data Flows capabilities
    https://sqlbits.com/Sessions/Event18/Azure_Data_Factory_v2_with_Data_Flows_capabilities
  • Getting started with real-time analytics with Kafka and Spark in Microsoft Azure
    https://sqlbits.com/Sessions/Event18/Getting_started_with_real_time_analytics_with_Kafka_and_Spark_in_Microsoft_Azure
  • Scalable real-time insights with Azure Stream Analytics
    https://sqlbits.com/Sessions/Event18/Scalable_real_time_insights_with_Azure_Stream_Analytics
  • Improving SQL Server Performance with Indexing
    https://sqlbits.com/Sessions/Event18/Improving_SQL_Server_Performance_with_Indexing
  • Performance Tuning of Azure SQL DB
    https://sqlbits.com/Sessions/Event18/Performance_Tuning_of_Azure_SQL_DB
  • Plan Stability with Automatic Tuning in SQL Server and Azure
    https://sqlbits.com/Sessions/Event18/Query_Store_and_Automatic_Tuning_in_SQL_Server
  • Monitoring AzureSQLDB
    https://sqlbits.com/Sessions/Event18/Monitoring_AzureSQLDB

Online training

Microsoft Learn (free)

• Azure Fundamentals
  https://docs.microsoft.com/en-us/learn/paths/azure-fundamentals/
• Azure for the Data Engineer
  https://docs.microsoft.com/en-us/learn/paths/azure-for-the-data-engineer/
• Store data in Azure
  https://docs.microsoft.com/en-us/learn/paths/store-data-in-azure/
• Work with relational data in Azure
  https://docs.microsoft.com/en-us/learn/paths/work-with-relational-data-in-azure/
• Work with NoSQL data in Azure Cosmos DB
  https://docs.microsoft.com/en-us/learn/paths/work-with-nosql-data-in-azure-cosmos-db/
• Large-Scale Data Processing with Azure Data Lake Storage Gen2
  https://docs.microsoft.com/en-us/learn/paths/data-processing-with-azure-adls/
• Implement a Data Streaming Solution with Azure Streaming Analytics
  https://docs.microsoft.com/en-us/learn/paths/implement-data-streaming-with-asa/
• Implement a Data Warehouse with Azure Synapse Analytics
  https://docs.microsoft.com/en-us/learn/paths/implement-sql-data-warehouse/
• Data engineering with Azure Databricks
  https://docs.microsoft.com/en-us/learn/paths/data-engineer-azure-databricks/
• Perform data science with Azure Databricks
  https://docs.microsoft.com/en-us/learn/paths/perform-data-science-azure-databricks/

Instructor-led training

Microsoft Learning Partner

• Course DP-200T01-A: Implementing an Azure Data Solution
  https://docs.microsoft.com/en-us/learn/certifications/courses/dp-200t01
• Course DP-201T01-A: Designing an Azure Data Solution
  https://docs.microsoft.com/en-us/learn/certifications/courses/dp-201t01

Exam Objectives

Implement Data Storage Solutions (40-45%)

• Implement non-relational data stores
  • Implement a solution that uses Cosmos DB, Data Lake Storage Gen2, or Blob storage
    https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-dotnet-application
    https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-static-website-host
    https://docs.microsoft.com/en-us/azure/data-factory/load-azure-data-lake-storage-gen2
  • Implement data distribution and partitions
    https://docs.microsoft.com/en-us/azure/cosmos-db/distribute-data-globally
    https://docs.microsoft.com/en-us/azure/cosmos-db/partition-data
  • Implement a consistency model in Cosmos DB
    https://docs.microsoft.com/en-us/azure/cosmos-db/consistency-levels-choosing
  • Provision a non-relational data store
    https://docs.microsoft.com/en-us/azure/cosmos-db/create-cosmosdb-resources-portal
  • Implement for high availability, disaster recovery, and global distribution
    https://docs.microsoft.com/en-us/azure/cosmos-db/tutorial-global-distribution-sql-api
    https://docs.microsoft.com/en-us/azure/cosmos-db/online-backup-and-restore
  • Provide access to data to meet security requirements
    https://docs.microsoft.com/en-us/azure/cosmos-db/secure-access-to-data
• Implement relational data stores
  • Configure elastic pools
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool-manage
  • Configure geo-replication
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-active-geo-replication-portal
  • Provide access to data to meet security requirements
    https://docs.microsoft.com/bs-latn-ba/azure/sql-database/sql-database-security-tutorial
  • Implement for high availability, disaster recovery, and global distribution
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-implement-geo-distributed-database
  • Implement data distribution and partitions for Azure Synapse Analytics (formerly known as  SQL Data Warehouse)
    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition
    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/load-data-from-azure-blob-storage-using-polybase
  • Implement PolyBase
    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/load-data-wideworldimportersdw
• Manage data security
  • Implement data masking
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started-portal
  • Encrypt data at rest and in motion
    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-encryption-tde
    https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-howto-site-to-site-resource-manager-portal

Manage and Develop Data Processing (25-30%)

• Develop batch processing solutions
  • Develop batch processing solutions by using Data Factory and Azure Databricks
    https://www.youtube.com/watch?v=CZQOxPY7UuA
  • Ingest data by using PolyBase
    https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/load-data-from-azure-blob-storage-using-polybase
  • Implement the integration runtime for Data Factory
    https://docs.microsoft.com/en-us/azure/data-factory/create-azure-integration-runtime
  • Implement Copy Activity within Azure Data Factory
    https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-overview
  • Create linked services and datasets
  • Create pipelines and activities
    https://docs.microsoft.com/en-us/azure/data-factory/concepts-pipelines-activities
    https://docs.microsoft.com/en-us/azure/data-factory/quickstart-create-data-factory-powershell
  • Implement Mapping Data Flows in Azure Data Factory
    https://docs.microsoft.com/en-us/azure/data-factory/concepts-data-flow-overview
  • Create and schedule triggers
    https://docs.microsoft.com/en-us/azure/data-factory/how-to-create-schedule-trigger
  • Implement Azure Databricks clusters, notebooks, jobs, and autoscaling
    https://docs.azuredatabricks.net/user-guide/clusters/sizing.html
  • Ingest data into Azure Databricks
    https://docs.microsoft.com/en-us/azure/azure-databricks/databricks-extract-load-sql-data-warehouse
• Develop streaming solutions
  • Configure input and output
    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-define-inputs
    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-documentdb-output
  • Select the appropriate windowing functions
    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-window-functions
  • Implement event processing by using Stream Analytics
    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-real-time-event-processing-reference-architecture

Monitor and Optimize Data Solutions (30-35%)

• Monitor data storage
  • Monitor relational and non-relational data sources
  • Implement Blob storage monitoring
    https://docs.microsoft.com/en-us/azure/storage/common/storage-monitor-storage-account
  • Implement Data Lake Storage monitoring
    https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-diagnostic-logs
  • Implement SQL Database monitoring
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitor-tune-overview
  • Implement Azure Synapse Analytics monitoring
    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-monitor-workload-portal
  • Implement Cosmos DB monitoring
    https://docs.microsoft.com/en-us/azure/cosmos-db/monitor-accounts
  • Configure Azure Monitor alerts
    https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-metric
  • Implement auditing by using Azure Log Analytics
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing
• Monitor data processing
  • Monitor Data Factory pipelines
    https://docs.microsoft.com/en-us/azure/data-factory/monitor-using-azure-monitor
  • Monitor Azure Databricks
    https://docs.microsoft.com/en-us/azure/data-factory/monitor-using-azure-monitor
  • Monitor Stream Analytics
    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-monitor-and-manage-jobs-use-powershell
  • Configure Azure Monitor alerts
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-insights-alerts-portal
  • Implement auditing by using Azure Log Analytics
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing
• Optimize Azure data solutions
  • Troubleshoot data partitioning bottlenecks
    https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning-strategies
  • Optimize Data Lake Storage
    https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-performance-tuning-guidance
  • Optimize Stream Analytics
    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-parallelization
  • Optimize Azure Synapse Analytics
    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-best-practices
  • Optimize SQL Database
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitor-tune-overview
  • Manage the data lifecycle
    https://docs.microsoft.com/en-us/azure/storage/blobs/storage-lifecycle-management-concepts