Discuss Databricks Databricks Certified Data Analyst Associate Exam Topic 4 Question 1

A data analysis team is working with the table_bronze SQL table as a source for one of its most complex projects. A stakeholder of the project notices that some of the downstream data is duplicative. The analysis team identifies table_bronze as the source of the duplication.

Which of the following queries can be used to deduplicate the data from table_bronze and write it to a new table table_silver?

CREATE TABLE table_silver AS

SELECT DISTINCT *

FROM table_bronze;

CREATE TABLE table_silver AS

INSERT *

FROM table_bronze;

CREATE TABLE table_silver AS

MERGE DEDUPLICATE *

FROM table_bronze;

INSERT INTO TABLE table_silver

SELECT * FROM table_bronze;

INSERT OVERWRITE TABLE table_silver

SELECT * FROM table_bronze;

Suggested Answer: A

Option A uses theSELECT DISTINCTstatement to remove duplicate rows from thetable_bronzeand create a new tabletable_silverwith the deduplicated data.This is the correct way to deduplicate data using Spark SQL12. Option B simply inserts all the rows fromtable_bronzeintotable_silver, without removing any duplicates. Option C is not a valid syntax for Spark SQL, as there is noMERGE DEDUPLICATEstatement. Option D appends all the rows fromtable_bronzeintotable_silver, without removing any duplicates. Option E overwrites the existing data intable_silverwith the data fromtable_bronze, without removing any duplicates.Reference:Delete Duplicate using SPARK SQL,Spark SQL - How to Remove Duplicate Rows

Get Premium Databricks Certified Data Analyst Associate Questions as Interactive Web-Based Practice Test or PDF Real Exam Format Detailed Explanations Instant Access Get Your Practice Test

17 comments · newest first

Brice 10 months ago

Option C? Never heard of that syntax before. Is it even valid?

Cristina 11 months ago

Totally agree with A! It's the simplest and most effective.

Ethan 11 months ago

Wait, can you really use "MERGE DEDUPLICATE"? Sounds off.

Melita 11 months ago

I think Option B could work too, but not for deduplication.

Raelene 11 months ago

Option A is definitely the way to go!

Cristy 12 months ago

I don't think MERGE DEDUPLICATE is a standard SQL command, so Option C seems wrong. But I'm not completely confident about the others either.

Brandee 12 months ago

I feel like we practiced a similar question where we had to create a new table without duplicates. Option A sounds familiar, but I can't recall if there were other methods we discussed.

Rashad 12 months ago

I'm not entirely sure, but I think INSERT INTO might just add more duplicates instead of removing them. So, maybe not Option D?

Josefa 1 year ago

I remember we discussed using DISTINCT to remove duplicates in SQL. Option A seems like the right choice.

Deandrea 1 year ago

Option B with the "INSERT *" syntax looks a bit strange to me. I don't think that's valid SQL, so I'll probably rule that one out.

Bong 1 year ago

Option C mentions "MERGE DEDUPLICATE", which sounds promising, but I'm not familiar with that syntax. I'll have to double-check the documentation to see if that's a valid SQL operation.

Ty 1 year ago

I'm a bit unsure about the difference between Option D and Option E. Both seem to be using INSERT statements, but I'm not sure if there's a meaningful difference between them.

Tomoko 1 year ago

Hmm, this looks like a pretty straightforward deduplication task. I think Option A with the DISTINCT keyword is the way to go here.

Pearlie 1 year ago

I'm leaning towards Option A as well. The DISTINCT keyword is a classic way to remove duplicates, and it's a simple and reliable solution.

Dawne 1 year ago

I think this is a pretty straightforward question. The security guards likely have the right to check IDs and bags for safety reasons, so I'll go with A.

Raelene 1 year ago

Hmm, I'm a little unsure about this one. I'll need to carefully review the Scrum principles and how they relate to risk management.

Annice 1 year ago

I'm a little unsure here. I'll need to review my notes on the different accounting pronouncements to make sure I choose the right one.

Databricks Certified Data Analyst Associate Exam - Topic 4 Question 1 Discussion