Data Warehousing on AWS Training Data Warehousing on AWS: SQL 3

Question 1

When using a COPY command in Redshift, which SQL technique best avoids duplicate rows if the load is rerun?

Accepted Answer

Using a staging table with DELETE/INSERT or MERGE

Answer

Loading into a staging table and using DELETE or MERGE to update the target prevents duplicates when rerunning incremental loads.

Question 2

Which Redshift SQL clause partitions rows into groups for window function calculations without reducing the result set?

Accepted Answer

PARTITION BY inside OVER()

Answer

PARTITION BY inside the OVER() clause divides rows into groups for window functions while keeping all rows in the output.

Question 3

In Redshift SQL, what is the effect of using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in a window frame?

Accepted Answer

Computes a running calculation from the first row to the current row

Answer

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines a cumulative window frame from the partition start to the current row.

Question 4

Which data type should you use in Redshift to store variable-length character strings up to 65,535 bytes?

Accepted Answer

VARCHAR

Answer

VARCHAR (or CHARACTER VARYING) stores variable-length strings and accepts a maximum length up to 65,535 bytes in Redshift.

Question 5

What is a lateral join (LATERAL) used for in Amazon Redshift SQL?

Accepted Answer

Allowing a subquery to reference columns from preceding table references

Answer

LATERAL allows a subquery or function in the FROM clause to reference columns from tables listed earlier in the same FROM clause.

Data Warehousing on AWS Training Practice Test