Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 61:

  HOTSPOT

  You have two Azure SQL databases named DB1 and DB2.

  DB1 contains a table named Table 1. Table1 contains a timestamp column named LastModifiedOn. LastModifiedOn contains the timestamp of the most recent update for each individual row.

  DB2 contains a table named Watermark. Watermark contains a single timestamp column named WatermarkValue.

  You plan to create an Azure Data Factory pipeline that will incrementally upload into Azure Blob Storage all the rows in Table1 for which the LastModifiedOn column contains a timestamp newer than the most recent value of the

  WatermarkValue column in Watermark.

  You need to identify which activities to include in the pipeline. The solution must meet the following requirements:

  Minimize the effort to author the pipeline.

  Ensure that the number of data integration units allocated to the upload operation can be controlled.

  What should you identify? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  Correct Answer:

  

• Question 62:

  HOTSPOT

  You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool. You plan to deploy a solution that will analyze sales data and include the following:

  A table named Country that will contain 195 rows

  A table named Sales that will contain 100 million rows

  A query to identify total sales by country and customer from the past 30 days

  You need to create the tables. The solution must maximize query performance.

  How should you complete the script? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 63:

  HOTSPOT

  You have an Azure Data Factory pipeline that contains a data flow. The data flow contains the following expression.

  

  Hot Area:

  

  Correct Answer:

  

• Question 64:

  HOTSPOT

  You have an Azure Synapse Analytics serverless SQL pool, an Azure Synapse Analytics dedicated SQL pool, an Apache Spark pool, and an Azure Data Lake Storage Gen2 account.

  You need to create a table in a lake database. The table must be available to both the serverless SQL pool and the Spark pool.

  Where should you create the table, and Which file format should you use for data in the table? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 65:

  HOTSPOT

  You have an Azure Synapse Analytics dedicated SQL pool.

  You need to create a table named FactInternetSales that will be a large fact table in a dimensional model. FactInternetSales will contain 100 million rows and two columns named SalesAmount and OrderQuantity. Queries executed on

  FactInternetSales will aggregate the values in SalesAmount and OrderQuantity from the last year for a specific product. The solution must minimize the data size and query execution time.

  How should you complete the code? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 66:

  HOTSPOT

  You have an Azure Blob storage account that contains a folder. The folder contains 120,000 files. Each file contains 62 columns.

  Each day, 1,500 new files are added to the folder.

  You plan to incrementally load five data columns from each new file into an Azure Synapse Analytics workspace.

  You need to minimize how long it takes to perform the incremental loads.

  What should you use to store the files and format?

  Hot Area:

  

  Correct Answer:

  

  Box 1 = timeslice partitioning in the foldersThis means that you should organize your files into folders based on a time attribute, such as year, month, day, or hour. For example, you can have a folder structure like /yyyy/mm/dd/file.csv. This way, you can easily identify and load only the new files that are added each day by using a time filter in your Azure Synapse pipeline12. Timeslice partitioning can also improve the performance of data loading and querying by reducing the number of files that need to be scanned

  Box = 2 Apache Parquet This is because Parquet is a columnar file format that can efficiently store and compress data with many columns. Parquet files can also be partitioned by a time attribute, which can improve the performance of incremental loading and querying by reducing the number of files that need to be scanned123. Parquet files are supported by both dedicated SQL pool and serverless SQL pool in Azure Synapse Analytics2.

• Question 67:

  HOTSPOT

  You have an Azure Synapse Analytics pipeline named Pipeline1 that contains a data flow activity named Dataflow1.

  Pipeline1 retrieves files from an Azure Data Lake Storage Gen 2 account named storage1.

  Dataflow1 uses the AutoResolveIntegrationRuntime integration runtime configured with a core count of 128. You need to optimize the number of cores used by Dataflow1 to accommodate the size of the files in storage1. What should you configure? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  Correct Answer:

  

• Question 68:

  HOTSPOT

  You have an Azure Data Factory pipeline that has the activity shown in the following exhibit.

  

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

  Hot Area:

  

  Correct Answer:

  

• Question 69:

  HOTSPOT

  You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool named Pool1 and an Azure Data Lake Storage account named storage1. Storage1 requires secure transfers.

  You need to create an external data source in Pool1 that will be used to read .orc files in storage1.

  How should you complete the code? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azure-sqldw-latestandpreserve-view=trueandtabs=dedicated

• Question 70:

  HOTSPOT

  You are building a database in an Azure Synapse Analytics serverless SQL pool.

  You have data stored in Parquet files in an Azure Data Lake Storege Gen2 container.

  Records are structured as shown in the following sample.

  {

  "id": 123,

  "address_housenumber": "19c",

  "address_line": "Memory Lane",

  "applicant1_name": "Jane",

  "applicant2_name": "Dev"

  }

  The records contain two applicants at most.

  You need to build a table that includes only the address fields.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: CREATE EXTERNAL TABLE

  An external table points to data located in Hadoop, Azure Storage blob, or Azure Data Lake Storage. External tables are used to read data from files or write data to files in Azure Storage. With Synapse SQL, you can use external tables to

  read external data using dedicated SQL pool or serverless SQL pool.

  Syntax:

  CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }

  ( [ ,...n ] )

  WITH (

  LOCATION = 'folder_or_filepath',

  DATA_SOURCE = external_data_source_name,

  FILE_FORMAT = external_file_format_name

  Box 2. OPENROWSET

  When using serverless SQL pool, CETAS is used to create an external table and export query results to Azure Storage Blob or Azure Data Lake Storage Gen2.

  Example:

  AS

  SELECT decennialTime, stateName, SUM(population) AS population FROM

  OPENROWSET(BULK

  'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_pop ulation_county/year=*/*.parquet',

  FORMAT='PARQUET') AS [r]

  GROUP BY decennialTime, stateName

  GO