DP-203: Data Engineering on Microsoft Azure
You are receiving an error message in Azure Synapse Analytics, You want to view information about the service and help to solve the problem, what can you use to quickly check the availability of the service?
Network performance monitor
Diagnose and solve problems
Azure monitor
Answer is Diagnose and solve problems
Diagnose and solve problems. Diagnose and solve problems can quickly show you the service availability. Azure Monitor allows you in collecting, analyzing, and acting on telemetry from both cloud and on-premises environments. Network performance monitor measure the performance and reachability of the networks that you have configured.
How long is the Recovery Point Objective for Azure Synapse Analytics?
4 hours
8 hours
12 hours
16 hours
Answer is 8 hours.
Azure Synapse Analytics has a Recovery Point Objective of 8 hours.
You have an enterprise data warehouse in Azure Synapse Analytics named DW1 on a server named Server1.
You need to verify whether the size of the transaction log file for each distribution of DW1 is smaller than 160 GB.
What should you do?
On the master database, execute a query against the sys.dm_pdw_nodes_os_performance_counters dynamic management view.
From Azure Monitor in the Azure portal, execute a query against the logs of DW1.
On DW1, execute a query against the sys.database_files dynamic management view.
Execute a query against the logs of DW1 by using the Get-AzOperationalInsightSearchResult PowerShell cmdlet.
Answer is On the master database, execute a query against the
sys.dm_pdw_nodes_os_performance_counters dynamic management view.The following query returns the transaction log size on each distribution. If one of the log files is reaching 160 GB, you should consider scaling up your instance or limiting your transaction size.
-- Transaction log size SELECT instance_name as distribution_db, cntr_value*1.0/1048576 as log_file_size_used_GB, pdw_node_id FROM sys.dm_pdw_nodes_os_performance_counters WHERE instance_name like 'Distribution_%' AND counter_name = 'Log File(s) Used Size (KB)'
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-monitor
You have an enterprise data warehouse in Azure Synapse Analytics.
You need to monitor the data warehouse to identify whether you must scale up to a higher service level to accommodate the current workloads.
Which is the best metric to monitor?
More than one answer choice may achieve the goal. Select the BEST answer.
CPU percentage
DWU used
DWU percentage
Data IO percentage
Answer is DWU used
DWU used, defined as DWU limit * DWU percentage, represents only a high-level representation of usage across the SQL pool and is not meant to be a comprehensive indicator of utilization. To determine whether to scale up or down, consider all factors which can be impacted by DWU such as concurrency, memory, tempdb, and adaptive cache capacity. We recommend running your workload at different DWU settings to determine what works best to meet your business objectives.
Reference:
https://docs.microsoft.com/bs-latn-ba/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-concept-resource-utilization-query-activity
You plan to create a dimension table in Azure Synapse Analytics that will be less than 1 GB.
You need to create the table to meet the following requirements:
- Provide the fastest query time.
- Minimize data movement during queries.
Which type of table should you use?
hash distributed
heap
replicated
round-robin
Answer is Replicated
Usually common dimension tables or tables that doesn't distribute evenly are good candidates for round-robin distributed table. Note: Dimension tables or other lookup tables in a schema can usually be stored as round-robin tables. Usually these tables connect to more than one fact tables and optimizing for one join may not be the best idea. Also usually dimension tables are smaller which can leave some distributions empty when hash distributed.
Round-robin by definition guarantees a uniform data distribution.
Reference:
https://blogs.msdn.microsoft.com/sqlcat/2015/08/11/choosing-hash-distributed-table-vs-round-robin-distributed-table-in-azure-sql-dw-service/
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:
- Create four partitions based on the order date.
- Ensure that each partition contains all the orders placed during a given calendar year.
How should you complete the T-SQL command?
check
Box 1: LEFT
RANGE LEFT: Specifies the boundary value belongs to the partition on the left (lower values). The default is LEFT.
Box 2: 20090101, 20100101, 20110101, 20120101
FOR VALUES ( boundary_value [,...n] ) specifies the boundary values for the partition. boundary_value is a constant expression.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse
You have a SQL pool in Azure Synapse that contains a table named dbo.Customers. The table contains a column name Email.
You need to prevent nonadministrative users from seeing the full email addresses in the Email column. The users must see values in a format of aXXX@XXXX.com instead.
What should you do?
From Microsoft SQL Server Management Studio, set an email mask on the Email column.
From the Azure portal, set a mask on the Email column.
From Microsoft SQL Server Management studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.
From the Azure portal, set a sensitivity classification of Confidential for the Email column.
Answer is From the Azure portal, set a mask on the Email column.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview
You are designing an enterprise data warehouse in Azure Synapse Analytics. You plan to load millions of rows of data into the data warehouse each day.
You must ensure that staging tables are optimized for data loading.
You need to design the staging tables.
What type of tables should you recommend?
Round-robin distributed table
Hash-distributed table
Replicated table
External table
Answer is Round-robin distributed table
You have a SQL pool in Azure Synapse.
A user reports that queries against the pool take longer than expected to complete.
You need to add monitoring to the underlying storage to help diagnose the issue.
Which two metrics should you monitor?
Cache used percentage
DWU Limit
Snapshot Storage Size
Active queries
Cache hit percentage
Answers are Cache used percentage and Cache hit percentage
Cache used percentage: Cache used is the sum of all bytes in the local SSD cache across all nodes and cache capacity is the sum of the storage capacity of the local SSD cache across all nodes.
Cache hit percentage: Cache hits is the sum of all columnstore segments hits in the local SSD cache and cache miss is the columnstore segments misses in the local SSD cache summed across all nodes.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-concept-resource-utilization-query-activity
You have a SQL pool in Azure Synapse.
You discover that some queries fail or take a long time to complete.
You need to monitor for transactions that have rolled back.
Which dynamic management view should you query?
sys.dm_pdw_nodes_tran_database_transactions
sys.dm_pdw_waits
sys.dm_pdw_request_steps
sys.dm_pdw_exec_sessions
Answer is sys.dm_pdw_nodes_tran_database_transactions
You can use Dynamic Management Views (DMVs) to monitor your workload including investigating query execution in SQL pool.
If your queries are failing or taking a long time to proceed, you can check and monitor if you have any transactions rolling back.
Example:
-- Monitor rollback
SELECT
SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END), t.pdw_node_id, nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor#monitor-transaction-log-rollback