DP-900

Descriptive analytics summarizes historical data to answer what happened, for example through reports and dashboards of past sales. Explaining why something happened is diagnostic analytics, forecasting what is likely to happen is predictive analytics, and recommending actions to affect outcomes is prescriptive analytics.

Statement 1 (No): Normalization splits data into separate tables for each entity and links them with primary and foreign keys, so it creates relationships between tables rather than eliminating them. Statement 2 (Yes): Each fact is stored once in the table for its entity instead of being repeated across rows, which reduces duplicate data. Statement 3 (Yes): Because each value is stored in one place and related through keys, updates cannot leave inconsistent copies behind, which improves data integrity.

In ETL, data is transformed by a separate engine, often using staging tables, before it is loaded into the destination, so the data arrives at the target already processed. Needing a target store powerful enough to transform data describes ELT, where transformation happens inside the target. ETL is often used precisely because source and target schemas differ, and the target does not have to be a relational database.

Batch processing collects data over a period and processes it in groups, for example on a schedule, so results are available only after a delay and that latency is accepted. Processing data as it arrives in real time is stream processing. Batches load many rows at once rather than one row at a time, and batch jobs commonly run in parallel rather than only serially.

Cognitive analytics applies human-like AI capabilities such as speech recognition, vision and language understanding to unstructured data, and converting speech in audio files to text is a typical example. Descriptive analytics reports what happened, predictive analytics forecasts what will happen, and prescriptive analytics recommends actions; none of them turns audio into text.

Asking why sales increased last month looks for the cause of a past event, which is diagnostic analytics; descriptive analytics would only report that sales went up. Deciding how to allocate a budget across inventory items asks what action to take, which is prescriptive analytics, going beyond predictive forecasting of demand. Finding the people mentioned in business documents requires extracting entities from unstructured text with AI, which is cognitive analytics.

Customer: "customer" is the top-level object that contains all the other properties, so it is the root object. Address: "address" is enclosed in curly braces inside the customer object, so it is a nested object. Social media: "social media" is enclosed in square brackets and holds a list of objects (one per service), so it is a nested array.

Data model: a central Sales table holds foreign keys to Customer, Product, SalesPerson and Warehouse, and none of those tables references another table, so the model is a star schema; a snowflake schema would have dimension tables linked to further sub-dimension tables. Customer: Customer describes an entity that the Sales facts are analyzed by and has no foreign keys of its own, so it is a dimension table; Sales is the fact table, and a bridge table would resolve a many-to-many relationship, which this model does not have.

In a dedicated SQL pool, applications connect to a single Control node, whose distributed query engine splits each query into parallel operations that run on the Compute nodes. There is one Control node, so processing is not spread across control nodes, and client connections are handled by the Control node rather than redirected to compute nodes.

A clustered index sorts and stores the data rows of a table based on its key values, which is why a table can have only one. A FileTable stores files and directories in SQL Server, a foreign key enforces a relationship to another table, and a stored procedure is saved T-SQL code; none of them determines the physical order of the rows.

The dropdown should be completed with transactional writes. Relational databases are built for online transaction processing (OLTP): many small, ACID-compliant inserts and updates against a fixed schema. Writes with varying data structures suit a schema-flexible NoSQL store, geographically distributed multi-region writes are a strength of services such as Azure Cosmos DB, and frequently changing relationships between entities are better modelled in a graph database.

Statement 1 (Yes): Batch jobs commonly read source files, process them and write the output to new files in a data lake or other file store. Statement 2 (Yes): The processed batch output is often loaded into an analytical data store such as a relational data warehouse or Azure SQL Database for reporting. Statement 3 (Yes): Batch output can also be served from a low-latency NoSQL store such as HBase or Azure Cosmos DB; batch processing does not restrict the type of sink.

Extract reads the data from the source, the CRM system. Load writes the raw data into the target, the data warehouse. In ELT the transformation also happens in the data warehouse, using the target store's own processing power instead of a separate transformation engine. A separate in-memory data integration tool performing the transform is the ETL pattern, which is the tempting wrong choice here.

The dropdown should be completed with descriptive. Descriptive analytics answers what is happening or has happened, and comparing current enrollment to capacity simply reports the present state. Predictive analytics forecasts what will happen, prescriptive analytics recommends actions to take, and cognitive analytics draws inferences using AI techniques; none of these apply to a plain comparison of current figures.

A treemap shows hierarchical data as nested rectangles whose size and color represent values. The key influencers visual analyzes the data to show which factors most drive a selected outcome or value. A scatter chart plots points on two numeric axes to reveal the relationship or correlation between two numerical values.

GRS and RA-GRS both copy data asynchronously to a secondary region paired with the primary, so the data automatically leaves the primary region; RA-GRS additionally allows read access to the secondary copy. LRS keeps three copies within a single datacenter, and ZRS spreads copies across availability zones in the same region, so neither replicates outside the region.

Statement 1 (Yes): With PaaS, Azure provisions and manages the underlying servers, operating system and database engine, so you avoid installing and configuring them yourself as you would on an IaaS virtual machine. Statement 2 (No): In PaaS the operating system is managed and patched by Microsoft; controlling the OS version requires IaaS, such as SQL Server on an Azure virtual machine. Statement 3 (No): Only some offerings support pausing, such as the serverless tier of Azure SQL Database with auto-pause; provisioned Azure SQL Database and Azure Cosmos DB, for example, cannot simply be paused, so the claim that all can is false.

DML statements work with the data in tables: SELECT, INSERT, UPDATE and DELETE. INSERT adds rows, so it is DML. GRANT and REVOKE are Data Control Language (DCL) statements that manage permissions, and DISABLE (as in DISABLE TRIGGER) belongs to object definition and management rather than data manipulation.

A view is a virtual table defined by a stored SELECT query; it can join customer and order data and include calculated columns, and other users simply query the view to rerun it. An index only speeds up data access, a scalar function returns a single value rather than a result set, and a table would store a static copy of the data instead of rerunning the query.

The dropdown should be completed with simple lookups. A key/value store retrieves an opaque value by its unique key very quickly, which makes it ideal for simple insert and lookup operations such as caching or session state. Enforcing constraints, table joins and multi-row transactions are relational database strengths that key/value stores generally do not provide.