SIEM is legacy - building a threat hunting app on a Security Data Lake

SIEM has been the backbone of security operations for 20 years. It was built for a different era, when log volumes were manageable, detection meant static rules, and “AI” wasn’t part of the conversation.

I’ve spent the last few years migrating security operations away from legacy SIEM toward a Databricks-based Security Data Lake. The difference isn’t just architectural. It changes what’s possible.

Static rules become ML models. Correlation across siloed sources becomes a single SQL query. Retention that used to cost a fortune becomes cheap Delta storage. And threat hunting, which in SIEM means fighting the query language, becomes asking a question in plain English.

There’s one more thing nobody talks about enough: in a legacy SIEM you write detection logic in whatever proprietary language the vendor decided to give you. You wait for the next release to get a feature you need. You celebrate when they ship a new dashboard as if it’s a gift.

On a Data Lake you write Python. You write Spark. You use any library that exists. You build exactly what your team needs, not what the vendor decided to package.

To make that concrete, I built a threat hunting app running natively on Databricks Apps.

Architecture

#

Before any code, the decisions that matter at scale.

flowchart TB
    subgraph Sources["Security Telemetry"]
        EDR["EDR"]
        NET["Network"]
        WIN["Windows"]
        DNS["DNS"]
        DLP["DLP"]
        PROXY["Proxy"]
    end

    subgraph Lakehouse["Databricks Lakehouse"]
        Delta["Delta Tables\n(one per source)"]
        UC["Unity Catalog\nRBAC + Row/Column Filters"]
        WH["Serverless SQL\nWarehouse"]
    end

    subgraph App["Databricks App"]
        UI["Streamlit UI"]
        LLM["Foundation Model\n(natural language → SQL)"]
        HB["Hunt Board\n(Delta-backed)"]
    end

    Sources --> Delta
    UC --> Delta
    WH --> Delta
    UI --> WH
    UI --> LLM
    LLM --> WH
    UI --> HB

    style LLM fill:#6366f1,color:#fff
    style Delta fill:#1b7c3d,color:#fff
    style WH fill:#1b7c3d,color:#fff

Data layer: Delta tables as the single source of truth. All security telemetry lands in Delta. No copies, no separate indexes per tool. One table per source type, governed by Unity Catalog. Every query in the hunting app runs on the same data your detection pipelines and dashboards use. No sync lag, no version drift.

Access model: roles mapped to query scope. Unity Catalog row and column filters enforce what each role can see. An L1 analyst and a threat hunter query the same tables but see different data. The app doesn’t manage permissions, Delta does. If you manage access in the app layer, you’ll eventually have a gap.

Compute: serverless SQL warehouse. Hunting queries are unpredictable in size and frequency. Serverless scales to zero between sessions and handles concurrent hunters without sizing decisions.

AI layer: natural language to SQL. The hunter describes what they’re looking for, the model generates SQL. The generated SQL is always shown to the hunter before execution because transparency matters in security workflows. More on the implementation below.

Frontend: Streamlit on Databricks Apps. Three files: app.py, app.yaml, requirements.txt. No build step, no infrastructure. Config() without arguments handles auth automatically through the Apps OAuth layer. The app inherits workspace permissions, so there’s no separate access control to maintain.

Connection: zero credentials in code

#

The entire auth story is four lines:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

from databricks.sdk.core import Config
from databricks import sql

cfg = Config()  # auto-configured in Databricks Apps

@st.cache_resource
def get_conn():
    return sql.connect(
        server_hostname=cfg.host,
        http_path=HTTP_PATH,
        credentials_provider=lambda: cfg.authenticate,
    )

Config() without arguments picks up the app’s service principal through the Apps OAuth layer. No client IDs, no secrets in environment variables, no token management. The connection is cached per session via @st.cache_resource, so you get one persistent connection per user, not one per query.

Every SQL call goes through a single helper:

1
2
3
4
5

def run_sql(query: str) -> pd.DataFrame:
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute(query)
        return cur.fetchall_arrow().to_pandas()

fetchall_arrow() instead of fetchall() because Arrow columnar format is significantly faster for large result sets. The connector deserializes directly into a Pandas DataFrame without row-by-row conversion.

Hypothesis to SQL: the AI layer

#

This is the part that changes the workflow. A threat hunter shouldn’t need to remember column names or write JOINs from memory. They should describe what they’re looking for.

The key is grounding. An LLM without schema context will hallucinate table and column names. The fix is simple: inject the actual schema into the system prompt at query time via DESCRIBE TABLE.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

def hypothesis_to_sql(hypothesis: str, table: str) -> str:
    schema_df = run_sql(f"DESCRIBE TABLE {table}")
    schema_str = "\n".join(
        f"  {r['col_name']} {r['data_type']}"
        for _, r in schema_df.iterrows()
        if not str(r.get("col_name", "")).startswith("#")
    )

    w = WorkspaceClient()
    resp = w.serving_endpoints.query(
        name=LLM_ENDPOINT,
        messages=[
            ChatMessage(
                role=ChatMessageRole.SYSTEM,
                content=f"""You are a senior threat hunter and Databricks SQL expert.
Convert the user's hypothesis into a valid Databricks SQL query.
Target table: {table}
Schema:
{schema_str}
Return ONLY the SQL query. No markdown, no explanation, no backticks."""
            ),
            ChatMessage(role=ChatMessageRole.USER, content=hypothesis),
        ],
        max_tokens=600,
    )
    return resp.choices[0].message.content.strip()

A few things to note:

• DESCRIBE TABLE runs live against Unity Catalog. If a column is added or renamed, the next hypothesis picks it up automatically. No hardcoded schemas to maintain.
• The schema filter (startswith("#")) strips Spark metadata rows from the DESCRIBE output. Those are partition info and table properties, not columns the LLM should reference.
• ChatMessage with typed roles from databricks.sdk.service.serving. The SDK’s native types, not raw dicts.
• max_tokens=600 because SQL queries don’t need 2048 tokens. Keeping it tight reduces latency and prevents the model from generating explanatory text after the query.
• The model endpoint (databricks-meta-llama-3-3-70b-instruct) is a Foundation Model. No deployment needed, pay-per-token, available in every workspace.

The generated SQL is never executed blindly. It’s displayed in an editable text area. The hunter reviews it, optionally modifies, and clicks “Run query” only when they’re satisfied:

1
2
3
4
5
6
7
8

st.session_state["sql"] = st.text_area(
    "sql_edit",
    value=st.session_state["sql"],
    height=200,
    label_visibility="collapsed",
)
if st.button("▶ Run query", type="primary"):
    df = run_sql(st.session_state["sql"])

In security, auto-executing AI-generated queries against your full telemetry is not acceptable. The human stays in the loop.

When the LLM endpoint is unavailable (offline workspace, rate limit, testing), the app falls back to a demo query, a CTE-based baseline comparison that demonstrates the pattern without requiring a live model.

Three views

#

Hunt Workspace

#

The hunter types a hypothesis in plain English. The app translates it to SQL, runs it against Delta, returns results. One-click pivot from any result row to the full entity timeline.

The screenshot shows a real hypothesis: “Hosts that established outbound connections to external IPs between 02:00-05:00 UTC and had no prior history of external communication in the last 30 days.” The model generated a CTE-based query with a baseline comparison, exactly what a hunter would write manually, but in seconds instead of minutes.

Results include a risk column and a one-click “Timeline” pivot for each entity. 8 rows matched, execution took 2.3 seconds across ~847MB of data.

The pivot is simple session state manipulation. Clicking a host name writes it to st.session_state and triggers a rerun that opens the Entity Timeline pre-filled:

1
2
3
4

if st.button(f"→ {row['src_host']}", key=f"pivot_{i}"):
    st.session_state["timeline_entity"] = row["src_host"]
    st.session_state["timeline_type"]   = "host"
    st.rerun()

Results can be exported to CSV or saved directly to the Hunt Board with one click.

Entity Timeline

#

Click any entity (host, user, IP, process) and get its complete event timeline across all sources in one view. Events grouped by day, filterable by source type. The kind of pivot that takes 20 minutes in a SIEM takes seconds here.

The timeline for fin-ws-0471 tells a clear story: normal VPN logon on March 8, then on March 10 an unusual-hour logon, a suspicious DNS resolution, an encoded PowerShell execution, and finally an outbound connection to 185.220.101.47:4444, a known Tor exit node. Six different data sources (network, EDR, Windows, DNS, DLP, proxy), one coherent view. This is what a Lakehouse makes trivial and a SIEM makes painful.

Events are rendered with source icons and risk-colored badges, grouped by day with collapsible sections:

1
2
3
4
5
6
7
8
9

RISK_ICON = {"HIGH": "🔴", "MED": "🟡", "LOW": "🔵", "OK": "⚪"}
SRC_ICON  = {"network": "🌐", "edr": "🛡️", "windows": "🖥️",
             "dns": "📡", "dlp": "📁", "proxy": "🔀"}

df["_day"] = df["event_time"].astype(str).str[:10]
for day, group in df.groupby("_day", sort=False):
    with st.expander(f"📅 {day} - {len(group)} events", expanded=(day == df["_day"].iloc[0])):
        for _, row in group.iterrows():
            # render time | source icon | event details | risk badge

The source filter uses st.pills for quick toggling between All, network, EDR, Windows, DNS, DLP, proxy. One click narrows the view to a single telemetry source.

All data sources live in the same Lakehouse. No cross-system API calls, no data stitching, no waiting for index rebuilds. One query, all sources, sub-second response.

Hunt Board

#

Hypothesis → In Progress → Confirmed → Dismissed. Each hunt is persisted in a Delta table. Full history auditable and queryable, because in a regulated environment, “we investigated this” needs to be provable.

The board state is backed by Delta with MERGE INTO, so every status change is atomic and versioned:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

def save_hunt(hunt: dict):
    run_sql(f"""
        MERGE INTO {HUNTS_TABLE} AS t
        USING (SELECT '{hunt["id"]}' AS id) AS s ON t.id = s.id
        WHEN MATCHED THEN UPDATE SET
            title   = '{hunt["title"]}',
            status  = '{hunt["status"]}',
            analyst = '{hunt.get("analyst", "")}',
            tags    = '{json.dumps(hunt.get("tags", []))}',
            note    = '{hunt.get("note", "")}',
            updated = '{datetime.now().strftime("%Y-%m-%d")}',
            progress = {hunt.get("progress", 0)}
        WHEN NOT MATCHED THEN INSERT
            (id, title, status, analyst, tags, note, updated, progress)
        VALUES (...)
    """)

Status transitions are two buttons per card: “Start” / “Confirm” to advance, “Dismiss” to close. Each transition calls save_hunt() with the new status and triggers st.rerun(). No separate state management. Delta is the state.

Because it’s Delta, you get time travel for free. “What was the state of all hunts on March 10?” is a single query:

1
2
3

SELECT * FROM security.hunts.board
TIMESTAMP AS OF '2026-03-10'
WHERE status = 'in_progress'

Try doing that in Jira.

Demo mode

#

The app is designed to work without a live Databricks environment. When WAREHOUSE_ID is not set, every component falls back gracefully:

• Hunt Workspace: the LLM fallback returns a realistic CTE-based demo query with synthetic results
• Entity Timeline: shows the fin-ws-0471 attack chain with hardcoded events across six source types
• Hunt Board: loads sample hunts covering lateral movement, DNS tunneling, privilege escalation, and confirmed IOCs

This matters for two reasons: you can show the concept to stakeholders without provisioning a warehouse, and you can develop the UI locally without network access.

1
2
3
4
5

@st.cache_resource
def get_conn():
    if not WAREHOUSE_ID:
        return None  # triggers demo mode throughout the app
    return sql.connect(...)

Deploy

#

The whole thing is three files:

app.py            # the entire application
app.yaml          # Databricks Apps config
requirements.txt  # databricks-sdk, databricks-sql-connector, streamlit, pandas

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# app.yaml
command:
  - streamlit
  - run
  - app.py
env:
  - name: WAREHOUSE_ID
    value: "your_warehouse_id"
  - name: LLM_ENDPOINT
    value: "databricks-meta-llama-3-3-70b-instruct"
  - name: EVENTS_TABLE
    value: "security.events.network_connections"
  - name: HUNTS_TABLE
    value: "security.hunts.board"

Upload to your Databricks workspace, create an App, point it at the folder, start. Config() handles auth. Unity Catalog handles access. The hunts table auto-creates on first use via CREATE TABLE IF NOT EXISTS ... USING DELTA.

What this is not

#

This is a PoC, not a finished product. The hunt board doesn’t have drag-and-drop in the Streamlit version. The LLM prompt will produce wrong SQL on sufficiently unusual schemas. There’s no user identity beyond what Databricks Apps passes through.

The production version of this is a different conversation, one I’m not ready to have publicly yet.

What it does show: when you stop treating the Security Data Lake as a cheaper SIEM and start treating it as a different category of tool, the ceiling moves significantly.

Links:

• databricks-threathunt - full source code (Apache 2.0)
• Databricks Apps documentation
• Foundation Model APIs
• Unity Catalog row and column filters
• Delta Lake time travel

If you found this useful, let’s connect: linkedin.com/in/mariusz-derela-30649a69