ExamPark
Course menuDEA-C01

Exams / DEA-C01 / Loading and unloading Redshift / Question 2

DEA-C01 practice question: Loading and unloading Redshift (2 of 2)

Data Store Management A free question from our original DEA-C01 practice exam. Try it, then open the answer to see why each option is right or wrong.

Last verified against current AWS documentation ·How we write our questions

A logistics broker runs a daily pipeline that moves data between Amazon S3 and Amazon Redshift. The pipeline loads carrier files into a staging table, and it exports a shipment summary back to S3 for a data lake that Athena queries by month.

The team wants the loads to read only the files it intends to load, to avoid stored credentials in SQL, and to write the export in a columnar format partitioned by month.

Which THREE options meet these requirements? (Choose three.)

Choose three answers.

  1. A. Use COPY with an IAM role attached to the cluster, so the SQL statement does not contain access keys.
  2. B. Run an INSERT statement for each row in a file, so that each load is limited to the rows the team intends to load.
  3. C. Use COPY with a manifest that lists the exact objects to load, so stray or partial files in the prefix are not loaded.
  4. D. Use UNLOAD with plain text output and no partitions, then rename the files in S3 by month with a script after each run.
  5. E. Use UNLOAD with PARQUET format and PARTITION BY on the month column, so the export writes columnar month prefixes.
  6. F. Store the access key and secret key in a table in the cluster, and read them into each COPY statement at run time.
Show the answer and explanation

Answers: A, C, E

  • A. Right: Correct. Attaching an IAM role to the cluster and naming it in COPY removes access keys from the SQL. The role needs read access to the bucket and prefix that the load uses. Learn more: Amazon Redshift best practices for loading data (opens in a new tab)
  • B. Wrong: Row-by-row inserts are slow for bulk data and do not use COPY's parallel loading. The option misses the performance goal.
  • C. Right: Correct. A manifest names the exact files that COPY should read, so extra objects in the prefix are ignored. This keeps each load limited to the files the team intends to load. Learn more: Amazon Redshift best practices for loading data (opens in a new tab)
  • D. Wrong: Plain text is row-based, and renaming files by script adds a fragile step. The option does not produce the columnar, partitioned layout the team asked for.
  • E. Right: Correct. UNLOAD can write Parquet files and partition the output by a column, which creates month prefixes that Athena can prune. The layout matches the data lake's query pattern. Learn more: UNLOAD (opens in a new tab)
  • F. Wrong: Storing keys in a table keeps long-term credentials in the database, which the team wants to avoid. The option fails the credentials requirement.

Explanation

Three practices meet the stated needs. An IAM role attached to the cluster avoids access keys in SQL, a manifest limits COPY to the files the team intends to load, and UNLOAD with Parquet and PARTITION BY writes a columnar layout partitioned by month. Row-by-row inserts, plain text exports and stored keys each miss one requirement.

Study

Keep practicing

Answer questions with a score at the end, or practice just this topic with the explanation after each one. No sign-up needed.

Take the free DEA-C01 practice examPractice Loading and unloading RedshiftAll Loading and unloading Redshift questions

Want more on Loading and unloading Redshift? The 5 full-length practice sets have 10 more Loading and unloading Redshift questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets