DEA-C01 Data Store Management Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  SELECT * FROM SVV_TABLE_INFO ORDER BY unsorted DESC;

  Why this is correct

  SVV_TABLE_INFO exposes per-table statistics including the unsorted percentage, so ordering by unsorted DESC surfaces the tables with the most unsorted rows. That directly identifies where a VACUUM SORT is needed to address the slow query performance.

• ✗

  SELECT * FROM PG_CATALOG;

  Why it's wrong here

  PG_CATALOG holds system catalogue views describing metadata such as table and column definitions, not per-table sort statistics. It is tempting because it is a queryable schema, and would be correct when inspecting object definitions rather than identifying the percentage of unsorted rows.

• ✗

  SELECT * FROM STV_TBL_PERM;

  Why it's wrong here

  STV_TBL_PERM lists permanent table metadata such as distribution keys and sort keys, but reports no per-row sort status, so it cannot reveal unsorted rows. It is tempting because it is the go-to catalogue view for inspecting table design, and would be correct when auditing distribution styles or identifying tables lacking a sort key.

• ✗

  SELECT * FROM STL_LOAD_ERRORS;

  Why it's wrong here

  STL_LOAD_ERRORS records rows rejected during COPY loads, so it cannot reveal unsorted rows in a table. It is tempting because it is a common Redshift diagnostic view, and would be correct when investigating why a bulk load failed rather than diagnosing sort-key effectiveness.

Go deeper

Related to this question