DEA-C01 practice question: Troubleshooting and tuning pipelines (2 of 3)
Data Operations and Support 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.
A dental chain's Athena queries on a claims table began failing last Tuesday with a HIVE_PARTITION_SCHEMA_MISMATCH error after the upstream system changed one column's type from int to bigint. Only the partitions written before the change are affected, and the table's own schema already shows the new type.
The team wants the queries to work again on all partitions without rewriting the data files.
Which action should the team take?
Choose one answer.
AA. Drop the affected partitions and add them again, for example with MSCK REPAIR TABLE, so that they take the table's current schema.
BB. Run MSCK REPAIR TABLE on the claims table, which updates the column types of every existing partition to match the table.
CC. Delete the claims table and recreate it with the new column type, then run a crawler to find all of the partitions again.
DD. Rewrite all of the old data files in the new type with a CTAS query, and point the table at the new files.
Show the answer and explanation
Answer: A
A. Right: Correct. Athena stores a schema for the table and for each partition, and the error means the old partitions still carry the int type. Dropping those partitions and re-adding them registers them with the table's schema, and the data files stay where they are. Learn more: Troubleshoot issues in Athena (opens in a new tab)
B. Wrong: MSCK REPAIR TABLE adds partitions that exist in S3 but are missing from the catalog. It does not change the column types of partitions that already exist, so the mismatch remains. Learn more: MSCK REPAIR TABLE (opens in a new tab)
C. Wrong: Recreating the table can work, but it removes the table definition and its settings, and it is more work than the fix needs. The option is not the minimal change the team wants.
D. Wrong: A rewrite changes the data, which the team wants to avoid, and the cost of rewriting a full claims table is high. The option is more work than updating partition metadata.
Explanation
Athena checks each partition's column types against the table schema, and partitions registered before the type change trigger the mismatch error. Dropping and re-adding those partitions, for example with MSCK REPAIR TABLE, gives them the table's schema without touching the data. Running MSCK REPAIR alone does not change partitions that already exist, and rewriting or recreating the table does more than needed.
Want more on Troubleshooting and tuning pipelines? The 5 full-length practice sets have 13 more Troubleshooting and tuning pipelines questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →