Microsoft DP-600: How to Identify Duplicate Email Addresses in Microsoft Fabric Data Warehouse Table Using T-SQL

Learn the correct T-SQL statements to find and return up to 1,000 duplicate email addresses in a Microsoft Fabric data warehouse table. Follow this step-by-step solution for the DP-600 certification exam.

Question

You have a Fabric tenant that contains a data warehouse named DW1. DW1 contains a table named DimCustomer. DimCustomer contains the fields shown in the following table.

ID	CustomerKey	CustomerAltKey	Title	FirstName	LastName	AddressLine1	City
1	29546	[email protected]	Mr.	Christopher	Beck	93-2501, Blackfriars Road	London
2	29587	[email protected]	Mr.	John	Brooks	99-3 Forest Works	London
3	29612	[email protected]	Mr.	Richard	Byham	Knaves Beech Ind	High Wycombe

You need to identify duplicate email addresses in DimCustomer. The solution must return a maximum of 1,000 records.

Which four T-SQL statements should you run in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Statement:

1. SELECT TOP(1000) CustomerAltKey, COUNT(*)
2. GROUP BY CustomerAltKey
3. FROM DimCustomer
4. SELECT CustomerAltKey, COUNT(*)
5. LIMIT 1000
6. HAVING COUNT(*) > 1
7. WHERE COUNT(*) > 1

Answer

To identify duplicate email addresses in the DimCustomer table and return a maximum of 1,000 records, you should run the following four T-SQL statements in sequence:

1. SELECT TOP(1000) CustomerAltKey, COUNT(*)
2. FROM DimCustomer
3. GROUP BY CustomerAltKey
4. HAVING COUNT(*) > 1

Explanation

1. SELECT TOP(1000): This clause limits the result set to a maximum of 1,000 records.
2. CustomerAltKey, COUNT(*): The SELECT statement retrieves the CustomerAltKey column and counts the number of occurrences using COUNT(*).
3. FROM DimCustomer: Specifies the source table for the query.
4. GROUP BY CustomerAltKey: Groups the result set by the CustomerAltKey column, allowing the COUNT(*) to calculate the number of occurrences for each email address.
5. HAVING COUNT(*) > 1: Filters the grouped result set to include only the CustomerAltKey values that appear more than once, thus identifying the duplicate email addresses.

The other statements in the list are either incorrect or in the wrong order. The LIMIT clause is not a valid T-SQL keyword, and the WHERE clause cannot be used with an aggregate function like COUNT(*).

By running this T-SQL statement, you will successfully identify and return up to 1,000 duplicate email addresses from the DimCustomer table in your Microsoft Fabric data warehouse.

Microsoft DP-600 certification exam assessment practice question and answer (Q&A) dump including multiple choice questions (MCQ) and objective type questions, with detail explanation and reference available free, helpful to pass the Microsoft DP-600 exam and earn Microsoft DP-600 certification.