SQL Server Advanced Data Warehousing: Expert-Level Quiz

Complete the script to create a data warehouse staging table:

CREATE TABLE StagingSales (
    SaleID INT,
    ProductID INT,
    CustomerID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
) ON ___;

You are tasked with configuring the Similarity of Similar percentage that defines a match in a matching policy containing one matching rule. In what similarity percentage will a similarity score of 0 always be generated?

Fill in the missing code to create a data warehouse ETL process using SSIS:

CREATE PACKAGE ___;

Which of the following is not a type od SCD?

Which of the following is the right sequence of jobs to load data into warehouse?

There is a Microsoft SQL server equipped with Data Quality Services (DQS); your task is to evaluate the data's completeness and uniqueness within the matching policy. To accomplish this, you decide to establish a matching rule. Would this approach achieve the intended objective?

The major difference between non-addictive facts and conformed facts is that conformed facts cannot sum up any of the dimensions in the fact table. Whereas, a non-additive fact is a table across multiple data marts and fact tables.

Which of the following transformations would you use to extract the maximum number of posts made about a person from a social media archive?

Complete the script to create an indexed view:

CREATE VIEW vw_SalesSummary
____________
AS
SELECT SaleDate, SUM(Amount) AS TotalSales
FROM dbo.Sales
GROUP BY SaleDate;

What is the primary purpose of a data warehouse?

What describes dimension tables?

Partitioning is done for many reasons, such as assisting backup recovery, enhancing performance, and easy management.

What is data scrubbing?

Fill in the missing code to create a partitioned table:

CREATE TABLE Sales (
    SaleID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
) ON ___ (SaleDate);

Which of the following statements is correct?

Complete the script to create a clustered columnstore index:

CREATE ____________ CCI_Sales
ON Sales;

Which command is used to create a view in SQL Server?

_________ vw_ProductSales
AS
SELECT ProductID, SUM(Amount) AS TotalSales
FROM FactSales
GROUP BY ProductID;

Which SQL Server feature allows you to create a data warehouse ETL process?

Fill in the missing code to create a data warehouse fact table:

CREATE TABLE FactSales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    CustomerID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
) ON ___;