SQL Server Advanced Data Warehousing: Expert-Level Quiz

SQL Server Advanced Data Warehousing (Expert-Level Quiz)

Test your knowledge of SQL Server Advanced Data Warehousing with this expert-level quiz. These questions will test your knowledge of advanced data warehousing practices, focusing on practical implementation through scripts and code and general practical questions. Seasoned professionals looking to validate their expertise and ensure their data warehousing solutions are efficient and robust will find this quiz intriguing.

1 / 20

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

First load data into fact tables then dimension tables, then Aggregates if any

Does not matter if we load either of fact, dimensions, or aggregates

First Aggregates then load data into dimension tables, then fact tables

First load data into dimension tables, then fact tables, then Aggregates if any

2 / 20

What describes dimension tables?

Units of measures

Keys

Facts

Entities

3 / 20

Complete the script to create a clustered columnstore index:

CREATE ____________ CCI_Sales
ON Sales;

CLUSTERED COLUMNSTORE INDEX

NONCLUSTERED INDEX

NONCLUSTERED COLUMNSTORE INDEX

CLUSTERED INDEX

4 / 20

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 ___;

CREATE_FACT_TABLE

PRIMARY

FACT

DIMENSION

5 / 20

Fill in the missing code to create a partitioned table:

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

PARTITIONS

SCHEME

PARTITION

FILEGROUP

6 / 20

Which of the following is not a type od SCD?

None of the above

Type 0

Type 1

Type 2

7 / 20

What is the purpose of the WITH SCHEMABINDING option in a view?

To encrypt the view

To create a partitioned view

To ensure the view is bound to the schema of the underlying tables

To create an indexed view

8 / 20

What is the primary purpose of a data warehouse?

To store and manage transactional data

To automate backups

To secure sensitive data

To store historical data for analysis and reporting

9 / 20

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?

100

10 / 20

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

Term Lookup Transformation

Data Mining Query Transformation

Term Extraction Transformation

Fuzzy Lookup Transformation

11 / 20

Which of the following statements is correct?

MDW consist of three components

SQL Server Express instances can be targets

All of the above

Setting up the MDW is a one-step process

12 / 20

Fill in the missing code to create a data warehouse schema:

CREATE SCHEMA ___;

DataWarehouse

WarehouseData

WarehouseDatabase

13 / 20

What is the syntax for sp_add_collector_type procedure?

core.sp_add_collector_type [ @collector_type_uid = ] ‘collector_type_uid’

core.sp_add_collector [ @collector_type_uid = ] ‘collector_type_uid’

core.sp_add_collector_type [ @collector_type_uid = ].

None of the above

14 / 20

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

False

True

15 / 20

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

SQL Server Integration Services (SSIS)

SQL Server Analysis Services (SSAS)

SQL Server Reporting Services (SSRS)

SQL Server Management Studio (SSMS)

16 / 20

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

CREATE PACKAGE ___;

ETLProcess

SSISPackage

DataLoad

DataWarehouse

17 / 20

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;

CREATE INDEX

CREATE SCHEMA

CREATE TABLE

CREATE VIEW

18 / 20

What is data scrubbing?

A process to load the data in the data warehouse and to create the necessary indexes

A process to upgrade the quality of data before it is moved into a data warehouse

A process to upgrade the quality of data after it is moved into a data warehouse

19 / 20

When considering the task of importing country data from a flat file into SQL Server and Oracle databases, which transformation would be employed?

Merge Transformation

Lookup Transformation

Cache Transform

Multicast Transformation

20 / 20

Which of the following stores information about how the management data warehouse reports should group and aggregate performance counters?

core.wait_categories

core.performance_counter_report_group_items

core.snapshots_internal

core.supported_collector_types_internal

Your score is

The average score is 0%

SQL Server advanced data warehousing is critical for businesses seeking to optimize data storage, retrieval, and analysis.

Besides, the domain of SQL Server data warehousing is reserved for those who have ventured beyond basic database administration. It’s for those engaged in strategic data handling at scale.

This expert-level quiz will test your knowledge of complex queries, data transformations, and warehouse architectures that support vast amounts of data.

Good luck.