Exam 70-767 topic 1 question 62 discussion

Actual exam question from Microsoft's 70-767

Question #: 62
Topic #: 1

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are designing a data warehouse and the load process for the data warehouse.
You have a source system that contains two tables named Table1 and Table2. All the rows in each table have a corresponding row in the other table.
The primary key for Table1 is named Key1. The primary key for Table2 is named Key2.
You need to combine both tables into a single table named Table3 in the data warehouse. The solution must ensure that all the nonkey columns in Table1 and
Table2 exist in Table3.
Which component should you use to load the data to the data warehouse?

A. the Slowly Changing Dimension transformation
B. the Conditional Split transformation
C. the Merge transformation
D. the Data Conversion transformation
E. an Execute SQL task
F. the Aggregate transformation
G. the Lookup transformation

Show Suggested Answer

Suggested Answer: G 🗳️
The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.
You can configure the Lookup transformation in the following ways:
Specify joins between the input and the reference dataset.
Add columns from the reference dataset to the Lookup transformation output.
Etc.
Incorrect Answers:
F: The Aggregate transformation applies aggregate functions, such as Average, to column values and copies the results to the transformation output. Besides aggregate functions, the transformation provides the GROUP BY clause, which you can use to specify groups to aggregate across.
References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/lookup-transformation

by ialberto at June 8, 2020, 1:40 p.m.

Comments

Submit Cancel

othman_ee

4 years, 10 months ago

Correct answer G

upvoted 4 times

...

ialberto

5 years ago

I think the correct answer is C, because is the same than former question. You have 2 tables that have a relationship by a key and you want to load all columns nonkeys in table destination. Anyone have a different opinion?

upvoted 3 times

behjat

4 years, 11 months ago

No, it says "The solution must ensure that all the nonkey columns in Table1 and Table2 exist in Table3." It wants you to specify the existence/availability of records among source tables and the destination table. The Lookup transformation looks fine.

upvoted 4 times

eceb

4 years, 10 months ago

I don't understand. With a merge transformation you can use a join between the two tables and select all the columns of both sources. You can also retrieve the columns of the second table using a lookup, but why do you think a merge transformation is wrong?

upvoted 1 times

anon456

4 years, 10 months ago

No, Merge Transformation doesn't join two tables. Merge Transformation just puts all rows from two tables into one table. Since the rows are corresponding, each of them would be in the resulting table twice (one with the columns from table 1 filled and one with the columns from table 2 filled) instead of just once with all non-key columns from both tables. To join two tables you need to use a "Merge Join Transformation", but since that's not an chosable answer it has to be "Lookup Transformation". At least that's my understanding, correct me if I'm wrong pls :D

upvoted 6 times

eceb

4 years, 10 months ago

Oh yes! when I was talking about "merge transformation" I was really thinking in a "merge join transformation", but as you refer that's not a chosable answer. In this case, I agree, lookup is fine.

upvoted 2 times

...

eceb

4 years, 10 months ago

I agree. Merge transformation combines data from 2 datasets into one dataset. Lookup can find matching rows but cannot combine data from two sources

upvoted 1 times

anon456

4 years, 10 months ago

It is not about combining data in the sense of adding the rows though, cause "All the rows in each table have a corresponding row in the other table." So you really just need to take the rows from one table and add the columns and data from the other table and that is exactly what a Lookup Tansformation does.

upvoted 4 times

eceb

4 years, 10 months ago

Thanks for your answer. I agree.

upvoted 2 times

...