HOTSPOT
Overview:
Relecloud is a social media company that processes hundreds of millions of social media posts per day and
sells advertisements to several hundred companies.
Relecloud has a Microsoft SQL Server database named DB1 that stores information about the advertisers. DB1
is hosted on a Microsoft Azure virtual machine.
Relecloud has two main offices. The offices are located in San Francisco and New York City.
The offices connect to each other by using a site-to-site VPN. Each office connects directly to the Internet.
Relecloud modifies the pricing of its advertisements based on trending topics. Topics are considered to be
trending if they generate many mentions in a specific country during a 15-minute time frame. The highest
trending topics generate the highest advertising revenue.
Relecloud wants to deliver reports to the advertisers by using Microsoft Power BI. The reports will provide realtime data on trending topics, current advertising rates, and advertising costs for a given month.
Relecloud will analyze the trending topics data, and then store the data in a new data warehouse for ad-hoc
analysis. The data warehouse is expected to grow at a rate of 1 GB per hour or 8.7 terabytes (TB) per year.
The data will be retained for five years for the purpose of long-term trending.
Requirements:
Management at Relecloud must be able to view which topics are trending to adjust advertising rates in near
real-time.
Relecloud plans to implement a new streaming analytics platform that will report on trending topics.
Relecloud plans to implement a data warehouse named DB2.
Relecloud identifies the following technical requirements:
Social media data must be analyzed to identify trending topics in real-time.
The use of Infrastructure as a Service (IaaS) platforms must minimized, whenever possible.
The real-time solution used to analyze the social media data must support scaling up and down without
service interruption.
Relecloud identifies the following technical requirements for the advertisers:
The advertisers must be able to see only their own data in the Power BI reports.
The advertisers must authenticate to Power BI by using Azure Active Directory (Azure AD) credentials.
The advertisers must be able to leverage existing Transact-SQL language knowledge when developing the
real-time streaming solution.
Members of the internal advertising sales team at Relecloud must be able to see only the sales date of the
advertisers to which they are assigned.
The internal Relecloud advertising sales team must be prevented from inserting, updating, and deleting
rows for the advertisers to which they are not assigned.
The internal Relecloud advertising sales team must be able to use a text file to update the list of advertisers,
and then to upload the file to Azure Blob storage.
Relecloud identifies the following requirements for DB1:
Data generated by the streaming analytics platform must be stored in DB1.
The user names of the advertisers must be mapped to CustomerID in a table named Table2.The advertisers in DB1 must be stored in a table named Table1 and must be refreshed nightly.
The user names of the employees at Relecloud must be mapped to EmployeeID in a table named Table3.
Relecloud identifies the following requirements for DB2:
DB2 must have minimal storage costs.
DB2 must run load processes in parallel.
DB2 must support massive parallel processing.
DB2 must be able to store more than 40 TB of data.
DB2 must support scaling up and down, as required.
Data from DB1 must be archived in DB2 for long-term storage.
All of the reports that are executed from DB2 must use aggregation.
Users must be able to pause DB2 when the data warehouse is not in use.
Users must be able to view previous versions of the data in DB2 by using aggregates.
Relecloud identifies the following requirements for extract, transformation, and load (ETL):
Data movement between DB1 and DB2 must occur each hour.
An email alert must be generated when a failure of any type occurs during ETL processing.
Sample code and data:
You execute the following code for a table named rls_table1.
You use the following code to create Table1.
create table table1
(customerid int,
salespersonid int
…
)
Go
The following is a sample of the streaming data.
You implement DB2.
You need to configure the tables in DB2 to host the data from DB1. The solution must meet the requirements
for DB2.
Which type of table and history table storage should you use for the tables? To answer, select the appropriate
options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer: 
Explanation:
From Scenario: Relecloud plans to implement a data warehouse named DB2.
Box 1: Temporal table
From Scenario:
Relecloud identifies the following requirements for DB2:
Users must be able to view previous versions of the data in DB2 by using aggregates.
DB2 must be able to store more than 40 TB of data.
A system-versioned temporal table is a new type of user table in SQL Server 2017, designed to keep a full
history of data changes and allow easy point in time analysis. A temporal table also contains a reference to
another table with a mirrored schema. The system uses this table to automatically store the previous version of
the row each time a row in the temporal table gets updated or deleted. This additional table is referred to as the
history table, while the main table that stores current (actual) row versions is referred to as the current table or
simply as the temporal table.
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
Box 2: Clustered Columnstore
From Scenario:
The data warehouse is expected to grow at a rate of 1 GB per hour or 8.7 terabytes (TB) per year. The data will
be retained for five years for the purpose of long-term trending.
Relecloud identifies the following requirements for DB2:
Data from DB1 must be archived in DB2 for long-term storage.
How SQL Database deletes aged rows?
The cleanup process depends on the index layout of the history table. It is important to notice that only history
tables with a clustered index (B-tree or columnstore) can have finite retention policy configured.
https://docs.microsoft.com/en-us/sql/relational-databases/tables/manage-retention-of-historicaldata-in-system-versioned-temporal-tables