Hello friends if you are looking for DP – 203 Multiple choice questions with answers | DP – 203 MCQ with answers | DP – 203 Question answers dumps | DP – 203 Objective type questions | DP – 203 Microsoft Free question answers
1.You have a table in an Azure Synapse Analytics dedicated SQL pool. The table was created by using the following Transact-SQL statement.
CREATE TABLE [dbo].[DimEmployee](
[EmployeeKey] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[FirstName] varchar NOT NULL,
[LastName] varchar NOT NULL,
[JobTitle] varchar NULL,
[LastHireDate] [date] NULL,
[StreetAddress] varchar NOT NULL,
[City] varchar NOT NULL,
[StateProvince] varchar NOT’ NULL,
[Portalcode] varchar NOT NULL
)
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?
A. [ManagerEmployeelD] [smallint] NULL
B. [ManagerEmployeeKey] [smallint] NULL
C. [ManagerEmployeeKey] [int] NULL
D. [ManagerName] varchar NULL
C
2.You have an Azure Synapse workspace named MyWorkspace that contains an Apache Spark database named mytestdb.
You run the following command in an Azure Synapse Analytics Spark pool in MyWorkspace.
CREATE TABLE mytestdb.myParquetTable(
EmployeelD int,
EmployeeName string,
EmployeeStartDate date)
USING Parquet-
You then use Spark to insert a row into mytestdb.myParquetTable. The row contains the following data.
EmployeeName
EmployeelD
EmployeeStartDate
Alice
24
One minute later, you execute the following query from a serverless SQL pool in MyWorkspace.
SELECT EmployeelD –
FROM mytestdb.dbo.myParquetTable
WHERE EmployeeName = Alice’;
What will be returned by the query?
A.24
B. an error
C. a null value
A
3.You have a table named SalesFact in an enterprise data warehouse in Azure Synapse Analytics. SalesFact contains sales data from the past 36
months and has the following characteristics:
cs* Is partitioned by month
Contains one billion rows
cs* Has clustered columnstore index
At the beginning of each month, you need to remove data from SalesFact that is older than 36 months as quickly as possible.
Which three actions should you perform in sequence in a stored procedure? To answer, move the appropriate actions from the list of actions to the
answer area and arrange them in the correct order.
Switch the partition containing the stale data
from SalesFact to SalesFact.Work —>
Create an empty table named SalesFact_Work
that has the same schema as SalesFact.
Truncate the partition containing the stale data —>
Switch the partition containing the stale data
from SalesFact to SalesFact,Work.
Drop the SalesFact_Work table. ——->Drop the SalesFact_Work table.
Create an empty table named SalesFact_Work
that has the same schema as SalesFact.
4.You are planning the deployment of Azure Data Lake Storage Gen2.
You have the following two reports that will access the data lake:
cs* Reportl: Reads three columns from a file that contains 50 columns.
<=® Report2: Queries a single record based on a timestamp.
You need to recommend in which format to store the data in the data lake to support the reports. The solution must minimize read times.
What should you recommend for each report? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
ANS-
5.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?
ans – {Region}/{SubjectMatter(s)}/{yyyy}/{mm}/{dd}/{hh}
6.You need to output files from Azure Data Factory. Which file format should you use for each type of output? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Ans –
7.You use Azure Data Factory to prepare data to be queried by Azure Synapse Analytics serverless SQL pools.
Files are initially ingested into an Azure Data Lake Storage Gen2 account as 10 small JSON files. Each file contains the same data attributes and data from a subsidiary of your company. You need to move the files to a different folder and transform the data to meet the following requirements: Provide the fastest possible query times. Automatically infer the schema from the underlying files. How should you configure the Data Factory copy activity? To answer, select the appropriate options in the answer area.
Flatten hierarchy
Merge files
Preserve hierarchy
CSV
JSON __
Parquet
TXT
8.You have an Azure Data Lake Storage Gen2 container.
Data is ingested into the container, and then transformed by a data integration application. The data is NOT modified after that. Users can read
files in the container but cannot modify the files. You need to design a data archiving solution that meets the following requirements:
c® New data is accessed frequently and must be available as quickly as possible.
Data that is older than five years is accessed infrequently but must be available within one second when requested. Data that is older than seven years is NOT accessed. After seven years, the data must be persisted at the lowest cost possible.
Costs must be minimized while maintaining the required availability.
How should you manage the data? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point
Ans –
Five-year-old data:
Delete the blob.
Move to archive storage.
Move to cool storage.
Move to hot storage.
Seven-year-old data:
Delete the blob.
Move to archive storage.
Move to cool storage.
Move to hot storage.
9.You need to create a partitioned table in an Azure Synapse Analytics dedicated SQL pool. How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value may be used
once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Ans – CREATE TABLE tablel
(
ID INTEGER,
coll VARCHAR(IO),
col2 VARCHAR(IO)
) WITH
(
DISTRIBUTION = HASH (ID) ,
PARTITION – (ID RANGE LEFT FOR VALUES (1, 1000000, 2000000))
10.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?
Ans – as a Type 2 slowly changing dimension (SCD) table
11.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.
A. Add the managed identity to the Sales group.
B. Use the managed identity as the credentials for the data load process.
C. Create a shared access signature (SAS).
D. Add your Azure Active Directory (Azure AD) account to the Sales group.
E. Use the shared access signature (SAS) as the credentials for the data load process.
F. Create a managed identity.
Ans – ABF
12.You have an enterprise data warehouse in Azure Synapse Analytics.
Using PolyBase, you create an external table named [Ext].[Items] to query Parquet files stored in Azure Data Lake Storage Gen2 without importing
the data to the data warehouse.
The external table has three columns.
You discover that the Parquet files have a fourth column named ItemID.
Which command should you run to add the ItemID column to the external table?
Ans – DROP EXTERNAL TABLE [Ext].[Items]
CREATE EXTERNAL TABLE [Ext].[Items]
([ItemID] [int] NULL,
[ItemName] nvarchar(50) NULL,
[ItemType] nvarchar(20} NULL,
[ItemDescription] nvarchar(250))
WITH
(
LOCATION= 1/Iterns/’,
DATA_SOURCE = AznreDataLakeStore,
FILE_FORMAT = PARQUET,
REJECTTYPE = VALUE,
REJECTVALUE = 0
};
13.You have two Azure Storage accounts named Storagel 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 Storagel to Storage2 by using a Data Factory copy activity. The solution must meet the following
requirements: No transformations must be performed.
The original folder structure must be retained. 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.
Answer Area
Binary
Parquet
Delimited text
FlattenHierarchy
MergeFiles
PreserveHierarchy
14.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?
A. geo-redundant storage (GRS)
B. read-access geo-redundant storage (RA-GRS)
C. zone-redundant storage (ZRS)
D. locally-redundant storage (LRS)
Ans – b
15.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?
A. geo-redundant storage (GRS)
B. geo-zone-redundant storage (GZRS)
C. locally-redundant storage (LRS)
D. zone-redundant storage (ZRS)
Ans – d
16.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.
Ans –
Distribution -Hash
Replicated
Round-robin
Indexing – Clustered _
Clustered columnstore
Heap
Partitioning –
