Implementation anti-patterns

Flattening away your detection logic.

When migrating from SIEM to lakehouse, teams flatten nested JSON to optimize columnar storage. In the process they lose semantic relationships — and detections silently break. This isn't schema conversion. It's semantic translation.

Reading time: 17 minutes. Evidence tier: B–C (architectural analysis plus practitioner patterns; production case studies pending).

A financial services firm migrated 50 TB/day of AWS CloudTrail logs from Splunk to ClickHouse in December 2024. The team celebrated: query performance improved 10×, infrastructure costs dropped 85%, and analyst complaints about slow searches vanished overnight.

Six weeks later, during a routine SOC 2 Type II audit, the auditor asked: "Show me all IAM privilege escalations without MFA for the past 90 days." The detection engineer ran the query. Zero results.

That couldn't be right. The team manually reviewed CloudTrail logs and found 23 privilege escalation events where users attached IAM policies without multi-factor authentication — exactly the scenario the detection rule was supposed to catch.

The detection logic hadn't failed. It had been quietly broken during migration, returning empty results instead of alerts. For six weeks, a critical security control was blind.

What went wrong? The migration team flattened CloudTrail's nested JSON structure to optimize ClickHouse's columnar storage. In the process, they lost a critical semantic relationship: in CloudTrail, the absence of the mfaAuthenticated field means "no MFA," not "NULL." The flattened schema treated absence as NULL, and the detection rule — checking WHERE mfaAuthenticated = false — returned nothing.

Security decisions require context — who did what, under what circumstances. User identity alone isn't enough; you need session context (MFA status, assumed role, source identity).

AWS CloudTrail example:

{
  "eventName": "AttachUserPolicy",
  "userIdentity": {
    "type": "IAMUser",
    "userName": "alice@example.com",
    "sessionContext": {
      "attributes": {
        "mfaAuthenticated": "false",
        "creationDate": "2025-02-10T14:23:01Z"
      },
      "sessionIssuer": {
        "type": "Role",
        "arn": "arn:aws:iam::123456789:role/AdminRole"
      }
    }
  },
  "requestParameters": {
    "userName": "bob",
    "policyArn": "arn:aws:iam::aws:policy/AdministratorAccess"
  }
}

Detection rule: "Alert on IAM privilege escalation (AttachUserPolicy, PutUserPolicy, AddUserToGroup) by users without MFA." Requires traversing userName → sessionContext → attributes → mfaAuthenticated. The critical semantic: the field mfaAuthenticated is only present when MFA was used. Absence equals no MFA.

The naive ClickHouse translation fails silently:

-- Naive ClickHouse translation (FAILS SILENTLY)
SELECT eventTime, userIdentity_userName, eventName, requestParameters_policyArn
FROM cloudtrail_flat
WHERE eventName IN ('AttachUserPolicy', 'PutUserPolicy', 'AddUserToGroup')
  AND userIdentity_sessionContext_attributes_mfaAuthenticated = 'false';  -- Never matches!

When MFA is used the field is present with value "true". When MFA is not used the field is absent from JSON — not present, not "false". The flattened schema converts absent to NULL. The detection query checks = 'false' against a NULL column. Result: zero alerts, silent failure.

The fix: either correct the query semantics or preserve the structure.

-- Approach 1: fix query logic — treat NULL as "no MFA"
SELECT eventTime, userIdentity_userName, eventName, requestParameters_policyArn
FROM cloudtrail_flat
WHERE eventName IN ('AttachUserPolicy', 'PutUserPolicy', 'AddUserToGroup')
  AND (userIdentity_sessionContext_attributes_mfaAuthenticated != 'true'
       OR userIdentity_sessionContext_attributes_mfaAuthenticated IS NULL);

-- Approach 2: use Nested types
CREATE TABLE cloudtrail_nested (
    eventTime DateTime,
    eventName String,
    userIdentity Nested(
        type String,
        userName String,
        sessionContext Nested(
            attributes Nested(
                mfaAuthenticated String,
                creationDate DateTime
            )
        )
    ),
    requestParameters Nested(
        userName String,
        policyArn String
    )
);

Lesson: absence is meaningful in security logs. Flattening must preserve semantic distinctions (NULL vs absent vs false).

EDR detection logic chains process execution to network activity ("PowerShell downloads script from external IP," "Office app spawns cmd.exe then connects to C2"). Flattening can break the association between a process and its network connections.

Wrong approach: denormalize (one row per network connection):

CREATE TABLE edr_flat (
    eventTime DateTime,
    hostname String,
    process_name String,
    process_pid UInt32,
    process_commandLine String,
    network_destinationIp String,
    network_destinationPort UInt16,
    network_destinationDomain String,
    network_protocol String
);
-- Result: multiple rows per event (one per network connection)

The query "process with HTTP connection to non-CDN" requires aggregation across rows: 45 seconds on ClickHouse versus 3 seconds on Elasticsearch (where the nesting was preserved). PID reuse can mix unrelated processes, generating false negatives.

Correct approach: nested arrays:

CREATE TABLE edr_nested (
    eventTime DateTime,
    hostname String,
    process Nested(
        name String,
        pid UInt32,
        commandLine String,
        parentProcess Nested(
            name String,
            pid UInt32
        )
    ),
    network Array(Tuple(
        direction String,
        protocol String,
        destinationIp String,
        destinationPort UInt16,
        destinationDomain String,
        bytesTransferred UInt64
    ))
);

-- Query: PowerShell with encoded command connecting to non-CDN
SELECT eventTime, hostname,
       process.name[1] as proc_name,
       process.commandLine[1] as cmd,
       arrayFilter(n -> n.2 = 'HTTP' AND n.5 NOT IN ('cloudfront.net', 'akamai.net'), network)
FROM edr_nested
WHERE process.name[1] = 'powershell.exe'
  AND process.commandLine[1] LIKE '%EncodedCommand%'
  AND arrayExists(n -> n.2 = 'HTTP' AND n.5 NOT IN ('cloudfront.net', 'akamai.net', 'fastly.net'), network);

Performance: no aggregation overhead, query executes in under a second on billions of events. Denormalizing arrays for "simplicity" breaks performance at scale. Nested types preserve relationships and perform better.

Advanced threats follow multi-step patterns. Temporal chains within events (authentication attempts) are broken by denormalizing arrays. Temporal chains across events (lateral movement: RDP → SMB → exec) are not schema problems — they're query-complexity problems.

Azure AD authentication example with detection rule "2+ failed password attempts followed by successful SMS (MFA bypass indicator)":

-- Correct: nested array preserves sequence
CREATE TABLE azuread_auth_nested (
    eventTime DateTime,
    userPrincipalName String,
    appDisplayName String,
    authenticationDetails Array(Tuple(
        authMethod String,
        succeeded Bool,
        attemptTimestamp DateTime
    ))
);

-- Query: 2+ failed password, then successful SMS
SELECT eventTime, userPrincipalName, appDisplayName, authenticationDetails
FROM azuread_auth_nested
WHERE arrayCount(a -> a.1 = 'Password' AND a.2 = false, authenticationDetails) >= 2
  AND arrayExists(a -> a.1 = 'SMS' AND a.2 = true, authenticationDetails);

Denormalizing to one row per attempt loses array ordering — the sequence has to be reconstructed via timestamp grouping, which is fragile. Use nested types for within-event sequences.

Solution 0: Iceberg V3 Variant type (recommended for new deployments)

For semi-structured logs where field presence carries information — CloudTrail, Kubernetes audit, Azure Activity, Okta system logs — and for multi-engine query environments, this is now the preferred answer. Iceberg V3 (with Parquet's matching Variant type, ratified October 2025) introduces a native semi-structured column type that preserves JSON's absence-vs-null distinction, supports queryable nested structures, and works across all engines that adopt V3 (Spark, Trino, DuckDB, Snowflake — engine support rolling out through 2026). Unlike ClickHouse Nested, Variant is engine-portable.

CREATE TABLE cloudtrail_v3 (
    eventTime TIMESTAMP,
    eventName STRING,
    userIdentity VARIANT,        -- preserves the entire nested userIdentity object
    requestParameters VARIANT,
    sourceIPAddress STRING,
    awsRegion STRING
) USING iceberg
TBLPROPERTIES ('format-version' = '3');

-- Detection rule: still works, semantics preserved
SELECT eventTime,
       variant_get(userIdentity, '$.userName', 'STRING') as userName,
       eventName
FROM cloudtrail_v3
WHERE eventName IN ('AttachUserPolicy', 'PutUserPolicy', 'AddUserToGroup')
  AND variant_get(userIdentity, '$.sessionContext.attributes.mfaAuthenticated', 'STRING') IS NULL;
-- IS NULL correctly captures "field absent = no MFA"

Pros: cross-engine portable; preserves field-absence semantics by construction; no per-engine learning curve; future-proofed. Cons: engine adoption uneven through 2026 (verify your query engine supports variant_get and Variant-aware predicate pushdown); fewer production references than ClickHouse Nested or Hydrolix.

Solution 1: ClickHouse Nested types

Still valid for ClickHouse-only stacks. Relationships preserved, performance nearly as good as flat columns, production-validated at Netflix scale (tag sharding 3s → 700ms). Trade-offs: Nested syntax learning curve, limited JOIN support on arrays, ClickHouse-specific.

CREATE TABLE cloudtrail_nested (
    eventTime DateTime,
    eventName String,
    userIdentity_userName String,
    resources Nested(
        ARN String,
        type String
    )
) ENGINE = MergeTree()
ORDER BY (eventTime, eventName);

SELECT eventTime, userIdentity_userName, eventName
FROM cloudtrail_nested
WHERE eventName = 'DeleteBucket'
  AND arrayExists(r -> r LIKE '%sensitive%', resources.ARN)
  AND arrayExists(t -> t = 'AWS::S3::Bucket', resources.type);

Solution 2: Hybrid architecture (hot flat + cold nested)

For mixed workloads (80% simple aggregations, 20% complex forensics):

Logs → Kafka → Dual-Write
  ├─→ ClickHouse Hot Tier (Flattened, 7–30 days)
  └─→ Iceberg Cold Tier (Nested, 90–365 days)

Query Router:
  - Time < 30 days → ClickHouse (sub-second, flattened)
  - Time > 30 days → Trino + Iceberg (5–10 seconds, nested)

Benefits: performance for recent data, structure for forensics, cost-optimized. Trade-offs: dual maintenance, storage duplication during overlap window.

Flattening isn't just schema conversion — it's semantic translation. Relationships matter as much as field values.

1. Absence is meaningful in security logs. MFA field absent equals no MFA, not NULL. Flattened schemas must preserve semantic distinctions.
2. Arrays are common in security data. CloudTrail resources[], EDR network_connections[], Azure AD authenticationDetails[]. Denormalizing breaks relationships and performance. Use nested types — or Iceberg V3 Variant.
3. Test correctness, not just performance. Parallel runs must validate alert parity. If SIEM produces 1,250 alerts/week and lakehouse produces 1,248, investigate the missing two. Slow and correct beats fast and wrong.
4. Hybrid architectures balance trade-offs. Hot flat plus cold nested solves the 80/20 problem. 80% of queries hit recent data with simple filters (sub-second, flattened). 20% hit historical forensics (5–10 seconds acceptable, nested).
5. Document relationship dependencies before flattening. Map detection logic dependencies first. Schema translation comes second. Teams that focus on schema mapping miss semantic dependencies — and break detections.