DP-203 Flashcards
ou have a table in an Azure Synapse Analytics dedicated SQL pool. The table was created by using the following Transact-SQL statement.
Question
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. [ManagerEmployeeID] [smallint] NULL
B. [ManagerEmployeeKey] [smallint] NULL
C. [ManagerEmployeeKey] [int] NULL
D. [ManagerName] varchar NULL
[ManagerEmployeeKey] [int] NULL
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:
✑ 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
Move to cool storage
Move to archive storage -
You are implementing a star schema in an Azure Synapse Analytics dedicated SQL pool.
You plan to create a table named DimProduct.
DimProduct must be a Type 3 slowly changing dimension (SCD) table that meets the following requirements:
- The values in two columns named ProductKey and ProductSourceID will remain the same.
- The values in three columns named ProductName, ProductDescription, and Color can change.
You need to add additional columns to complete the following table definition.
Which three columns should you add? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. [EffectiveStartDate] [datetime] NOT NULL
B. [EffectiveEndDate] [datetime] NOT NULL
C. [OriginalProductDescription] NVARCHAR(2000) NOT NULL
D. [IsCurrentRow] [bit] NOT NULL
E. [OriginalColor] NVARCHAR(50) NOT NULL
F. [OriginalProductName] NVARCHAR(100) NULL
[OriginalProductDescription] NVARCHAR(2000) NOT NULL
[OriginalColor] NVARCHAR(50) NOT NULL
[OriginalProductName] NVARCHAR(100) NULL
You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a fact table named Table1. Table1 contains sales data. Sixty-five million rows of data are added to Table1 monthly.
At the end of each month, you need to remove data that is older than 36 months. The solution must minimize how long it takes to remove the data.
How should you partition Table1, and how should you remove the old data? To answer, select the appropriate options in the answer area.
Partition by date with one partition per month
switch the oldest partition to another table named Table2 and drop Table2
You have an Azure subscription that contains an Azure Synapse Analytics serverless SQL pool.
You execute the following query.
Question
Where will the rows returned by the query be stored?
A.
in a file in a data lake
B.
in a relational database
C.
in a global temporary table
D.
in a session temporary table
in a file in a data lake
You are deploying a lake database by using an Azure Synapse database template.
You need to add additional tables to the database. The solution must use the same grouping method as the template tables.
Which grouping method should you use?
A.
partition style
B.
business area
C.
size
D.
facts and dimensions
business area
You have an Azure data factory connected to a Git repository that contains the following branches:
- main: Collaboration branch
- abc: Feature branch
- xyz: Feature branch
You save changes to a pipeline in the xyz branch.
You need to publish the changes to the live service.
What should you do first?
A.
Publish the data factory.
B.
Create a pull request to merge the changes into the main branch.
C.
Create a pull request to merge the changes into the abc branch.
D.
Push the code to a remote origin.
Create a pull request to merge the changes into the main branch.
You have an Azure subscription that contains an Azure data factory named ADF1.
From Azure Data Factory Studio, you build a complex data pipeline in ADF1.
You discover that the Save button is unavailable, and there are validation errors that prevent the pipeline from being published.
You need to ensure that you can save the logic of the pipeline.
Solution: You enable Git integration for ADF1.
Does this meet the goal?
A.
Yes
B.
No
Yes
You have an Azure subscription that contains an Azure data factory named ADF1.
From Azure Data Factory Studio, you build a complex data pipeline in ADF1.
You discover that the Save button is unavailable, and there are validation errors that prevent the pipeline from being published.
You need to ensure that you can save the logic of the pipeline.
Solution: You view the JSON code representation of the resource and copy the JSON to a file.
Does this meet the goal?
A.
Yes
B.
No
Yes
You have an Azure subscription that contains an Azure data factory named ADF1.
From Azure Data Factory Studio, you build a complex data pipeline in ADF1.
You discover that the Save button is unavailable, and there are validation errors that prevent the pipeline from being published.
You need to ensure that you can save the logic of the pipeline.
Solution: You export ADF1 as an Azure Resource Manager (ARM) template.
Does this meet the goal?
A.
Yes
B.
No
No
You have an Azure Databricks workspace.
You read data from a CSV file by using a notebook, and then load the data to a DataFrame.
You need to add rows from the DataFrame to an existing Delta table by using Python code.
How should you complete the code? To answer, select the appropriate options in the answer area.
Add to a delta table => format is delta
Add the rows to existing table => append
You have an Azure subscription that contains an Azure Cosmos DB for NoSQL account named account1. The account1 account contains a container named Container1 that has the following configurations:
- Analytical store: On
- TTL: 3600
You need to remove analytical store support from Container1. The solution must meet the following requirements:
- Minimize the impact on the apps that reference Container1.
- Minimize storage usage.
Which four actions should you perform in sequence?
Create a new container named Container2 and copy the contents of Container1 to Container 2
Delete Container1
Create another container named Container1 and copy the contents of Container2 to Container 1
Delete Container2
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.
Distribution
Partition
You have an Azure Synapse Analytics dedicated SQL pool named SQL1 that contains a hash-distributed fact table named Table1.
You need to recreate Table1 and add a new distribution column. The solution must maximize the availability of data.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
You have an Azure subscription that contains an Azure data factory named ADF1.
From Azure Data Factory Studio, you build a complex data pipeline in ADF1.
You discover that the Save button is unavailable, and there are validation errors that prevent the pipeline from being published.
You need to ensure that you can save the logic of the pipeline.
Solution: You disable all the triggers for ADF1.
Does this meet the goal?
A.
Yes
B.
No
No
You have an Azure subscription that contains the resources shown in the following table.
Question
You need to implement Azure Synapse Link for Azure SQL Database.
Which two actions should you perform on sql1? Each correct answer presents a part of the solution.
NOTE: Each correct selection is worth one point.
A.
Update the firewall rules to allow Azure services to access sql1.
B.
Enable the system-assigned managed identity.
C.
From the Access control (IAM) settings, assign the Contributor role to the system-assigned managed identity of workspace1.
D.
Disable Transparent Data Encryption (TDE).
Update the firewall rules to allow Azure services to access sql1.
From the Access control (IAM) settings, assign the Contributor role to the system-assigned managed identity of workspace1.
You have an Azure Data Lake Storage account that contains CSV files. The CSV files contain sales order data and are partitioned by using the following format.
/data/salesorders/year=xxxx/month=y
You need to retrieve only the sales orders from January 2023 and February 2023.
How should you complete the query? To answer, select the appropriate options in the answer area.
