You configure monitoring from an Azure Synapse Analytics implementation. The implementation uses PolyBase to load data from comma-separated value (CSV) files stored in Azure Data Lake Storage Gen2 using an external table. Files with an invalid schema cause errors to occur.You need to monitor for an invalid schema error.For which error should you monitor?

Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11" for linked server "(null)". Query aborted- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected outof total 1 rows processed.

EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect: Error [com.microsoft.polybase.client.KerberosSecureLogin] occurred while accessing external file.'

Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11" for linked server "(null)". Query aborted- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected outof total 1 rows processed.

EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect: Error [Unable to instantiate LoginClass] occurred while accessing external file.'

EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect: Error [No FileSystem for scheme: wasbs] occurred while accessing external file.'

You have an Azure Synapse Analytics dedicated SQL pool.You run PDW_SHOWSPACEUSED('dbo.FactInternetSales'); and get the results shown in the following table. Which statement accurately describes the dbo.FactInternetSales table?

All distributions contain data.

The table contains less than 10,000 rows.

The table uses round-robin distribution.

You have two fact tables named Flight and Weather. Queries targeting the tables will be based on the join between the following columns. You need to recommend a solution that maximizes query performance.What should you include in the recommendation?

In the tables use a hash distribution of ArrivalAirportID and AirportID.

In the tables use a hash distribution of ArrivalDateTime and ReportDateTime.

In the tables use a hash distribution of ArrivalAirportID and AirportID.

In each table, create an IDENTITY column.

In each table, create a column as a composite of the other two columns in the table.

You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and a database named DB1. DB1 contains a fact table named Table1. You need to identify the extent of the data skew in Table1.What should you do in Synapse Studio?

Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.

Connect to the built-in pool and run sys.dm_pdw_nodes_db_partition_stats.

Connect to Pool1 and run DBCC CHECKALLOC.

Connect to the built-in pool and run DBCC CHECKALLOC.

Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.

A company purchases IoT devices to monitor manufacturing machinery. The company uses an Azure IoT Hub to communicate with the IoT devices. The company must be able to monitor the devices in real-time. You need to design the solution.What should you recommend?

Azure Data Factory instance using Azure Portal

Azure Data Factory instance using Azure PowerShell

Azure Stream Analytics cloud job using Azure Portal

Azure Data Factory instance using Microsoft Visual Studio

You have a table in an Azure Synapse Analytics dedicated SQL pool. The table was created by using the following Transact-SQL statement. You need to alter the table to meet the following requirements:Ensure that users can identify the current manager of employees. Support creating an employee reporting hierarchy for your entire company. Provide fast lookup of the managers’ attributes such as name and job title.Which column should you add to the table?

[ManagerEmployeeKey] [int] NULL

[ManagerEmployeeID] [smallint] NULL

[ManagerEmployeeKey] [smallint] NULL

[ManagerEmployeeKey] [int] NULL

[ManagerName] [varchar](200) NULL

You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit. You create an external table named ExtTable that has LOCATION='/topfolder/'.When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool, which files are returned?

File1.csv, File2.csv, File3.csv, and File4.csv

You are designing the folder structure for an Azure Data Lake Storage Gen2 container. Users will query data by using a variety of services including Azure Databricks and Azure Synapse Analytics serverless SQL pools. The data will be secured by subject area. Most queries will include data from the current year or current month.Which folder structure should you recommend to support fast queries and simplified folder security?

/{SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}_{YYYY}_{MM}_{DD}.csv

/{SubjectArea}/{DataSource}/{DD}/{MM}/{YYYY}/{FileData}_{YYYY}_{MM}_{DD}.csv

/{DD}/{MM}/{YYYY}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}.csv

/{YYYY}/{MM}/{DD}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}.csv

/{SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}_{YYYY}_{MM}_{DD}.csv

You need to design an Azure Synapse Analytics dedicated SQL pool that meets the following requirements:Can return an employee record from a given point in time.Maintains the latest employee information. Minimizes query complexity. How should you model the employee data?

as a Type 2 slowly changing dimension (SCD) table

as a degenerate dimension table

as a Type 2 slowly changing dimension (SCD) table

You have an enterprise-wide Azure Data Lake Storage Gen2 account. The data lake is accessible only through an Azure virtual network named VNET1. You are building a SQL pool in Azure Synapse that will use data from the data lake. Your company has a sales team. All the members of the sales team are in an Azure Active Directory group named Sales. POSIX controls are used to assign the Sales group access to the files in the data lake. You plan to load data to the SQL pool every hour.You need to ensure that the SQL pool can load the sales data from the data lake. Which three actions should you perform? Each correct answer presents part of the solution. NOTE: Each area selection is worth one point.

Use the managed identity as the credentials for the data load process.

Add your Azure Active Directory (Azure AD) account to the Sales group.

Add the managed identity to the Sales group.

Use the managed identity as the credentials for the data load process.

Create a shared access signature (SAS).

Add your Azure Active Directory (Azure AD) account to the Sales group.

Use the shared access signature (SAS) as the credentials for the data load process.

You have an Azure Data Lake Storage Gen2 container that contains 100 TB of data. You need to ensure that the data in the container is available for read workloads in a secondary region if an outage occurs in the primary region. The solution must minimize costs. Which type of data redundancy should you use?

read-access geo-redundant storage (RA-GRS)

locally-redundant storage (LRS)

You plan to implement an Azure Data Lake Gen 2 storage account. You need to ensure that the data lake will remain available if a data center fails in the primary Azure region. The solution must minimize costs. Which type of replication should you use for the storage account?

geo-zone-redundant storage (GZRS)

locally-redundant storage (LRS)

You are designing a fact table named FactPurchase in an Azure Synapse Analytics dedicated SQL pool. The table contains purchases from suppliers for a retail store. FactPurchase will contain the following columns. FactPurchase will have 1 million rows of data added daily and will contain three years of data.Transact-SQL queries similar to the following query will be executed daily.SELECTSupplierKey, StockItemKey, IsOrderFinalized, COUNT(*)FROM FactPurchaseWHERE DateKey >= 20210101AND DateKey <= 20210131GROUP By SupplierKey, StockItemKey, IsOrderFinalizedWhich table distribution will minimize query times?

hash-distributed on PurchaseKey

hash-distributed on IsOrderFinalized

You build a data warehouse in an Azure Synapse Analytics dedicated SQL pool. Analysts write a complex SELECT query that contains multiple JOIN and CASE statements to transform data for use in inventory reports. The inventory reports will use the data and additional WHERE parameters depending on the report. The reports will be produced once daily.You need to implement a solution to make the dataset available for the reports. The solution must minimize query times. What should you implement?

an ordered clustered columnstore index

You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a partitioned fact table named dbo.Sales and a staging table named stg.Sales that has the matching table and partition definitions. You need to overwrite the content of the first partition in dbo.Sales with the content of the same partition in stg.Sales. The solution must minimize load times. What should you do?

Switch the first partition from stg.Sales to dbo.Sales.

Insert the data from stg.Sales into dbo.Sales.

Switch the first partition from dbo.Sales to stg.Sales.

Switch the first partition from stg.Sales to dbo.Sales.

Update dbo.Sales from stg.Sales.

You are designing a fact table named FactPurchase in an Azure Synapse Analytics dedicated SQL pool. The table contains purchases from suppliers for a retail store. FactPurchase will contain the following columns. FactPurchase will have 1 million rows of data added daily and will contain three years of data.Transact-SQL queries similar to the following query will be executed daily.SELECTSupplierKey, StockItemKey, COUNT(*)FROM FactPurchaseWHERE DateKey >= 20210101AND DateKey <= 20210131GROUP By SupplierKey, StockItemKeyWhich table distribution will minimize query times?

hash-distributed on PurchaseKey

You are implementing a batch dataset in the Parquet format. Data files will be produced be using Azure Data Factory and stored in Azure Data Lake Storage Gen2. The files will be consumed by an Azure Synapse Analytics serverless SQL pool. You need to minimize storage costs for the solution.What should you do?

Create an external table that contains a subset of columns from the Parquet files.

Use Snappy compression for files.

Use OPENROWSET to query the Parquet files.

Create an external table that contains a subset of columns from the Parquet files.

Store all data as string in the Parquet files.

You are designing a data mart for the human resources (HR) department at your company. The data mart will contain employee information and employee transactions.From a source system, you have a flat extract that has the following fields:EmployeeIDFirstNameLastNameRecipientGrossAmountTransactionIDGovernmentIDNetAmountPaidTransactionDateYou need to design a star schema data model in an Azure Synapse Analytics dedicated SQL pool for the data mart.Which two tables should you create? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

a dimension table for Transaction

a dimension table for EmployeeTransaction

Microsoft DP-203 Practice Test 2 - Free Online Exam Prep & Questions

Question 1 / 40

You have a SQL pool in Azure Synapse.

A user reports that queries against the pool take longer than expected to complete. You determine that the issue relates to queried columnstore segments. You need to add monitoring to the underlying storage to help diagnose the issue. Which two metrics should you monitor? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

Snapshot Storage Size

Cache used percentage

DWU Limit

Cache hit percentage

Comment (0)

Microsoft DP-203 Practice Test 2

Question

Microsoft DP-203 Practice Tests

Practice Tests