DP-900

Question 1 CertyIQ
HOTSPOT -
To complete the sentence, select the appropriate option in the answer area.
Hot Area:
Explanation:
Descriptive Analytics tells you what happened in the past. Diagnostic Analytics helps you understand why something
happened in the past. Diagnostic analytics ask about the present. They drill down into why something has happened and
helps users diagnose issues. Descriptive analytics ask about the past. They want to know what has been happening to
the business and how this is likely to affect future sales.
Question 2 CertyIQ
HOTSPOT -
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:
2022 Latest AZ-900 Exam Actual Questions and other exam series on CertyIQ (YouTube Channel)
Explanation:
Normalization is the process of organizing data in a database. This includes creating tables and establishing
relationships between those tables according to rules designed both to protect the data and to make the database more
flexible by eliminating redundancy and inconsistent dependency. https://docs.microsoft.com/en-
us/office/troubleshoot/access/database-normalization-
description#:~:text=Normalization%20is%20the%20process%20of,eliminating%20redundancy%20and%20inconsistent%2
0dependency
Question 3 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Extract, transform, and load (ETL) is a data pipeline used to collect data from various sources, transform the data
according to business rules, and load it into a destination data store.
Reference:
https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/etl
Question 4 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Answer C is correct in this context.
Advantages of using Azure Batch
Azure Batch is especially well-suited to running large-scale parallel and high-performance computing (HPC) batch jobs.
The service handles everything for you -- managing and scheduling all the nodes and applications required to run your
scenarios. And it's a free service, so you only pay for the underlying compute, storage, and networking resources that
you use.
Batch processing is parallelly
https://docs.microsoft.com/en-us/learn/modules/run-parallel-tasks-in-azure-batch-with-the-azure-cli/2-what-is-azure-
batch
Question 5 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Transcribe means converting from Audio to Text. This is similar to Alexa, Alexa is a Cognitive device. Cognitive is the
correct answer.
https://docs.microsoft.com/en-us/learn/modules/data-analytics-microsoft/2-data-analysis
Question 6 CertyIQ
DRAG DROP -
Match the types of analytics that can be used to answer the business questions.
To answer, drag the appropriate analytics type from the column on the left to its question on the right. Each analytics
type may be used once, more than once, or not at all.
NOTE: Each correct match is worth one point.
Select and Place:
Explanation:
Box 1: Diagnostic -
Diagnostic Analytics: At this stage you can begin to answer some of those why questions. Historical data can begin to
be measured against other data to answer the question of why something happened in the past. This is the process of
gathering and interpreting different data sets to identify anomalies, detect patters, and determine relationships.
Box 2: Prescriptive -
Prescriptive analytics is a combination of data, mathematical models, and various business rules to infer actions to
influence future desired outcomes.
Predictive analytics, broadly speaking, is a category of business intelligence that uses descriptive and predictive
variables from the past to analyze and identify the likelihood of an unknown future outcome
Box 3: Descriptive -
Generally speaking, data analytics comes in four types:
✑ Descriptive, to answer the question: What's happening?

✑ Diagnostic, to answer the question: Why's happening?
✑ Predictive, to answer the question: What will happen?
✑ Prescriptive, to answer the question: What actions should we take?
Reference:
https://demand-planning.com/2020/01/20/the-differences-between-descriptive-diagnostic-predictive-cognitive-
analytics/
https://azure.microsoft.com/en-us/blog/answering-whats-happening-whys-happening-and-what-will-happen-with-iot-
analytics/
Question 7 CertyIQ
Your company uses both relational and non-relational data.

You need to determine which tools are suitable for each of the following tasks.
What tools should you use? To answer, drag the appropriate option to the correct task. Each option may be used once,
more than once, or not at all.
Drag and drop the answers
Correct Answers: -
Explanation:
You should use Azure Data Factory to extract data from external sources. Azure Data Factory allows you to extract data
from both relational and non-relational data stores.
You should use Azure Data Lake Storage to maintain relational and NoSQL data in a data store. You can load raw data
into Azure Data Lake storage.
You should use Azure Synapse Analytics to analyze relational and NoSQL data. You can analyze a high volume of
structured and unstructured data with Azure Synapse Analytics.
Question 8 CertyIQ
HOTSPOT -
Hot Area:
Correct Answers: -
Explanation:
Azure Databricks is an analytics platform based on Apache Spark. Azure Databricks is a complete platform for big data
processing, streaming, and machine learning optimized for the Microsoft Azure cloud services platform and built on top
of Apache Spark.
Azure Databricks can handle batch and stream processing. You can also perform real-time data processing and event
streaming from Azure Event Hubs with Azure Databricks.
Azure Databricks provides an interactive workspace for exploration and data visualization. Azure Databricks provides a
workspace for collaboration between data scientists, data engineers, and business analysts. You can run notebooks in R,
Python, Scala, or SQL, and interact with the data very quickly.
Question 9 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/massively-parallel-processing-mpp-
architecture
Question 10 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns
included in the index definition. There can be only one clustered index per table, because the data rows themselves can
be stored in only one order.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-
described?view=sql-server-ver15
Question 11 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Disadvantages of non-relational databases include: Data Consistency non-relational databases do not perform ACID
transactions.
Note: Relational databases are optimized for writes. They are optimized for consistency and availability. Advantages of
relational databases include simplicity, ease of data retrieval, data integrity, and flexibility.
Incorrect Answers:
Use a relational database when data that you work with is structured, and the structure is not subject to frequent
changes.
Use Cloud storage (no relational database) for geographically distributed writes.
Reference:
https://towardsdatascience.com/choosing-the-right-database-c45cd3a28f77
Question 12 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Box 1: Yes -
Big data solutions often use long-running batch jobs to filter, aggregate, and otherwise prepare the data for analysis.
Usually, these jobs involve reading source files from scalable storage (like HDFS, Azure Data Lake Store, and Azure
Storage), processing them, and writing the output to new files in scalable storage.
Box 2: Yes -
Box 3: Yes -
Reference:
https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/batch-processing
Question 13 CertyIQ
DRAG DROP -
Your company plans to load data from a customer relationship management (CRM) system to a data warehouse by using
an extract, load, and transform (ELT) process.
Where does data processing occur for each stage of the ELT process? To answer, drag the appropriate locations to the
correct stages. Each location 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.
Select and Place:
Explanation:
Box 1: The CRM system -
Data is extracted from the CRM system.
Box 2: The data warehouse -

Data is loaded to the data warehouse.

In the ELT pipeline, the transformation occurs in the target data store. Instead of using a separate transformation
engine, the processing capabilities of the target data store are used to transform data.
https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/etl#extract-load-and-transform-elt
Question 14 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Generally speaking, data analytics comes in four types (Figure 1):
Descriptive, to answer the question: What's happening?
Diagnostic, to answer the question: Why's happening?
Predictive, to answer the question: What will happen?
Prescriptive, to answer the question: What actions should we take?
Reference:
analytics/
Question 15 CertyIQ
DRAG DROP -
Match the types of visualizations to the appropriate descriptions.
To answer, drag the appropriate visualization type from the column on the left to its description on the right. Each
visualization type may be used once, more than once, or not at all.
Select and Place:
Explanation:
Box 1: Tree map -
Treemaps are charts of coloured rectangles, with size representing value. They can be hierarchical, with rectangles
nested within the main rectangles.
Box 2: Key influencer -

A key influencer chart displays the major contributors to a selected result or value.
Box 3: Scatter -
Scatter and Bubble charts display relationships between 2 (scatter) or 3 (bubble) quantitative measures -- whether or
not, in which order, etc.
Question 16 CertyIQ
You need to create an Azure Storage account.

Data in the account must replicate outside the Azure region automatically.
Which two types of replication can you use for the storage account? Each correct answer presents a complete solution.
A. zone-redundant storage (ZRS)
B. read-access geo-redundant storage (RA-GRS)
C. locally-redundant storage (LRS)
D. geo-redundant storage (GRS)
Explanation:
D: Azure Storage offers two options for copying your data to a secondary region:
✑ Geo-redundant storage (GRS)
✑ Geo-zone-redundant storage (GZRS)
B: With GRS or GZRS, the data in the secondary region isn't available for read or write access unless there is a failover to
the secondary region. For read access to the secondary region, configure your storage account to use read-access geo-
redundant storage (RA-GRS) or read-access geo-zone-redundant storage (RA-GZRS).
Reference:
https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy#redundancy-in-a-secondary-region
Question 17 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Box 1: Yes -
Like IaaS, PaaS includes infrastructure "servers, storage, and networking" but also middleware, development tools,
business intelligence (BI) services, database management systems, and more. PaaS is designed to support the complete
web application lifecycle: building, testing, deploying, managing, and updating.
PaaS allows you to avoid the expense and complexity of buying and managing software licenses, the underlying
application infrastructure and middleware, container orchestrators such as Kubernetes, or the development tools and
other resources
Box 2: No -
You manage the applications and services you develop, and the cloud service provider typically manages everything
else.
Box 3: No -
There really is no way to pause / stop billing for your Azure SQL Database.
Microsoft's official answer "Yes, you can export your database. Delete the Azure SQL database and that will pause
billing. Then when you need it you can create a new database and import your previously exported DB."
Reference:
https://azure.microsoft.com/en-us/overview/what-is-paas
Question 18 CertyIQ
Which statement is an example of Data Manipulation Language (DML)?

A. REVOKE
B. DISABLE
C. INSERT
D. GRANT
Explanation:
There's 3 type of Relational data SQL:
DDL (Data Definition Language) CREATE, DROP, ALTER, RENAME, COMMENT AND TRUNCATE.
DML (Data Manipulation Language) SELECT, INSERT INTO, DELETE, UPDATE.
DCL (Data Control Language) REVOKE & GRANT.
TCL (Transactional Control Language) COMMIT & ROLLBACK
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-reference-tsql-
statements
Question 19 CertyIQ
You have a SQL query that combines customer data and order data. The query includes calculated columns.
You need to create a database object that would allow other users to rerun the same SQL query.
What should you create?
A. an index
B. a view
C. a scalar function
D. a table
Explanation:
A view is a virtual table whose contents are defined by a query. A view acts as a filter on the underlying tables referenced
in the view. The query that defines the view can be from one or more tables or from other views in the current or other
databases.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/views/views
Question 20 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Box 1: simple lookups -
A key/value store associates each data value with a unique key. Most key/value stores only support simple query, insert,
and delete operations. To modify a value (either partially or completely), an application must overwrite the existing data
for the entire value. In most implementations, reading or writing a single value is an atomic operation.
An application can store arbitrary data as a set of values. Any schema information must be provided by the application.
The key/value store simply retrieves or stores the value by key.
Reference:
https://docs.microsoft.com/en-us/azure/architecture/guide/technology-choices/data-store-overview
Question 21 CertyIQ
DRAG DROP -
Match the types of data to the appropriate Azure data services.
To answer, drag the appropriate data type from the column on the left to its service on the right. Each data type may be
used once, more than once, or not at all.
Select and Place:
Explanation:
Box 1: Image files -
Azure Blob storage is suitable for image files.
Box 2: - Relationship between employees

the relationship between employees is one of the first examples of a graph database (and therefore Gremlin API).
Box 3: Key/value pairs

Key/value pairs = Azure Table Storage
Reference: https://docs.microsoft.com/en-us/azure/storage/tables/table-storage-design-modeling
Question 22 CertyIQ
DRAG DROP -
Match the Azure Data Lake Storage Gen2 terms to the appropriate levels in the hierarchy.
To answer, drag the appropriate term from the column on the left to its level on the right. Each term may be used once,
more than once, or not at all.
Select and Place:
Correct Answers: -
Explanation:
Box 1: Azure Storage account -
Azure file shares are deployed into storage accounts, which are top-level objects that represent a shared pool of storage.
Box 2: Container -
Reference:
https://docs.microsoft.com/en-us/learn/modules/explore-provision-deploy-non-relational-data-services-azure/3-
azure-data-lake-gen2
Question 23 CertyIQ
What are two characteristics of real-time data processing? Each correct answer presents a complete solution.
A. Data is processed periodically
B. Low latency is expected
C. High latency is acceptable
D. Data is processed as it is created
Explanation:
Real time processing deals with streams of data that are captured in real-time and processed with minimal latency to
generate real-time (or near-real-time) reports or automated responses.
Reference:
https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/real-time-processing
Question 24 CertyIQ
DRAG DROP -
Match the Azure Data Factory components to the appropriate descriptions.
To answer, drag the appropriate component from the column on the left to its description on the right. Each component
may be used once, more than once, or not at all.
Select and Place:
Explanation:
Box 1: Dataset -
Datasets must be created from paths in Azure datastores or public web URLs, for the data to be accessible by Azure
Machine Learning.
Box 2: Linked service -

Linked services are much like connection strings, which define the connection information needed for Data Factory to
connect to external resources.
Box 3: Pipeline -
A pipeline is a logical grouping of activities that together perform a task.
Reference:
https://k21academy.com/microsoft-azure/dp-100/datastores-and-datasets-in-azure/
https://docs.microsoft.com/en-us/azure/data-factory/concepts-linked-services
https://docs.microsoft.com/en-us/azure/data-factory/concepts-pipelines-activities
Question 25 CertyIQ
DRAG DROP -
Match the types of workloads to the appropriate scenarios.
To answer, drag the appropriate workload type from the column on the left to its scenario on the right. Each workload
Select and Place:
Explanation:
Box 1: Batch -
Batch processing refers to the processing of blocks of data that have already been stored over a period of time.
Box 2: Streaming -
Stream processing is a big data technology that allows us to process data in real-time as they arrive and detect
conditions within a small period of time from the point of receiving the data. It allows us to feed data into analytics tools
as soon as they get generated and get instant analytics results.
Box 3: Batch -
Reference:
https://docs.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/batch-processing
Question 26 CertyIQ
Which type of database is used to store structured data?

A. Graph
B. Relational
C. Column Family
D. Key-value
Explanation:
Relational databases store structured data in tables. Records in separate tables are related to each other using keys,
which are columns storing unique values per table row. Tables allow the storage of records in the same structure,
containing the same attributes. Structured data is usually stored in a relational fashion; it minimizes storage costs
through reducing duplication of data. Structured data is commonly queried using SQL (structured query language), which
is why non-relational databases that store semi-structured data are sometimes referred to as NoSQL.
Key-value databases are non-relational and can be used to store semi-structured data. Semi-structured data allows for
variation between each instance of a data entity. For example, one record may contain a single email address as an
attribute, another may contain three, and another may not contain any email addresses. Key-value databases allow two
components of data to be stored related to an individual record: a unique key and a value, which can contain different
types, formats and amounts of data per record.
Column family databases are non-relational and can be used to store semi-structured data. Tables in column family
databases can be divided into groups (column-families), holding sets of columns that are related to each other.
Graph databases are non-relational and can be used to store semi-structured data. Graph databases contain an
additional dimension to many other types of database; they store entities as nodes and allow relationships to other
nodes (edges) to have a direction. For instance, a parent/child relationship between two person nodes would indicate
which is the parent and which is the child.
Question 27 CertyIQ
Your company recently reported sales from the third quarter.

You have the chart shown in the following exhibit.
Which type of analysis is shown in the fourth quarter?
A. predictive
B. prescriptive
C. descriptive
D. diagnostic
Explanation:
Predictive, to answer the question: What will happen?
Reference:
analytics/
analytics/
Question 28 CertyIQ

A. REVOKE
B. DISABLE
C. CREATE
D. UPDATE
Explanation:
Data Manipulation Language (DML) affect the information stored in the database. Use these statements to insert,
update, and change the rows in the database.
BULK INSERT -
DELETE -
INSERT -
SELECT -
UPDATE -
MERGE –
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/statements
Question 29 CertyIQ
HOTSPOT -
Hot Area:
Correct Answer: -
Explanation:
It doesn't matter if a human or an algorithm generates the subtitles. First the content of the audio file must be
recognized. This is a recognizing. Closed captions are a text version of the spoken part of a television, movie or
computer presentation.
It’s created for the deaf people especially, definitely cognitive because it is doing something on its own.
https://azure.microsoft.com/en-us/services/cognitive-services/speech-to-text/#overview
Question 30 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Generally speaking, data analytics comes in four types:
1. Descriptive, to answer the question: What's happening?
2. Diagnostic, to answer the question: Why's happening?
3. Predictive, to answer the question: What will happen?
4. Prescriptive, to answer the question: What actions should we take?
Reference:
analytics/
Question 31 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
With ELT, the data store used to perform the transformation is the same data store where the data is ultimately
consumed.
Reference:
Question 32 CertyIQ
You need to create an Azure resource to store data in Azure Table storage.
Which command should you run?
A. az storage share create
B. az storage account create
C. az cosmosdb create
D. az storage container create
Explanation:
Table is created under storage account as mentioned in the link
https://docs.microsoft.com/en-us/azure/storage/tables/table-storage-overview
also URL format for Azure Table Storage is
http://<storage account>.table.core.windows.net/<table>
Question 33 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Azure Cosmos DB SQL API accounts provide support for querying items using the Structured Query Language (SQL)
syntax.
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/choose-api
Question 34 CertyIQ
You need to modify a view in a relational database by adding a new column.

Which statement should you use?
A. MERGE
B. ALTER
C. INSERT
D. UPDATE
Explanation:
ALTER - add/delete/modify column
UPDATE - edit existing row
INSERT - Insert new row
to add a column in a view Alter (https://docs.microsoft.com/en-us/sql/relational-databases/tables/add-columns-to-a-
table-database-engine?view=sql-server-ver15 )
Insert adds one or more rows to a table (https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-
sql?view=sql-server-ver15 )
Question 35 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Box one is yes - You can reduce transfer sensitive data with transformations on data source.
Box two is No - ELT use the compute resource from destination system.
Box three is Yes. ELT minimizes time with transformations are going to be processed on target data destination
systems. (The key point with ELT is that the data store used to perform the transformation is the same data store where
the data is ultimately consumed. This data store reads directly from the scalable storage, instead of loading the data
into its own proprietary storage. This approach skips the data copy step present in ETL, which often can be a time-
consuming operation for large data sets.)
Question 36 CertyIQ
HOTSPOT -
You plan to deploy a PostgreSQL database to Azure.
Which hosting model corresponds to the available deployment options? To answer, select the appropriate options in the
answer area.
Hot Area:
Explanation:
The correct answer is IaaS and PaaS.
under Postgres on Azure VMs, everything is customer managed that means Postgres on Azure VMs is an IAAS.
https://docs.microsoft.com/en-us/azure/postgresql/overview-postgres-choose-server-options
Question 37 CertyIQ
HOTSPOT -
Hot Area:
Correct Answer:
Explanation:
Graph databases can efficiently perform queries across the network of nodes and edges and analyze the relationships
between entities.
Reference: https://docs.microsoft.com/en-us/azure/architecture/guide/technology-choices/data-store-overview
Question 38 CertyIQ
Which Azure storage solution provides native support for POSIX-compliant access control lists (ACLs)?
A. Azure Table storage
B. Azure Data Lake Storage
C. Azure Queue storage
D. Azure Files
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-access-control
Question 39 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
A relational database stores data in tables. Tables are organized into columns and row.
Question 40 CertyIQ
HOTSPOT -
Hot Area:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-
engine?view=sql-server-ver15
Question 42 CertyIQ
DRAG DROP -
Match the types of analytics that can be used to answer the business questions.
To answer, drag the appropriate analytics type from the column on the left to its question on the right. Each analytics
Select and Place:
Explanation:
Box 1: Diagnostic -
Diagnostic Analytics: At this stage you can begin to answer some of those why questions. Historical data can begin to
be measured against other data to answer the question of why something happened in the past. This is the process of
gathering and interpreting different data sets to identify anomalies, detect patters, and determine relationships.
Box 2: Prescriptive -
Prescriptive analytics is a combination of data, mathematical models, and various business rules to infer actions to
influence future desired outcomes.
Predictive analytics, broadly speaking, is a category of business intelligence that uses descriptive and predictive
variables from the past to analyze and identify the likelihood of an unknown future outcome
Box 3: Cognitive -
Reference:
analytics/
analytics/
Question 43 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
A view - presents content defined by a query
Question 44 CertyIQ
Which type of database is Azure Database for PostgreSQL?

A. Platform as a service (PaaS)
B. Infrastructure as a service (IaaS)
C. Microsoft SQL Server
D. on-premises
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/postgresql/overview-postgres-choose-server-options
Question 45 CertyIQ
Which storage solution supports access control lists (ACLs) at the file and folder level?
A. Azure Data Lake Storage
B. Azure Queue storage
C. Azure Blob storage
D. Azure Cosmos DB
Explanation:
Azure Data Lake Storage Gen2 implements an access control model that supports both Azure role-based access control
(Azure RBAC) and POSIX-like access control lists (ACLs).
Reference:
https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-access-control
Question 46 CertyIQ
HOTSPOT -
Select the answer that correctly completes the sentence.
Hot Area:
Correct Answer: -
Explanation:
A treemap chart divides the chart area into rectangles that represent the different levels and relative sizes of the data
hierarchy.
Reference:
https://docs.microsoft.com/en-us/sql/reporting-services/report-design/tree-map-and-sunburst-charts-in-reporting-
services?view=sql-server-ver15
Question 47 CertyIQ
What is a characteristic of batch processing?

A. The data ingested during batch processing must be processed as soon as the data is received.
B. Large datasets must be split into batches of less than 1 GB before the data can be processed.
C. There is a noticeable time delay between ingesting data and obtaining the data processing results.
D. Batch processing can only process data that is structured.
Explanation:
Answer is C - There is a delay between ingestion and output because the batch has to be full for the processing to start.
Batching is perfectly capable of handling any sort of data, including unstructured data that is usually sent as streams.
Question 48 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
An aggregate function performs a calculation on a set of values, and returns a single value. The following are aggregate
functions:
✑ APPROX_COUNT_DISTINCT
✑ AVG
✑ CHECKSUM_AGG
✑ COUNT
✑ COUNT_BIG
✑ GROUPING
✑ GROUPING_ID
✑ MAX
✑ MIN
✑ STDEV
✑ STDEVP
✑ STRING_AGG
✑ SUM
VAR -
✑ VARP
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15
Question 49 CertyIQ
HOTSPOT -
Hot Area:
Correct Answer:
Explanation:
Box 1: Yes -
Batch processing refers to processing of high volume of data in batch within a specific time span. It processes large
volume of data all at once. Batch processing is used when data size is known and finite. It takes little longer time to
processes data.
Box 2: Yes -
Stream processing refers to processing of continuous stream of data immediately as it is produced. It analyzes
streaming data in real time. Stream processing is used when the data size is unknown and infinite and continuous.
Box 3: No -
Reference:
https://www.geeksforgeeks.org/difference-between-batch-processing-and-stream-processing/
Question 50 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Reference:
https://teachcomputerscience.com/relational-databases/
Question 51 CertyIQ
You have an inventory management database that contains the following table.
Which statement should you use in a SQL query to change the inventory quantity of Product1 to 270?
A. INSERT
B. MERGE
C. UPDATE
D. CREATE
Explanation:
ALTER - add/delete/modify column
UPDATE - update existing row
INSERT - Insert new row
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15
Question 52 CertyIQ
Your company needs to implement a relational database in Azure. The solution must minimize ongoing maintenance.
Which Azure service should you use?
A. Azure HDInsight
B. Azure SQL Database
C. Azure Cosmos DB
D. SQL Server on Azure Virtual Machines
Explanation:
Minimize maintenance: so it should a PaaS + Relational Database = Azure SQL Database
SQL Database - https://azure.microsoft.com/en-us/products/azure-sql/database/#features
Fully managed SQL database automates updates, provisioning, and backups so you can focus on application
development
Reference: https://azure.microsoft.com/en-us/services/sql-database/#features
Question 53 CertyIQ
You are writing a set of SQL queries that administrators will use to troubleshoot an Azure SQL database.
You need to embed documents and query results into a SQL notebook.
What should you use?
A. Microsoft SQL Server Management Studio (SSMS)
B. Azure Data Studio
C. Azure CLI
D. Azure PowerShell
Explanation:
B. Azure Data Studio.
It allows you to save your query and even create charts or simple reports that you can re-visit.
Reference:
https://www.mssqltips.com/sqlservertip/5997/create-sql-server-notebooks-in-azure-data-studio/
Question 54 CertyIQ
DRAG DROP -
Match the terms to the appropriate descriptions.
To answer, drag the appropriate term from the column on the left to its description on the right. Each term may be used
once, more than once, or not at all.
Select and Place:
Explanation:
Reference:
https://en.wikipedia.org/wiki/Table_(database)#:~:text=A%20table%20is%20a%20collection,table%20format%20within%
20a%20database.&text=In%20relational%20databases%2C%20and%20flat,a%20row%20and%20column%20intersect
https://en.wikipedia.org/wiki/View_(SQL)
https://en.wikipedia.org/wiki/Database_index#:~:text=A%20database%20index%20is%20a,maintain%20the%20index%20
data%20structure .
Question 55 CertyIQ
You have an e-commerce application that reads and writes data to an Azure SQL database.
Which type of processing does the application use?
A. stream processing
B. batch processing
C. Online Analytical Processing (OLAP)
D. Online Transaction Processing (OLTP)
Explanation:
OLTP is designed to serve as a persistent data store for business or front-end applications. OLTP administers day to day
transaction of an organization.
Reference:
https://sqlwizard.blog/2020/03/15/sql-server-oltp-vs-olap/
Question 56 CertyIQ
When can you use an Azure Resource Manager template?

A. to automate the creation of an interdependent group of Azure resources in a repeatable way
B. to apply Azure policies for multi-tenant deployments
C. to provision Azure subscriptions
D. to control which services and feature administrators and developers can deploy from the Azure portal
Explanation:
You can automate deployments and use the practice of infrastructure as code. In code, you define the infrastructure that
needs to be deployed
To implement infrastructure as code for your Azure solutions, use Azure Resource Manager templates (ARM templates).
The template is a JavaScript Object Notation (JSON) file that defines the infrastructure and configuration for your
project. The template uses declarative syntax, which lets you state what you intend to deploy without having to write the
sequence of programming commands to create it. In the template, you specify the resources to deploy and the
properties for those resources.
Reference:
https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/overview
Question 57 CertyIQ
You have an Azure SQL database that you access directly from the Internet.
You recently changed the public IP address of your computer.
After changing the IP address, you can no longer access the database. You can connect to other resources in Azure.
What is a possible cause of the issue?
A. role-based access control (RBAC)
B. Dynamic Host Configuration Protocol (DHCP)
C. Domain Name Service (DNS)
D. a database-level firewall
Explanation:
The Azure SQL Database firewall lets you decide which IP addresses may or may not have access to either your Azure
SQL Server or your Azure SQL database.
When creating an Azure SQL Database, the firewall needs to be configured before anyone will be able to access the
database. By default, no external access to your SQL
Database will be allowed until you explicitly assign permission by creating a firewall rule.
Reference:
https://www.sqlshack.com/configuring-the-azure-sql-database-firewall/
Question 58 CertyIQ
DRAG DROP -
Match the tools to the appropriate descriptions.
To answer, drag the appropriate tool from the column on the left to its description on the right. Each tool may be used
Select and Place:
Correct Answer: -
Explanation:
Box 1: Microsoft SQL Server Management Studio (SSMS)
SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL
Server to Azure SQL Database.
Box 2: Microsoft Visual Studio Code

Visual Studio Code is a streamlined code editor with support for development operations like debugging, task running,
and version control. It aims to provide just the tools a developer needs for a quick code-build-debug cycle and leaves
more complex workflows to fuller featured IDEs, such as Visual Studio IDE.
Box 3: Azure Data Studio -

Azure Data Studio offers a modern, keyboard-focused SQL coding experience that makes your everyday tasks easier with
built-in features, such as multiple tab windows, a rich
SQL editor, IntelliSense, keyword completion, code snippets, code navigation, and source control integration (Git). Run
on-demand SQL queries, view and save results as text,
JSON, or Excel. Edit data, organize your favorite database connections, and browse database objects in a familiar object
browsing experience.
Box 4: Microsoft SQL Server Data Tools (SSDT)

SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, databases in
Azure SQL, Analysis Services (AS) data models,
Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, you can design and deploy any SQL
Server content type with the same ease as you would develop an application in Visual Studio.
Reference:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
https://code.visualstudio.com/docs/supporting/FAQ
https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is-azure-data-studio
https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
Question 59 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Box 1: Yes -
Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-
column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the
record.
Box 2: No -
An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view.
Box 3: Yes -
A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns
and rows of data.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/tables/tables https://docs.microsoft.com/en-
us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described https://docs.microsoft.com/en-
us/sql/relational-databases/views/views?view=sql-server-ver15
Question 60 CertyIQ
Which command-line tool can you use to query Azure SQL databases?
A. sqlcmd
B. bcp
C. azdata
D. Azure CLI
Explanation:
The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of
available modes:
At the command prompt.
In Query Editor in SQLCMD mode.
In a Windows script file.
In an operating system (Cmd.exe) job step of a SQL Server Agent job.
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15
The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt.
Incorrect Answers:
B: The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a
user-specified format.
D: The Azure CLI is the defacto tool for cross-platform and command-line tools for building and managing Azure
resources.
Reference:
https://docs.microsoft.com/en-us/sql/tools/overview-sql-tools?view=sql-server-ver15
Question 61 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Box 1: Yes -
Box 2: Yes -
Box 3: Yes -
Azure Defender provides security alerts and advanced threat protection for virtual machines, SQL databases, containers,
web applications, your network, and more.
Azure Defender provides security alerts and advanced threat protection for virtual machines, SQL databases, containers,
web applications, your network, and more.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview
https://azure.microsoft.com/en-us/blog/announcing-sql-atp-and-sql-vulnerability-assessment-general-availability/
https://docs.microsoft.com/en-us/azure/security-center/azure-defender
Question 62 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
A) Yes from https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is?view=sql-server-ver15
Use Azure Data Studio if you: Are connecting to a SQL Server 2019 big data cluster
B) Yes from https://docs.microsoft.com/en-us/learn/modules/query-azure-sql-data-warehouse/4-query-dw-using-ssms
C) Yes from https://docs.microsoft.com/en-us/azure/mariadb/connect-workbench
Question 63 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Reference:
Question 64 CertyIQ
HOTSPOT -
You have the following SQL query.
What are dbo.Products and ProductName? To answer, select the appropriate options in the answer area.
Hot Area:
Question 65 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Reference:
https://azure.microsoft.com/en-gb/blog/hot-patching-sql-server-engine-in-azure-sql-database/
https://azure.microsoft.com/en-us/services/sql-database/#product-overview
Question 66 CertyIQ
Which statement is an example of Data Definition Language (DDL)?

A. SELECT
B. JOIN
C. UPDATE
D. CREATE
Explanation:
Data Definition Language (DDL) statements defines data structures. Use these statements to create, alter, or drop data
structures in a database. These statements include:
✑ ALTER
✑ Collations
✑ CREATE
✑ DROP
✑ DISABLE TRIGGER
✑ ENABLE TRIGGER
✑ RENAME
✑ UPDATE STATISTICS
✑ TRUNCATE TABLE
✑ INSERT
Reference:
Question 67 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Box 1: Yes -
Azure Data Studio is a cross-platform database tool for data professionals using on-premises and cloud data platforms
on Windows, macOS, and Linux.
You can use Azure Data Studio to connect to an Azure SQL Database server. You'll then run Transact-SQL (T-SQL)
statements to create and query Azure SQL databases.
Box 2: No -
SQL Server Management Studio is for configuring, managing, and administering all components within Microsoft SQL
Server, not to create SQL notebooks.
Instead use Azure Data Studio to create SQL notebook.
Box 3: Yes -
You can use the Azure Data Studio to restore databases.
Reference:
https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is-azure-data-studio
Question 68 CertyIQ
You are deploying a software as a service (SaaS) application that requires a relational database for Online Transaction
Processing (OLTP).
Which Azure service should you use to support the application?
A. Azure Cosmos DB
B. Azure HDInsight
C. Azure SQL Database
D. Azure Synapse Analytics
Explanation:
Azure SQL Database is relational database and a managed service.
Incorrect Answers:
A, B: Cosmos DB, HDInsight are non-relational databases.
D: Azure Synapse Analytics is for data warehousing, not for Online Transaction Processing
Reference:
https://cloud.netapp.com/blog/azure-cvo-blg-azure-database-review-your-guide-for-database-assessment
Question 69 CertyIQ
What are two benefits of platform as a service (PaaS) relational database offerings in Azure, such as Azure SQL
Database? Each correct answer presents a complete solution.
A. access to the latest features
B. complete control over backup and restore processes
C. in-database machine learning services
D. reduced administrative effort for managing the server infrastructure
Explanation:
A: Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the
database management functions such as upgrading, patching, backups, and monitoring without user involvement.
D: SQL Database delivers predictable performance with multiple resource types, service tiers, and compute sizes. It
provides dynamic scalability with no downtime, built-in intelligent optimization, global scalability and availability, and
advanced security options. These capabilities allow you to focus on rapid app development and accelerating your time-
to-market, rather than on managing virtual machines and infrastructure.
Reference:
Question 70 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Box 1: No -
Microsoft handles all patching and updating of the SQL and operating system code. You don't have to manage the
underlying infrastructure.
Box 2: Yes -
SQL Database is a fully managed service that has built-in high availability, backups, and other common maintenance
operations.
Box 3: No -
Reference:
Question 71 CertyIQ
DRAG DROP -
You have a table named Sales that contains the following data.
You need to query the table to return the average sales amount per day. The output must produce the following results.
How should you complete the query? 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.
Select and Place:
Explanation:
Box 1: SELECT -
Box 2: GROUP BY -
Example:
When used with a GROUP BY clause, each aggregate function produces a single value covering each group, instead of a
single value covering the whole table. The following example produces summary values for each sales territory in the
AdventureWorks2012 database. The summary lists the average bonus received by the sales people in each territory, and
the sum of year-to-date sales for each territory.
SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson -
GROUP BY TerritoryID;
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql
Question 72 CertyIQ
When you create an Azure SQL database, which account can always connect to the database?
A. the Azure Active Directory (Azure AD) account that created the database
B. the server admin login account of the logical server
C. the Azure Active Directory (Azure AD) administrator account
D. the sa account
Explanation:
When you first deploy Azure SQL, you specify an admin login and an associated password for that login. This
administrative account is called Server admin.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/single-database-create-quickstart
Question 73 CertyIQ
Which statement is an example of Data Definition Language (DDL)?

A. SELECT
B. INSERT
C. DELETE
D. DROP
Explanation:
Data Definition Language (DDL) statements defines data structures. Use these statements to create, alter, or drop data
structures in a database. These statements include:
✑ ALTER
✑ Collations
✑ CREATE
✑ DROP
✑ DISABLE TRIGGER
✑ ENABLE TRIGGER
✑ RENAME
✑ UPDATE STATISTICS
✑ TRUNCATE TABLE
✑ UPDATE
Reference:
Question 74 CertyIQ
A team of developers has computers that run Windows 10 and Ubuntu Desktop.
The developers need to connect to and query an Azure SQL database from each of their computers. The developers
require code assistance features such as IntelliSense.
What should the developers use?
A. sqlcmd
B. Microsoft SQL Server Management Studio (SSMS)
C. Azure Data Studio
D. Azure Data Explorer
Explanation:
IntelliSense = Azure Data Studio
Azure Data Studio is a cross-platform database tool for data professionals who use on-premises and cloud data
platforms on Windows, macOS, and Linux.
Azure Data Studio offers a modern editor experience with IntelliSense, code snippets, source control integration, and an
integrated terminal.
Reference:
https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio
Question 75 CertyIQ
HOTSPOT -
Hot Area:
Explanation:
Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse
Analytics against the threat of malicious offline activity by encrypting data at rest.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview?tabs=azure-
portal
Question 76 CertyIQ
You need to ensure that users use multi-factor authentication (MFA) when connecting to an Azure SQL database.
Which type of authentication should you use?
A. service principal authentication
B. Azure Active Directory (Azure AD) authentication
C. SQL authentication
D. certificate authentication
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-mfa-ssms-overview
Question 77 CertyIQ
Explanation:
Data is organized in tables containing rows and columns in a relational database. Relational databases use multiple
tables with rows and columns to store structured data, with each row containing the same set of columns.
Data is not organized in documents containing fields and values in a relational database. Documents containing fields
and values are a type of semi-structured data with a more flexible structure. Documents are usually represented in
JavaScript Object Notation (JSON) format, and documents can have different fields to represent the same class of
information.
Data is not organized in tables containing keys and values in a relational database. This semi-structured data is used in
a key-value store, which is similar to a relational table. However, each row (represented as key) can have a different set
of columns (represented as value).
Data is not organized in graphs containing edges and nodes in a relational database. This semi-structured data is used
by graph databases, which are specialized to store and query information about complex relationships.
Question 78 CertyIQ
What type of data is JavaScript Object Notation (JSON) format data an example of?
Choose the correct answer
A. Unstructured
B. Semi-structured
C. Relational
D. Structured
Explanation:
A JSON format data file is an example of semi-structured data. In a JSON document, each data field is identified by a
label, followed by a colon, and the field value.
Curly brackets are used to enclose the document and any subdocuments. Azure Cosmos DB is the most common
storage solution for this type of data.
Unstructured data includes files like video or audio files with no schema structure. This type of data is usually stored in
Azure Blob storage.
Structured data and relational data refer to the same data structure with data in a highly normalized format and stored in
multiple related tables. The most common storage solution is some type of SQL database management system.
Question 79 CertyIQ
You need to evaluate the characteristics of semi-structured data.

Correct Answer: -
Explanation:
You do not need to define a strict schema on semi-structured data. With semi-structured data, you do not need to define
a schema. It focuses on storing the entity attributes data as-is rather than manipulating the data in tables and columns,
that follow a strict schema, like a relational database.
You can use semi-structured data to store data that has a highly variable structure. You can store entities with different
fields with semi-structured data, for example, a customer in an e-commerce platform could have multiple contact
numbers or addresses, while another customer could have only one contact number. Semi-structured data provides you
with this flexibility.
You can use Extensible Markup Language (XML) or JavaScript Object Notation (JSON) as a file format to store semi-
structured data. Both file formats are flexible enough to store semi-structured data. You can add new attributes to
entities by adding new keys to a JSON object or adding new elements or attributes to an XML document.
Question 80 CertyIQ
Correct Answer: -
Explanation:
Vehicle is a data entity. A data entity models a real-world object, which has characteristics (attributes). The car hire
company's data models two entities: vehicle and rental. In the case of the vehicle, its attributes are the license plate,
make, model, color and year purchased.
License plate is a key value. License plate is a unique value for vehicles. It is referenced in both the vehicle spreadsheet
and the rental spreadsheet, allowing a single vehicle to be related to many rentals; this is called a one-to-many
relationship. The license plate value acts as a foreign key for rentals.
The car hire company's data is structured. Structured data is regular; the same set of information is recorded for each
entity instance (in this instance, each line in the spreadsheet). For this reason, it is often referred to as tabular data. In
semi-structured data, similar information with some variation is recorded for each instance and it is often hierarchical
(one record can be a parent to another). For example, one record could record a home address and an e-mail address,
and the next one might have a home address and a shipping address but not an e-mail address. For this car hire
company, the same information is held for all records in the vehicle and rental spreadsheets, and so the data is
structured. The rental and vehicle tables are related, and the two spreadsheets act as a basic relational database.
Want all PDFs of DP900?

Please drop us mail at: - certyiqoffical@gmail.com
Please find the videos of this AZ-900/AI-900/AZ-305/

AZ-104 /DP-900/ SC-900 and other Microsoft exam
series on
B. Product
C. Purchase
D. Purchase Date
Explanation:
Email is an attribute of the Customer entity. It is a column on the Customer data table. Attributes represent
characteristics of a data entity, which models a real-world object. For tabular data, the table is the data entity and
columns within the table are attributes.
Purchase Date is an attribute of the Purchase entity. It is a column on the Purchase data table.
Purchase is a data table, analogous to an entity. Data entities model real-world objects and their characteristics are
attributes. In structured, tabular data, data entities are often modeled as tables and the individual columns are their
attributes (or characteristics). The individual columns on the Purchase data table (ID, Purchase Date, Customer ID,
Product ID, Quantity and Payment Taken) are attributes of the Purchase entity.
Product is also a data table. The individual columns on the Purchase data table (ID, Name and Type) are attributes of the
Product entity.
Question 82 CertyIQ
DRAG DROP -
Your company plans to load data from a customer relationship management (CRM) system to a data warehouse by using
an extract, load, and transform (ELT) process.
Where does data processing occur for each stage of the ELT process? To answer, drag the appropriate locations to the
correct stages. Each location 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.
Select and Place:
Explanation:
Box 1: The CRM system -
Data is extracted from the CRM system.

Data is loaded to the data warehouse.

Load and transformation occurs in the Data Warehouse in ELT.
https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/etl#extract-load-and-transform-elt
Question 83 CertyIQ
Correct Answer: -
Explanation:
Semi-structured data does not need to adhere to a fixed schema. Semi-structured data allows for flexibility in the data
being stored. Each instance of a data element (e.g. a customer) can have different attributes. which can be stored in a
different order. Structured data requires a fixed schema; that is, each row of data must contain the same set of
attributes in the same order.
Semi-structured data allows for variation between instances of data entities. Semi-structured data allows the storage of
different sets of attributes per instance of a data entity. For example, one customer may have two email addresses and
no mobile phone number, and a second customer might have three mobile phone numbers and one email address.
Semi-structured data allows for a hierarchical schema. Semi-structured data allows data entities to be set up in a
hierarchical fashion; that is, some entities can be modelled in a parent/child relationship. For example, it can represent a
complex manager and employee relationship, whereby an employee may report to many managers, or a manager may be
responsible for many employees, potentially sharing responsibility for some employees with other managers.
Semi-structured data does not require all data entities to have the same attributes. This is true for structured data. Semi-
structured data allows for variation between instances of data entities; different attributes and numbers of the same
attribute may be recorded for each instance.
Question 84 CertyIQ
You are working with a relational database that stores structured data.
Which component of a table is used to store the unique identifier for instances of that data entity?
A. Primary key
B. Row
C. Index
D. Foreign key
Explanation:
A primary key is the name given to a specific column in a database table that stores a unique identifier for each row,
which is analogous to an instance of a data entity.
A foreign key is the name given to any column in a database table which references rows in another table by their own
unique identifier. This allows the construction of relationships between two data entity instances. For example, the
classroom table has a row with IDs 1 and 2. The teachers table has a row with ID 3. Both classroom rows can reference
the teachers table row 3 to indicate that the same teacher is teaching in both classrooms.
Rows are instances of a data entity; a set of values for each column in the database table. Rows contain a primary key
value, which acts as a unique identifier for that instance of the data entity.
An index is created from several columns to improve the speed of queries. They can (and usually do) include the primary
key of the table.
Question 85 CertyIQ
HOTSPOT -
Hot Area:
Correct Answer: -
Explanation:
Each document in a document database typically contains all data for a single entity. The data contained in the
document can vary between documents. Each document is identified by a unique key used to identify the document and
each document is written or retrieved as a single block.
Documents in a document database do not use the same data schema for all documents. The schema is defined
internally in the document, and each individual document can have a different schema. This allows for easy support of
denormalized data and variations between entities.
Documents in a document database do not support relationships enforced between documents. Document databases do
not provide a way to establish relationships between documents.
Question 86 CertyIQ
Your company is writing an application that will receive real-time events from multiple sources which may vary in
schema. The application should be able to write the data to a data store as quickly as possible. It should also be able to
lookup the value from the data store by using a unique key.
Which is the best type of data store you can use?
A. Column-family
B. Graph
C. Table
D. Key/value
Explanation:
You should use a key/value data store. A key/value data store functions essentially as a large hash table and is
optimized for fast data writes. Each data row is referenced by a single key value. The only operations supported are
simple query, insert, and delete operations. Data updates require the application to rewrite the data for the entire value.
Queries can be run by a key or a range of keys.
You should not use a column-family (columnar) data store. A column-family data store is similar to a relational data
store in that data is organized as rows and columns, but the columns are divided into column families that can store
multiple values in a single column. A row does not necessarily have a value in each column family. Columns within a
column family are physically stored in the same file.
You should not use a table data store. A table data store uses a row and column data format with the data somewhat
normalized but the same schema is not enforced across all rows. Each row can have a different number of columns. In
Azure Table storage, data is organized based on a partition key and a row key. The partition key identifies the partition in
which the data is stored, and the row uniquely identifies the row within the partition.
You should not use a graph data store. A graph data store is designed to support extensive, complex relationships
between entities. This helps to make it easier to perform complex relation analysis.
Question 87 CertyIQ
You are designing a database for a new social media platform. The solution must support complex relationship analysis,
including values such as age, location, pets, and musical preferences.
What type of data store should you use?
A. Graph
B. Columnar
C. Object
D. Document
Explanation:
You should use a graph data store, which is made up of entities and relationships that are referred to as nodes and
edges. You can have multiple relationships between entities, including hierarchical relationships. A graph data store is
designed to support extensive, complex relationships between entities. This helps to make it easier to perform complex
relation analysis.
You should not use a document store. A document store supports semi-structured documents. Each document is
identified as a single key, and the data schema is defined internally in each document named fields and values.
Therefore, the schema and content can vary between documents. Each document typically contains the data for a single
entity. Relationships are not defined between documents.
You should not use a column-family (columnar) data store. A column-family data store is similar to a relational data
store in that data is organized as rows and columns, but the columns are divided into column families that can store
multiple values in a single column. A row does not necessarily have a value in each column family. Columns within a
column family are physically stored in the same file. Data is denormalized and relationships are not defined between
entities.
You should not use an object store. An object store is used to store unstructured data, such as audio or video files, and it
does not provide for relationship analysis.
Question 88 CertyIQ
Drag and drop the answers: -
Correct Answer: -
Explanation:
For large audio and video files that are used as the source for streaming content, you should choose an object data
store. Files of this type are unstructured, non-relational data. The typical storage solution for this type of file is an object
store, such as Azure Blob storage.
Semi-structured data with each entity providing its own field definitions is a description of document-type data, and
therefore a document store is your best choice. Documents are written and retrieved as complete documents. The
embedded field definitions make it possible to query documents in order to retrieve field values. You would typically use
an Azure Cosmos DB storage solution.
For entities and relationships, including multiple and complex relationships with other entities, you should use a graph
data store. A graph data store is made up of entities and relationships that are referred to as nodes and edges. You can
have multiple relationships between entities, including hierarchical relationships. A graph data store is designed to
support extensive, complex relationships between entities. This helps to make it easier to perform complex relation
analysis.
None of the descriptions refer to data that should be stored in a key/value data store. A key/value store functions
essentially as a large hash table and is optimized for fast data writes. Each data row is referenced by a single key value.
The only operations supported are simple query, insert, and delete operations. Data updates require the application to
rewrite the data for the entire value. Queries can be run by key or a range of keys.
Question 89 CertyIQ
A. Document
B. Object
C. Graph
D. Key/value
Explanation:
You should recommend an object data store. Object data stores are optimized for storing and retrieving large binary
objects or blobs such as text files, images, and videos. An example of service capable of storing this kind of information
is Azure Blob Storage.
You should not recommend a document data store. A document data store is used to store semi-structured data in fields
and values with a flexible structure. Documents are usually represented in JavaScript Object Notation (JSON) format and
can have different fields to represent the same class of information.
You should not recommend a key/value data store. A key/value data store is used to store values associated with a
unique key. This kind of data store is optimized for performing simple lookups using the value of the key.
You should not recommend a graph data store. A graph data store manages nodes and edges. Nodes represent entities,
and edges specify the relationships between these entities.
Question 90 CertyIQ
You need to implement a non-relational data store for a new application. The application needs to store data in JSON
format with a flexible schema. All the entity details should be stored together in the same structure.
Which type of non-relational data store should you use?
A. Key/value
B. Document
C. Object
D. Graph
Explanation:
You should use a document data store. A document data store contains the entire data for an entity stored in single
documents with a unique id. This document supports a flexible schema and the entity data is usually stored in JSON
format.
You should not use a key/value data store. A key/value data store consists of a simple and quick data structure where
you can store some information in a value that is identified by a key.
You should not use a graph data store. A graph data store consists of graphs containing edges and nodes and is used to
store and query complex relationships among entities.
You should not use an object data store. An object data store is used to store large binary objects, such as images and
media files.
Question 90 CertyIQ
You have an application that stores information in the format shown in the exhibit below:
You need to recommend the most appropriate data store for this application.
Which type of data store should you use?
A. Choose the correct answer
B. Object
C. Column-family
D. Key/value
Explanation:
You should use a column-family data store. A column-family data store consists of row identifiers and a group of
information stored in a column. Each group of information is stored in independent columns, just like the customers'
identities are shown in the exhibit.
You should not use a relational data store. A relational data store consists of rows and columns defined in tables, and
each table stores a specific set of data in a normalized format. In this scenario, the data is stored in a single column, in
which each row contains denormalized data.
You should not use an object data store. An object data store consists of large binary objects, such as images, media
files, and other types of files.
You should not use a key/value data store. A key/value data store consists of a simple and quick data structure where
you can store some information in a value that is identified by a key.
Question 91 CertyIQ
data store consists of edges and nodes used to store and query complex relationships among
A key/value entities. It stores all the entities as nodes and builds the relationship between those entities by using
A columnar edges.
A graph
An object
Correct Answer: -
Explanation:
A graph data store consists of edges and nodes used to store and query complex relationships among entities. It stores
all the entities as nodes and builds the relationship between those entities by using edges.
A key/value data store consists of a simple and quick data structure where you can store some information in a value
that is identified by a key.
A columnar data store consists of row identifiers and a group of information stored in a column. Each group of
information is stored in independent columns.
An object data store consists of large binary objects, such as images, media files, and other types of files.
Question 92 CertyIQ
You work as a data engineer.

Which two data stores types are non-relational? Each correct answer presents a complete solution.
Choose the correct answers
A. a document database
B. a graph database
C. Azure Database for MariaDB
D. a SQL database
Explanation:
Document databases and graph databases are examples of non-relational data stores. Document databases store data
in JSON or XML format and do not require all documents to have the same structure. Graph databases store information
in the form of edges and nodes. They are used to represent complex relationships such as social interactivity.
The Azure Database for MariaDB and SQL databases are examples of relational databases. Relational databases store
information in the form of tables, which you can connect through relationships. Relational databases are used for highly
structured data.
Question 93 CertyIQ
Your company application requires you to store user profile data, like shipping addresses and user preferences. Each
user can have multiple addresses and different preference fields. All the user data should be stored and retrieved from a
single data structure.
You need to use the best non-relational data store for this type of data.
Which type of non-relational data store should you use?
A. Column-family database
B. Key-value store
C. Document database
D. Graph database
Explanation:
You should use a document database. A document data store consists of entities that have their related data stored in a
single document. This document supports a flexible schema and the entity data is usually stored in JSON format. They
can be used for maintaining the user profile information, making it possible to store all information in a single block.
You should not use a key-value, non-relational data store. Key-value stores are highly optimized for simple data
structures. A Key-value store associates each data value with a key that can be used to access the data.
You should not use a column-family database. Column-family databases store data in rows and columns, grouping
related columns into columns-families that are used together. Instead of a document-based database, you can use a
column-family database to retrieve only the required columns-families instead of all user data.
You should not use a graph database. Graph databases store information in the form of edges and nodes. They are used
to represent complex relationships such as social interactivity.
Question 94 CertyIQ
Which file format is shown in the below exhibit?
A. JSON
B. Parquet
C. XML
D. CSV
Explanation:
The file format shown in the exhibit is an extensible markup language (XML). XML uses tags (elements) for entities
within angle brackets, much like HTML. Attributes are added inside tags in the following format, separated by spaces.
XML is a traditional format for web applications, but many newer applications now use JSON instead. The following is an
example of XML:
<attribute name="attribute value">
JavaScript object notation (JSON) encloses entities in parentheses, 0); collections in square brackets, []; attributes
inside entities in the below format, separated by commas. JSON is a newer way of storing semi structured data than
XML, and is commonly used by web applications.
("attribute name":"attribute value")
Parquet contains row groups, each of which stores chunks of data. It stores data in columns rather than rows. Parquet is
a file format developed by Apache; it is useful for efficient querying of very complex data, such as that generated by
social networking.
Comma-separated values (CSV) files are simple lists of attributes separated by a delimiter, which is often a comma.
Sometimes, the names of the columns will be added in the first row. CSV is a very simple and ubiquitous structure that
can be easily interpreted by older applications. CSV data would look like the example below.
attribute 1 name, attribute 2 name
attribute 1 value, attribute 2 value
Question 95 CertyIQ
You need to identify the components of a graph database.

To answer, drag the appropriate component to each description. A component may be used once, more than once or not
at all.
Correct Answer: -
Explanation:
Graph databases store semi-structured data.
Nodes represent instances of data entities, like individual people. Nodes are analogous to rows in a table within a
relational database.
Edges represent the relationships between nodes; they are also called graphs or relationships. Edges can be directed or
undirected, depending on the structure of the individual database.
Properties represent attributes of data against a node. For instance, a person might have an email address or phone
number. Properties are analogous to columns on a table in a relational database. However, remember that NoSQL
databases allow for flexibility on the attributes stored against a node. For example, a node may have many instances of
a single attribute or it may be absent.
Directions are a property of an edge. Edges can be directed or undirected. Directed edges store two pieces of
information relating to each of the nodes they connect. For instance, a parent/child directed edge would store which
person node represents the parent and which the child. Undirected edges link nodes together, where the direction of the
relationship does not matter; for example, a friendship between two people.
Question 96 CertyIQ
Correct Answers: -
Explanation:
Relational databases store structured data.
A row represents an instance of a data entity, like an individual person or company. You can think of a row like a line in a
spreadsheet.
A key represents a relationship between two individual data entities. Each table has a primary key column, which gives
each row a unique value (usually numerical) that acts as an identifier that can be referenced using a foreign key column
on another table.
A column represents a data attribute on a table. An example might be an e-mail address column in the table Person.
Each row (individual person record) may have an e-mail address value in that column.
A table represents a data entity itself, like a person. A table has columns (attributes) and rows (instances of the entity).
Question 97 CertyIQ
Correct Answers: -
Explanation:
Comma-Separated Values (CSV) files are structured. They store plain text separated by field delimiters, often commas,
creating a set schema. Each row of data must have the same set of attributes in the same order. You can save
structured data files, like Excel spreadsheets, as .csv files to provide a flexible format, which many applications can use.
Extensible Markup Language (XML) files are semi-structured. They store elements and attributes in tags in a hierarchical
schema, which look like HTML tags, e.g. <customer />, but they can be defined according to data storage requirements.
XML is a good choice for storing semi-structured data, as elements and tags are flexible.
Binary Large Object (BLOB) files store raw binary data without a schema. BLOB files are used for unstructured data, such
as audio and video.
JavaScript Object Notation (JSON) files store data entities in a hierarchical schema, much like XML. Each entity can
have a different set or number of attributes, making it a good choice to store semi-structured data.
Question 98 CertyIQ
The Customer table of a relational database is shown in the below exhibit. This table references values in the City table.
Which feature of this table is highlighted?

A. Non-clustered index
B. Instance of customer entity
C. Foreign key
D. Primary key
Explanation:
The primary key is highlighted. The primary key column contains values that are unique identifiers for a particular row. In
this scenario, the ID column stores a unique number for each customer.
Foreign keys are not highlighted. Foreign key columns contain values referencing the Primary key of another table. The
City ID column is a foreign key column; it references the primary key values of the Customer table, normalizing the data.
Normalization is the process of removing duplicate data. Referencing a row in the City table means that we do not have
to store additional columns in the Customer table for every row in the City table.
An instance of a customer entity is not highlighted. Each row of data shown in the exhibit represents a different
customer, an instance of the customer data entity.
A non-clustered index is not highlighted. A non-clustered index is a structure of a database table that works as an index
page in a textbook. It is used to speed up queries.
Question 99 CertyIQ
In reference to online transaction processing (OLTP), for each of the following statements, select Yes if the statement is
true. Otherwise, select No.
Correct Answers: -
Explanation:
OLTP systems are used to record day-to-day business activities and interactions as they occur. This includes activities
such as orders taken, services performed, and payments received or made.
In an OLTP system, data is highly normalized with the schema strongly enforced on write. OLTP systems are usually
structured around a relational data store supporting transactional applications.
An OLTP workload has heavy write requirements with minimal (in comparison) read requirements.
In an OLTP environment, changes made are rolled back automatically if a transaction is not completed so that no
transaction is left in a partially completed state. This is known as atomicity and is a requirement for OLTP.
Question 100 CertyIQ
For each application description, identify whether it describes an online transactional processing (OLTP) or an online
analytic processing (OLAP) workload.
To answer, select OLTP or OLAP from the drop-down menus.
Correct Answers: -
Explanation:
An application to perform data mining on historic data collected from multiple relational and non-relational sources is an
example of an OLAP workload. OLAP applications often manipulate data based on complex queries. Data mining queries
are complex multidimensional queries that are designed to discover insights from the data that are not immediately
apparent.
An application to process hundreds of user purchases per minute, including updates to inventory on hand, is an example
of an OLTP workload. OLTP applications are optimized for write operations and entering and updating data across
multiple, related tables. Partial changes made to data are rolled back automatically if a transaction is not completed so
that no transaction is left in a partially completed state.
An application to support warehouse sales and shipping for physical warehouses and multiple international locations is
an example of an OLTP application. OLTP transactions can be distributed geographically and supported by one or more
relational databases. This scenario requires a solution that supports consistent and reliable data writes.
An application to provide loosely normalized data to support report generation is an example of an OLAP workload.
Companies will often maintain live data for transactional processing and a separate copy of historic data for analysis
and report generation. This prevents analytic processing from interfering with the performance during transactional
processing.
HOTSPOT -
Hot Area:
Correct Answers: -
Explanation:
Online analytical processing (OLAP) systems are designed to perform complex analyses and provide business
intelligence. OLAP is used for analytical workloads, such as:
• Generating complex ad-hoc reports that include several aggregations
• Performing big data analysis on a NoSQL database
Online transaction processing (OLTP) systems are designed to perform business transactions as they occur.
OLTP is used for transactional workloads, such as:
• Performing e-commerce transactions
• Tracking inventory management systems
You need to support a reporting system that performs data mining in a large amount of data.
Which type of workload or processing should you use?
A. Stream processing
B. Online transaction processing (OLTP)
C. Online analytical processing (OLAP)
D. Batch processing
Explanation:
You should use an OLAP workload. You can use OLAP workloads to organize large business databases and perform
complex analytics, like data mining, without negatively affecting transactional systems.
You should not use an OLTP workload. You can use OLTP workloads with transactional systems used in the day-to-day
operations of an organization, like accounting, financial, and other systems, that require strong consistency for
transactions.
You should not use a stream processing workload. You can use streaming processing workloads to handle a continuous
stream of data used by time-critical operations.
You should not use a batch processing workload. You can use batch processing workloads to collect a group of data
with a scheduled time interval or when a certain amount of data has arrived that is not time sensitive.
Your company uses an accounting system to keep track of all types of financial transactions.
Which type of workload or processing is this an example of?
A. Batch processing
B. Stream processing
C. Online analytical processing (OLAP)
D. Online transaction processing (OLTP)
Explanation:
This is an example of an OLTP workload. You can use OLTP workloads with transactional systems used in the day-to-day
operations of an organization, like accounting, financial, and other systems, that require strong consistency for
transactions.
OLAP workloads organize large business databases and perform complex analytics, like data mining. without negatively
affecting transactional systems.
A stream processing handles a continuous stream of data used by time-critical operations.
Batch processing collects a group of data within a scheduled time interval or when a certain amount of non-time-
sensitive data has arrived.
Your company uses an Azure SQL database to store information about your sales cycle, including customers, quotes,
orders and invoices.
As part of a new disaster recovery procedure, you need to ensure that a backup of the database is taken and added to
Azure Blob Storage every night.
Which role is the most appropriate to perform this task?
A. A Database Administrator
B. A Data Analyst
C. A Data Scientist
D. A Data Engineer
Explanation:
A Database Administrator is the most appropriate role to perform this task. Database Administrators (DBAs) are
responsible for maintaining and designing databases; they do the day-to-day work of keeping the company's data-related
systems up and running. They will work with business stakeholders to perform tasks like updating the database
structure to reflect new business requirements (where needed), ensuring an acceptable database performance and
creating backups and copies of the database.
A Data Analyst is not the most appropriate role to perform this task. Data Analysts are responsible for interpreting the
data generated by the business, using it to create visual reports or identify trends that can provide business insights.
They do not deal with the mechanics of how the data is recorded; they only look at the data itself.
A Data Engineer is not the most appropriate role to perform this task. Data Engineers are responsible for manipulating
data (performing cleansing or enrichment routines) and moving it where it needs to go (via pipelines or other
integrations). Although they often have the same skillsets as Data Scientists, this is a more practical, hands-on job; they
make day-to-day systems by connecting individual data sources together.
A Data Scientist is not the most appropriate role to perform this task. Data Scientists are responsible for working with
data on a mathematical level. They design and implement processes like machine learning to gain insights from existing
data. Their job is to ask questions about a data set and use theories and experiments to find out the answers.
Your company is hiring a new Data Analyst, and is putting together a job description for the role.
Which three statements relate to the tasks of a Data Analyst? Each correct answer presents a complete solution
A. To identify trends and relationships in data
B. To create and maintain data models and datasets
C. To design and maintain a data warehouse
D. To design and implement disaster recovery plans
E. To create reports and dashboards
Explanation:
The new Data Analyst will need to create reports and dashboards. This will be the main focus of a Data Analyst's job.
They use tools such as Power BI and Tableau to tell a story with data, creating visualizations that illustrate the trends
and relationships they have uncovered for business users.
The Data Analyst will identify trends and relationships in data. It is part of a Data Analyst's job to use their skills to
uncover insights in data that are useful to the business.
The Data Analyst will also create and maintain data models and datasets. When creating reports, Data Analysts will
ingest raw data and then shape it into a different model, which will provide the correct basis and optimal performance
for the particular report they are building. They will often create and maintain curated sets of data that are useful for
people to report on, which can belong to models or be in a stand-alone format.
The Data Analyst will not need to design and implement disaster recovery plans. This task involves working with the data
at source (for example, within an Azure SQL database), which is not part of a Data Analyst's job. This task is part of a
Database Administrator's job.
The Data Analyst's job will not include designing and maintaining a data warehouse. A data warehouse is a central store
of data, ingesting data from many sources and storing it in a way that meets the company's requirements for data
transformation or analytics. This task would form part of the job of a Data Engineer.
Which of these tools would a Data Analyst use most of the time?
A. Power BI
B. Azure Databricks
C. SQL Server Management Studio
D. Azure Data Studio
Explanation:
Power BI is a data analytics tool that allows users to build data models, reports and dashboards to visualize and
interpret data. As such, it would typically be used by a Data Analyst.
SQL Server Management Studio is a tool that allows users to design and maintain SQL Server databases and related
components. A Database Administrator would typically use it.
Azure Databricks is an analytics platform used by Data Engineers and Data Scientists to ingest and transform data and
develop machine learning models.
Azure Data Studio is a cloud-based cross-platform database tool used by Database Administrators. Similar to SQL Server
Management Studio, it allows them to build complex queries for raw data with intelligence to assist them, and create
reports and dashboards to check the performance and status of databases they manage.
Correct Answers: -
Explanation:
Relational data is stored in multiple tables. Each table is made up of rows and columns. Each row represents an instance
of an entity for which you want to store information. The columns contain information about the entities, with a given
column value for each entity.
The tabular structure of relational databases means that each of the rows in a table will have the same number of
columns. Not all columns will necessarily contain a value. Data tables let you have any number of rows.
The primary key is a unique value assigned to a row. Even if all other columns' information is the same in two rows, you
must have a column or a group of columns to store the primary key value that makes each row unique. When setting up
relationships between tables through foreign keys, each foreign key value must have a corresponding value in a primary
key.
Normalization is the process used to split an entity into multiple tables. This helps to minimize data duplication through
the use of related tables. For example, an online order might need to include customer information and information
about the items ordered. Rather than putting all of this information in order, you can have foreign keys pointing to the
detailed customer and item information in other tables.
Correct Answers: -
Explanation:
In a relational database, you can use normalization to eliminate data repetition and inconsistent dependencies by
separating data into multiple tables and relating these tables by using a foreign key.
A primary key is a constraint that is used to enforce data integrity in relational databases by indicating which column (or
combination of columns) uniquely identifies each row in a table. It must have a unique value across a table. A primary
key is also used to create relationships between different tables.
A clustered index is a data structure associated with a table that defines the order in which rows are stored on a disk.
Correct Answers: -
Explanation:
An index is the structure used to sort values to help optimize query performance. Most relational database management
systems (RDMSs) support clustered and nonclustered indexes. The difference is that a table is physically sorted in
clustered index order when it is used and a nonclustered index does not change the table order, but it contains pointers
to the appropriate table rows.
A view is a virtual table whose contents are defined through a query. A simple view might look like the following:
CREATE VIEW CustLabels
AS
SELECT c.CustomerNumber, c.CustomerName
FROM Customers c
This would create a view that includes the CustomerNumber and CustomerNamecolumn values. Once created, a view
can be used as a data source in other queries.
The primary key is the value used to identify each table entity as unique. The primary key value for each row will be
unique.
None of the descriptions are for a table. A table is the basic storage structure for a relational database consisting of
rows and columns.
None of the descriptions are for a foreign key. Foreign keys are used with primary keys to establish relationships
between tables. The foreign key in one table is associated with the primary key in another table. A foreign key will be
associated with only one primary key, but multiple foreign keys can be associated with the same primary key.
Which application is best supported through the use of a relational database?

A. Data gathering for a customer resource management (CRM) application must support and retain values for
entities with different numbers of values.
B. An application that must be able to rapidly load values from different Internet of Things (IoT) sensors that are
coded by time into temporary storage with the data treated as transparent.
C. An order processing engine for an online sales application that needs to perform high-speed online transaction
processing (OLTP) operations.
D. An application where users need fast, reliable access to streaming media files that vary in size between 20 GB
and 5 MB.
Explanation:
The application best suited to a relational data store is an order processing engine for an online sales application that
needs to perform high-speed online transaction processing (OLTP) operations. Characteristics of data in relational
databases include:
Highly normalized with enforced schemas
Requires high integrity and strong consistency
Relationships are maintained between data tables
In addition to order management, relational databases are typically used to support inventory control and accounting
applications.
For an application where users need fast, reliable access to streaming media files that vary in size between 20 GB and 5
MB, you should choose an object storage solution such as Azure Blob storage or Azure Data Lake Storage Gen2.
When gathering data for a customer resource management (CRM) application that must support and retain values for
entities with different numbers of values, you would most likely use a document storage solution. Document data is
semi-structured with each document internally defining its own schema. Each document is written and retrieved as a
single block. You would most likely use Azure Cosmos DB as your storage solution.
To support an application that must be able to rapidly load values from different loT sensors that are coded by time into
temporary storage with the data treated as transparent you would most likely use a key/value storage solution. You
would most likely use the Azure Cosmos DB Table API or Azure Table storage as your data store.
You need the query to be readily available to users. You want to maintain the normalization of your database.
What should you create?
A. Index
B. View
C. Key
D. Table
Explanation:
You should create a view. A view is a virtualized table that is recreated each time you execute the view query. The
creation table for a simple view might look like the following:
CREATE VIEW CustLabels
AS
SELECT c.CustomerNumber, c.CustomerName
FROM Customers c
This would create a view that includes the CustomerNumber and CustomerName column values. Once created, a view
can be used as a data source in other queries.
Because the view is a virtualized table, its content is not physically stored in the database but is instead repopulated
each time it is required. This helps to keep your data normalized and prevents data duplication.
You should not create an index. An index is a relational database structure that is used to sort data to help optimize
searches during queries.
You should not create a table. Creating a table would result in data duplication, and it would also require you to code a
method to keep the table up-to-date when data is modified in the source table.
You should not create a key. Relational database management systems (RDMSS) typically use primary and foreign keys.
Primary keys are used to enforce uniqueness on rows in a table. Foreign keys are used with primary keys to establish
relationships between tables.
Correct Answers: -
Explanation:
A primary key is a constraint used to enforce data integrity in relational databases by indicating which column (or
combination of columns) uniquely identifies each row in a table. The primary key must have a unique value across a
table and is also used to create relationships between different tables.
A foreign key is a constraint used to create relationships between tables by referencing a primary key from other tables.
A heap is a table that does not have a clustered index implemented. In a heap, data is stored in an unordered structure.
Correct Answers: -
Explanation:
A view is a database object where content is based on a query. You can use a view to query data from one or more
tables and display the data in a different structure, like filtering or joining data from different tables.
A table is a database object that stores data in rows and columns. Tables are used to represent entities with each row
representing a single entity. Each characteristic from this entity is represented as a column.
A heap is a table that does not have a clustered index implemented. A clustered index is a data structure associated with
a table that defines the order that rows are stored in a disk. In a heap, data is stored in an unordered structure.
An index is a structure that improves the read performance to find specific data in a table.
In a relational database, what is the purpose of an index?

A. To improve read performance by searching for specific data in a table.
B. To enforce data integrity by indicating which column uniquely identifies each row in a table.
C. To query data from one or more tables and display the data in a different format.
D. To create relationships between tables.
Explanation:
The purpose of an index in a relational database is to improve read performance by searching for specific data in a table.
An index stores values and references to specific rows in a table and uses these values to locate the specific rows more
quickly.
An index does not enforce data integrity by indicating which column uniquely identifies each row in a table. This is the
purpose of a primary key.
An index does not create relationships between tables. This is the purpose of a foreign key.
An index does not query data from one or more tables and display the data in a different format. This is the purpose of a
view.
You need to determine when to use a relational database.

Which is the best scenario to use a relational database?
A. Dynamic and semi-structured data schema
B. Large images and videos
C. Data in columnar format
D. Guarantee strong consistency while processing transactions
Explanation:
You should use a relational database to guarantee strong consistency while processing transactions. A relational
database can guarantee the consistency and availability of the data while processing transactions. A relational database
must ensure Atomicity, Consistency, Isolation, Durability (ACID) for transactional workloads.
You should not use a relational database to store data in columnar format. A columnar data store consists of row
identifiers and a group of information stored in a column. Each group of information is stored in a keyspace, composed
of a single column. You should use a column family database like Apache Cassandra to store this kind of data instead.
You should not use a relational database for dynamic and semi-structured data schema. A relational database requires a
data schema, defined as tables and columns. You should use a document database like MongoDB to store this kind of
data instead.
You should not use a relational database for large images and videos. A relational database is not optimized to store
binary data. You should use a binary large object (Blob) like Azure Blob storage instead.
Correct Answers: -
Explanation:
You should use a primary key to uniquely identify each row in a table. A primary key enforces uniqueness automatically
by creating a unique constraint on primary key columns.
You should use a view to easily access aggregated results from a table. Views are derived from a result of a table. You
can also filter view data.
You should use an index to quickly access rows using specific product tags in a table. You can create an index in the
product column in a table to quickly access rows using the product tag. Indexes help you to quickly find the desired rows
based on your query.
You should not use a stored procedure. Stored procedures are used to execute a set of commands or SQL statements in
a single batch. They are often used for more complex logic. They can return output from input parameters.
Which command is a Data Manipulation Language (DML) command?

A. UPDATE
B. DROP
C. ALTER
D. CREATE
Explanation:
The UPDATE command is an example of a DML command. It is used to edit column values for rows in a table.
The SQL language has four basic types of commands. DML commands are used to manipulate rows in a
table. They include:
• DELETE
• INSERT
• SELECT
• UPDATE
Data Definition Language (DDL) commands are used to create, modify, and delete database objects. They include:
• ALTER
. CREATE
• DROP
. RENAME
Data Control Language (DCL) commands are for access control and permission management. They include:
• DENY
. GRANT
• REVOKE
Which SQL command should you use to remove a database table and its content from a database?
A. UPDATE
B. DELETE
C. DROP
D. ALTER
Explanation:
You should use the DROP command to remove a database table and its content. The DROP command is an example of a
Data Definition Language (DDL) command. DDL commands are used to create, modify, and drop database objects.
You should not use the ALTER command. ALTER is also a DDL command, but it is used to modify a database object,
such as adding a column to a table.
You should not use the DELETE or UPDATE commands. These are both Data Manipulation Language (DML) commands
that are used to manipulate data, such as running queries to retrieve data. The DELETE command is used to delete rows
from a table. The UPDATE command is used to edit column values.
You manage an Azure SQL Database in your Azure subscription.

You need to identify a Data Manipulation Language (DML) SQL statement.
Which statement is a DML statement?
A. INSERT
B. ALTER
C. CREATE
D. DROP
Explanation:
INSERT is a DML statement type. DML statements are used to manipulate information stored in a relational database.
Other DML statements include UPDATE, DELETE, and SELECT.
CREATE, ALTER, and DROP are not DML statements. These statements are classified as Data Definition Language (DDL)
statements. DDL statements are used to define data structures in a relational database, like tables, views, and indexes.
You manage an Azure SQL Database in your Azure subscription.

You need to identify a Data Definition Language (DDL) SQL statement.
Which statement is a DDL statement?
A. SELECT
B. DELETE
C. ALTER
D. INSERT
Explanation:
ALTER is a DDL statement type. DDL statements are used to define data structures in a relational database, like tables,
views, and indexes. Other DDL statements are CREATE and DROP.
SELECT, INSERT, and DELETE are not DDL statements. These statements are classified as Data Manipulation Language
(DML) statements. DML statements are used to manipulate information stored in a relational database.
You have a table named Products in an Azure SQL Database. The table contains the data as shown in the below exhibit.
You need to set the stock quantity of a product with ID 5323 to 250.
Which SQL statement should you use?
A. INSERT
B. TRUNCATE
C. CREATE
D. UPDATE
Explanation:
You should use the UPDATE statement. The UPDATE statement can update data in the existing rows of a table. You can
update the stock quantity to 250 for the product with id 5323 by using the following query:
UPDATE SET StockQuantity = 250 WHERE ProductId = 5323;
You should not use the TRUNCATE statement. The TRUNCATE statement removes all rows from a table without logging
the individual row deletions.
You should not use the INSERT statement. The INSERT statement is used to add new rows in a table.
You should not use the CREATE statement. The CREATE statement is used to define database objects, like tables, views,
indexes, and others.
You have an Azure Database for MySQL that is used to store employee salaries. The table structure is shown in below
the exhibit.
You need to query the top 10 salaries.
How should you complete the SQL query? To answer, select the appropriate options from the drop-down menus.
Choose the correct options
Correct answer: -
Explanation:
You should complete the SQL query as follows:
SELECT * FROM employees.salaries
ORDER BY salary DESC
LIMIT 10;
You should use the SELECT clause to return all fields from the salaries table.
You should not use the SELECT TOP(10) clause. You can use SELECT TOP(10) with SQL Server or other T SQL
compatible databases to return the specified number of rows.
You should not use the SELECT MAX(salary) clause. You can use this clause to return the maximum salary in the
salaries table.
You should use the ORDER BY salary DESC clause to order the salaries in descending order, from the highest to the
lowest salary.
You should not use the ORDER BY salary ASC or ORDER BY salary clauses. This returns the salaries ordered from the
lowest to the highest salary.
You should use the LIMIT 10; clause to limit the number of rows returned from the query and show the top 10 salaries.
You should not use the LIMIT 1, 10; clause. This limits the number of rows returned to 10, starting from the second row.
In this case, the top salary will be omitted, returning the second highest salary to the eleventh one, instead of the top ten.
You should not use the DESC; clause. There is no difference if you insert a line break after the ORDER BY clause.
If you are using a T-SQL compatible database, a valid query to retrieve the top ten salaries would be:
SELECT TOP (10)
FROM employees.salaries
ORDER BY salary DESC;
Correct answer: -
Explanation:
You should use the INSERT statement with the DML group. DML statements deal with information stored in the database
table. The following are examples of DML statements: SELECT, INSERT, UPDATE, and DELETE.
You should use the ALTER statement with the DDL group. DDL statements deal with creating, altering, or deleting a
database object or structure. The following are examples of DDL statements: CREATE, ALTER, DROP, and RENAME.
You should not use the GRANT statement for either the DDL or DML group. GRANT statements are related to the Data
Control Language (DCL) group. They are used to grant permission on database objects.
You have a table named Orders as shown in the below exhibit.
You need to find the total quantity ordered by each customer as shown in the exhibit query results.
What should you do to complete the query? To answer, select the appropriate options from the drop-down menus.
Correct answer: -
Explanation:
You should use the following query to find the total quantity ordered by each customer:
SELECT CustomerID, SUM(Quantity) FROM Orders GROUP BY CustomerID
You use the SUMO) aggregate function to calculate the total quantity. To be able to see the total quantity bought by each
customer, you need to add CustomerID to the GROUP BY clause allowing it to show aggregated results by CustomerID.
Correct Answers: -
Explanation:
DROP is used to remove objects from a database. It can be used to remove columns, views, stored procedures or whole
tables. In order to remove rows, you should use DELETE.
SELECT is used to retrieve rows from a table. By default, it will be performed on all rows unless a filtering criteria is
added to the statement.
INSERT is used to add new rows to a table. It can add a single row, or multiple rows at a time.
WHERE is used to filter rows the statement applies to. It will be used after the initial statement, such as SELECT or
UPDATE. Its usage will be within a WHERE clause.
Which two of the following statements are characteristics of database normalization? Each correct answer presents part
of the solution.
A. Improved data integrity
B. Reduced query complexity
C. Fewer tables
D. Fewer foreign keys
E. Less data to store
Explanation:
Less data to store is a characteristic of normalization. Normalization removes redundant data by removing duplication,
resulting in less data overall. Potentially, this could lead to a reduction in your storage costs.
Improved data integrity is also a characteristic of normalization. Normalization removes duplicate data; every time you
have the same data added in more than one place, you increase the possibility of being updated inconsistently due to an
error.
Fewer tables are not a characteristic of normalization. Normalization increases the number of tables by adding each
data entity into a different table and referencing records in other tables via foreign key columns.
Reduced query complexity is not a characteristic of normalization. It is simpler to write a query on one table than on two,
as you will need to join the two tables via a relationship between the data to write your query. Normalization increases
the number of tables, which means that you will need to use more joins in your queries and increase their complexity.
Fewer foreign keys is not a characteristic of normalization. Normalization increases the number of tables by splitting
duplicated data into its own table, which is then referenced in the original table via a foreign key. Normalization will
therefore increase the number of foreign keys in the database.
A company is moving its on-premises databases to Azure. The databases support custom applications. Two
applications are stable only when they are used with SQL Server 2016 SP2.
What should you choose?
A. SQL Server on virtual machine (VM)
B. Azure SQL Database elastic pool
C. Azure SQL Managed Instance
D. Azure SQL Database serverless
Explanation:
You should choose SQL Server on VM. This type of move is often called lift-and-shift and provides for rapid migration to
the cloud. SQL Server on VM is the option most compatible with your on-premises database servers and is the only
deployment option that lets you run a SQL Server version other than the latest stable version. This is an Infrastructure as
a Service (laaS) deployment, where you are responsible for managing the operating system and database server.
Azure SQL Database serverless, Azure SQL Database elastic pool, and Azure SQL Managed Instance are all Platform as a
Service (PaaS) deployments. With PaaS, you do not have access to the operating system, and some database
administrative activities, such as keeping SQL Server updated, are handled for you. These deployments run the most
recent stable SQL Server version. While these implementations are close, they are not 100% compatible with an on-
premises implementation.
The deployment option most compatible with an on-premises instance is SQL Server managed instance. This can be
used in most lift-and-shift scenarios, but it is limited to the latest SQL Server version.

Correct Answers: -
Explanation:
Azure SQL Database does not allow you to choose the SQL Server engine version. Azure SQL Database uses the latest
stable version of the SQL Server engine. You should use SQL Server on Azure Virtual Machine (VM) to choose which SQL
Server version to use.
Azure SQL Database includes a fully managed backup service and automatically applies security patches in the
underlying operating system. Azure SQL Database is a platform as a service (PaaS) database offering. where most of
the database management functions are handled by Azure. This includes backups, operating system updates, and
applying security patches.
Correct Answers: -
Explanation:
Azure Database for PostgreSQL can be deployed as a single server or as a cluster. You can deploy Azure Database for
PostgreSQL as a single server or as a Hyperscale (Citus) cluster. Azure Database for PostgreSQL Hyperscale (Citus) can
horizontally scale queries across multiple machines by using sharding.
Azure Database for PostgreSQL enforces TLS connections by default. Enforcing TLS connections improves security by
encrypting the connection between the client and the database server. You can disable the TLS connection in single
server deployments, but this is not advised.
Azure Database for PostgreSQL supports Azure AD authentication. You can configure Azure AD authentication with
Azure Database for PostgreSQL to enable users to connect to the database using their own credentials. This centralizes
the users' management in one place.
Correct Answers: -
Explanation:
You should classify Azure SQL Database as a PaaS offering. Most of the database management functions are handled
by Azure in Azure SQL Database, including a fully managed backup service. They automatically apply security patches in
the underlying operating system.
You should classify SQL Server on Azure VM as an laaS offering. You should use SQL Server on Azure VM to have
complete access to the infrastructure, allowing you to choose which SQL Server version to use and to configure the
underlying operating system. This model of offering is good for lift and shift scenarios, where you move an on-premises
virtual machine as-is to the cloud.
You should classify Azure SQL Managed Instance as a PaaS offering. Azure SQL Managed Instance brings more
compatibility and resources that are really close to on-premises SQL Server deployments. However, you can still have the
benefits of a PaaS offering, like a fully managed backup service, automatic application of security patches, and high
availability.
You should not classify any of these services as SaaS, SaaS is offered as ready-to-use applications in the cloud, such as
emails, communication tools, and office tools, such as Microsoft 365.
Your manager asks you to plan the migration of an on-premises SQL Server 2019 database to a cloud service. The on-
premises database uses Database Mail and SQL Server Agent.
You need to choose an offering that can minimize cost and administration.
Which database solution should you use?
A. Azure SQL Managed Instance
B. SQL Server on Azure Virtual Machines
C. Azure database for MariaDB
D. Azure SQL Database
Explanation:
You should use Azure SQL Managed Instance. Azure SQL Managed Instance is used for deployment where you need to
have complete feature parity with SQL Server on-premises. You should use a managed instance if your database uses
features like SQL Server Agent and Database Mail.
You should not use Azure SQL Database. Azure SQL Database gives you a single database in the cloud with minimal cost
and administration, in which you can create databases and tables. Azure manages administrative tasks such as backup
and recovery. However, Azure SQL Database does not implement features like SQL Server Agent and Database Mail,
which is required in this scenario.
You should not use SQL Server on Azure Virtual Machines, SQL Server on Azure Virtual Machines gives you a similar
administrative experience as your on-premises server, which will therefore increase your administrative effort.
You should not use an Azure database for MariaDB. An Azure database for MariaDB allows you to have MariaDB
community edition implementation in Azure. MariaDB is a relational database management system.
You work as an Azure Database administrator.

You need to identify characteristics of relational Azure data services.
Which two options should you use for Platform-as-a-Service (PaaS) relational data services? Each correct answer
presents a complete solution.
A. Azure Cosmos DB
B. Azure SQL Database Managed Instance
C. SQL Server on Azure Virtual Machines
Explanation:
You should use Azure SQL Database for PaaS relational data services offerings. Azure SQL Database is a managed
database server in the cloud.
You can also use Azure SQL Database Managed Instance for PaaS relational data services offerings. Azure SQL
Database Managed Instance allows you to run multiple databases in the same instance.
You should not use SQL Server on Azure Virtual Machines. SQL Server is an Infrastructure-as-a-Service (laaS) offering for
data services. It allows you to run SQL Server on an Azure virtual machine by providing the relevant infrastructure.
You should not use Azure Cosmos DB. Azure Cosmos DB is a PaaS offering mainly targeted for non relational data
stores. It allows you to store documents and objects.
Correct Answers: -
Explanation:
SQL Server on Azure Virtual Machines (VMs) is an Infrastructure as a Service (laaS) service. This form of cloud service
requires the most responsibility for the customer, as they need to manage everything other than the physical devices
used. SQL Server on Azure Virtual Machines creates virtual hardware for customers to use as they wish.
Azure SQL Managed Instance is a Platform as a Service (PaaS) service. It does not require customers to manage
physical hardware or operating systems and includes automated software updates and backup: customers typically use
this service to migrate existing systems to the cloud.
Azure SQL Database is a Platform as a Service (PaaS) service. Similar to Azure SQL Managed Instance, this is a fully
managed solution that handles backups, monitoring, and upgrades for the customer. Customers use this service as a
high availability data layer for their cloud-based systems.
SaaS stands for Software as a Service. SaaS does not require the customer to manage physical devices, operating
systems, or updates to the software. Users only need to create accounts and manage access. An example of SeaS would
be Microsoft Dynamics 365 or Power BI.
You are assisting with building a business case for adopting the Azure SQL Database service.
Which three of the below are advantages of the Azure SQL Database service over other available Azure SQL services?
Each correct answer presents a complete solution.
A. Lower cost
B. Fully compatible with on-premises SQL Server installations
C. Full control over the SQL server engine
D. Higher availability
E. Minimal administration
F. Database Mail-supported
Explanation:
Azure SQL Database has a lower cost. It is a relational database, designed for modern cloud applications. It is used by
customers that require a high availability, and a scalable database layer for their cloud applications.
Azure SQL Database is a platform-as-a-service (PaaS) service. Although Azure SQL Managed Instance is also a PaaS
service, Azure SQL Database service eliminates more administrative tasks like removing the need to manage the SQL
Server configuration:
Azure SQL Database service offers 99 995% uptime, in comparison with 99.99% that the other services offer.
Azure SQL Database service is not fully compatible with on-premises SQL Server installations. Some features are
available in on-premises SQL Server that are not available in Azure SQL Database service. Azure SQL Managed Instance
offers almost 100% compatibility as it has additional configuration options, whereas SQL Server on Azure Virtual
Machines is fully compatible as it allows the most granular level of user configuration.
Azure SQL Database services do not allow full control over the SQL Server engine. The Azure SQL Database service
performs some tasks for the user, such as performing back-ups and installing patches, but only SQL Server on Azure
Virtual Machines allows full control over the engine. This is because SQL Server on Azure Virtual Machines is an
infrastructure as a service (laaS) service, which requires users to manage operating systems.
Azure SQL Database services do not support Database Mail. This feature, which allows the database to send email
messages to users, is not available in the Azure SQL Database service. It is, however, offered in Azure SQL Managed
Instance and SQL Server on Azure Virtual Machines.
HOTSPOT -
Hot Area:
Correct Answers: -
Explanation:
Azure Database for MySQL provides automated back-ups. Azure Database for MySQL is a platform as a service (PaaS)
service. It offers full back-up, incremental back-up, and point-in-time database restore functions.
Azure Database for MySQL is a relational database management system. MySQL is an open source relational database
management system. The Azure Database for MySQL service is a PaaS implementation of MySQL on the Azure cloud.
Azure Database for MySQL supports two deployment options: single server, which has minimal required configuration,
handles most database functions, such as back-ups, for the user and is best suited for existing applications, and flexible
server, which allows more granular control and is best suited for new deployments.
You work for a finance company that tracks information regarding stock prices over time. It uses an on premises
MariaDB server to store this data with a temporal dimension, allowing queries to compare stocks and predictions over
time.
Which three actions will result from your company migrating its systems to Azure Database for MariaDB? Each answer
represents a complete solution.
A. Having the ability to choose the operating system of the server
B. Having built-in performance monitoring
C. Having automatic backups
D. Having high availability
E. Having the need to migrate to Azure SQL Database
Explanation:
Migrating the company's systems to Azure Database for MariaDB will result in:
• Having high availability - Azure Database for MariaDB guarantees 99.99% up-time for its service, which is often called
four nines.
Having automatic backups Azure Database for MariaDB provides automatic backups and enables administrators to
enact a point-in-time restore for up to 35 days.
Having built-in performance monitoring - Azure Database for MariaDB provides various metrics and . logs to help
administrators understand how their database performs. They can also set up alerts based on these metrics if an item
needs their attention.
Azure Database for MariaDB is a Platform as a Service (PaaS) service, meaning that Microsoft, and not the user, is
responsible for the infrastructure of the virtual hardware it runs on (including the operating systems).
Azure Database for MariaDB will allow the company to continue using MariaDB, but as a service. It will not need to adapt
its systems to run on Azure SQL Database or perform a migration.
Which Azure storage service includes support for Hot Cool and Archive access tiers?
A. Azure File
B. Azure Blob
C. Azure Table
Explanation:
Azure Blob is the only Azure storage option that supports access tiers. The default is the Hot tier, which is designed for
frequently accessed data. The Cool tier is optimized for data that will be stored for at least 30 days. The Cool tier has
lower storage costs than the Hot tier but higher costs for early access. The Archive tier is designed for data that is rarely
accessed and will remain in storage for at least 180 days. Access to Archive tier data requires the data to be rehydrated
to a Hot or Cool tier. This can mean a latency of several hours. Access tier support requires Data Lake Storage Gen2.
Azure Blob also supports two performance tiers. The Standard performance tier provides for high performance using
hard disk-based storage media. The Premium performance provides greater throughput than the Standard tier and uses
solid-state drive (SSD) media. The Standard and Premium tiers are also supported for other storage options including
Azure File storage and Azure SQL Database.
Azure Table and Azure File do not support access tiers. Access tiers is a feature supported through Cosmos DB. Azure
Table and Azure File are distinct storage types and are not implemented through Cosmos DB APIs. Table storage is used
for storing structured, non-relational data. File storage provides file storage with shared access, similar to a file server.

HOTSPOT -
Hot Area:
Correct Answers: -
Explanation:
Both GPv2 and File Storage account accounts support Azure file share, but you can only create a premium Azure file
share in a File Storage account. You can create a standard file share account only in a GPv2 account.
You can configure a premium Azure file share for LRS and, in select regions, for ZRS. A standard file storage account
supports GRS, but not a premium file share.
You can use a premium Azure file share to replace or supplement traditional on-premises file shares. This is true for
both standard and premium file share. This includes scenarios where application data is moved to the cloud but
applications continue to run on-premises.
Correct Answers: -
Explanation:
Azure File storage supports direct mounting by Windows, macOS, and Linux. This includes support for concurrent access
from the cloud and on-premises. Azure File storage can be used to supplement or replace on-premises file server
shares.
Azure File storage does not allow you to select the underlying hardware and operating system. Azure File storage is
implemented as a serverless file service in which you have neither direct access to, or administrative responsibilities for,
the underlying architecture. The one infrastructure choice you can make is between hard disk (HDD) standard file shares
and solid-state disk (SSD) premium file shares.
Azure File storage is not the recommended storage solution for key/value storage implementation. Microsoft
recommends Azure Cosmos DB Core (SQL) API for new key/value requirements. Key/value storage is also supported by
Azure Table storage and Cosmos DB Table API.
Azure File storage does not support redundancy across multiple regions by default. Standard file shares support locally-
redundant storage (LRS) by default with options for zone redundant storage (ZRS), geo redundant storage (GRS), and
geo-zone-redundant storage (GZRS). Replication across multiple regions is supported as an option, but not as a default
setting. The large file share feature and premium file shares support LRS and ZRS only.
You plan to reduce storage costs to store older, non-structured data in Azure Blob storage. The data needs to be
accessible occasionally to generate reports that must be available as soon as possible.
You need to choose the most appropriate data tier for this data.
Which tier should you use?
A. Cool
B. Hot
C. Archive
Explanation:
You should use the cool tier. This tier is optimized for storing data that is infrequently accessed, with a lower storage
cost than the hot tier.
You should not use the hot tier. This tier is optimized for frequently accessed data and has the highest storage cost
among the storage tiers.
You should not use the archive tier. This tier is optimized for storing data that is rarely accessed, with the lowest storage
cost among the storage tiers. The archive tier data is saved in offline storage, requiring you to wait for the rehydration of
the data to an online tier before you can access the data.
You are migrating an application to Azure. This application uses a shared network folder as a data store.
You need to move the shared network folder to Azure storage.
Which type of Azure storage should you use?
A. Azure File storage
B. Azure Queue storage
D. Azure Table storage
Explanation:
You should use Azure File storage. Azure File storage provides file shares compatible with the Server Message Block
(SMB) protocol, replacing traditional on-premises file servers with a cloud solution.
You should not use Azure Queue storage. Azure Queue storage is a service used for storing messages that are used by
distributed applications. A queue message can be up to 64 KB in size.
You should not use Azure Table storage. Azure Table storage is used to store data as rows and columns, forming a table
in which the number of columns may vary according to each row.
You should not use Azure Blob storage. Azure Blob storage can store unstructured data, such as binary objects, images,
media files, and large text files, in a cost-efficient and scalable manner.
Q66
You need to describe which elements compose a key in Azure Table storage.
Which two elements compose a key? Each correct answer presents part of the solution.
A. Row key
B. Value
C. Timestamp
D. Table name
E. Partition key
Explanation:
The two elements that compose a key in Azure Table storage are the partition key and the row key. Data stored in Azure
Table storage is referred to as rows and columns, and it forms a table in which the columns may vary according to each
row. The rows in a table are split into partitions, and related rows are grouped based on a common property. This
common property is called a partition key. The partition key identifies the partition inside the Azure Table storage and a
row key is used to uniquely identify each row in a given partition.
The table name does not compose a key in Azure Table storage. The table name is an identifier in the Azure Storage
account used to store a set of data in the key/value format.
The value does not compose a key in Azure Table storage. The value represents the other properties related to a given
key. This is the data that is returned when you query a given key.
The timestamp does not compose a key in Azure Table storage. The timestamp is a property used to record the time an
entity was last modified. The timestamp is used internally by the Azure Table storage to provide optimistic concurrency.
Correct Answer: -
Explanation:
In Azure Table storage, a group of columns is not stored in different partitions. The rows in a table are split into
partitions, which group together related rows based on a common property. This common property is called the partition
key.
The number of columns in each row may not be exactly the same. Azure Table lets you store semi structured data.
Unlike in a relational table, each row can have different columns of data.
In Azure Table storage, data is stored as rows and columns, forming a table in which the number of columns may vary
according to each row.
You need to store training videos for company users.

What Azure data service should you use?
A. Azure Table storage
B. Azure File storage
Explanation:
You should use Azure Blob storage to store training videos. Azure Blob storage allows you to store large object files
such as images, videos, and virtual machines (VMs).
You should not use Azure Table storage to store training videos. Azure Table storage allows you to store semi-structured
data into key/value format. This means it stores data into a rows and columns format, but unlike a relational database,
each row has a key and each column contains entire data value.
You should not use Azure File storage to store training videos. Azure File storage allows you to create file shares in the
cloud, which can be accessible for network users.
You should not use Azure SQL database to store training videos. Azure SQL database allows you to store relational data
in a cloud database.
Choose the correct options: -
Correct Answer: -
Explanation:
To implement folder and directory level security in Azure Storage, you need to enable hierarchical namespace. Enabling
hierarchical namespace allows you to organize your blob containers in folders and directories, allowing you to define
POSIX-compatible permissions and role-based access control (RBAC) in your container.
You should not use the cool access tier as default or use premium performance. You can use the cool access tier to
reduce storage costs, and premium performance to improve data access performance. These features are not related to
folder or directory access control in Azure Storage.
Your company is using Azure Files.

You need to improve the performance of serving files to users inside your on-premises network.
What should you use to synchronize the Azure Files locally shared files with a local cache inside your company network?
A. AzCopy
B. Azure File Sync
C. Azure Files AD Authentication
D. Azure storage account
Explanation:
You should select Azure File Sync. The Azure File Sync service connects an on-premises server with cached copies of
files with Azure File Storage data. This will allow users to access files via the cached copies inside your on-premises
network, which improves performance.
You should not select AzCopy. This utility allows users to upload files to Azure File Storage.
You should not select Azure storage account. A storage account is a shared pool for storage. You can use it
to create Azure file shares or Blob Storage containers.
You should not select Azure Files AD Authentication. This allows users to authenticate via single sign-on (SSO) when
they access an Azure file share and a directory, just as they would with Office 365 applications. There would be no
improvement in file-serving performance.
Your company is using Azure blob storage to store recordings of conference speeches as video files, and the marketing
department wishes to start storing audio recordings as well.
You need to explain how they can separate this type of files from the existing videos.
What should you recommend?
A. Storage account
B. Container
C. Archive tier
D. Blob
Explanation:
You should recommend a container. Containers act like folders in a file system under your Blob Storage account. They
are used to logically group related blob files together. For instance, those with different extensions. You can create an
unlimited number of containers, and each container can store an unlimited number of blobs.
You should not recommend a storage account. Storage accounts are your unique namespace in Azure, containers live
under them to logically group different types of files together.
You should not recommend a blob. Blobs (or binary large objects) are individual files. Blobs are added to containers,
which act like directories under your storage account.
You should not recommend an archive tier. The archive tier is one of the three tiers (hot, cool, and archive) which
governs how quickly you can retrieve blobs. The archive tier is the slowest retrieval (and therefore the cheapest), which
is intended for blobs that you will not need to access frequently.
Match each data application with the best Cosmos DB API choice.
To answer, select the most appropriate Cosmos DB API from the drop-down list.
Correct Answers: -
Explanation:
Microsoft recommends that any new data project created from scratch uses the Core (SQL) API. The Core (SQL) API is
used to store semi-structured data in a document and uses a SQL-like query language to manipulate the data stored in
the documents. This includes applications with key/value data. This type of application is also supported by the Table
API, but the Core (SQL) API is recommended as the best solution because it provides improved indexing and a richer
query experience.
When creating a new application that analyzes detailed relationship information for non-relational data you should use
the Gremlin API. This is one of the few cases where the Core (SQL) API is not recommended as the best solution. The
statement described a graph database to which the Gremlin API is specifically suited.
When moving application data to the cloud that uses semi-structured documents to store data, you should use the Core
(SQL) API. The Core (SQL) API gives you the ability to create, query, and update data documents.
You should use the Cassandra API when moving column-family format data to the cloud to support an existing
application. Microsoft suggests limiting the use of the Cassandra API to support existing data, such as when moving
data to the cloud. The Cassandra API is specifically designed to support column-family data.
None of the data applications should use the Azure Table API or MongoDB API. Microsoft recommends that either
should only be used when supporting an existing data application, such as when moving an Azure Table store or
MongoDB to Azure.
Complete the Cosmos DB general resource hierarchy used by all APIs.

To answer, select the correct resource from the drop-down menus.
Correct Answer: -
Explanation:
An Azure Cosmos account is topmost in the resource hierarchy. You must have this before you can create an Azure
Cosmos DB database instance. You can then create API-specific containers, such as tables, collections, or graphs. You
create items, the entities for which you are storing data, inside a container. Examples include documents, nodes, edges,
or rows.
There is no blob in the Cosmos DB storage hierarchy. Azure Blob is another storage option in Azure. A storage blob has
its own hierarchy of a storage account, container (called a container), and then a blob. Items being stored are contained
in the blob, adding a fifth layer to that hierarchy.
Match a Cosmos DB API to a type of non-relational data.
To answer, drag the appropriate API to each data type. Each API may be used once, more than once, or not at all.
Correct Answers: -
Explanation:
You should use the Cassandra API to store columnar data. This API is compatible with Apache Cassandra databases,
which are column-family databases used to store columnar data consisting of row identifiers and a group of information
stored in a column. Each group of information is stored in independent data structures named keyspaces.
You should use the Gremlin API to store graph data. This API is compatible with Gremlin, which is a graph database that
stores nodes and edges used for complex relationships among entities.
You should use the Core API to store JSON documents. This API is used for document-based databases that store semi-
structured data in JSON format. A document usually contains all data from an entity, and each document can have
different fields of data.
You should not use the Table API. You can use the Table API to store key-value data organized as rows and columns,
forming a table in which the number of columns may vary according to each row. This API is compatible with Azure
Table storage.
You need to identify which non-relational data stores support multi-region writes and read replicas.
To answer, select the appropriate options from the drop-down menus.
Correct
Answer: -
Explanation:
Cosmos DB Table API supports multi-region writes and read replicas. You can configure read replicas in a Cosmos DB
account to multiple regions, including support to create multi-region writes.
Azure Table storage supports multi-region reads replicas only. You can configure read replicas in Azure Table storage by
configuring the storage account to use Read-access geo-redundant storage (RA-GRS) redundancy. This enables a
readable replica in a secondary region. However, you cannot write data in the secondary region.
You need to implement an Azure Cosmos DB data store.

Which API should you use for each non-relational data store implementation? To answer, drag the appropriate API to the
correct data store. Each API may be used once, more than once, or not at all.
Correct Answers: -
Explanation:
You should use the Azure Cosmos DB Core API or Table API to implement a Key-value store. Key-value stores are highly
optimized for simple lookups scenarios. Azure Cosmos DB provides Table API for key value store implementation.
Although Core API implements a document-based database to store JSON-like semi-structured data, Microsoft
recommends using the Core API for new applications, where you can use a document with an item ID and a single value
field to represent a key-value store.
You should use Azure Cosmos DB Cassandra API to implement a column family database. Column family database
stores data in rows and columns. It is suitable to represent weather or time-series activity. Azure Cosmos DB provides
Cassandra API for column family database implementation.
You should use Azure Cosmos DB Gremlin API to implement a graph database. Graph database stores information in the
form of edges and nodes. It is used to represent complex relationships such as social interactivity. Azure Cosmos DB
You work for a company that is developing a new massively multiplayer online (MMO) game, which will be launched in 20
countries next year. The data architects have opted to use Azure Cosmos DB as the games database tier.
What are two characteristics of using Azure Cosmos DB? Each correct answer presents a complete solution.
A. It requires no administration.
B. It supports a single application programming interface (API).
C. It has very low latency for data reads and writes.
D. It supports multiple geographic regions.
E. It supports relational data only.

Explanation:
Azure Cosmos DB has very low latency for data reads and writes, and it is very fast and elastically scalable. It will
support the very fast data reads and writes required for an online game. Halo 5: Guardians uses Azure Cosmos DB as its
database tier.
Azure Cosmos DB supports multiple geographic regions. You can enable multi-region writes, adding the Azure regions of
your choice to your Cosmos DB account so that globally distributed users can each work with data in their local replica.
Azure Cosmos DB is platform as a service (PaaS) application, and so the Database Administrators do need to perform
some administration with this model.
Azure Cosmos DB is a NoSQL database, meaning that it is designed to support semi-structured data, such as that used
by an online game.
Azure Cosmos DB supports multiple APIs such as Cassandra, Gremlin, and Table.
Which two of the following Azure Cosmos DB APIs are used to query data using the SQL syntax? Each correct answer
presents a complete solution.
A. Cassandra
B. Table
C. Core
D. Gremlin
E. MongoDB
Explanation:
The Core (SQL) API allows developers to work with Cosmos DB data using SQL syntax.
The Cassandra API is compatible with Apache Cassandra, a column-family structured database. It supports SQL syntax
to allow developers to manipulate and retrieve data.
The Gremlin API allows developers to work with graph data. It supports graph syntax.
The Table API allows developers to work with key-value data in Cosmos DB. It supports requests based on a namespace,
much like retrieving data from Azure Blob Storage.
The MongoDB API supports MongoDB Query Language (MQL), which is object-oriented.
HOTSPOT -
Hot Area:
Correct Answers: -
Explanation:
Azure Cosmos DB allows simpler queries to retrieve data than a relational database. Azure Cosmos DB is a NoSQL
database that supports storing data in a denormalized way. Denormalization reduces the number of tables within a data
model because all attributes from an entity are stored together rather than in different tables. With fewer tables, data
read (or retrieval) queries become less complex due to the reduced table joins needed.
Azure Cosmos DB does not reduce data duplication through normalization. As a NoSQL database, Azure Cosmos DB
supports the denormalization of data, which increases the need for data duplication but also reduces the complexity of
the schema, and, therefore, the queries needed to manipulate it. Normalization is the process of reducing data
duplication by creating additional reference tables, which are supported by relational databases.
Azure Cosmos DB supports applications with a global user base. Azure Cosmos DB supports multi-region writes,
allowing users to work with a replicated set of data in their own region.
You need to determine the most appropriate database tier for each of the use cases below.
To answer, drag the appropriate database to the use case. A database may be used once, more than once or not at all.
Correct Answer: -
Explanation:
Azure SQL database is appropriate for the human resources system. As Azure SQL database uses a fixed schema,
meaning the data will not change over time, a relational database like Azure SQL is a good choice.
Azure Cosmos DB is appropriate for the social network. Representing complex relation structures between the entities is
best stored in a NoSQL database, specifically in a graph database.
Azure Cosmos DB is appropriate for the chemical plant. Azure Cosmos DB is very low-latency and highly scalable,
allowing it to ingest and process huge amounts of data. This makes it perfect for loT (internet of things) scenarios like
sensor data.
A database administrator writes the following query for an Azure Cosmos DB environment.
:> g.v().hasLabel('person').order().by('firstName', decr)
Which Azure Cosmos DB API does the query refer to?
A. Gremlin API
B. Table API
C. Core API
D. Cassandra API
E. MongoDB API
Explanation:
The query refers to the Gremlin API. Gremlin syntax includes functions to operate on nodes (instances of data entities)
and edges (relationships between nodes), enabling users to navigate around the complex graph structure. The g. in the
example query stands for graph. The example statement will retrieve person vertices in descending order of their first
names.
The query does not refer to the Core API, also called SQL API. SQL syntax uses keywords in capital letters, spaces, and
quotes for string values. An example would be as follows:
SELECT FirstName, LastName

FROM PERSON
WHERE IsChild = true
The above query would return the first and last name columns of all rows on the person table marked as children.
The query does not refer to the Table API. This API allows the manipulation of key/value pair data. An example query
would be:
https://<mytableendpoint>/People (PartitionKey' Harp', Rowkey='Walter')
The above query would retrieve an entity from the People table, by filtering on the PartitionKey Harp and the Rowkey
Walter.
The query does not refer to the Cassandra API. Query syntax for this API looks similar to that used for the Core API.
The query does not refer to the MongoDB API. This API uses an object-oriented syntax for queries. An example would be:
db.people.find({"isChild": true })
The above query would retrieve people that are children.
You are evaluating use cases for Azure Synapse Analytics.

Which is the most appropriate use case for Azure Synapse Analytics?
A. To store massive amounts of unstructured data in a hierarchical structure.
B. To serve as data storage for online transactional processing (OLTP) workloads.
C. To create dashboards and data visualizations from tabular data.
D. To perform very complex queries and aggregations on a large amount of relational data.
Explanation:
The most appropriate use case for Azure Synapse Analytics is to perform very complex queries and aggregations on a
large amount of relational data. You can provision Synapse SQL pools to quickly execute complex queries across
multiple computer nodes thanks to the Synapse SQL massively parallel processing (MPP) architecture.
The most appropriate use case for Azure Synapse Analytics is not to create dashboards and data visualizations from
tabular data. You should use Power BI for that instead.
The most appropriate use case for Azure Synapse Analytics is not to store massive amounts of unstructured data in a
hierarchical structure. You should use Azure Data Lake Storage for that instead.
The most appropriate use case for Azure Synapse Analytics is not to serve as data storage for online transactional
processing (OLTP) workloads. You should instead use a relational database service like Azure SQL Database.
To complete the sentence, select the appropriate option from the drop-down menu.
Correct Answer: -
Explanation:
Azure HDInsight is a big data processing service which is used to provision and manage a cluster of open source
analytics solutions such as Apache Spark, Hadoop, and Kafka.
Azure Databricks is a complete platform for big data processing, streaming, and machine learning, which is optimized
for the Microsoft Azure cloud services platform and built on top of Apache Spark.
Azure Analysis Services is a service used to build multidimensional or tabular models used by online analytical
processing (OLAP) queries. You can combine data from multiple sources, such as Azure Synapse Analytics, Azure Data
Lake Store, Azure Cosmos DB, and others to build the tabular models.
HOTSPOT -
You are reviewing the data model 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.
NOTE: Each correct selection is worth one point
Hot Area:
Explanation:
Box 1: star schema -
In computing, the star schema is the simplest style of data mart schema and is the approach most widely used to
develop data warehouses and dimensional data marts. The star schema consists of one or more fact tables referencing
any number of dimension tables. The star schema is an important special case of the snowflake schema, and is more
effective for handling simpler queries.
Example:
Incorrect Answers:
The data in the question is not normalized.
The snowflake schema is a variation of the star schema, featuring normalization of dimension tables. Example:
Note: A snowflake schema is a logical arrangement of tables in a multidimensional database such that the entity
relationship diagram resembles a snowflake shape. The snowflake schema is represented by centralized fact tables
which are connected to multiple dimensions.[citation needed]. "Snowflaking" is a method of normalizing the dimension
tables in a star schema. When it is completely normalized along all the dimension tables, the resultant structure
resembles a snowflake with the fact table in the middle.
Box 2: dimension -
The star schema consists of one or more fact tables referencing any number of dimension tables.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview
https://en.wikipedia.org/wiki/Star_schema
https://en.wikipedia.org/wiki/Snowflake_schema https://azure.microsoft.com/en-us/blog/data-models-within-azure-
analysis-services-and-power-bi/
You need to evaluate which role each service performs in modern data warehousing.
To answer, select the appropriate options from the drop-down menus.
Correct Answers: -
Explanation:
Azure Data Factory is used to ingest data from data sources. You can ingest data from both relational data and non-
structured data from multiple sources with Azure Data Factory.
Azure Data Lake Storage is used to store raw data. You can store raw, unstructured data, such as text files, logs, and
images, in order to allow these to be processed quickly at a later stage.
Azure Synapse Analytics is used to model and serve data. You can load relational data ingested from Azure Data Factory
in Azure Synapse Analytics using Synapse SQL pool, and you can also read unstructured data stored in Azure Data Lake
Storage using Polybase. Combining both relational and unstructured data, you can perform complex analytics and serve
data for later stages.
Power BI is used to visualize data. You can build interactive reports and dashboards with Power BI, allowing business
users to analyze this data and deliver insights throughout your organization.
HOTSPOT -
Hot Area:
Correct Answers: -
Explanation:
Azure Data Lake Storage is not built on top of Azure File storage. Azure Data Lake Storage Gen2 is built on top of Azure
Blob storage, combining the features of the previous generation of Azure Data Lake Storage with Azure Blob storage.
Azure Data Lake Storage is capable of storing a large amount of data in a cost-effective way. Azure Data Lake Storage
can store large amounts of data, such as hundreds of terabytes and more, and you only pay for what you use. You can
reduce the storage cost even more by using features such as storage lifecycle to archive or move data that is not used
frequently to cheaper storage tiers.
Azure Data Lake Storage enables hierarchical namespace compatible with Hadoop Distributed File System (HDFS).
Azure Data Lake Storage provides a layer to access Azure Blob Storage data as an HDFS storage, including support to
organize files in directories and subdirectories, allowing you to examine large quantities of data quickly.
Match each Azure service with its role in a data warehouse architecture.
To answer, drag the appropriate Azure service to its place in the data warehouse diagram. An Azure Service may be used
Correct Answer: -
Explanation:
Data ingestion is the process of combining your structured, semi-structured, and unstructured data into a common data
store. Azure Data Factory is specifically designed to provide end-to-end support for extract transform-load (ETL)
operations for data warehouse data load. Often, moving data into a data warehouse requires more aggressive cleaning
and transformation than is supported through Data Factory.
The consolidated data is stored in Azure Blob storage through Azure Data Lake Storage. This gives you a flexible storage
environment to give access to Azure Databricks for more intensive data analytics to prepare cleaned and transformed
data.
Native connectors let you move data at scale from Azure Databricks to Azure Synapse Analytics, which acts as a single
hub for your structured data. From here, the data is available for detailed analysis and reporting, including using Azure
Analysis Services to give end-users access to the data.
Azure Cosmos DB is not part of the Azure data warehouse infrastructure as a non-relational data store.
HOTSPOT -
You need to evaluate Azure Data Factory.
Hot Area:
Correct Answer: -
Explanation:
Azure Data Factory can have multiple pipelines. Azure Data Factory allows you to create multiple data pipelines.
Pipeline is a logical grouping of activities that performs a task. You can have multiple activities in a pipeline.
Activities in a pipeline can either run sequentially or operate in parallel. An activity represents a step in a pipeline.
HOTSPOT -
You have the following JSON document.
Use the drop-down menus to select the answer choice that completes each statement based on the information
presented in the JSON document.
Hot Area:
Explanation:
SocialMedia is an Array object, as someone underline due to [ ], references:
https://docs.microsoft.com/en-us/answers/questions/200268/adf-reading-array-of-objects-in-data-flow.html
https://docs.microsoft.com/en-us/azure/data-factory/format-json


series on
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
When to use batch processing.
You might expect latencies when using batch processing. For many situations, however, this type of delay before the
transfer of data begins is not a big issue - the processes that use this function are not mission critical at the exact
moment.
Reference:
https://www.bmc.com/blogs/what-is-batch-processing-batch-processing-explained/

A. REVOKE
B. DISABLE
C. INSERT
D. GRANT
Explanation:
Correct Answer: C
Data Manipulation Language (DML) affect the information stored in the database. Use these statements to insert,
update, and change the rows in the database.
BULK INSERT -
✑ DELETE
✑ INSERT
✑ SELECT
✑ UPDATE
✑ MERGE
Reference:
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
The same set of columns.
In relational databases, a row is a data record within a table. Each row, which represents a complete record of specific
item data, holds different data within the same structure. A row is occasionally referred to as a tuple.
Incorrect:
Not: a key value pair.
Unlike relational databases, key-value databases do not have a specified structure. Relational databases store data in
tables where each column has an assigned data type. Key-value databases are a collection of key-value pairs that are
stored as individual records and do not have a predefined data structure.
Reference:
https://www.techopedia.com/definition/4425/database-row
https://www.techtarget.com/searchdatamanagement/tip/NoSQL-database-types-explained-Key-value-store
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
For some semi-structured data formats (e.g. JSON), data sets are frequently a simple concatenation of multiple
documents.
Reference:
https://docs.snowflake.com/en/user-guide/semistructured-considerations.html
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Semi-structured data -
Semi-structured data (e.g., JSON, CSV, XML) is the bridge between structured and unstructured data. It does not have a
predefined data model and is more complex than structured data, yet easier to store than unstructured data.
Reference:
https://www.ibm.com/cloud/blog/structured-vs-unstructured-data
Which property of a transactional workload guarantees that each transaction is treated as a single unit that either
succeeds completely or fails completely?
A. atomicity
B. isolation
C. durability
D. consistency
Explanation:
Correct Answer: A
The purpose of making transactions atomic is to prevent different transactions from interfering with one another. This
can only happen if more than one user process is trying to access the database at the same time, as when a server
allows several clients to use it concurrently. The simplest way to enforce atomicity is for the DBMS to refuse to start any
transaction until the previous one has committed.
Reference:
https://www.sciencedirect.com/topics/computer-science/atomic-transaction
ou have a data store that has the structure shown in the following exhibit.
Which type of data store is this?
A. key/value
B. object data
C. graph
D. time series
Explanation:
Correct Answer: C
A graph database stores two types of information, nodes and edges. Edges specify relationships between nodes. Nodes
and edges can have properties that provide information about that node or edge, similar to columns in a table. Edges
can also have a direction indicating the nature of the relationship.
Reference:
Which type of database contains nodes and edges?

A. graph
B. key/value
C. columnar
D. time series
Explanation:
Correct Answer: A
A graph database stores two types of information, nodes and edges. Edges specify relationships between nodes. Nodes
and edges can have properties that provide information about that node or edge, similar to columns in a table. Edges
can also have a direction indicating the nature of the relationship.
Reference:
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
A data analyst's primary skill set revolves around data acquisition, handling, and processing.
Incorrect:
* A data engineer requires an intermediate level understanding of programming to build thorough algorithms along with
a mastery of statistics and math.
* A data scientist needs to be a master of both worlds. Data, stats, and math along with in-depth programming
knowledge for Machine Learning and Deep
Learning.
Reference:
https://www.edureka.co/blog/data-analyst-vs-data-engineer-vs-data-scientist/
DRAG DROP -
Match the tools to the appropriate descriptions.
To answer, drag the appropriate tool from the column on the left to its description on the right. Each tool may
be used once, more than once, or not at all.
Select and Place:
Explanation:
Correct Answer:
Box 1: Microsoft SQL Server Management Studio (SSMS)
SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL
Server to Azure SQL Database.
Box 2: Azure Data Studio -

Azure Data Studio offers a modern, keyboard-focused SQL coding experience that makes your everyday tasks easier with
built-in features, such as multiple tab windows, a rich SQL editor, IntelliSense, keyword completion, code snippets, code
navigation, and source control integration (Git). Run on-demand SQL queries, view and save results as text, JSON, or
Excel. Edit data, organize your favorite database connections, and browse database objects in a familiar object browsing
experience.
Box 3: Microsoft SQL Server Data Tools (SSDT)
SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, databases in
Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports.
With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an
application in Visual Studio.
Reference:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
https://code.visualstudio.com/docs/supporting/FAQ https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is-
azure-data-studio https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
Your company needs to ensure that an Azure virtual machine can connect to Azure SQL databases without exposing the
databases to the internet.
What should you use?
A. Azure Application Gateway
B. Azure Traffic Manager
C. Azure DNS
D. Azure Private Link
Explanation:
Correct Answer: D
Azure Private Link enables you to access Azure PaaS Services (for example, Azure Storage and SQL Database) and
Azure hosted customer-owned/partner services over a private endpoint in your virtual network.
Traffic between your virtual network and the service travels the Microsoft backbone network. Exposing your service to
the public internet is no longer necessary
Reference:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-private-link-private-endpoint-
connectivity/ba-p/1235573
DRAG DROP -
Match the Azure SQL services to the appropriate use cases.
To answer, drag the appropriate service from the column on the left to its use case on the right. Each service may be
NOTE: Each correct match its worth one point.
Select and Place:
Explanation:
Correct Answer:
Box 1: Azure SQL Database single database
Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on
workload demand and bills for the amount of compute used per second. The serverless compute tier also automatically
pauses databases during inactive periods when only storage is billed and automatically resumes databases when
activity returns.
Scenarios well suited for serverless compute
Single databases with intermittent, unpredictable usage patterns interspersed with periods of inactivity, and lower
average compute utilization over time.
Single databases in the provisioned compute tier that are frequently rescaled and customers who prefer to delegate
compute rescaling to the service.
New single databases without usage history where compute sizing is difficult or not possible to estimate prior to
deployment in SQL Database.
Box 2: Azure SQL Managed Instance
Azure SQL Managed Instance is the intelligent, scalable cloud database service that combines the broadest SQL Server
database engine compatibility with all the benefits of a fully managed and evergreen platform as a service.
Box 3: Azure SQL Database elastic pool
Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that
have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set
number of resources at a set price. Elastic pools in SQL Database enable software as a service (SaaS) developers to
optimize the price performance for a group of databases within a prescribed budget while delivering performance
elasticity for each database.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql
https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview?view=azuresql
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview
Which T-SQL statement should be used to instruct a database management system to use an index instead of
performing a full table scan?
A. SELECT
B. WHERE
C. JOIN
Explanation:
Correct Answer: A
Table hints override the default behavior of the Query Optimizer for the duration of the data manipulation language
(DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table
scan or index seek, or other options. Table hints are specified in the FROM clause of the DML statement and affect only
the table or view referenced in that clause.
This applies to the following commands:
DELETE -
INSERT -
SELECT -
UPDATE -
MERGE -
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Azure Synapse Analytics is an platform as a service (PAAS) that combines data integration, warehousing, and analytics
into one solution.
Azure Synapse Analytics offers cloud-based, relational data warehousing services, massively parallel processing (MPP)
scale-out technology, and enough computational power to efficiently manage petabytes and petabytes of data.
Incorrect:
* Azure Database for MariaDB is a relational database service based on the open-source MariaDB Server engine. It's a
fully managed database as a service offering that can handle mission-critical workloads with predictable performance
and dynamic scalability.
* Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the
database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure
SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with
99.99% availability. PaaS capabilities built into Azure SQL Database enable you to focus on the domain-specific
database administration and optimization activities that are critical for your business.
Reference:
https://cswsolutions.com/blog/posts/2021/august/what-is-azure-synapse-analytics/
https://www.integrate.io/blog/what-is-azure-synapse-analytics/
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way,
therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates
a clustered index on that particular column.A
Reference:
https://www.sqlshack.com/what-is-the-difference-between-clustered-and-non-clustered-indexes-in-sql-server
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
A primary key is a special relational database table column (or combination of columns) designated to uniquely identify
each table record (row).
Reference:
https://www.techopedia.com/definition/5547/primary-key
HOTSPOT -
Hot Area:
Explanation:
Correct Answer: A View
A database view is a subset of a database and is based on a query that runs on one or more database tables.
Reference:
https://www.ibm.com/docs/SSWT9A_7.6.0/com.ibm.mbs.doc/configur/c_views.html
Which Azure service provides the highest compatibility for databases migrated from Microsoft SQL Server 2019
Enterprise edition?
A. Azure SQL Database
B. Azure Database for MySQL
C. Azure SQL Managed Instance
D. an Azure SQL Database elastic pool
Explanation:
Correct Answer: C
SQL Managed Instance has near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine,
providing a native virtual network (VNet) implementation that addresses common security concerns, and a business
model favorable for existing SQL Server customers.
Note: Azure SQL Managed Instance is the intelligent, scalable cloud database service that combines the broadest SQL
Server database engine compatibility with all the benefits of a fully managed and evergreen platform as a service.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-
overview?view=azuresql
Which storage solution supports role-based access control (RBAC) at the file and folder level?
A. Azure Disk Storage
B. Azure Data Lake Storage
D. Azure Queue storage
Explanation:
Correct Answer: B
Azure Data Lake Storage Gen2 implements an access control model that supports both Azure role-based access control
(Azure RBAC) and POSIX-like access control lists (ACLs). This article summarizes the basics of the access control
model for Data Lake Storage Gen2.
ACLs are applied on the file and folder level. The key thing to remember is that you are always going to need RBAC
Control Plane permissions in combination with ACLs. Best practice is to assign your security principals RBAC Reader
role on the Storage Account/Container level and continue with more restrictive ACLs on the file and folder level.
There are two types of ACLs:
Access ACLs: They control access to an object. An object can be a file or a folder.
Default ACLs: These are ACLs assigned on the folder level only which get inherited as Access ACLs by the child file or
folder.
You need to store data in Azure Blob storage for seven years to meet your company's compliance requirements. The
retrieval time of the data is unimportant. The solution must minimize storage costs.
Which storage tier should you use?
A. Archive
B. Hot
C. Cool
Explanation:
Correct Answer: A -> Archive
Hot - Optimized for storing data that is accessed frequently.
Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days.
Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency
requirements (on the order of hours).
Which type of non-relational data store supports a flexible schema, stores data as JSON files, and stores the all the data
for an entity in the same document?
A. document
B. columnar
C. graph
D. time series
Explanation:
Correct Answer: A
Document is correct
https://docs.microsoft.com/en-us/azure/architecture/guide/technology-choices/data-store-overview#column-family-
databases
What are three characteristics of an Online Transaction Processing (OLTP) workload? Each correct answer presents a
complete solution. (Choose three.)
A. denormalized data
B. heavy writes and moderate reads
C. light writes and heavy reads
D. schema on write
E. schema on read
F. normalized data
Explanation:
Correct Answer: BDF
OLTP
Schema: Schema on write, strongly enforced
Workload: Heavy writes, moderate reads
Normalization: Highly normalized
B: Transactional data tends to be heavy writes, moderate reads.
D: Typical traits of transactional data include: schema on write, strongly enforced
F: Transactional data tends to be highly normalized.
Reference:
https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-transaction-processing
Which two activities can be performed entirely by using the Microsoft Power BI service without relying on Power BI
Desktop? Each correct answer presents a complete solution. (Choose two.)
A. report and dashboard creation
B. report sharing and distribution
C. data modeling
D. data acquisition and preparationS
Explanation:
Correct Answer: AB
A. Dashboard -> Services Only, Report - Services also OK
B. Sharing -> Services Only.
C. Modeling -> Desktop Only
D. Data acquisition -> Mandy data sources -> Desktop
--------------
B. correct for sure
C. must wrong
Due to Desktop not for Dashboards, so A better than D.
---------------
So, A & B for me.
With Power BI Desktop, you can connect to multiple different sources of data, and combine them (often called modeling)
into a data model.
Similarly, data acquisition (from a single source or multiple sources) and preparation (transformation and cleaning of
data) are done using power bi desktop.
Without PowerBI Desktop, the only options left are A and B, which come under power bi service.
Ref: https://docs.microsoft.com/en-us/power-bi/fundamentals/desktop-what-is-desktop
HOTSPOT -
Hot Area:
Explanation:
Correct Answer: Gremlin API.
https://docs.microsoft.com/en-us/azure/cosmos-db/graph-introduction
Querying a graph database is done using Gremlin language.
Note: Gremlin language is not a SQL-like language. It is based on gremlin API.
Example: g.V().hasLabel('person').has('age', gt(40)) --- filter query
You need to develop a solution to provide data to executives. The solution must provide an interactive graphical
interface, depict various key performance indicators, and support data exploration by using drill down.
What should you use in Microsoft Power BI?
A. a dashboard
B. a report
C. a dataflow
D. Microsoft Power Apps
Explanation:
Correct Answer: A Dasboard.
as it requires interactive graphical interface with KPIs and drilldown. You can achieve that in dashboard.
What is a benefit of the Azure Cosmos DB Table API as compared to Azure Table storage?
A. provides resiliency if an Azure region fails
B. supports partitioning
C. provides a higher storage capacity
D. supports a multi-master model
Explanation:
Correct Answer: The multi-master
tested in Azure portal.
1. create account in Azure Cosmo DB account
2. under left hand menu bar, locate "Settings" --> "Replicate data globally" <-- click it
3. Turn on "Enable" --> "Multi-region writers. explanation shown in "!" mark :
Multi-master capability allows you to take advantage of the provisioned throughput for your databases and containers
across the globe.
Your company needs to design a database that shows how changes in network traffic in one area of a network affect
network traffic in other areas of the network.
A. graph
B. key/value
C. document
D. columnar
Explanation:
Correct Answer: A
Data as it appears in the real world is naturally connected. Traditional data modeling focuses on defining entities
separately and computing their relationships at runtime. While this model has its advantages, highly connected data can
be challenging to manage under its constraints.
A graph database approach relies on persisting relationships in the storage layer instead, which leads to highly efficient
graph retrieval operations. Azure Cosmos
DB's Gremlin API supports the property graph model.
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/graph-introduction#introduction-to-graph-databases
Which three objects can be added to a Microsoft Power BI dashboard? Each correct answer presents a complete
solution. (Choose three.)
A. a report page
B. a Microsoft PowerPoint slide
C. a visualization from a report
D. a dataflow
E. a text box
Explanation:
Correct Answer: ACE
A "Page Report", you can pin a whole page to panel, from Power bi service.
Reference:
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-dashboards
https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-add-widget
DRAG DROP -
Match the datastore services to the appropriate descriptions.
To answer, drag the appropriate service from the column on the left to its description on the right. Each service may be
Select and Place:
Explanation:
Correct Answer:
Box 1: Azure Cosmos DB -
In Azure Cosmos DB's SQL (Core) API, items are stored as JSON. The type system and expressions are restricted to deal
only with JSON types.
Box 2: Azure Files -

Azure Files offers native cloud file sharing services based on the SMB protocol.
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-working-with-json
https://cloud.netapp.com/blog/azure-smb-server-message-block-in-the-cloud-for-azure-files
Which Azure Data Factory component provides the compute environment for activities?
A. a linked service
B. an integration runtime
C. a control flow
D. a pipeline
Explanation:
Correct Answer: B
The Integration Runtime (IR) is the compute infrastructure used by Azure Data Factory to provide the following data
integration capabilities across different network environments:
✑ Data Flow
✑ Data movement
✑ Activity dispatch
✑ SSIS package execution

Reference:
https://docs.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime
You need to store data by using Azure Table storage.

What should you create first?
A. an Azure Cosmos DB instance
B. a storage account
C. a blob container
D. a table
Explanation:
Correct Answer: B
First create an Azure storage account, then use Table service in the Azure portal to create a table.
Note: An Azure storage account contains all of your Azure Storage data objects: blobs, files, queues, and tables.
Reference:
https://docs.microsoft.com/en-us/azure/storage/tables/table-storage-quickstart-portal
https://docs.microsoft.com/en-us/azure/storage/common/storage-account-create
You need to recommend a data store service that meets the following requirements:
✑ Native SQL API access
✑ Configurable indexes
What should you recommend?
A. Azure Files
B. Azure Blob storage
C. Azure Table storage
D. Azure Cosmos DB
Explanation:
Correct Answer: D
Azure Cosmos DB comes with native Core (SQL) API support.
In Azure Cosmos DB, data is indexed following indexing policies that are defined for each container. The default indexing
policy for newly created containers enforces range indexes for any string or number. This policy can be overridden with
your own custom indexing policy.
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/sql/how-to-manage-indexing-policy
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Box 1: Yes -
For read access to the secondary region, configure your storage account to use read-access geo-redundant storage (RA-
GRS) or read-access geo-zone- redundant storage (RA-GZRS).
Box 2: No -
Box 3: Yes -
Box 4: Yes -
Azure Cosmos DB supports multi-region writes.
Ref: https://docs.microsoft.com/en-au/azure/storage/common/storage-redundancy#read-access-to-data-in-the-
secondary-region
Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to
protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a
failover from the primary to secondary region. When you enable read access to the secondary region, your data is
available to be read ****** at all times ******, including in a situation where the primary region becomes unavailable.
For read access to the secondary region, enable read-access geo-redundant storage (RA-GRS) or read-access geo-zone-
redundant storage (RA-GZRS).
Reference:
https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy
https://manojchoudhari.wordpress.com/2019/12/16/azure-cosmos-db-enable-multi-region-writes
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Box 1: No -
You can duplicate a dashboard. The duplicate ends up in the same Power BI workspace.
There is no current functionality that allows you to move reports from one workspace to another.
Box 2: No -
Box 3: Yes –
-------------------------------------------
Copy Dashboards (No):
"The new dashboard is saved in the same workspace as the original." (https://docs.microsoft.com/en-us/power-
bi/create-reports/service-dashboard-copy)
Single dataset (No):
ONE dashboard...
- is associated with a single workspace
- can display visualizations from many different datasets
- can display visualizations from many different reports
- can display visualizations pinned from other tools (for example, Excel)
(https://docs.microsoft.com/en-us/power-bi/fundamentals/service-basic-concepts#dashboards)
Excel (Yes, see above)
Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-across-workspaces
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-dashboardshttps://powerbi.microsoft.com/en-
us/excel-and-power-bi/
complete solution. (Choose three.)
D. schema defined in a database
E. schema defined when reading unstructured data from a database
F. normalized data
Explanation:
Correct Answer: BDF
D: Typical traits of transactional data include: schema on write, strongly enforced. The schema is defined in a database.
Reference:
You manage an application that stores data in a shared folder on a Windows server.
You need to move the shared folder to Azure Storage.
Which type of Azure Storage should you use?
A. queue
B. blob
C. file
D. table
Explanation:
Correct Answer: C
Azure file shares can be mounted concurrently by cloud or on-premises deployments of Windows, Linux, and macOS.
Azure file shares can also be cached on
Windows Servers with Azure File Sync for fast access near where the data is being used.
Reference:
https://azure.microsoft.com/en-us/services/storage/files/
Your company is designing a database that will contain session data for a website. The data will include notifications,
personalization attributes, and products that are added to a shopping cart.
Which type of data store will provide the lowest latency to retrieve the data?
A. key/value
B. graph
C. columnar
D. document
Explanation:
Correct Answer: C
Column-family database is the low latency store type.
Examples
• Recommendations
• Personalization
• Sensor data
• Telemetry
• Messaging
• Social media analytics
• Web analytics
• Activity monitoring
• Weather and other time-series data
The diffirence is:
- Key/value: in order to modify a value (either partially or completely), an application must overwrite the existing data for
the entire value
- Columnar: columns are divided into groups known as column families, and Within a column family, new columns can be
added dynamically, and rows can be sparse SO take less time for modification.
The answer is correct, C - columnar
You have an application that runs on Windows and requires access to a mapped drive.
Which Azure service should you use?
A. Azure Files
B. Azure Blob storage
C. Azure Cosmos DB
D. Azure Table storage
Explanation:
Correct Answer: A
Azure Files is Microsoft's easy-to-use cloud file system. Azure file shares can be seamlessly used in Windows and
Windows Server.
To use an Azure file share with Windows, you must either mount it, which means assigning it a drive letter or mount point
path, or access it via its UNC path.
Reference:
https://docs.microsoft.com/en-us/azure/storage/files/storage-how-to-use-files-windows
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Box 1: No -
The API determines the type of account to create. Azure Cosmos DB provides five APIs: Core (SQL) and MongoDB for
document data, Gremlin for graph data,
Azure Table, and Cassandra. Currently, you must create a separate account for each API.
Box 2: Yes -
Azure Cosmos DB uses partitioning to scale individual containers in a database to meet the performance needs of your
application. In partitioning, the items in a container are divided into distinct subsets called logical partitions. Logical
partitions are formed based on the value of a partition key that is associated with each item in a container.
Box 3: No -
Logical partitions are formed based on the value of a partition key that is associated with each item in a container.
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/partitioning-overview
Your company is designing an application that will write a high volume of JSON data and will have an application-defined
schema.
A. columnar
B. key/value
C. document
D. graph
Explanation:
Correct Answer: C
json files can define multiple keys and values, thus it is documents
Key/Value databases can not query json objects, you need to fully retrieve and then process...that means you have really
litle query options, just a simple group by query will take longer than on a Document type database(the right answer)
You need to recommend a non-relational data store that is optimized for storing and retrieving text files, videos, audio
streams, and virtual disk images. The data store must store data, some metadata, and a unique ID for each file.
Which type of data store should you recommend?
A. key/value
B. columnar
C. object
D. document
Explanation:
Correct Answer: C
Object storage is optimized for storing and retrieving large binary objects (images, files, video and audio streams, large
application data objects and documents, virtual machine disk images). Large data files are also popularly used in this
model, for example, delimiter file (CSV), parquet, and ORC. Object stores can manage extremely large amounts of
unstructured data.
Reference:


series on
CertyIQ Official YouTube channel (FREE PDFs): -

Please Subscribe to CertyIQ YouTube Channel to get notified for latest exam dumps by clicking
on the below image, it will redirect to the CertyIQ YouTube page.
Connect with us @ LinkedIn Telegram
Contact us for other dumps: -

contact.certyiqofficial@gmail.com
For any other enquiry, please drop us a mail at

enquiry.certyiqofficial@gmail.com
Your company is designing a data store for internet-connected temperature sensors.

The collected data will be used to analyze temperature trends.
A. relational
B. time series
C. graph
D. columnar
Explanation:
Correct Answer: B
Time series data is a set of values organized by time. Time series databases typically collect large amounts of data in
real time from a large number of sources.
Updates are rare, and deletes are often done as bulk operations. Although the records written to a time-series database
are generally small, there are often a large number of records, and total data size can grow rapidly.
Link for time series
https://docs.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/analytical-data-stores
Telemetry and time series databases are an append-only collection of objects. Telemetry databases efficiently index
data in a variety of column stores and in-memory structures, making them the optimal choice for storing and analyzing
vast quantities of telemetry and time series data.
Reference:
HOTSPOT -
Hot Area:
Explanation:
Correct Answer: YYY
Reference:
https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-get-started-portal
https://docs.microsoft.com/en-us/azure/storage/common/storage-account-overview
https://azure.microsoft.com/en-us/pricing/details/bandwidth/

HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Graph = Gremlin
Reference : https://azure.microsoft.com/en-us/blog/a-technical-overview-of-azure-cosmos-db/
API - Gremlin // Container is projected as -Graph // Item is projected as -Nodes and Edges
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/create-graph-gremlin-console
At which two levels can you set the throughput for an Azure Cosmos DB account? Each correct answer presents a
complete solution.
A. database
B. item
C. container
D. partition
Explanation:
Correct Answer: AC
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/set-throughput
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/high-availability
What is a characteristic of non-relational data?
A. no indexes
B. self-describing entities
C. a well-defined schema
D. no unique key values
Explanation:
Correct Answer: B
https://www.coursera.org/lecture/explore-core-data-concepts-microsoft-azure/explore-the-characteristics-of-non-
relational-data-nP41V
HOTSPOT -
Hot Area:
Explanation:
Correct Answer: Polybase
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15
You need to gather real-time telemetry data from a mobile application.

Which type of workload describes this scenario?
A. Online Transaction Processing (OLTP)
B. batch
C. massively parallel processing (MPP)
D. streaming
Explanation:
Correct Answer: D
as real-time is always Straming.. OLTP is delayed upto 30 sec
Reference:
https://docs.microsoft.com/en-in/azure/azure-monitor/overview
You have a dedicated SQL pool in Azure Synapse Analytics that is only used actively every night for eight hours.
You need to minimize the cost of the dedicated SQL pool as much as possible during idle times. The solution must
ensure that the data remains intact.
What should you do on the dedicated SQL pool?
A. Scale down the data warehouse units (DWUs).
B. Pause the pool.
C. Create a user-defined restore point.
D. Delete the pool
Explanation:
Correct Answer: B Pause the pool
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-compute-
overview
Which Azure Data Factory component initiates the execution of a pipeline?

A. a control flow
B. a trigger
C. a parameter
D. an activity
Explanation:
Correct Answer: B
Trigger.
Pipeline runs are typically instantiated by passing arguments to parameters that you define in the pipeline. You can
execute a pipeline either manually or by using a trigger.
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers#trigger-execution
Your company has a reporting solution that has paginated reports. The reports query a dimensional model in a data
warehouse.
Which type of processing does the reporting solution use?
A. stream processing
B. batch processing
D. Online Transaction Processing (OLTP)
Explanation:
Correct Answer: C
DWH --> OLAP
data warehouse is for OLAP, DBMS is for OLTP
Reference:
https://datawarehouseinfo.com/how-does-oltp-differ-from-olap-database/
DRAG DROP -
Match the types of activities to the appropriate Azure Data Factory activities.
To answer, drag the appropriate activity type from the column on the left to its Data Factory activity on the right. Each
activity type may be used once, more than once, or not at all.
Select and Place:
Explanation:
Correct Answer:
Box 1: Data movement -
Box 2: Data transformation -
A pipeline could contain a set of activities that ingest and clean log data, and then kick off a mapping data flow to
analyze the log data.
Box 3: Control -
Lookup Activity is a control flow activity.
Lookup Activity can be used to read or look up a record/ table name/ value from any external source. This output can
further be referenced by succeeding activities.
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/concepts-pipelines-activities#data-transformation-activities
complete solution.
D. schema on write
E. schema on read
F. normalized data
Explanation:
Correct Answer: BDF
D: Typical traits of transactional data include: schema on write, strongly enforced
Reference:
Which two activities can be performed entirely by using the Microsoft Power BI service without relying on Power BI
Desktop? Each correct answer presents a complete solution.
A. report and dashboard creation
B. report sharing and distribution
C. data modeling
D. data acquisition and preparation
Explanation:
Correct Answer: AB
A. reports/dashboard
B. sharing
according to this link: https://docs.microsoft.com/en-us/power-bi/fundamentals/service-service-vs-desktop
HOTSPOT -
Hot Area:
Explanation:
Correct Answer: Control flow (https://docs.microsoft.com/en-us/azure/data-factory/introduction)
Control flow is an orchestration of pipeline activities that includes chaining activities in a sequence, branching, defining
parameters at the pipeline level, and passing arguments while invoking the pipeline on-demand or from a trigger. It also
includes custom-state passing and looping containers, that is, For-each iterators.
You need to develop a solution to provide data to executives. The solution must provide an interactive graphical
interface, depict various key performance indicators, and support data exploration by using drill down.
What should you use in Microsoft Power BI?
A. a view
B. a report
C. a dataflow
D. Microsoft Power Apps
Explanation:
Correct Answer: B
Reference:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill
Which two Azure services can be used to provision Apache Spark clusters? Each correct answer presents a complete
solution.
A. Azure Time Series Insights
B. Azure HDInsight
C. Azure Databricks
D. Azure Log Analytics
Explanation:
Correct Answer: BC
https://docs.microsoft.com/en-us/azure/hdinsight/spark/apache-spark-overview
You have a quality assurance application that reads data from a data warehouse.
Which type of processing does the application use?
A. Online Transaction Processing (OLTP)
B. batch processing
D. stream processing
Explanation:
Correct Answer: C
Quality assurance application focuses on improving the software development process and making it efficient and
effective as per quality standards.
OLAP applies complex queries to large amounts of historical data, aggregated from OLTP databases and other sources,
for data mining, analytics, and business intelligence projects.
Moreover, the purpose of OLAP ----> Plan, solve problems, support decisions, discover hidden insights.
According to the question, the application is reading data from the data warehouse for quality assurance which is clearly
- discovering insights from the data. Thus, the answer is OLAP.
Reference:
Which three objects can be added to a Microsoft Power BI dashboard? Each correct answer presents a complete
solution.
A. a report page
B. a Microsoft PowerPoint slide
C. a visualization from a report
D. a dataflow
E. a text box
Explanation:
Correct Answer: ACE
A,C,E
https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-pin-live-tile-from-report
https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-add-widget
HOTSPOT -
Hot Area:
Explanation:
Correct Answer: Yes , No , yes
Dashboard is associate for a single workspace .
Dashboard visualization from different datasets and reports.
Dashboard visualization from other tools example: Excel
"ONE dashboard...
- is associated with a single workspace
- can display visualizations from many different datasets
- can display visualizations from many different reports
- can display visualizations pinned from other tools (for example, Excel)"
Reference: https://docs.microsoft.com/en-us/power-bi/fundamentals/service-basic-concepts
Which Azure Data Factory component provides the compute environment for activities?
A. SSIS packages
B. an integration runtime
C. a control flow
D. a pipeline
Explanation:
Correct Answer: B
The Integration Runtime (IR) is the compute infrastructure used by Azure Data Factory to provide the following data
integration capabilities across different network environments:
✑ Data Flow
✑ Data movement
✑ Activity dispatch
✑ SSIS package execution

Reference: https://docs.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Paginated Reports in Power BI now allows users to generate these fixed-layout documents optimized for printing and
archiving, such as PDF and Word files.
These document-style reports with visualizations that provide additional control, like which tables expand horizontally
and vertically to display all their data and continue from page to page as needed.
Reference:
https://powerbi.microsoft.com/en-us/blog/announcing-paginated-reports-in-power-bi-general-availability/
What are two uses of data visualization? Each correct answer presents a complete solution.
A. Represent trends and patterns over time
B. Implement machine learning to predict future values
C. Communicate the significance of data
D. Enforce business logic across reports
Explanation:
Correct Answer: AC
Data visualization is a key component in being able to gain insight into your data. It helps make big and small data easier
for humans to understand. It also makes it easier to detect patterns, trends, and outliers in groups of data.
Data visualization brings data to help you find key business insights quickly and effectively.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-data-visualization
You need to use Transact-SQL to query files in Azure Data Lake Storage Gen 2 from an Azure Synapse Analytics data
warehouse.
What should you use to query the files?
A. Azure Functions
B. Microsoft SQL Server Integration Services (SSIS)
C. PolyBase
D. Azure Data Factory
Explanation:
Correct Answer: C
Polybase is the correct answerd,
https://docs.microsoft.com/en-gb/learn/modules/explore-data-storage-processing-azure/3-explore-azure-synapse-
analytics
"Use SQL pools in Synapse Analytics for the following scenarios:
Complex reporting. You can use the full power of Transact-SQL to run complex SQL statements that summarize and
aggregate data.
Data ingestion. PolyBase enables you to retrieve data from many external sources and convert it into a tabular format.
You can reformat this data and save it as tables and materialized views in Azure Synapse."
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Box 1: No -
You can duplicate a dashboard. The duplicate ends up in the same Power BI workspace.
There is no current functionality that allows you to move reports from one workspace to another.
Box 2: No -
Box 3: Yes -
Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-across-workspaces
https://powerbi.microsoft.com/en-us/excel-and-power-bi/
complete solution.
D. schema defined in a database
E. schema defined when reading unstructured data from a database
F. normalized data
Explanation:
Correct Answer: BDF
D: Typical traits of transactional data include: schema on write, strongly enforced. The schema is defined in a database.
Reference:
What is the primary purpose of a data warehouse?

A. to provide answers to complex queries that rely on data from multiple sources
B. to provide transformation services between source and target data stores
C. to provide read-only storage of relational and non-relational historical data
D. to provide storage for transactional line-of-business (LOB) applications
Explanation:
Correct Answer: A
this is the main purpose of a data warehouse.
With C you could just create read-only replica's.
In the textbook DP-900T00-A-Microsoft Azure Data Fundamentals Module 4 Describe modern data warehousing:
''A data warehouse gathers data from many different sources within an organization...The focus of a data warehouse is
to provide answers to complex queries...''
DRAG DROP -
Match the Azure services to the appropriate locations in the architecture.
To answer, drag the appropriate service from the column on the left to its location on the right. Each service
may be used once, more than once, or not at all.
Select and Place:
Explanation:
Correct Answer:
Box Ingest: Azure Data Factory -
You can build a data ingestion pipeline with Azure Data Factory (ADF).
Box Preprocess & model: Azure Synapse Analytics
Use Azure Synapse Analytics to preprocess data and deploy machine learning models.
Reference:
https://docs.microsoft.com/en-us/azure/machine-learning/how-to-data-ingest-adf
https://docs.microsoft.com/en-us/azure/machine-learning/team-data-science-process/sqldw-walkthrough
DRAG DROP -
Match the types of workloads to the appropriate scenarios.
To answer, drag the appropriate workload type from the column on the left to its scenario on the right. Each
workload type may be used once, more than once, or not at all.
Select and Place:
Explanation:
Correct Answer:
Box 1: Batch -
The batch processing model requires a set of data that is collected over time while the stream processing model
requires data to be fed into an analytics tool, often in micro-batches, and in real-time.
The batch Processing model handles a large batch of data while the Stream processing model handles individual
records or micro-batches of few records.
In Batch Processing, it processes over all or most of the data but in Stream Processing, it processes over data on a
rolling window or most recent record.
Box 2: Batch -
Box 3: Streaming -
Reference:
https://k21academy.com/microsoft-azure/dp-200/batch-processing-vs-stream-processing
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Note: The data warehouse workload encompasses:
✑ The entire process of loading data into the warehouse
✑ Performing data warehouse analysis and reporting
✑ Managing data in the data warehouse
✑ Exporting data from the data warehouse

Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-
management
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Box 1: No -
Box 2: Yes -
You can construct pipeline hierarchies with data factory.
Box 3: Yes -
Reference:
https://mrpaulandrew.com/2019/09/25/azure-data-factory-pipeline-hierarchies-generation-control/
DRAG DROP -
Match the Azure services to the appropriate requirements.
To answer, drag the appropriate service from the column on the left to its requirement on the right. Each service may be
Select and Place:
Explanation:
Correct Answer:
- Azure Synapse Analytics: Output data to parquet format
- Azure Data Lake Storage: Store data that is in parquet format
- Azure SQL Database: Persist a tabular representation of data that is stored in parquet format
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Box 1: Yes -
Compute is separate from storage, which enables you to scale compute independently of the data in your system.
Box 2: Yes -
You can use the Azure portal to pause and resume the dedicated SQL pool compute resources.
Pausing the data warehouse pauses compute. If your data warehouse was paused for the entire hour, you will not be
charged compute during that hour.
Box 3: No -
Storage is sold in 1 TB allocations. If you grow beyond 1 TB of storage, your storage account will automatically grow to 2
TBs.
Reference:
https://azure.microsoft.com/en-us/pricing/details/synapse-analytics/
What should you use to build a Microsoft Power BI paginated report?

A. Charticulator
B. Power BI Desktop
C. the Power BI service
D. Power BI Report Builder
Explanation:
Correct Answer: D
Power BI Report Builder is the standalone tool for authoring paginated reports for the Power BI service.
Reference:
https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-report-builder-power-bi
DRAG DROP -
Match the Azure services to the appropriate locations in the architecture.
To answer, drag the appropriate service from the column on the left to its location on the right. Each service may be
Select and Place:
Explanation:
Correct Answer:
Box 1: Azure Data factory -
Relevant Azure service for the three ETL phases are Azure Data Factory and SQL Server Integration Services (SSIS).
Box 2: Azure Synapse Analytics -
You can copy and transform data in Azure Synapse Analytics by using Azure Data Factory
Note: Azure Synapse Analytics connector is supported for the following activities:
✑ Copy activity with supported source/sink matrix table
✑ Mapping data flow
✑ Lookup activity
✑ GetMetadata activity
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Reference:
https://docs.microsoft.com/en-us/azure/databricks/scenarios/what-is-azure-databricks
https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-overview
https://docs.microsoft.com/en-us/azure/data-factory/introduction
Which scenario is an example of a streaming workload?

A. sending transactions that are older than a month to an archive
B. sending transactions daily from point of sale (POS) devices
C. sending telemetry data from edge devices
D. sending cloud infrastructure metadata every 30 minutes
Explanation:
Correct Answer: C
Because telemetry data from edge devices need to be processed in real-time, not in batches.
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
A) doesn't make any sense as there is no data processing in the background. Batch means to process a big volume of
data in a particular time of the day (e.g. in the night with idle VMs)
B) You can once per day if you define it as such but in any case you are limited by "one day". So it's no.
C) Nonsense. Batch data is not a design for RT/streaming data OTA. You would use a different solution as data bricks.
Reason that data bricks service exists.
D) Correct. You can define your condition. Once it's met, you can execute it.
HOTSPOT -
Hot Area:
Explanation:
Correct Answer:
Option C says it sends the "readingS" once a day, which makes me think it has multiple readings and wait to send them
all once a day. So with given information I'm more inclined to Yes, Yes, No.
A bar chart showing year-to-date sales by region is an example of which type of analytics?
A. predictive
B. prescriptive
C. descriptive
D. diagnostic
Explanation:
Correct Answer: C
Nothing is being diagnosed here, but something is being described. So I would say C


series on
CertyIQ Official YouTube channel (FREE PDFs): -

Please Subscribe to CertyIQ YouTube Channel to get notified for latest exam dumps by clicking
on the below image, it will redirect to the CertyIQ YouTube page.
Connect with us @ LinkedIn Telegram
Contact us for other dumps: -

contact.certyiqofficial@gmail.com
For any other enquiry, please drop us a mail at

enquiry.certyiqofficial@gmail.com

DP-900

Uploaded by

Copyright:

Available Formats

DP-900

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DP-900

Uploaded by

Copyright:

Available Formats

Question 1 CertyIQ

✑ Descriptive, to answer the question: What's happening?

Your company uses both relational and non-relational data.

Box 2: The data warehouse -

Box 3: The data warehouse -

Box 2: Key influencer -

You need to create an Azure Storage account.

Which statement is an example of Data Manipulation Language (DML)?

Box 2: - Relationship between employees

Box 3: Key/value pairs

Box 2: Linked service -

Which type of database is used to store structured data?

Your company recently reported sales from the third quarter.

Which statement is an example of Data Manipulation Language (DML)?

You need to modify a view in a relational database by adding a new column.

Which type of database is Azure Database for PostgreSQL?

What is a characteristic of batch processing?

When can you use an Azure Resource Manager template?

Box 2: Microsoft Visual Studio Code

Box 3: Azure Data Studio -

Box 4: Microsoft SQL Server Data Tools (SSDT)

Which statement is an example of Data Definition Language (DDL)?

Which statement is an example of Data Definition Language (DDL)?

You need to evaluate the characteristics of semi-structured data.

Want all PDFs of DP900?

Please find the videos of this AZ-900/AI-900/AZ-305/

Box 2: The data warehouse -

Box 3: The data warehouse -

Choose the correct answer

Which is the best type of data store you can use?

Choose the correct answer

Drag and drop the answers: -

You work as a data engineer.

C. Azure Database for MariaDB

Which file format is shown in the below exhibit?

You need to identify the components of a graph database.

Which feature of this table is highlighted?

B. Instance of customer entity

Question 100 CertyIQ

Question 101 CertyIQ

Question 102 CertyIQ

B. Online transaction processing (OLTP)

C. Online analytical processing (OLAP)

Question 103 CertyIQ

C. Online analytical processing (OLAP)

D. Online transaction processing (OLTP)

Question 104 CertyIQ

A. To identify trends and relationships in data

B. To create and maintain data models and datasets

C. To design and maintain a data warehouse

D. To design and implement disaster recovery plans

E. To create reports and dashboards

Question 105 CertyIQ

C. SQL Server Management Studio

D. Azure Data Studio

Question 106 CertyIQ

Question 107 CertyIQ

Drag and drop the answers: -

Question 109 CertyIQ