Writing Detection Rules That Actually Work Across Eight Databases

One of the hardest engineering problems we have tackled at Vigilense AI is detection portability. When your security data lives in OpenSearch, Snowflake, BigQuery, Databricks, Amazon Redshift, S3+Athena, PostgreSQL, MongoDB, ClickHouse, and TimescaleDB all at once, writing a detection rule that works everywhere is not a matter of clever string interpolation. It is an architecture problem.

This post walks through the technical challenge, the abstraction approach we built, and the performance tradeoffs we had to navigate.

The Problem: Query Languages Are Not Interchangeable

Let me state the obvious thing that is easy to underestimate: Splunk SPL does not translate to Snowflake SQL. Elastic KQL is not BigQuery. Every database has its own query language, its own performance characteristics, its own way of handling time-series data, and its own quirks around null values, type coercion, and aggregation semantics.

A detection rule that checks for "more than 5 failed login attempts from the same IP within 10 minutes" sounds simple. But the implementation varies wildly:

• In OpenSearch, you are writing a DSL query with date histograms and bucket aggregations.
• In Snowflake, you are writing SQL with window functions and QUALIFY clauses.
• In BigQuery, you are writing Standard SQL with TIMESTAMP_DIFF and nested subqueries.
• In MongoDB, you are building an aggregation pipeline with $match, $group, and $bucket stages.
• In S3+Athena, you are writing Presto SQL against partitioned Parquet files with completely different performance characteristics.

If you ask a security engineer to maintain the same detection logic across all of these, you are asking them to be an expert in eight different query languages simultaneously. That does not scale.

The Abstraction Layer: One Rule, Eight Backends

Our approach is what we call the Unified Detection Engine. The core idea: security engineers write detection logic once in an abstract format, and the system translates that logic into each database's native query language at execution time.

This is not a lowest-common-denominator approach. We do not limit detection logic to what all backends support equally. Instead, we built a translation layer that understands the strengths and idioms of each backend and generates optimized native queries.

Query Federation

When a detection rule fires, the query federation layer handles four things:

1. Native query translation: The abstract rule is compiled into the target database's native format, whether that is SQL, DSL, MQL, or something else entirely. Each translation target has its own optimizer that generates idiomatic queries, not generic ones.
2. Query plan optimization: Before execution, the system analyzes the target database's schema, indexes, and partitioning strategy to generate an efficient query plan. A naive translation might work, but a naive translation against a 100TB Snowflake warehouse will cost you real money in compute credits.
3. Parallel query execution: If a detection rule needs to correlate data across multiple backends (for example, matching firewall logs in OpenSearch against identity events in Snowflake), the queries execute in parallel and the results are joined in memory.
4. Result streaming for large datasets: For rules that scan large volumes of historical data, results stream back incrementally rather than buffering the entire result set. This keeps memory usage predictable and allows the detection engine to start processing matches before the full scan completes.

Schema Discovery and Field Normalization

Detection portability breaks down fast if every backend uses different field names for the same concept. One database stores the source IP in src_ip, another in source.address, another in srcAddr.

Our schema discovery system handles this automatically. When you connect a new data source, the system maps the data schema and normalizes field names to a common taxonomy. This means a detection rule that references source_ip will work regardless of whether the underlying database calls it src_ip, source.address, or ClientIP.

Field mappings can be customized. If your organization uses non-standard field names or has legacy schemas that do not conform to common formats like ECS or OCSF, you can define custom mappings that persist across all detection rules.

How Schema Discovery Works in Practice

When a new database connection is established, the discovery process runs in three phases:

1. Structure scan: The system introspects the database schema to understand tables, columns, data types, and relationships.
2. Sample analysis: A small sample of actual data is analyzed to understand the semantic meaning of each field. A column named ts might be a timestamp, a string, or an integer depending on the source.
3. Mapping generation: The system proposes field mappings to the normalized taxonomy and flags any ambiguities for human review.

This process runs in seconds for most backends. The result is a mapping layer that detection rules can rely on without caring about the underlying schema.

Cross-Backend Signal Correlation

The real power of the Unified Detection Engine is not just running the same rule on different backends. It is correlating signals across all your security data regardless of where it lives.

Consider a detection scenario: an attacker uses stolen credentials (identity data in Snowflake) to access a VPN (network logs in OpenSearch), then moves laterally to a database server (cloud audit logs in BigQuery), and exfiltrates data (DLP events in S3+Athena).

No single backend has the full picture. The detection engine queries each backend in parallel, correlates the results by entity (user, IP, hostname), and constructs a unified timeline of the attack. One detection rule captures the entire kill chain across four different data stores.

Performance: The Numbers

Performance varies significantly by backend, data volume, and query complexity. Here are the benchmarks we track internally:

• OpenSearch/Elasticsearch: Sub-500ms query latency at 10TB+ data volumes. This is the fastest backend for real-time detection because the data is pre-indexed and optimized for search.
• Snowflake: Under 2 seconds at 100TB+ data volumes. The warehouse auto-scaling handles burst query loads well, but cold warehouse spin-up adds latency on the first query of a session.
• S3+Athena: Under 5 seconds at 1PB+ data volumes. This is the cost-optimized tier for historical analysis and retroactive threat hunting. Latency is higher because Athena scans Parquet files on read, but the cost per query is a fraction of what you would pay to index the same data.

The tradeoff is explicit: faster backends cost more to operate, slower backends cost less. Our query planner takes this into account and routes queries to the appropriate backend based on the detection rule's latency requirements.

Query Plan Optimization Details

Naive query translation would work for small datasets but falls apart at scale. A few examples of optimizations the query planner applies:

• Partition pruning: For time-series data in S3+Athena or BigQuery, the planner adds partition filters to avoid scanning data outside the detection window.
• Index selection: For OpenSearch, the planner routes queries to the most selective index based on the fields referenced in the detection rule.
• Aggregation pushdown: Where possible, aggregations are pushed down to the database engine rather than computed in the detection layer. This reduces data transfer and leverages each backend's native optimization.
• Cost-based routing: For rules that could run on multiple backends (if the same data exists in both OpenSearch and Snowflake), the planner estimates the cost and latency of each option and routes accordingly.

What This Means for Security Teams

The practical implication is straightforward: your detection engineers write rules once. They do not need to know Snowflake SQL or OpenSearch DSL or MongoDB aggregation pipelines. They write detection logic in an abstract format, and the engine handles the translation, optimization, and execution.

This also means your detection coverage is not limited by where your data lives. If you add a new database to your architecture, existing detection rules automatically start covering the new data source once the schema mapping is in place. No rewriting. No porting. No regression testing against a new query language.

We built this because the alternative is untenable. Security teams should not spend their time translating queries between databases. They should spend it writing better detection logic and hunting threats.

If you want to see the detection abstraction layer in action across your own data sources, reach out for a technical deep dive.