Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 101:

  HOTSPOT

  You are designing an application that will store petabytes of medical imaging data

  When the data is first created, the data will be accessed frequently during the first week. After one month, the data must be accessible within 30 seconds, but files will be accessed infrequently. After one year, the data will be accessed

  infrequently but must be accessible within five minutes.

  You need to select a storage strategy for the data. The solution must minimize costs.

  Which storage tier should you use for each time frame? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Explanation:

  Box 1: Hot

  Hot tier - An online tier optimized for storing data that is accessed or modified frequently. The Hot tier has the highest storage costs, but the lowest access costs.

  Box 2: Cool

  Cool tier - An online tier optimized for storing data that is infrequently accessed or modified. Data in the Cool tier should be stored for a minimum of 30 days. The Cool tier has lower storage costs and higher access costs compared to the Hot

  tier.

  Box 3: Cool

  Not Archive tier - An offline tier optimized for storing data that is rarely accessed, and that has flexible latency requirements, on the order of hours. Data in the Archive tier should be stored for a minimum of 180 days.

  

  Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/access-tiers-overview https://www.altaro.com/hyper-v/azure-archive-storage/

• Question 102:

  HOTSPOT

  You have a Microsoft SQL Server database that uses a third normal form schema.

  You plan to migrate the data in the database to a star schema in an A?\ire Synapse Analytics dedicated SQI pool.

  You need to design the dimension tables. The solution must optimize read operations.

  What should you include in the solution? to answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Denormalize to a second normal form Denormalization is the process of transforming higher normal forms to lower normal forms via storing the join of higher normal form relations as a base relation. Denormalization increases the performance in data retrieval at cost of bringing update anomalies to a database.

  Box 2: New identity columns The collapsing relations strategy can be used in this step to collapse classification entities into component entities to obtain at dimension tables with single-part keys that connect directly to the fact table. The single-part key is a surrogate key generated to ensure it remains unique over time.

  Example:

  

  Note: A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.

• Question 103:

  HOTSPOT

  From a website analytics system, you receive data extracts about user interactions such as downloads, link clicks, form submissions, and video plays.

  The data contains the following columns.

  

  You need to design a star schema to support analytical queries of the data. The star schema will contain four tables including a date dimension.

  To which table should you add each column? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: DimEvent

  Box 2: DimChannel

  Box 3: FactEvents Fact tables store observations or events, and can be sales orders, stock balances, exchange rates, temperatures, etc

  Reference: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

• Question 104:

  HOTSPOT

  You are developing a solution using a Lambda architecture on Microsoft Azure.

  The data at test layer must meet the following requirements:

  Data storage:

  1.

  Serve as a repository (or high volumes of large files in various formats.

  2.

  Implement optimized storage for big data analytics workloads.

  3.

  Ensure that data can be organized using a hierarchical structure.

  Batch processing:

  1.

  Use a managed solution for in-memory computation processing.

  2.

  Natively support Scala, Python, and R programming languages.

  3.

  Provide the ability to resize and terminate the cluster automatically.

  Analytical data store:

  1.

  Support parallel processing.

  2.

  Use columnar storage.

  3.

  Support SQL-based languages.

  You need to identify the correct technologies to build the Lambda architecture.

  Which technologies should you use? To answer, select the appropriate options in the answer area NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Data storage: Azure Data Lake Store

  A key mechanism that allows Azure Data Lake Storage Gen2 to provide file system performance at object storage scale and prices is the addition of a hierarchical namespace.

  This allows the collection of objects/files within an account to be organized into a hierarchy of directories and nested subdirectories in the same way that the file system on your computer is organized. With the hierarchical namespace enabled,

  a storage account becomes capable of providing the scalability and cost-effectiveness of object storage, with

  file system semantics that are familiar to analytics engines and frameworks.

  Batch processing: HD Insight Spark

  Aparch Spark is an open-source, parallel-processing framework that supports in-memory processing to boost the performance of big-data analysis applications.

  HDInsight is a managed Hadoop service. Use it deploy and manage Hadoop clusters in Azure. For batch processing, you can use Spark, Hive, Hive LLAP, MapReduce.

  Languages: R, Python, Java, Scala, SQL

  Analytic data store: SQL Data Warehouse

  SQL Data Warehouse is a cloud-based Enterprise Data Warehouse (EDW) that uses Massively Parallel Processing (MPP).

  SQL Data Warehouse stores data into relational tables with columnar storage.

  References:

  https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-namespace

  https://docs.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/batchprocessing

  https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-overviewwhat-is

• Question 105:

  HOTSPOT

  You have a SQL pool in Azure Synapse.

  You plan to load data from Azure Blob storage to a staging table. Approximately 1 million rows of data will be loaded daily. The table will be truncated before each daily load.

  You need to create the staging table. The solution must minimize how long it takes to load the data to the staging table.

  How should you configure the table? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Hash

  Hash-distributed tables improve query performance on large fact tables. They can have very large numbers of rows and still achieve high performance.

  Incorrect Answers:

  Round-robin tables are useful for improving loading speed.

  Box 2: Clustered columnstore

  When creating partitions on clustered columnstore tables, it is important to consider how many rows belong to each partition. For optimal compression and performance of clustered columnstore tables, a minimum of 1 million rows per

  distribution and partition is needed.

  Box 3: Date

  Table partitions enable you to divide your data into smaller groups of data. In most cases, table partitions are created on a date column.

  Partition switching can be used to quickly remove or replace a section of a table.

  Reference:

  https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition

  https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

• Question 106:

  HOTSPOT

  You have two Azure Storage accounts named Storage1 and Storage2. Each account holds one container and has the hierarchical namespace enabled. The system has files that contain data stored in the Apache Parquet format.

  You need to copy folders and files from Storage1 to Storage2 by using a Data Factory copy activity. The solution must meet the following requirements:

  1.

  No transformations must be performed.

  2.

  The original folder structure must be retained.

  3.

  Minimize time required to perform the copy activity.

  How should you configure the copy activity? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Parquet

  For Parquet datasets, the type property of the copy activity source must be set to ParquetSource.

  Box 2: PreserveHierarchy

  PreserveHierarchy (default): Preserves the file hierarchy in the target folder. The relative path of the source file to the source folder is identical to the relative path of the target file to the target folder.

  Incorrect Answers:

  FlattenHierarchy: All files from the source folder are in the first level of the target folder. The target files have autogenerated names.

  MergeFiles: Merges all files from the source folder to one file. If the file name is specified, the merged file name is the specified name. Otherwise, it's an autogenerated file name.

  Reference:

  https://docs.microsoft.com/en-us/azure/data-factory/format-parquet

  https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-data-lake-storage

• Question 107:

  HOTSPOT

  You have an enterprise data warehouse in Azure Synapse Analytics that contains a table named FactOnlineSales. The table contains data from the start of 2009 to the end of 2012.

  You need to improve the performance of queries against FactOnlineSales by using table partitions. The solution must meet the following requirements:

  1.

  Create four partitions based on the order date.

  2.

  Ensure that each partition contains all the orders places during a given calendar year.

  How should you complete the T-SQL command? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Range Left or Right, both are creating similar partition but there is difference in comparison

  For example: in this scenario, when you use LEFT and 20100101,20110101,20120101 Partition will be, datecol<=20100101, datecol>20100101 and datecol<=20110101, datecol>20110101 and datecol<=20120101, datecol>20120101 But if you use range RIGHT and 20100101,20110101,20120101

  Partition will be, datecol<20100101, datecol>=20100101 and datecol<20110101, datecol>=20110101 and datecol<20120101, datecol>=20120101 In this example, Range RIGHT will be suitable for calendar comparison Jan 1st to Dec 31st Reference:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15

• Question 108:

  HOTSPOT

  You have an Azure Synapse Analytics dedicated SQL pool that contains the users shown in the following table.

  

  User1 executes a query on the database, and the query returns the results shown in the following exhibit.

  

  User1 is the only user who has access to the unmasked data.

  Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: 0

  The YearlyIncome column is of the money data type.

  The Default masking function: Full masking according to the data types of the designated fields

  Use a zero value for numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).

  Box 2: the values stored in the database

  Users with administrator privileges are always excluded from masking, and see the original data without any mask.

  Reference:

  https://docs.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview

• Question 109:

  HOTSPOT

  The following code segment is used to create an Azure Databricks cluster.

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Yes

  A cluster mode of ‘High Concurrency’ is selected, unlike all the others which are ‘Standard’. This results in a worker type of Standard_DS13_v2.

  Box 2: No

  When you run a job on a new cluster, the job is treated as a data engineering (job) workload subject to the job workload pricing. When you run a job on an existing cluster, the job is treated as a data analytics (all-purpose) workload subject to

  all-purpose workload pricing.

  Box 3: Yes

  Delta Lake on Databricks allows you to configure Delta Lake based on your workload patterns.

  Reference:

  https://adatis.co.uk/databricks-cluster-sizing/

  https://docs.microsoft.com/en-us/azure/databricks/jobs

  https://docs.databricks.com/administration-guide/capacity-planning/cmbp.html

  https://docs.databricks.com/delta/index.html

• Question 110:

  HOTSPOT

  A company plans to use Platform-as-a-Service (PaaS) to create the new data pipeline process. The process must meet the following requirements:

  Ingest:

  1.

  Access multiple data sources.

  2.

  Provide the ability to orchestrate workflow.

  3.

  Provide the capability to run SQL Server Integration Services packages.

  Store:

  1.

  Optimize storage for big data workloads.

  2.

  Provide encryption of data at rest.

  3.

  Operate with no size limits.

  Prepare and Train:

  1.

  Provide a fully-managed and interactive workspace for exploration and visualization.

  2.

  Provide the ability to program in R, SQL, Python, Scala, and Java.

  3.

  Provide seamless user authentication with Azure Active Directory.

  Model and Serve:

  1.

  Implement native columnar storage.

  2.

  Support for the SQL language

  3.

  Provide support for structured streaming.

  You need to build the data integration pipeline.

  Which technologies should you use? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Ingest: Azure Data Factory

  Azure Data Factory pipelines can execute SSIS packages.

  In Azure, the following services and tools will meet the core requirements for pipeline orchestration, control flow, and data movement: Azure Data Factory, Oozie on HDInsight, and SQL Server Integration Services (SSIS).

  Store: Data Lake Storage

  Data Lake Storage Gen1 provides unlimited storage.

  Note: Data at rest includes information that resides in persistent storage on physical media, in any digital format. Microsoft Azure offers a variety of data storage solutions to meet different needs, including file, disk, blob, and table storage.

  Microsoft also provides encryption to protect Azure SQL Database, Azure Cosmos DB, and Azure Data Lake.

  Prepare and Train: Azure Databricks

  Azure Databricks provides enterprise-grade Azure security, including Azure Active Directory integration.

  With Azure Databricks, you can set up your Apache Spark environment in minutes, autoscale and collaborate on shared projects in an interactive workspace. Azure Databricks supports Python, Scala, R, Java and SQL, as well as data

  science frameworks and libraries including TensorFlow, PyTorch and scikit-learn.

  Model and Serve: Azure Synapse Analytics

  Azure Synapse Analytics/ SQL Data Warehouse stores data into relational tables with columnar storage.

  Azure SQL Data Warehouse connector now offers efficient and scalable structured streaming write support for SQL Data Warehouse. Access SQL Data Warehouse from Azure Databricks using the SQL Data Warehouse connector.

  Note: Note: As of November 2019, Azure SQL Data Warehouse is now Azure Synapse Analytics.

  Reference:

  https://docs.microsoft.com/bs-latn-ba/azure/architecture/data-guide/technology-choices/pipeline-orchestration-data-movement

  https://docs.microsoft.com/en-us/azure/azure-databricks/what-is-azure-databricks