dbt for security data.

The pattern I see often enough that it qualifies as a category: a detection engineering team maintains some number of SQL files that transform raw log sources into a normalized schema. Eighty files is roughly typical. Each file is a few hundred lines, covers one log source (CloudTrail, Azure AD, Windows Security events, VPC Flow, EDR telemetry), and produces a table the detection rules query against. The files live in a shared drive or a Git repo that nobody runs CI on.

The schema is OCSF, or some internal pre-OCSF normalization, or both. When OCSF v1.3 ships and the team needs to migrate, the work is exactly what you'd expect: you open eighty files, find the field mappings that changed, edit each one, and hope you got them all, then you test by running the downstream detection rules and watching for the ones that go silently to zero, and you discover the missed field three weeks later during a postmortem.

The data engineering team next door doesn't work this way. They've been using dbt for years. When the warehouse schema changes, they update one shared piece of SQL (dbt calls it a macro), run the test suite, watch the build pass or fail in CI, and ship. The discipline isn't novel, because it's the same version control, modular code, and automated testing that any working software team uses, and dbt is mostly packaging it for SQL.

Detection engineers have rebuilt this pattern badly several times. LinkedIn's Moonbase platform (their internally-built CI/CD for security detections) is the most public example, and the engineering-blog write-up reads as parallel to dbt almost line-for-line, just with custom tooling. dbt is the off-the-shelf version of the same idea, and the cost to adopt is a Python install plus a few hours of learning curve, not a year of platform engineering.

The single biggest reason dbt makes sense for security data is the incremental materialization. CloudTrail at a 10,000-employee enterprise produces somewhere around 1 TB of raw events per day. Over a year of retention, that's roughly 365 TB of accumulated history. A naive transformation pipeline reprocesses the whole 365 TB every night to produce the next day's normalized table. That's ridiculous, and it's also exactly what a lot of homegrown SQL pipelines do, because the alternative is hand-rolling watermark logic.

An incremental model in dbt looks roughly like this:

-- models/normalized/cloudtrail_ocsf.sql
{{ config(materialized='incremental', unique_key='metadata_uid') }}

SELECT
  eventTime AS time,
  '3005' AS class_uid,
  eventName AS activity_name,
  userIdentity.principalId AS actor_user_uid,
  sourceIPAddress AS src_endpoint_ip,
  awsRegion AS cloud_region,
  eventID AS metadata_uid
FROM {{ source('raw_logs', 'cloudtrail_raw') }}

{% if is_incremental() %}
  WHERE eventTime > (SELECT MAX(time) FROM {{ this }})
{% endif %}

The config block at the top tells dbt to use the incremental strategy. The is_incremental() block is dbt's way of saying "only include this WHERE clause on incremental runs, and on the first build, scan everything." The double-braces are Jinja templates, which dbt uses to inject references and conditionals into otherwise-plain SQL.

On the first dbt run, the model processes the entire history. On every subsequent run, it processes only events newer than the latest row already in the target table. For a 365 TB historical dataset with 1 TB/day of new data, the daily delta is 1/365th of the work. The compute savings are roughly two orders of magnitude. The dbt community reports 85% to 99% reductions depending on workload shape, and that range matches my experience for batch-shaped security workloads. I'd treat any single percentage you see quoted as directional rather than precise; the real number depends on your retention window, your data volume, and how often you have to do a full refresh (schema changes, data-quality issues, occasional cleanups).

The honest caveats: incremental models require idempotency. If the same source row gets processed twice, the unique key tells dbt to merge rather than duplicate. They also require a reliable watermark column. CloudTrail's eventTime works because AWS guarantees it; a log source where timestamps are unreliable will silently drop late-arriving events that show up after the next dbt run. And schema changes typically force a full refresh, which means the historical compute bill comes due once every few months. None of this is a dealbreaker, but it's the kind of caveat you need to know going in.

One forward-looking note, labeled as such: Iceberg V3's row lineage opens a path to incremental models that don't need a watermark column at all, because the dbt model could query by row-lineage timestamp instead of by eventTime, which is robust to late-arriving data in a way the watermark approach is not. This is Tier D evidence: the dbt-iceberg adapter support for row-lineage-based incremental hasn't shipped as of early 2026, and the V3 features themselves are still rolling out across query engines through the year. Worth tracking if Iceberg is your table format. The feature mechanics are in the Iceberg V3 thesis-shift piece.

This is the pattern I think detection engineers will find most directly useful, because it maps onto a problem they already have: detection rules that fail silently when upstream data shifts. The textbook example: a CloudTrail field gets renamed, the rule's WHERE clause matches nothing, the rule produces zero alerts forever, and nobody notices until the postmortem on an incident that should have fired.

dbt tests are written in YAML for the simple cases and in SQL for the complex ones. A YAML test declaration looks like this:

# models/normalized/schema.yml
version: 2
models:
  - name: cloudtrail_ocsf
    columns:
      - name: time
        tests:
          - not_null
      - name: class_uid
        tests:
          - not_null
          - accepted_values:
              values: ['3005']
      - name: actor_user_uid
        tests:
          - not_null

Running dbt test against this schema produces pass-or-fail output for each assertion. If the upstream CloudTrail schema changes and userIdentity.principalId is suddenly null on a meaningful share of rows, the not_null test fails before any detection rule runs against the broken data. In a CI-gated workflow, the deploy blocks. In a scheduled-run workflow, the alert goes to the detection engineer rather than going silent.

Custom tests are SQL files in tests/ that return zero rows on success. The detection analog is direct: a custom test can encode "this detection rule should never fire below threshold X on known-good test accounts" or "yesterday's CloudTrail volume should not have dropped more than 50% from the trailing seven-day average." Both are assertions about data shape that you'd otherwise have to check manually, and both fail loudly when the upstream pipeline breaks rather than letting the failure go unnoticed until someone goes looking.

The freshness check is a special case worth calling out. Source freshness is a built-in dbt feature that runs SELECT MAX(loaded_at) FROM source_table and warns or errors if the answer is older than a configured threshold. "Warn if CloudTrail is more than 2 hours stale, error if more than 6 hours" turns into three lines of YAML. The threat-hunter complaint that opens half of the incident retrospectives I've seen ("I queried CloudTrail and got zero results; was my query wrong or was the data missing?") has a structural answer that doesn't require a human to remember to check.

A point of confusion I see often: dbt is not the engine. It compiles your SQL and submits it to whatever engine your profiles.yml points at. For a security data lake on Iceberg, that engine is most often Apache Spark, which handles the ACID writes, partition management, and distributed execution, while dbt's job is to manage the SQL, the test suite, the dependency order, and the documentation without ever moving the data itself.

The practical implication is that the choice of engine matters as much as the choice of dbt. If your team is on Databricks, the dbt-databricks adapter ships Spark execution against Delta or Iceberg tables under Unity Catalog. If you're on Snowflake, dbt-snowflake handles native Snowflake SQL. For a self-managed Iceberg lakehouse, dbt-spark with Iceberg configuration is the typical path. For a ClickHouse-fronted analytical layer, the community-maintained dbt-clickhouse adapter exists but is less mature. I'd treat it as usable but not as battle-tested as dbt-spark or dbt-snowflake.

A typical Iceberg incremental config in dbt looks like:

{{ config(
  materialized='incremental',
  file_format='iceberg',
  partition_by=['date(time)'],
  incremental_strategy='merge',
  unique_key='metadata_uid'
) }}

The incremental_strategy='merge' path is the one that benefits most from Iceberg V3's deletion vectors. Under V2, merge-strategy incremental models accumulated delete files that needed periodic compaction, which is one of the honest reasons dbt teams sometimes avoided merge-strategy on Iceberg, and V3 turns those point updates into metadata operations so the compaction tax mostly disappears. As with everything V3-related, engine support is rolling out through 2026, so check your Spark or Trino version before assuming the simpler operational profile. The deletion-vector mechanics are covered in the Iceberg V3 thesis-shift piece.

I don't have a SOC team blog post titled "How we use dbt for OCSF normalization" to cite as Tier A validation. What I have is three Tier B references that make the case directionally.

SnowAlert. Snowflake's open-source security analytics framework, active from 2018 to 2024, used dbt to manage detection rules as version-controlled SQL views. Detection rules lived in a snowalert.rules schema, with pre- and post-hooks for ingest automation, and the full dbt machinery (tests, dependency graph, modular SQL) applied to security detections rather than business intelligence. Snowflake deprecated SnowAlert in 2024 when they built first-party Alerts and Notifications, which I read as productization rather than abandonment, because Snowflake saw enough demand for detection-as-code to bring it into the platform, so the pattern was validated even though the specific framework was superseded.

LinkedIn Moonbase. LinkedIn rebuilt its threat detection infrastructure between 2020 and 2022 using "engineering-first" practices: version control for detection rules, CI/CD before deployment, modular code, automated testing. The LinkedIn Engineering blog write-up reads as a parallel to dbt's design without naming dbt. Data collection expanded roughly tenfold (gigabytes to petabytes) and triage time dropped from hours to minutes, and the gap LinkedIn filled with custom tooling is the same gap I'd argue dbt fills off the shelf.

Databricks and Snowflake security architectures. Both vendors document dbt as a recommended transformation layer in their security data lake reference architectures. The dbt-databricks and dbt-snowflake adapters are first-party and well-supported. The case studies published by the vendors mention dbt for security log processing, usually anonymized. This is Tier B (vendor documentation and anonymized case studies) rather than Tier A (named production deployment with disclosed methodology).

What's missing: a named SOC team writing publicly about how they manage their OCSF normalization and detection-as-code with dbt. I'd treat that gap as an opportunity for the team that's willing to write the blog post, not as a reason to wait. The structural argument (that detection engineers have the same software-engineering problems analytics engineers solved with dbt a decade ago) is strong enough that I'd start now and build the case study as you go.

The gaps and constraints worth knowing before adoption, in roughly the order they tend to surface:

• Real-time alerting is out of scope. dbt's smallest meaningful unit of work is a scheduled run, which lands at the fifteen-minute-to-hourly cadence in practice. Stream-processing tools like Cribl or Tenzir do the sub-second detection work. Use both, not one.
• Complex parsing belongs in Python. dbt is SQL-first. If your transformation needs non-trivial JSON parsing, ML feature engineering, or external API enrichment, the right tool is Python: either as a pre-step (a Python script that lands cleaned data in the lake, which dbt then transforms) or as a dbt model in Python (the dbt-python feature, available in dbt-spark and dbt-databricks, less mature elsewhere). The split is per-step, not per-pipeline.
• Orchestration is separate. dbt schedules dbt runs. It does not orchestrate upstream ingest, downstream alerts, or dependencies across different systems. Airflow, Dagster, or Prefect (or dbt Cloud's scheduler if you're paying for it) handle the orchestration layer.
• Some adapters are less mature than others. dbt-spark, dbt-snowflake, dbt-databricks, and dbt-bigquery are first-party and well-tested. dbt-clickhouse and dbt-duckdb are community-maintained and usable but rougher around the edges. If your stack is built around a less-common engine, expect to find more sharp edges than the marketing suggests.
• Tests are assertions, not guarantees. A dbt test that passes today on yesterday's data does not guarantee tomorrow's data passes. The test is a structural check at build time. You still need monitoring on the runs themselves, not just confidence that the SQL compiled.

None of these are reasons to skip dbt, but they're reasons to set expectations correctly and to build the surrounding infrastructure (orchestration, streaming, monitoring) in parallel rather than assuming dbt covers all of it.