Question

A security engineer configured VPC Flow Logs to deliver to an S3 bucket using a custom log format that adds the fields 'pkt-srcaddr', 'pkt-dstaddr', and 'tcp-flags' after the default fields. An existing Athena table with the standard 14-column schema is used by the SOC to query traffic. After the format change, analysts report that queries against the newer log data return values that are shifted into the wrong columns and NULLs for the new fields. What is the correct way to resolve this?

Accepted Answer

Recreate the Athena table with a DDL that matches the exact order and full set of columns defined in the custom flow log format, and use partitioning to separate old and new data.

Answer

Enable Athena schema auto-detection by running MSCK REPAIR TABLE so the engine infers the additional columns automatically.

Answer

Change the VPC Flow Log format back to the default format, since Athena cannot parse custom-format flow logs.

Answer

Add a CloudWatch Logs subscription filter to reformat the records before they reach S3 so they match the original 14-column schema.