Listen to this Post
Introduction:
The enterprise security landscape is undergoing a seismic shift. For years, Security Information and Event Management (SIEM) has been synonymous with platforms like Splunk and Microsoft Sentinel. However, a recurring question among security architects is whether modern data platforms—specifically Databricks, Snowflake, and ClickHouse—can replace traditional SIEMs. This article extracts the core debate from industry experts, exploring how organizations are leveraging cloud-native data warehouses to handle petabyte-scale telemetry, reduce costs, and enable advanced analytics that traditional SIEMs struggle to deliver.
Learning Objectives:
- Understand the architectural differences between traditional SIEMs and modern data lakehouse platforms for security analytics.
- Learn how to configure a basic security analytics pipeline using ClickHouse for log ingestion and threat detection.
- Explore query optimization techniques in Snowflake and Databricks for real-time security monitoring and incident response.
You Should Know:
- Building a Cost-Effective Security Analytics Pipeline with ClickHouse
The core argument for moving away from traditional SIEMs is economic and architectural. Traditional SIEMs often charge by ingest volume, leading to skyrocketing costs as log data grows. Platforms like ClickHouse offer a columnar database designed for massive scalability with significantly lower storage costs. Here is a step-by-step guide to setting up a basic log ingestion pipeline using ClickHouse on Linux, mimicking a “SIEM” backend.
Step‑by‑step guide explaining what this does and how to use it:
First, we need to install ClickHouse on an Ubuntu server. This transforms your environment into a high-performance analytics engine capable of handling hundreds of terabytes of security logs.
Linux Commands (Ubuntu/Debian):
1. Install ClickHouse sudo apt-get install -y apt-transport-https ca-certificates dirmngr sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754 echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list sudo apt-get update sudo apt-get install -y clickhouse-server clickhouse-client <ol> <li>Start the service sudo service clickhouse-server start</p></li> <li><p>Create a security logs table clickhouse-client --query "CREATE DATABASE security;" clickhouse-client --query "CREATE TABLE security.firewall_logs ( timestamp DateTime, src_ip String, dest_ip String, action String, bytes_transferred UInt64 ) ENGINE = MergeTree() ORDER BY (timestamp, src_ip);"
What this does: This sets up a high-performance columnar database. Unlike Splunk’s inverted index, ClickHouse compresses data heavily and allows for blazing-fast aggregations. To ingest data, you can stream JSON logs directly:
Simulating log ingestion from a file cat firewall.log | clickhouse-client --query="INSERT INTO security.firewall_logs FORMAT JSONEachRow"
This command reads a log file where each line is a JSON object and inserts it into the table. For a production “SIEM,” you would use a streaming engine like Kafka to feed this, bypassing the per-GB licensing costs of traditional vendors.
2. Integrating Snowflake for Long-Term Security Data Retention
Enterprises are using Snowflake not just as a data warehouse but as a “data lake” for security telemetry. The strategy is to use a lightweight SIEM for the “hot” data (last 30 days) and offload everything else to Snowflake for compliance, forensics, and threat hunting. This allows security teams to run complex SQL queries across years of data without keeping expensive SIEM licenses active.
Step‑by‑step guide explaining what this does and how to use it:
To integrate Snowflake into your security workflow, you typically use a tool like Splunk’s DB Connect or a custom Python script to federate queries. However, a modern approach is to use Snowflake’s native capabilities to analyze Parquet files stored in cloud blob storage (AWS S3, Azure Blob).
SQL Commands (Snowflake):
-- 1. Create an external stage pointing to S3 where your logs are stored CREATE OR REPLACE STAGE security_logs_stage URL = 's3://your-security-bucket/logs/' STORAGE_INTEGRATION = aws_integration; -- 2. Create a file format for Parquet logs CREATE OR REPLACE FILE FORMAT parquet_format TYPE = 'PARQUET'; -- 3. Query the logs directly from the stage without loading (External Table concept) SELECT $1:timestamp::timestamp as event_time, $1:source_ip::string as source_ip, $1:event_type::string as event_type FROM @security_logs_stage (FILE_FORMAT => 'parquet_format') WHERE event_time > DATEADD(day, -7, CURRENT_TIMESTAMP()) AND event_type = 'Suspicious Activity';
What this does: This setup allows security analysts to run SQL queries on cold storage data without moving it into Snowflake (though you can load it if needed). This is a fundamental shift from traditional SIEM architecture, turning security analytics into a data engineering problem rather than a licensing problem.
3. Detection Engineering with Databricks and PySpark
Databricks combines the best of data lakes (Delta Lake) and AI. For security teams, this means they can apply machine learning to detect anomalies—such as beaconing or credential stuffing—at a scale impossible in standard SIEMs. Instead of writing SPL (Splunk Processing Language), detection engineers use PySpark.
Step‑by‑step guide explaining what this does and how to use it:
We will simulate a simple anomaly detection script in a Databricks notebook to identify potential data exfiltration based on unusual outbound traffic volume from a single host.
Python / PySpark Code (Databricks Notebook):
Load Delta table containing firewall logs
df = spark.read.table("security.firewall_logs")
Aggregate traffic by source IP and destination IP over 5-minute windows
from pyspark.sql.functions import col, window, sum, count, stddev
windowed_traffic = df \
.groupBy(window(col("timestamp"), "5 minutes"), col("src_ip"), col("dest_ip")) \
.agg(sum("bytes_transferred").alias("total_bytes"), count("").alias("connection_count"))
Calculate standard deviation to find anomalies
stats = windowed_traffic.select("src_ip", "total_bytes").summary("stddev").collect()
threshold = float(stats[bash][1]) 3 3 standard deviations
Identify outliers
anomalies = windowed_traffic.filter(col("total_bytes") > threshold)
Write to a Delta table for alerting
anomalies.write.mode("append").saveAsTable("security.alerts")
What this does: This script performs statistical anomaly detection. While a traditional SIEM might require a costly machine learning toolkit add-on, Databricks provides these capabilities natively. The output `security.alerts` table can be connected to a visualization tool or a SOAR platform for automated response.
4. Optimizing Queries for Real-Time Detection
Moving to a data platform requires a shift in how analysts hunt for threats. SQL becomes the primary language. Optimizing these SQL queries is critical for performance to ensure that dashboards and alerts fire in near real-time.
Step‑by‑step guide explaining what this does and how to use it:
Let’s look at a performance comparison for a common detection scenario: finding successful logins from impossible travel locations (e.g., a login from the US and Japan within 1 hour).
Inefficient SQL (Snowflake/Databricks):
SELECT a.user_id, a.login_time as login_us, b.login_time as login_jp FROM auth_logs a JOIN auth_logs b ON a.user_id = b.user_id WHERE a.country = 'US' AND b.country = 'JP' AND ABS(DATEDIFF(minute, a.login_time, b.login_time)) < 60;
Optimized SQL using Windowing Functions:
WITH ranked_logins AS (
SELECT
user_id,
login_time,
country,
LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time) as prev_login,
LAG(country) OVER (PARTITION BY user_id ORDER BY login_time) as prev_country
FROM auth_logs
WHERE country IN ('US', 'JP')
)
SELECT user_id, prev_login, login_time, prev_country, country
FROM ranked_logins
WHERE prev_country != country
AND DATEDIFF(minute, prev_login, login_time) < 60;
What this does: The optimized query uses window functions to compare sequential logins for each user, scanning the table once. The inefficient query performs a self-join, which creates a Cartesian product before filtering, leading to massive resource consumption and slow alerting times. This SQL proficiency is now a core skill for detection engineers.
5. Security Caveat: Visibility vs. Control
While data platforms excel at analytics, they lack the native security controls of a dedicated SIEM. In a SIEM, you have role-based access control (RBAC) at the index level, data masking, and compliance auditing built-in. When using Databricks or Snowflake as a SIEM, you must manually implement “Security as Code.”
Step‑by‑step guide explaining what this does and how to use it:
In Snowflake, you can implement dynamic data masking to ensure that analysts can see network traffic patterns but not the raw payloads or personally identifiable information (PII) contained within them.
SQL Commands (Snowflake Security):
-- Create a masking policy
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string ->
CASE
WHEN CURRENT_ROLE() IN ('SECURITY_ADMIN', 'SOC_LEAD') THEN val
ELSE REGEXP_REPLACE(val, '.+@', '@')
END;
-- Apply the policy to the email column
ALTER TABLE security.auth_logs MODIFY COLUMN email SET MASKING POLICY email_mask;
What this does: This ensures that if a junior analyst queries the `auth_logs` table, they see `@company.com` instead of [email protected]. This level of granular security is essential for compliance (GDPR, HIPAA) and must be coded into the platform, as it is not enabled by default like it is in Splunk Enterprise Security or Sentinel.
What Undercode Say:
- Cost Optimization: The move to data platforms is driven by the unsustainable cost models of traditional SIEMs. Organizations are realizing that log retention for compliance does not require a high-cost SIEM license when a cloud data warehouse costs pennies per terabyte.
- Skill Shift: The future of detection engineering lies in SQL, Python (PySpark), and data engineering, not proprietary query languages like SPL. Security teams must upskill or risk being locked into legacy ecosystems.
- Architecture Consolidation: This trend signals a broader consolidation of the security stack into the corporate data platform. Security is no longer a siloed “logs” problem but a core “data” problem, enabling better correlation with business context.
Prediction:
Within the next three years, the distinction between a “SIEM” and a “Data Platform” will blur entirely. Major cloud providers will offer native security analytics modules that sit directly on top of their data warehouses (e.g., Microsoft Sentinel on Fabric, Google SecOps on BigQuery), forcing legacy SIEM vendors to either fully embrace a data lakehouse architecture or face irrelevance in the enterprise market. The battle will shift from “how much data can you ingest” to “how fast can you run AI on that data to stop a breach in real-time.”
▶️ Related Video (72% Match):
🎯Let’s Practice For Free:
IT/Security Reporter URL:
Reported By: Inode Siem – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
