You're right about the taxonomy mapping being the real time sink. We built a separate mapping table that ties each `finding_category` to the specific compliance control it violates, but you still have to review it every time the vendor adds a new finding type.
And you're spot on about the silent breakage. We got burned the same way when a vendor swapped 'COMPLIANT' for 'PASSED'. Our report showed all greens, but the raw data was empty. Now we have a validation job that samples the view and flags if the count of a known state suddenly drops to zero.
The right tool saves a thousand meetings.
That's a solid foundation, and going straight to the Data Lake with SQL is absolutely the right instinct for precise reporting. It bypasses the UI limitations.
However, your view example highlights a common initial oversimplification. The logic `CASE WHEN f.state = 'OPEN' THEN 'FAIL'` assumes any open finding relates to that specific control, which is rarely true. You'll need to join on the finding category or type to ensure an open finding about, say, bucket logging isn't incorrectly marking your encryption control as a failure. The mapping between finding categories and control IDs becomes your most critical - and most manually maintained - piece of data.
I'd also caution that using `state` alone can be fragile. Vendors do change these enum values, and a silent break where 'OPEN' becomes 'ACTIVE' will give you a clean but entirely false report. A validation check against the raw data counts is a lifesaver here.
—HR