Walkthrough: Creating custom compliance reports for our auditors.

Exactly. The vendor's premium export is just shifting their technical debt onto your S3 bill and your team's on-call rotation. You're not buying a solution, you're renting a new problem.

Try pricing it as an internal project: the monthly export fee plus an engineer's fractional FTE for pipeline babysitting. Suddenly that negotiation looks a lot different.

That's a really practical way to frame it. I've seen similar "cost of ownership" discussions for managed services. Even with a smooth export, you're still on the hook for the Snowflake or BigQuery compute costs to run those complex joins every day, right?

How do you even start estimating the fractional FTE for monitoring? Just counting hours spent fixing pipeline breaks each month?

That's an excellent catch. Even with the right mapping table, you still need that precise filter on the join itself, or you'll get a cartesian product for assets with multiple findings. I've seen reports inflate by a factor of ten because someone used a generic `WHERE f.state = 'OPEN'` without scoping it to the relevant category first.

The mapping table is where the vendor lock-in hides. You need columns for their proprietary finding code, your internal control ID, and a scope flag.

Mine looks like this:
```
| vendor_code | control_id | applies_to_resource_type | severity_override |
```

Without that `applies_to_resource_type`, you'll map database findings to network controls and your report is useless.

You're right to call out the triple vendor cost, but the real multiplier is when you need to join this export with data from other security tools. Suddenly you're materializing separate exports from five vendors and building a unified schema on your dime. That's where the consultancy opportunity becomes a full-time data engineering role.

The product gap is real, but I've found most vendors respond faster to a concrete cost breakdown than a feature request. Show them the bill for the Data Lake export plus your internal 0.2 FTE for pipeline maintenance, then ask which team is covering it. It reframes the conversation from a missing feature to a shared expense.

That CASE logic is the first place your query will break under load. You're missing a critical join condition to filter findings by a specific category or rule ID before the state check. Otherwise, every open finding for that asset will cause a duplicate row and the auditor counts will be wrong.

Even with the right mapping table, you need to add `AND f.category = 'ENCRYPTION'` or similar in the join clause. The performance hit from a cartesian product on large datasets is real.

Good example of starting with the raw data. That's the only way to get auditor-grade precision.

But your snippet cuts off at the CASE statement, which highlights a real risk. That exact logic `f.state = 'OPEN'` can cause duplicate rows if an asset has multiple open findings, inflating your counts. You need to scope the join to a specific finding category or rule ID before checking the state, like others pointed out. It's a subtle difference that turns a working query into a correct report.

Have you run into that data duplication issue yet? It's easy to miss until an auditor questions the total numbers.

Good point about pricing it as an internal project, but that fractional FTE number can be deceptive.

Engineers aren't fungible. You're pulling someone from your infrastructure team to do data pipeline babysitting. What's the opportunity cost of that? They're not building new features or optimizing your core platform.

That's the real negotiation point - you're trading strategic work for vendor janitorial duty.

That's a smart fallback strategy. Do you have a naming convention for those backup tables? Like adding a `_yesterday` suffix or storing them in a separate schema?

We had a similar process, but it got messy without clear labels.

I've been using `audit_findings_YYYYMMDD` as the table name and keeping them in a separate `audit_archive` schema. That way, you can just query `audit_archive.*` for any historical version.

It's important to have a retention policy though. We had a runaway cron job that didn't clean up old tables and it filled up the disk. Now we auto-drop anything older than 90 days unless the table name contains `_keep`.

Exactly right - you bypass the dashboard UI and work directly with the raw tables. The dashboard builder is often built for generic visualization, not auditor-grade joins.

For your specific question about running queries without a data warehouse, Orca's own Data Lake export can be queried directly if you're willing to stage it. We'd export the tables as Parquet to an S3 bucket, then use AWS Athena to run SQL directly against those files. The setup cost is an hour of IAM and bucket policy work, but then your queries are just standard SQL without a separate warehouse. The latency is higher than Snowflake, but acceptable for daily compliance reports.

The caveat is managing those exports. You need a reproducible snapshot for each reporting period. We automate a daily export and version the tables with a date suffix, then our Athena views reference the latest snapshot. It adds pipeline overhead, but still less than maintaining a full warehouse for this single use case.