Walkthrough: Creating custom compliance reports for our auditors.

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.

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.

Oh, the silent break scenario you mentioned is terrifying. So you could hand over a perfect-looking report that's completely wrong because the vendor changed a single word in their data schema? That seems like a huge risk.

How often do you actually run that validation check against the raw data? Is it something you do right before generating the report, or is it automated on a schedule?

So if you're skipping the UI and just using SQL anyway, why are you paying for Orca's fancy dashboard? The reports are the main thing. You're basically paying to extract your own data into a warehouse to build the reports they should provide.

You're on the right track, but that view is missing a critical WHERE clause to filter for the specific finding. It'll mark the control as FAIL for *any* open finding on that asset, even ones totally unrelated to encryption. You need to tie it to `finding_category` or `finding_type`. It should look more like this:

```sql
WHERE f.finding_category = 'aws_s3_bucket_encryption_disabled'
```

Without that, your first audit will be a mess of false positives and you'll spend the next week explaining why a public bucket finding failed your encryption control. The data's there, but you have to be surgical with your filters.

Exactly, that WHERE clause is the linchpin. You've hit on the core problem, which is mapping the vendor's own finding categories to your specific compliance controls.

A common tripping point here is that `finding_category` might be too granular for a control, while `finding_type` might be too broad. You might need to map multiple, slightly different categories (like 'aws_s3_bucket_encryption_disabled' and 'aws_s3_bucket_default_encryption_missing') to a single control for "Data at Rest Encryption".

That mapping table becomes your single source of truth, and it's a manual, living document. Forget to add a new category the vendor introduced last month, and suddenly you're reporting a false pass.

The mapping table is indeed the painful heart of it. We treat ours like any other source code dependency - it's version-controlled and part of our deployment pipeline. Every time we pull fresh vendor data, a job diffs the incoming `finding_category` list against our mapping table's known keys. New categories trigger a ticket for the security team to classify.

It still requires manual review, but at least the breakage is loud and happens in staging, not silently in the final report.

>Orca will rename a column or change an enum value and your view goes red. Ask me how I know.

Oh I know. Woke up to a pager alert at 3 AM once because `risk_score` became `severity_level`. No deprecation warning, no version bump. The dashboard was just... empty.

The real fun starts when they change the *meaning* behind a column but keep the name. Like when `status: OPEN` suddenly included "remediated but awaiting confirmation" entries. That one didn't break the view, just made the report useless.

So yeah, you're not just building a view. You're signing up for a monitoring job on their schema. Gotta parse their changelog like it's scripture.

>Ask me how I know.

You're lucky it just broke. Our view kept chugging on a null column and just returned zero results for a quarter. We passed our audit because the report was clean. Found out six months later. Good times.

Your foundational SQL approach is correct, but your conditional logic `WHEN f.state = 'OPEN'` is a critical oversimplification. You must first join your findings to a mapping table that links `finding_category` to your control ID. An open finding for 'aws_s3_bucket_public_access' should not fail 'CIS AWS 2.1.1'. Your current view would produce false positives for any open finding on the asset, not just findings relevant to encryption.

the `state` field itself is an unstable dependency. Vendors have been known to change these enumerated values without schema-level deprecation, or, more insidiously, alter their semantic meaning while keeping the label. Relying on it directly introduces operational risk. A more defensible pattern is to derive a status from a combination of immutable or versioned fields, like `first_detected_at` and `remediated_at`, and to monitor the distinct values in the `state` column as part of your data quality checks.

The timestamp tip is clutch for audit trails. We learned the hard way that CURRENT_DATE() can cause trouble if your reporting job runs past midnight though. Better to bake in a static snapshot timestamp from when the ETL job started.

On materializing tables, the refresh process is the whole ball game. We use Airflow to rebuild ours after each vendor sync, but you need a fallback strategy for when that process fails. Keeping the previous day's table as a backup version has saved us more than once.

That's a really good point about the state field being an unstable dependency. It never occurred to me that a vendor might change what 'OPEN' actually means without changing the column name.

You mentioned deriving status from `first_detected_at` and `remediated_at`. How do you handle the in-between states, like something being 'in progress'? Is that just a manual flag you manage separately from their raw data?

So you're saying the main tables to work with are `public.assets` and `public.findings`. That's a clear starting point I can follow. I've never used a Data Lake setup, though. Is the daily export to Snowflake something you configured entirely within Orca, or did you need another tool to handle that transfer? I'm trying to picture the first step before any SQL happens.

Your example view is incomplete, it cuts off after the CASE statement. That's the problem with these snippets.

But even if it were finished, your CASE logic is wrong. f.state = 'OPEN' is not enough to map to a control failure. You'd have dozens of irrelevant open findings failing a single encryption control.

The real work is in that mapping table you glossed over. Show us your mapping table structure, or this is just a toy example.

Discounts on the Data Lake export are rare because it's a premium feature designed to solve the reporting problems they create. You're not just paying twice, you're also assuming the operational cost of maintaining the pipeline they should provide.

The real negotiation point isn't the fee, it's the total cost of ownership. Frame it around the engineering hours spent on schema monitoring and pipeline breaks, like the examples in this thread. That operational burden is the actual premium.