Security teams often measure detection coverage by counting techniques in the MITRE ATT&CK matrix that have at least one detection rule tagged with a specific technique. This measure is easy to compute, but it doesn’t show whether the required telemetry exists or whether analysts work on generated alerts.

This post describes a specific coverage calculation model with three axes: visibility (telemetry), detection (rules), and response (alerts that analysts work on). I implemented that model as a set of Databricks jobs and a Databricks App (plus an optional AI/BI Dashboard) over Lakewatch and tested it in my workspace. The same model can be used with any SIEM that allows access to telemetry, detection rules, and alerts.

Why a rule-tag count is not enough

A rule tag is a statement by the rule author: “this rule is about technique T1110”. The tag can be correct, but the coverage number that you build only from tags has a few problems:

• No data check. A rule over Windows process events is tagged with T1059.001 (PowerShell), but the endpoint telemetry was never onboarded, or it stopped arriving two months ago. The rule exists, runs, and finds nothing.
• No response check. The rule fires, but alerts produced by it stay open and unassigned. Such a rule provides little operational value.
• No prioritization. All techniques count the same. A rule for a technique that nobody uses has the same weight as a rule for T1078 (Valid Accounts), which is used by many ATT&CK groups.

In my workspace, the tag-only method reports 58 of 697 techniques and sub-techniques (8.3%) as covered. Under the three-axis definition used in this post, only 17 techniques (2.4%) are “Monitored” or “Well-covered”. The 8.3% number hides the work that teams need to do.

Three axes and seven states

The method grades each technique and sub-technique from 0 to 3 on three independent axes. For example, a technique can have current telemetry, but it has no rules. Its visibility score is positive, but its detection score is 0, and its state is “Deaf”.

These scores give a state to a technique. The rules from the table below are evaluated from the top, and we stop after the first match:

The difference between the states is the main value of the method. Each state points to a different team and a different action: a “Blind” goes to data engineering, a “Deaf” goes to detection engineering, and a “Unactioned” goes to the SOC manager. When you calculate a percentage from a single source, you can’t perform such routing.

and each state maps to an action. Blind leads to “onboard a source”, Degraded to “fix data quality”, Deaf to “write a detection”, Silent to “verify firing and routing”, Unactioned to “assign or tune”, Monitored to “add depth”, Well-covered to “maintain”.

A “Silent” state can have multiple meanings: the technique doesn’t happen in your environment (good), or the rule is broken, or its output isn’t routed to analysts (bad). The current approach can’t distinguish between them. It only shows that there is no evidence that the path from the rule to the analyst works. To confirm it, run an adversary emulation (i.e., with Atomic Red Team) and check that an alert is generated.

Stale data also needs special handling. If one data source stops sending data for a day, all techniques that depend on it are marked as having degraded visibility. A technique with visibility 1 and a working rule keeps its state, but the app shows a “stale data” marker on the technique’s cell. Only a technique with stale data and no rule gets the “Degraded” state. As a result, a technique can be “Monitored” or “Well-covered” and still carry the stale-data warning.

Results from the demo workspace

I ran the coverage job on a workspace with Lakewatch v2. The workspace has 25 data sources, 164 detection rules (112 enabled, 157 with ATT&CK tags), and ~3k security alerts in the last 30 days. Many rules are per-source instances of the same detection (one rule per parsed table), and the job counts each such group once, so there are 82 distinct detections. The absolute numbers are low, but it’s sufficient to demonstrate the scoring behavior.

three-axis coverage (Monitored or Well-covered) is 17 of 697 (2.4%). The 41 tagged techniques that are not covered split into 23 Silent and 18 Unactioned.

All numbers in this post count techniques and sub-techniques together. If you count only the 222 parent techniques, the tag-only method gives 33 (14.9%), and the three-axis method gives 10 (4.5%). The ratio is almost the same, but make sure that you use the same scope when you compare numbers from different reports.

The query that shows that data is very simple, because all scores are in one mitre_coverage table:

FROM mitre_ref_v2.mitre_coverage
|> AGGREGATE
     count(*) AS total,
     count_if(tagged_rule_count > 0) AS tagged,
     count_if(technique_state IN ('Monitored', 'Well-covered')) AS covered,
     count_if(tagged_rule_count > 0 AND visibility_score = 0) AS tagged_blind,
     count_if(visibility_degraded) AS stale,
     ...

The results show several gaps that a tag count does not show.

• Blind is now a small group. When I first run the coverage job in this workspace, 327 techniques were “Blind” due to the missing data. After I onboarded more data sources, only 67 techniques are “Blind”, and 45 of them have no data component at all (see below). The number of “Deaf” techniques grew from 148 to 551: the new data made many techniques visible, but there are no rules for them yet. This is the expected next step: the gap moves from data engineering to detection engineering.
• Silent rules. 23 techniques have a solid rule but no alerts in 30 days, for example, T1053.005 (Scheduled Task), T1003.001 (LSASS Memory), and T1041 (Exfiltration Over C2 Channel). Each of these needs a test to prove that the rule can fire.
• Unactioned alerts. Of the 2,971 alerts, 1,165 are still open, 232 are assigned, and 1,234 have a classification (1,226 false positives and 8 true positives). Most of the resolved alerts come from triage and tuning sessions. The open alerts are not spread evenly: T1110 (Brute Force) has 78 alerts in the window, and nobody has assigned or classified any of them (45 are still open). A tag-only report presents this as strong coverage because the technique has 9 rules (6 of them tagged), although nobody is working the alerts.
• Noisy rules. T1033 (System Owner/User Discovery) and T1082 (System Information Discovery) are “Monitored” with response 3, because most of their 1,503 alerts are resolved. But 1,001 of these alerts are false positives. The response axis shows that analysts work the queue, and the false-positive count shows that the corresponding rules need tuning.
• Stale data. 21 techniques have visibility 1 because their only data sources stopped receiving new data, and all of them are in the “Degraded” state. In a production environment, investigate the stale-data warning first, because a current rule over stale data finds nothing.
• Empty tactics. Defense Impairment, the new tactic from v19, has 56 techniques and sub-techniques, and the demo workspace still has no rule for any of them. Impact and Discovery have rules for only 2 techniques each.

The results also expose a scoring limitation. T1021 (Remote Services) is “Well-covered”, with 6 rules and 21 alerts, but 20 of the alerts are open and unassigned. The response score is 2 (triaged), because the rule for “triaged” requires that at least one alert is engaged. A ratio threshold (for example, at least 50% of the alerts engaged) gives a stricter result. Pick the thresholds for your SOC, and write them down next to the report.

The scoring model is independent of Lakewatch, so it could be implemented on any SIEM. The following sections show how I implemented each axis with Lakewatch v1 and v2.

The reference data: ATT&CK in Delta tables

The first step is to load ATT&CK into tables. MITRE publishes ATT&CK as STIX 2.1 bundles, one file per version. A job loads the bundle for a selected version into Delta tables: tactics, techniques, groups, group-to-technique relationships, data components, and technique-to-data-component links. Pin the version: if the matrix changes under you, the coverage numbers change without a change in your environment.

The selection of a version matters a lot. ATT&CK v18 (October 28, 2025) deprecated the x_mitre_data_sources field of techniques, and MITRE says that the field is accurate only up to the April 2025 release. Many tools and mappings use this field to find which telemetry can see a technique, and they give wrong results on v18 and later. The link now goes through the new detection model:

technique <-[detects]- detection strategy
detection strategy -> x_mitre_analytic_refs -> analytic
analytic -> x_mitre_log_source_references[].x_mitre_data_component_ref
         -> data component

So a technique is linked to data components through all analytics of all detection strategies that detect it. The loader in the app does this walk and stores the result as a flat attack_technique_data_components table. The loader fails on versions older than 18.0 because they do not have this model.

detection strategy, which references analytics, which reference log sources with data components. On the telemetry side, a Lakewatch parsed table (v2) or an OCSF gold table (v1) is mapped to the same data components.

There are 2 other changes that you can see in the coverage numbers:

• v19 (April 2026) split the Defense Evasion tactic into Stealth and Defense Impairment, so the Enterprise matrix has 15 tactics now. Any coverage report that was built on Defense Evasion must be rebuilt.
• v20 (October 13, 2026) added techniques for attacks on AI systems, for example, Direct Prompt Injection and AI-Enabled Exfiltration. Enterprise v20 has 227 techniques, 479 sub-techniques, 703 detection strategies, and 1,770 analytics.

The numbers in this post use ATT&CK v19.1, which was current when I built the reference tables. To move to v20, we just need to update the version and the STIX URL in the configuration file, and re-run the reference and coverage jobs.

When the data components are known, we can see why some techniques cannot be measured with telemetry at all. In v19.1, 45 techniques have no data component: 30 in Reconnaissance and 15 in Resource Development. They happen outside of your environment, so no log source can see them. The app marks them “Blind”, but in a report you should separate them from real gaps.

Visibility: from parsed tables to data components

The visibility axis needs a mapping from your telemetry to ATT&CK data components. This is the most important part of the method, and it requires manual work. ATT&CK says which data components can see a technique, but it does not know your log sources.

In Lakewatch v1, the telemetry is normalized to OCSF, so the mapping is from OCSF classes (for example, Process Activity) to data components (for example, Process Creation). In Lakewatch v2, there is no OCSF normalization yet: each source has its own parsed table with the source columns, plus a shared lw_observables column for entity pivots. So the mapping for Lakewatch v2 is per parser. The coverage job finds the live parsed tables through the Lakewatch datasources API: each datasource lists its parsers and their target tables. Then it computes quality signals per parser.

A source is healthy (status 2) if it has rows, its newest event is inside the freshness SLA (48 hours in my configuration), and all required fields are populated above a threshold. If one of these checks fails, the source is degraded (status 1). A data component takes the best status of all sources mapped to it, and the technique visibility score comes from the statuses of its data components (0 if none is present, 3 if 2 or more are healthy). This method still isn’t perfect as it doesn’t take into account the all potential data sources that should provide telemetry. I.e., we have firewall logs only for AWS, but don’t have them for Azure and on-premise.

Detection: rules, tags, and external findings

The detection axis comes from the rules. In Lakewatch v2, the rules API returns each rule including the mitre attribute that is the list of technique and sub-technique IDs, its SQL query, and the schedule. The job uses 3 kinds of rule-to-technique mappings:

• Tagged. The mitre list of the rule. This is high confidence.
• Inferred. For a rule without tags, an LLM reads the rule name, description, and query, and try to infer technique IDs. The job checks the IDs against the pinned ATT&CK version, and caches the result by a hash of the rule content. An inferred mapping gives detection score 1 at most.
• External. Products such as EDR or cloud security services already map their findings to ATT&CK. In Lakewatch v2, a parser that declares the mitre_attack_technique observable puts these IDs into lw_observables.mitre_attack_technique_ids. The job finds these parsers from their specs and counts the technique IDs that appear in their tables.

Disabled rules are counted separately, and they don’t contribute to the score. A disabled rule is a common way to get a “covered” technique on a slide without any detection in production.

Response: Do analysts act on the alerts?

The response axis is the one that a rule-tag count can’t give you. In Lakewatch v2, the alerts API returns security alerts with their state, assignee, and classification (true/benign/false positive). Each alert refers to the rule that created it, so the job joins alerts to techniques through the same rule-to-technique index that the detection axis uses. This way both axes use the same mappings, including the inferred ones.

For each technique, the job looks at alerts from the last 30 days:

• No alerts: response 0 (silent).
• Alerts exist, but none is assigned, in progress, or classified: response 1 (ignored).
• At least one alert is engaged: response 2 (triaged).
• At least half of the alerts are resolved with a classification: response 3 (worked).

The false-positive count is kept too, because a rule that produces many false positives may require tuning even if it fires.

There are 2 v2-specific points:

• An alert in v2 can group several signals of a rule, so the counts are alert counts, not signal counts.
• The v2 alerts API can filter only on the alert creation time. An alert that was created before the window and analyst worked inside that window isn’t counted.

Threat-informed ranking of the gaps

A list of 551 “Deaf” techniques is not a plan. To create it, the method weights each technique by its relevance to adversaries. The default weight is based on the number of ATT&CK groups that use the technique (from the group-to-technique relationships in STIX). You can also select a threat profile, a list of groups that matter for your industry, and the weights then come only from these groups.

With the weights, we rank the actions, not the techniques. For “Deaf” techniques, the question is “which data component makes the most weighted techniques visible?”:

FROM mitre_ref_v2.mitre_coverage AS c
|> WHERE technique_state = 'Blind' AND size(data_components) > 0
|> JOIN mitre_ref_v2.threat_technique_weights AS w
     ON w.technique_id = c.technique_id AND w.profile_id = 'all-groups'
|> SELECT explode(c.data_components) AS component, c.technique_id, w.weight
|> AGGREGATE count(DISTINCT technique_id) AS techniques,
     round(sum(weight), 2) AS weighted
   GROUP BY component
|> ORDER BY weighted DESC
|> LIMIT 5

In the first run in my workspace, the answer was clear:

The plan was “onboard endpoint telemetry with process creation events”. I did it, and the same query now returns only a short tail (Response Content with 16 techniques is at the top).

Now most of the gaps are “Deaf”, so the same weights give a list of detections to write:

FROM mitre_ref_v2.mitre_coverage AS c
|> WHERE technique_state = 'Deaf'
|> JOIN mitre_ref_v2.threat_technique_weights AS w
     ON w.technique_id = c.technique_id AND w.profile_id = 'all-groups'
|> SELECT c.technique_id, c.technique_name, round(w.weight, 2) AS weight
|> ORDER BY weight DESC
|> LIMIT 5

For “Silent” and “Unactioned” techniques, the same ranking gives a list of rules to test or tune.

The ranking is deterministic: it is SQL over the coverage and weight tables. This is important when you report the plan to management, because the same input always gives the same result.

How this method is implemented

The implementation has 2 Lakeflow jobs and 3 consumers, and all of them are deployed using Declarative Automation Bundle. The jobs write Delta tables and consumers read these tables. Here is the full list of components:

• Reference job. Loads the pinned ATT&CK STIX bundle, the curated mappings (OCSF for v1, parsers for v2), and the threat profiles into Delta tables. It runs only on initial deployment, or when the ATT&CK version or a mapping changes.
• Coverage job. Collects the inputs (data quality stats, rules, external findings, and alerts), computes the three scores and the state, etc. and writes the mitre_coverage table plus a historical snapshot so we can track trends. It runs on a daily schedule by default.
• App. A Databricks app with a scorecard (including the tag-only versus three-axis contrast), the ATT&CK matrix colored by state, a gap queue for detection engineers, an ATT&CK Navigator layer export, and an embedded Genie agent for free-form questions over the coverage tables. It’s also possible to draft with a LLM a first version of a detection query for a specific technique, but it’s very generic right now.
• AI/BI dashboard. Counters for each axis, the tag-only versus three-axis contrast, techniques by state, and the ATT&CK matrix (I wrote about it in the blog post about custom visualizations). A scope filter selects if the counters will include sub-techniques or not. Use it when you don’t want to run an app to save costs (but you won’t get prioritization queue in this case).
• Genie agent. Answers free-form questions over the coverage and reference tables. You can use it in the app or directly in the Databricks UI.

When deployed, we can see the result as a matrix view in the app (or in the dashboard):

Lakewatch v1 and v2 use different sources for each axis, but everything after the data collection is done with the same code:

Asking questions about the coverage with Genie

The app & dashboard provide answers to the fixed set of questions that I selected, but each team has its own questions. For them, the bundle deploys a Genie agent over the coverage (mitre_coverage and mitre_coverage_snapshots) and the reference tables (techniques, tactics, groups, data components, threat profiles, etc.). This agent allows users to ask in plain English and get the result back (together with the SQL query that was generated).

I asked the agent deployed in my workspace some typical questions from different roles. All results below are from the same run as the numbers above.

SOC manager: what changed? The snapshot table keeps one copy of the coverage per day, so trend questions are easy. The question “How did the number of techniques in each technique state change between the first and the latest coverage snapshot?” gave this result:

Genie also wrote a short summary: techniques moved out of “Blind” and “Degraded”, and most of them moved into “Deaf”. This is the before/after picture from the results section, and you can use it in a status report without extra work.

A SOC manager does not need to ask this question every time. In Genie One you can run a request as a scheduled task: at the selected time, it will run the request, post the result in a chat thread, and send an email with the result and a PDF copy. You can create the task from the chat with a request like “Every Monday at 8:00, show how the number of techniques in each technique state changed since last week, and list the techniques that moved to Silent or Unactioned”, or just select the specific time in the UI. With a daily or weekly task, the coverage trend and the alert queue come to the SOC manager by email, and nobody has to remember to run them manually.

SOC manager: which rules make noise? The question “Which techniques have the most false positive alerts, and what share of their alerts are false positives?” returned T1033 and T1082 at the top, with 1,001 false positives of 1,503 alerts (66.6%), and then T1078 (Valid Accounts) with 118 of 235 (50.2%). These are the first candidates for tuning.

Detection engineer: what to write next? For “Deaf” techniques, the data is already there, so the question is which detection to write first. The question “Which Deaf techniques are used by the most threat groups? Show the top 10 with the data components we already have for them.” gave a ranked list that also shows the available data:

technique_id technique_name groups present_data_components T1083 File and Directory Discovery 51 Command Execution, File Access, File Creation, Process Creation T1204.001 Malicious Link 49 File Creation, Network Connection Creation, Network Traffic Content, Process Creation T1070.004 File Deletion 47 Command Execution, File Access, File Deletion, File Modification, Process Creation T1059.005 Visual Basic 46 Command Execution, Module Load, Process Creation, Script Execution T1566.002 Spearphishing Link 46 Application Log Content, Network Connection Creation, …

The order is the same as in the weighted ranking above, and the last column tells the engineer which parsed tables to start with.

Threat intelligence: what matters for our adversaries? The question “Which techniques should we prioritize for the ransomware threat profile? Show weight and current state.” returned the 63 techniques of the profile that are in a gap state: 42 Deaf, 9 Unactioned, 6 Silent, 4 Blind, and 2 Degraded. It left out the 3 Monitored and Well-covered techniques. Genie also noticed that all techniques have the same weight (1.0), so the weight does not help to order them. The reason is that the demo ransomware profile has only one group. A real profile needs several groups, so that the weights can separate the techniques.

Report for management: which tactics are weak? The question “Which tactics have the weakest coverage? Show per tactic the number of techniques, how many have a rule, and how many are Monitored or Well-covered.” gave the same picture as the results section: Defense Impairment (56 techniques), Resource Development (50), and Reconnaissance (46) have no rules, and Impact, Exfiltration, and Execution have rules, but no covered techniques.

Limitations

The presented method gives a better picture than a tag count, but it has limits that you may need to include in your reports if you use this approach:

• A data component is coarse. “Process Creation” is present if any process events arrive, but a detection for T1059.001 may need the full command line from all Windows hosts. Visibility 2 means “the right kind of data exists”, not “the data is enough for every analytic”.
• The mapping is manual work. The telemetry-to-data component mapping decides the visibility axis. Review it, version it, and treat the LLM fallback as a list of suggestions.
• A tag is not a test. The detection axis still trusts the rule author. To measure if a rule detects a technique, you need adversary emulation or replay of known attack data, and you can feed the results back into the detection score.
• Thresholds are choices. The freshness SLA, the completeness threshold, the response window, and the “triaged” rule change the result. The T1021 example above shows that.
• Techniques outside your environment. Reconnaissance and Resource Development aren’t visible in your telemetry. Report them separately, or cover them based on threat intelligence.

Conclusion

A coverage number that counts tagged rules is easy to compute, but it mixes together three different problems: missing data, missing rules, and alerts that nobody handles. When you measure the three axes separately, every gap gets a state, and every state has an owner and an action. With threat weights on top, the list of gaps becomes a ranked plan, for example, “onboard process telemetry first”, and after that, “write a detection for file and directory discovery”.

The method does not depend on a specific SIEM. You just need ATT&CK in tables, a mapping from your log sources to data components, access to the data quality of these sources, a list of rules with their ATT&CK mappings, and a list of alerts with their states. In Lakewatch, all of this is available via Lakewatch and Databricks REST API, so the entire pipeline is just a few Lakeflow jobs and SQL queries.

References

• ATT&CKcon 7.0 agenda
• MITRE ATT&CK STIX data
• ATT&CK v20 release notes (October 2026)
• ATT&CK v19 release notes (April 2026)
• Lakewatch documentation
• ATT&CK Navigator

Cross-post from blogspot…

This post uses the Common Crawl web graph to add that context. It tests graph presence, in-degree, links to feed indicators, connected components, a popular-site baseline, and a feed of newly observed domains. The results also show an important limit: the graph is most useful for linked web ecosystems and has little coverage of new or link-isolated domains.

This is a follow-up to Graph analytics for cybersecurity data in the lakehouse, which introduced GraphFrames on OCSF data and the idea that the security lakehouse is already a graph. Here the graph is external: the web itself.

About the Common Crawl web graph

The Common Crawl project publishes a web graph a few times a year. It has two levels. In the host graph the vertices are fully qualified host names, such as mail.example.com. In the domain graph the vertices are pay-level domain names such as example.com. The edges are hyperlinks: a page on one vertex links to a page on another vertex, aggregated to the vertex level.

This is a link-reputation graph, not an infrastructure graph. It does not record name servers, shared hosting, WHOIS registrants, or TLS certificates. Those attributes need a different data source and a different graph (maybe it will be a good topic for another post). The web graph answers one kind of question: how a domain sits in the web neighborhood, measured by who links to it and who it links to.

Two limits follow from how the data is produced, and both matter for security use:

• Freshness. A crawl is weeks to months old, so the graph is weakest exactly where security cares most: short-lived phishing and fast-flux command-and-control domains that are registered and burned before a crawl sees them. The freshness analysis measures this gap and closes part of it.
• Coverage bias. A domain can be missing because it is new, private, regional, or simply not linked. Absence is a signal to check, not proof of anything.

The data on the lakehouse

The graph is loaded into Unity Catalog as four tables built from the May, June, and July 2026 Common Crawl releases:

The vertex tables store the name in a reversed form. For example, rev_domain holds com.blogspot, which is SURT (Sort-friendly URI Reordering Transform) form of blogspot.com. To join the graph against a domain from telemetry, rebuild the forward name by reversing the dot-separated labels:

from pyspark.sql import functions as F

def fwd(col):                       # com.blogspot -> blogspot.com
    return F.array_join(F.reverse(F.split(F.col(col), r"\.")), ".")
domains = (
    spark.table("cybersecurity.graph.domain_vertices_2026_may_jun_jul")
    .select("id", "num_hosts", fwd("rev_domain").alias("domain"))
)

The tables do not carry precomputed centrality or PageRank columns, so any reputation score should be be explicitly computed. The domain vertices have one useful pre-built number, num_hosts - the count of identified hosts under that domain.

We use three more datasets in the later sections. The threat-intelligence feed threatintel.iocs is the known-bad source (described in a separate blog post). It already carries a psl.registered_domain column, so the pay-level domain is pre-computed. The popular-site baseline is datasets.top_sites, containing the Cisco Umbrella and Tranco top-1,000,000 lists (plus historical Alexa list). The freshness feed is datasets.new_sites, a daily feed of newly observed domains (only limited portion, like 100k/day).

Scale affects the method: scope your data before you compute

The domain graph has 2.77 billion edges and the host graph has 3.67 billion. A global PageRank or a global connected-components run over a graph this size is expensive and really not necessary for the tasks in this post. Every task fits naturally:

• Reputation and propagation only need the edges that touch a limited set of domains: the domains seen in your data, or the known-bad seeds.
• Clustering only needs the edges that run between the known-bad domains.

So we can do this as follows: take the small set of domains we care about, resolve them to vertex ids, and touch the large edge table once with a broadcast join that keeps only the rows for those ids. This runs on serverless compute and keeps the cost proportional to the question, not to the graph.

All the graph algorithms here were run on serverless Databricks using an unreleased pure-PySpark GraphFrames implementation, so you will need to use classic compute if you want to try this on your data.

Reputation as a feature

The first use case is enrichment. For every domain that matters to us, we want a cheap score that says how established the domain is on the web. Two numbers from the graph are enough to start: whether the domain is present at all, and its in-degree, the number of distinct domains that link to it.

The set of domains we care about comes from two sources. The first is the known-bad domains from the threat-intelligence table iocs (the domain, hostname, and URL-host indicators), reduced to their pay-level domain with the pre-computed psl.registered_domain column. The second is the domains observed in DNS telemetry. On this workspace that telemetry is the DNS queries from malware-traffic-analysis.net capture sessions, ingested as OCSF gold, with the synthetic demo data excluded (see blog post for details). That observed set is therefore a mix: the malicious domains a captured infection contacted, plus the ordinary domains any host contacts, such as content-delivery networks, update servers, and analytics endpoints.

Presence in the graph is an inner join against the vertex table. In-degree needs the large edge table, so we scope it: resolve the target domains to their vertex ids, broadcast that small set, and touch the 2.77 billion edges once, keeping only edges that point to a target id.

from pyspark.sql.functions import broadcast

present_ids = targets.join(domains, "domain").select("id").distinct()
in_degree = (
    spark.table("cybersecurity.graph.domain_edges_2026_may_jun_jul")
    .join(broadcast(present_ids), F.col("to_id") == F.col("id"))
    .groupBy("to_id").agg(F.count("*").alias("in_degree"))
)

The results show that the web graph is a sparse reference: most domains are not in it at all. Only 12.4% of the known-bad domains and 29.9% of the domains seen in the DNS table are present. Among the domains that are present, the ones seen in the capture traffic are more linked (median in-degree 7) than the known-bad ones (median in-degree 2), because the observed set is dominated by the ordinary, well-established domains a host contacts.

From presence and in-degree we derive one experimental enrichment column, web_reputation, with four values: absent (not observed in this graph snapshot), isolated (present but in-degree 0), weak (in-degree below 5), and established. The threshold of 5 is a working threshold for this analysis, not a general boundary between benign and malicious domains.

enriched = scored.withColumn(
    "web_reputation",
    F.when(~F.col("present"), "absent")
     .when(F.col("in_degree") == 0, "isolated")
     .when(F.col("in_degree") < 5, "weak")
     .otherwise("established"),
)

Consider this as a soft feature, not a verdict, and read it in two directions. Most known-bad domains, 87.6%, are absent: they have no observed history in this graph snapshot. This is consistent with short-lived phishing and command-and-control names. A sample from this group is typical: nicholaswilliams.online, 2344bet-online.top, spribegaming56.click, appstoreautomation.site, com-icjo.xin. A domain in your telemetry with an absent or isolated reputation is worth a closer look, because the crawl did not observe a linked presence.

The other direction is more useful than it first appears. Only 4.4% of the known-bad domains are established, and when we list them by in-degree the top entries are googleusercontent.com, weebly.com, tinyurl.com, squarespace.com, and sharepoint.com. These are shared hosting, website builders, link shorteners, and document services. A domain-level feed entry can identify abuse on one part of such a service without showing that the provider or its other tenants are malicious. A high web_reputation is therefore a reason to review the indicator context and possibly down-weight it, rather than assume that the whole domain is attacker-controlled. The pattern is clearest in the overlap class: among domains that are both observed in traffic and flagged bad, 25.5% are established, because active campaigns lean on established and compromised services.

The important caveat is that absence does not prove maliciousness. A missing domain can be new, regional, internal, or simply unlinked. The feature narrows attention — it does not make a decision.

Propagate proximity from feed indicators

In-degree measures how established a domain is, which is a general signal. A more direct security signal is proximity to domains in a threat-intelligence feed. A site that links to several feed indicators can be a useful review candidate, even if it is not itself in the feed. Previously I worked with a web-reputation system built on this idea. It started from a set of known-bad servers and propagated a bad rating to the hosts that connected to them, iterated over a large web graph.

The full method is personalized PageRank seeded on the indicator set, sometimes called BadRank, the inverse of TrustRank. Here I compute the first and dominant propagation hop, which is cheap and already useful: for every domain, count how many distinct feed indicators it links to. The seed set contains feed domains that are present in the graph and have an in-degree below 500. This experimental threshold excludes high in-degree shared services, such as googleusercontent.com and squarespace.com, whose labels would otherwise spread to a large part of the graph.

bad_seed = scored.where(
    "label LIKE '%known_bad%' AND present AND in_degree < 500"
).select("id")

link_to_bad = (
    spark.table("cybersecurity.graph.domain_edges_2026_may_jun_jul")
    .join(broadcast(bad_seed.withColumnRenamed("id", "to_id")), "to_id")
    .groupBy("from_id").agg(F.countDistinct("to_id").alias("bad_links"))
)

The seed set is 23,918 domains, and 205,463 domains link to at least one of them. The useful part is the new candidates: 41,905 domains that are not in the feed link to at least two known-bad domains, and 21,937 link to at least three.

Ranked by the number of known-bad links, the top new candidates split into three groups. The first is search-engine-optimization and backlink spam: backlinks.sbs (587 known-bad links), worldbusinesspromote.com, linksnatcher.art, indexaward.com, and domainanalysis.org. These are link-farm domains that exist to link between sites, so a hyperlink graph is well suited to finding them. The second is a shared-abuse service: freemyip.com (506 known-bad links), a dynamic-DNS provider that many unrelated malicious domains use. The third is a problem: popular legitimate domains that many bad sites link to, such as blogspot.com (1,440 known-bad links) and wikipedia.org (458). A domain can score high because it is inside a bad neighborhood or because it is a common link target, and the one-sided count cannot tell them apart. The popular-site baseline adds the missing half of the score.

Measure how feed indicators cluster

Propagation and expansion both assume that feed domains connect to each other. To test that directly, build the induced subgraph on the same 23,918 seed domains and keep only the edges where both endpoints are in the feed. connectedComponents then shows how the indicators group.

sg = GraphFrame(bad_vertices, bad_edges)
components = sg.connectedComponents()

The result sets a realistic expectation. Of the 23,918 known-bad domains in the graph, only 2,310 (about 9.7%), link to another known-bad domain at all, over 4,791 bad-to-bad links. The rest are link-isolated: they neither link to nor are linked from other feed indicators. This is consistent with command-and-control infrastructure that has no reason to publish links, although the graph alone cannot establish why each domain is isolated. Web-graph expansion cannot help with those.

The connected domains form 452 components. The largest holds 367 domains, and the next ones fall away to 292, 232, 42, and 26. The largest component is not one campaign. Its most connected vertices are ddns.us, changeip.us, dnsfailover.net, and ocry.com, the free dynamic-DNS domains of a single provider that many unrelated malicious domains use, so a shared abuse service bridges otherwise separate groups. This is the same collapse that popular hubs cause in the reputation analysis, one level down. The mid-size components are the more useful output: tight groups of feed indicators that link to each other and are worth reviewing together. One 26-domain component, for example, contains related news-style sites (newshour.site, mmnewsdaily.store, dailynews22.online, khnews777.online), and a 232-domain component is a set of machine-generated names on a shared template (advancedresearchdomain.com, advancedupskillinghub.com, advancedxplorationstation.com).

Add a popular-site baseline

The first propagation step scored a domain by how many feed indicators it links to, and that one-sided count put three different things at the top of the same list: linked indicator neighborhoods, link farms, and popular sites that everyone links to. The graph also shows which domains are popular, and that information is the other half of the score. The popular-site baseline is top_sites, the Cisco Umbrella and Tranco top-1,000,000 lists. Reduced to pay-level domains and kept to the ones present in the graph, it is 977,171 popular domains. Popularity does not prove that a domain is benign, but it helps identify hubs and shared services that need different treatment from small, dedicated domains.

The baseline supports three related checks.

Popular-site membership

The first is membership. 8,514 of the 41,905 propagation candidates are themselves on the top-sites list, including mex.com, blogspot.com, wikipedia.org, and the dynamic-DNS provider freemyip.com. A candidate that is itself a popular site is more likely to be a hub or shared service. This membership is a reason to inspect and possibly down-weight the result, not proof that the domain is benign.

Two-sided link score

A two-sided score subtracts exposure to the popularity baseline from exposure to feed indicators, with each side normalized by the size of its seed set:

net = F.col("bad_links") / n_bad_seed - F.col("good_links") / n_good_seed

This is the one-hop form of two-sided label propagation: links to the popularity baseline offset links to feed indicators. It demotes the popular link targets that topped the raw ranking. Rounded to two decimal places, blogspot.com falls to a net score of -0.26, backlinks.sbs to -0.22, and worldbusinesspromote.com to -0.18, because each of them links to far more popular domains than feed indicators.

Fraction of links to feed indicators

Raw link counts still reward domains with many outbound links, because a link farm with millions of out-links will touch many feed indicators by volume alone. The better signal is the fraction of a domain’s links that go to feed indicators, which needs the total out-degree:

cand = cand.withColumn("bad_frac", F.col("bad_links") / F.col("out_degree"))

By this measure the hubs and farms collapse to near zero: blogspot.com has a bad fraction of 0.0003 and backlinks.sbs 0.0007, because they link to everything. The high-fraction tail is a different population: machine-generated junk such as 031230708.xyz (bad fraction 0.80), and small, likely-compromised legitimate sites such as knva.org and mipsm.org, where the few outbound links point mostly at known-bad domains. A high bad fraction on a domain with only a handful of links is weak evidence. The ranking below uses 10 links to feed indicators as a working minimum.

Add a freshness feed

The graph’s main limit, as was mentioned at the beginning of the post, is freshness: it can miss new and short-lived domains, which is where security cares most. Two more datasets let us measure that gap and close part of it.

The first is WHOIS registration dates. On this workspace the WHOIS data covers mostly established and popular domains, so it says little about the feed: only 482 of the 170,961 known-bad domains that are absent from the graph have a WHOIS record at all. In this dataset, WHOIS and the link graph mostly cover established domains and provide little additional coverage for this feed.

The second is new_sites, a daily feed of newly-observed domains, 133.7 million of them from 2021 to September 2026. This is the freshness layer the graph lacks. It is a partial feed: the free service used here reports about 70,000 new domains per day, which is a fraction of all registrations, so its coverage is a lower bound and a complete feed of newly-registered domains would recover more. Even with it, measured against the known-bad feed, the two sources barely overlap and so they add up well: the graph contains 12.4% of the known-bad domains, new_sites contains 30.8%, and together they reach 39.3%. The freshness feed recovers 52,468 known-bad domains that the graph never saw.

The comparison also shows an association with age. Among the feed domains that appear in new_sites, the ones absent from the graph are measurably newer than the ones present: a median first-seen age of 572 days against 824 days, and 83.4% first appeared since 2024 against 59.2%. This supports freshness as one reason for missing coverage, but it does not explain every absent domain.

This gives a two-dimensional triage that neither source gives alone. Combine the graph reputation with the freshness flag: a domain that is absent or weak in the graph and also recently seen in new_sites has no observed link history and is relatively new, which is the highest-priority profile. A domain that is established and not recently seen is the opposite and is a lower-priority profile. It can still represent abuse of a legitimate service.

When this is useful

The web graph is a reputation and triage layer, not a detection engine. Presence and in-degree give a cheap measure of how established a domain is. Propagation from known-bad seeds gives a proximity-to-bad score that surfaces new candidates, and it is strongest on cross-linked ecosystems such as search-engine and affiliate spam. Both are features for ranking and prioritizing domains, and both distort on popular domains, which is why the popular-site baseline and the bad-fraction normalization matter: they reduce the influence of popular link targets.

The main limit is structural. Most feed domains are absent from the graph or link-isolated within it. Some are new or short-lived; others can be missing because of crawl coverage or because they do not publish links. The freshness feed covers part of that gap. Combined with the graph, it more than triples feed coverage, but a large share of the feed is still invisible to both. For those domains, other data is needed. The infrastructure graph of shared name servers, hosting, and certificates is a different data source and a separate topic.

One extension remains natural. The one-hop propagation and one-hop offset can both be replaced with full iterated propagation, closer to the production BadRank and TrustRank systems that score web reputation this way. This would let a bad or good rating travel more than a single link.

All of the results above were produced on Databricks with graphframes and plain Spark joins over the graph tables. The source code for Common Crawl ingestion and code for graph analysis is in the repository.

Cross-post from blogspot…

Databricks SQL now has a function for this operation: time_bucket. This post continues the series about new Databricks SQL features that are useful for cybersecurity. In this post, we'll look at how the time bucketing was done before, what changes with the introduction of time_bucket, and how it will help you to migrate queries/rules from other SIEMs. All queries in this post were run on a serverless Databricks SQL warehouse over the OCSF gold tables in a Lakewatch workspace.

How we bucketed time before

Before time_bucket was added, there were three common approaches, and each of them has a problem.

The first one is to use date_trunc function. It works well for whole units (minute, hour, day, week, month), but there is no way to ask for 5, 10, or 15 minutes. And if you try, the query does not fail - an unknown unit will just return NULL:

SELECT date_trunc('15 minutes', TIMESTAMP '2026-10-16 10:07:00');
-- NULL

When you do this in an aggregation, this is easy to miss: all rows will just go into a single NULL bucket.

The second approach is to perform arithmetic operations with Unix seconds. This is the most common pattern in existing queries, and LLM-based translations of KQL’s bin(TimeGenerated, 5m) often use it:

-- returns a STRING, not a TIMESTAMP
from_unixtime(unix_timestamp(time) - unix_timestamp(time) % 300)
-- returns a TIMESTAMP, but is hard to read and easy to get wrong
timestamp_seconds(floor(unix_seconds(time) / 300) * 300)

The first query returns a string, so you need to do one more cast before you can work with it. The second form is correct, but the bucket size is hidden in the arithmetic. For buckets that are not aligned to the epoch (shifts, fiscal quarters, weeks that start on Monday) you need to do more arithmetic operations.

The third approach is the window function. It accepts any fixed duration, and it can also create overlapping (sliding) windows. But it returns a struct (window.start and window.end) instead of a timestamp (although it's a huge issue, IMHO), and it does not support months or years. For simple bucketing, a function that returns a timestamp is easier to read and to join on.

The time_bucket function

The time_bucket function receives a bucket size, a timestamp, and an optional origin, and returns the start of the bucket that contains the timestamp:

time_bucket(bucketSize, ts [, origin])

• bucketSize is a constant interval: day-time intervals (INTERVAL 15 MINUTES, INTERVAL 7 DAYS) or year-month intervals (INTERVAL 1 MONTH, INTERVAL 3 MONTHS).
• ts is a value or column of TIMESTAMP or TIMESTAMP_NTZ, and the produced result will have the same type.
• origin is a constant timestamp that defines where the bucket grid starts. By default, it starts at 1970-01-01 00:00:00 (Unix epoch).

Examples:

SELECT
  time_bucket(INTERVAL 15 MINUTES, TIMESTAMP '2024-01-01 11:27:00'),
  -- 2024-01-01 11:15:00
  time_bucket(INTERVAL 1 HOUR, TIMESTAMP '2024-01-01 11:27:00',
              TIMESTAMP '1970-01-01 00:30:00'),
  -- 2024-01-01 10:30:00  (hours that start at :30)
  time_bucket(INTERVAL 3 MONTHS, TIMESTAMP '2026-10-18 15:30:00',
              TIMESTAMP '1970-02-01 00:00:00');
  -- 2026-08-01 00:00:00  (fiscal quarters that start in February)

The next figure shows the difference between date_trunc and time_bucket: on the same 54 events, date_trunc can only give you buckets equal to hours, while time_bucket gives you buckets of any size you need. (Inspired by Kent Marten's LinkedIn post).

Example: threshold detections per window

Many detection rules have the form “N or more events from one entity within M minutes”. With time_bucket, the window is clearly visible in the query:

FROM authentication
|> WHERE time >= current_timestamp() - INTERVAL 1 HOUR
     AND status_id = 2                -- Failure
     AND src_endpoint.ip IS NOT NULL
|> AGGREGATE COUNT(*) AS failures, COUNT(DISTINCT user.name) AS users
   GROUP BY src_endpoint.ip AS src_ip,
            time_bucket(INTERVAL 5 MINUTES, time) AS bucket_start
|> WHERE failures >= 20

Compare it with the same GROUP BY written with from_unixtime(... % 300): the logic is the same, but here the reader sees "5 minutes" directly, and bucket_start is a real timestamp that you can use in the alert output.

Fixed buckets have one known weakness: a burst that crosses a bucket edge is split into two or more buckets, and all parts can stay below the threshold. The origin argument gives a cheap fix: we count the same events again on a second grid that is moved by half a bucket. To show this, we generate a burst of 20 failed logins between 10:03:00 and 10:06:48:

WITH burst AS (
  SELECT '203.0.113.7' AS src_ip, ts
  FROM (SELECT explode(sequence(TIMESTAMP '2026-10-16 10:03:00',
                                TIMESTAMP '2026-10-16 10:06:48',
                                INTERVAL 12 SECONDS)) AS ts)
)
SELECT 'grid :00' AS grid, time_bucket(INTERVAL 5 MINUTES, ts) AS bucket_start,
       COUNT(*) AS failures
FROM burst GROUP BY ALL
UNION ALL
SELECT 'grid :02:30',
       time_bucket(INTERVAL 5 MINUTES, ts, TIMESTAMP '1970-01-01 00:02:30'),
       COUNT(*)
FROM burst GROUP BY ALL

In a rule, you combine both grids with UNION ALL and keep one row per source IP (i.e., with QUALIFY ROW_NUMBER() OVER (PARTITION BY src_ip ORDER BY failures DESC) = 1). With 2 grids, a burst that lasts at most half of the bucket size (2.5 minutes here) is always inside one bucket of one of the grids. Longer bursts can still be split. If you need an exact sliding window for every event, use a window function with RANGE BETWEEN INTERVAL 5 MINUTES PRECEDING AND CURRENT ROW. It is more precise but also more expensive for large tables.

Example: beaconing and persistence

A common beaconing hunt computes the gap between consecutive connections for each source and destination pair, and looks for a small standard deviation of the gap (a regular, machine-like cadence). On our lab data (2.09 million network_activity rows over 7 days), the classic query with gap_stddev < 30 seconds finds two pairs:

The data also contains a third implant that calls home about once per hour with random jitter. Its gaps are between 47 and 71 minutes, so the standard deviation is about 7 minutes, and the query does not find it. Existing C2 frameworks often add jitter to beacons to avoid this type of detection.

A bucket-based view tolerates jitter better. Instead of the exact gaps, we count the hourly buckets in which the pair was active, and divide this number by the number of hours between the first and the last connection:

FROM network_activity
|> WHERE time >= current_timestamp() - INTERVAL 7 DAYS
     AND src_endpoint.ip IS NOT NULL AND dst_endpoint.ip IS NOT NULL
|> EXTEND time_bucket(INTERVAL 1 HOUR, time) AS hour_bucket
|> AGGREGATE
     COUNT(DISTINCT hour_bucket) AS active_hours,
     COUNT(*) AS connections,
     MIN(time) AS first_seen,
     MAX(time) AS last_seen
   GROUP BY src_endpoint.ip AS src_ip, dst_endpoint.ip AS dst_ip
|> EXTEND TIMESTAMPDIFF(HOUR, time_bucket(INTERVAL 1 HOUR, first_seen), last_seen) + 1
          AS span_hours
|> EXTEND ROUND(active_hours / span_hours, 2) AS active_ratio,
          ROUND(connections / active_hours, 1) AS calls_per_active_hour
|> WHERE span_hours >= 24 AND active_ratio >= 0.7
|> ORDER BY active_ratio DESC, span_hours DESC

Now all three implants are in the result. Read the two last columns together: active_ratio shows persistence, and calls_per_active_hour shows the cadence. The 1-minute and 5-minute beacons fill every hour, so they have a ratio of 1.0 and 60 or 12 calls per hour. The hourly beacon has a lower ratio (0.88) because the jitter sometimes moves 2 calls into one hour and leaves the next hour empty. It has about 1 call per active hour. For this reason, the filter is >= 0.7 and not >= 0.9. If you want the ratio of a jittered hourly beacon to be closer to 1.0, use 2-hour buckets.

The first version of this hunt was sorted only by active_ratio, and the hourly beacon was ranked last, after the two fast beacons. The cadence column fixed the ranking. Always-on services (update servers, EDR and telemetry back-ends) also have a high ratio, so you still need an allow-list, or a check of how many hosts talk to the same destination.

Migrating queries from other SIEMs

Each major SIEM query language has a time-bucketing operator, and most of them accept any bucket size. Before time_bucket, the translation to Databricks SQL was either date_trunc (for whole units only) or Unix-seconds arithmetic. Now we have a direct mapping:

Here is a typical Sentinel analytics rule

SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != 0
| summarize failures = count() by bin(TimeGenerated, 15m), UserPrincipalName
| where failures > 10

and its translation to the OCSF authentication table:

FROM authentication
|> WHERE time > current_timestamp() - INTERVAL 1 HOUR
     AND metadata.product.name = 'SignInLogs'
     AND status_id = 2
|> AGGREGATE COUNT(*) AS failures
   GROUP BY time_bucket(INTERVAL 15 MINUTES, time) AS bucket_start,
            user.name AS user_principal_name
|> WHERE failures > 10

There are a few details where a direct mapping can give different numbers:

• Weeks. KQL counts time from 0001-01-01, which was a Monday, so bin(TimeGenerated, 7d) gives weeks that start on Monday. The default origin of time_bucket is 1970-01-01, which was a Thursday. For weeks that start on Monday, pass a Monday as the origin: time_bucket(INTERVAL 7 DAYS, time, TIMESTAMP '1970-01-05') (or use date_trunc('WEEK', time)). For 2026-10-18, the default origin returns Thursday 2026-10-15, and the Monday origin returns Monday 2026-10-12, which matches the KQL results.
• Time zones. Splunk and Sumo Logic create day buckets in the user’s time zone. With a TIMESTAMP argument, time_bucket creates day, month, and year buckets in the session time zone, which is usually UTC. If the analysts of the original SIEM worked in another time zone, the daily counts would shift by some hours. Set the session time zone, or convert the timestamp before you bucket it.
• Alignment options. Splunk bin ... aligntime=@h+30m and KQL bin_at map to the origin argument.

I added these mappings to the Lakewatch migration skills (covered in a separate post) and compared the resulting translations with and without the new guidance. Without it, the translations used from_unixtime string buckets and epoch weeks that start on Thursday. The Splunk translation generated calls to date_bin and date_trunc('15 minutes'), resulting in "date_bin does not exist in Databricks SQL" and date_trunc('15 minutes') returning NULL. With the guidance, the translations used time_bucket with the correct origin.

Limitations

There are some things to know before you use the function:

• It requires Databricks Runtime 19 or later (or a current channel for Databricks SQL warehouse). On older Databricks runtimes, use timestamp_seconds(floor(unix_seconds(time) / N) * N).
• bucketSize and origin must be constants. If you pass a column as origin, the query will fail with NON_FOLDABLE_INPUT, so you cannot use a separate grid for each entity (for example, buckets that start at the first event of each incident).
• Buckets are aligned to 1970-01-01 by default, which matters for weeks and for any size that does not divide evenly into a day.
• The function only maps a timestamp to a bucket. It does not create empty buckets, so for gap filling (i.e., KQL make-series, Splunk timechart with zeros) you still generate the buckets with sequence() and join the data to them.
• Fixed buckets have edges. For threshold rules, use 2 grids or a sliding window, as shown above. The window function with a slide duration (for example, window(time, '10 minutes', '5 minutes')) is another way to get overlapping buckets.

Wrap up

Everything that time_bucket does was possible before with date_trunc or arithmetic on Unix seconds. This function replaces the error-prone arithmetic with a readable function call. It accepts any interval size, returns a real timestamp, and its origin argument handles shifted grids, Monday-starting weeks, and fiscal quarters. For security teams, it helps in threshold detections, beaconing, and persistence hunts, etc. It helps a lot with migrating queries from KQL, SPL, etc.: bucketing operators are very common there, and now they have a direct equivalent in Databricks SQL.

References

• time_bucket function (Databricks documentation).
• bin() function and bin_at() function (Kusto Query Language documentation).
• Blog series: New Databricks SQL functionality applied for cybersecurity:
• Detecting attack sequences in Databricks SQL with MATCH_RECOGNIZE, Part 1, Part 2.
• Native IP functions for threat detection and hunting on Databricks.
• Why Databricks SQL pipe syntax is great for cybersecurity work.
• Parsing timestamps without the pain with new Databricks SQL functions.

Cross-post from blogspot…

Where are table names used in the Genie Agent?

Usually, people create an initial version of the agent in the Databricks UI (although you can also use an agent + Databricks skills), and if you want to deploy it with DABs, then you can extract the agent definition with databricks bundle generate genie-space. This command does two things:

• writes a YAML file with DABs resource declaration into the resources directory;
• writes a file with a .geniespace.json extension into the src directory; it contains a serialized agent definition.

The resource declaration references that JSON file via file_path. I.e., resources/lakewatch_security_operations.genie_space.yml contains the following:

resources:
  genie_spaces:
    security_operations:
      title: "Lakewatch Security Operations"
      warehouse_id: ${var.warehouse_id}
      file_path: ../src/lakewatch_security_operations.geniespace.json
      ...

The serialized agent uses fully-qualified Unity Catalog names (catalog.schema.table). And references to tables aren't only in the list of tables. For example, our “Lakewatch Security Operations” agent works with 22 OCSF gold tables, and its definition has 72 references to those tables:

Some of these references are table names in SQL (FROM fe_lakewatch_catalog.gold.authentication), and some of them are column references that use fully qualified table names (fe_lakewatch_catalog.gold.authentication.status_id = 2). Table names can also be used in text instructions and table descriptions.

In a real project, the development, staging, and production environments usually use different catalog, schema, or table names. When you deploy an example from above to a dev target that should read from dev_catalog.gold, and you don't replace references, the agent will still try to query the referenced tables and fail if the tables aren't visible in the given environment.

Why the built-in functionality isn’t applicable

One of the initial recommendations I received was to put the agent definition directly in the resource file. The serialized_space field accepts the serialized JSON as a YAML string, and in this case DABs interpolates variables in it, so ${var.catalog}.gold.authentication works (it was checked with CLI v1.19.0). So it works, but it's difficult to maintain:

• the definition of a tuned agent could be large (i.e., my example genie agents have sizes from 15 KB to 136 KB);
• a diff of a long JSON string in YAML is difficult to review;
• bundle generate genie-space always writes a separate JSON file. After each sync from the UI you must convert the file again and put the variables back.

The second try was to put bundle variables into the JSON file, for example ${var.catalog}.gold.authentication. This does not work: DABs reads the file into the serialized_space field but does not interpolate variables in it (at least not in CLI v1.19), so the agent receives ${var}.catalog} text as is. Even if this worked, the JSON file stops being a valid agent definition, and the round trip with the databricks bundle generate genie-space --resource <key> --force (edit in the UI, then sync back to Git) overwrites these placeholders.

The third approach was to perform a text replacement in a deployment script, i.e., using sed. This works for simple cases, but there were a few things that make it fail later:

• A rename of a schema and a rename of a single table in it must not interfere. If you run two replacements one after another, the output of the first can match the second.
• A replacement must check the name’s boundaries on both sides. For a table rename, people often replace main.intel.oss_iocs. with a dot at the end, so that the replacement does not change another table such as main.intel.oss_iocs_archive. But this pattern does not find FROM main.intel.oss_iocs WHERE ..., where a space follows the name. Without the dot, the replacement also changes the other table. (Of course, we can use word-boundary anchors in regexes, but you need to remember about them).
• Unity Catalog names are case-insensitive by default, and Genie sometimes writes them with backticks (`main`.`gold`.`authentication`), i.e., when they contain special characters.
• The Genie API requires that the list of tables be sorted by identifier. If a rewrite changes the order of table names, the API rejects the request with the error data_sources.tables must be sorted by identifier.
• Join specs use the short table name as a qualifier (authentication.actor.user.uid = account_change.actor.user.uid), and SQL snippets (filters and measures) can do the same. In our IoC agent, the filter “URL IoCs only” is oss_iocs.ioc_type = 'URL'. If you rename a table, you must also change these qualifiers. Genie does not check the SQL of snippets when you deploy the agent, so a filter with the old name is accepted, and it will fail only when Genie uses it to generate a SQL query.

I already had a script for importing of genie agents, but it didn’t integrate well with DABs. I also had an internal project that used PyDABs, but it was hard to reuse across multiple projects — you need to copy files manually, making bug fixes causing a headache, etc. So I decided to create a reusable implementation.

Python mutators in DABs

DABs supports the use of Python code (via the databricks-bundles package, often referred to as PyDABs) for two tasks: to define resources in Python, and to change resources that are already defined in YAML. The second task is based on the concept of mutators. A mutator is a function that receives the bundle configuration (with all resolved variables for the current target) and a resource of a given type, and returns a modified copy of that resource. The CLI calls the mutator for every resource of a given type during validate and deploy.

For Genie agents, the CLI first reads the file_path file into the serialized_space field, and then calls the mutators. This means that a mutator sees the full agent definition and can change it for each target.

Implementing the genie_agent_retarget mutator

This mutator is implemented in the pydabs-genie-agent-retarget repository. It has a single Python module, genie_agent_retarget. It reads rewrite rules from the bundle variable named genie_table_mapping. Each rule maps a source prefix to a target prefix. A single rule can define one of 3 levels:

The source and the target of a rule must have the same number of parts. If they do not, databricks bundle validate fails and shows the failing rule:

ValueError: rule 'main.gold' -> 'dev': source and target must have the same number of parts

We’re starting with the most specific rules: a table rule is used before a schema rule, and a schema rule is used before a catalog rule. A part of a rule can be in backticks — the same as in SQL. In YAML, a value cannot start with a backtick, so put such a rule in quotes: "`my-catalog`.gold": "`other-catalog`.gold".

The mutator works as follows:

1. It parses the serialized agent as JSON and checks every string value in it. Table names can be used in various places (table identifiers, join specs, SQL snippets, example and benchmark SQLs, instructions, descriptions), and additional fields could be added in future versions of the agent serialization format. This mutator doesn’t use a predefined list of fields, so the current implementation will also work for new fields that could be introduced in the future.
2. In each string, it finds names with 3 parts, where each part is a plain identifier or a backtick-quoted identifier. A longer chain such as catalog.schema.table.column is also a match (the first 3 parts are the table). A name that continues a longer chain on the left (for example, a.catalog.schema.table) doesn't match, and names with 1 or 2 parts (struct fields such as actor.user) are never changed.
3. For each name, it finds the most specific rule and rewrites that name once. The rewrite is done in one pass, so rules never chain.
4. If a table rule renamed a table, it also renames the short qualifiers of that table in the SQL fields: join specs, SQL snippets, example SQL, and benchmark SQL. It does not do this in free text, and it does not do it when another table within the agent has the same short name, because the qualifier is then ambiguous.
5. At the end, it sorts data_sources.tables by identifier, and returns the agent with an inline serialized_space (we also need to reset the file_path to None, because these two fields are mutually exclusive, so DABs will generate an error if both are specified).

If the rules do not change anything, the mutator returns the resource unchanged. So a target that uses the same table names as specified in the serialized content will get the original file.

An example bundle

The repository has an example bundle in the example/ directory. It deploys 3 agents from my previous threat analyst blog post: “Analysis of CVEs”, “Analysis of Indicators of Compromise”, and “Lakewatch Security Operations”. The definitions were created against fe_lakewatch_catalog.lakewatch_field (CVE and IoC tables) and fe_lakewatch_catalog.gold (OCSF tables). The bundle has 3 targets:

• dev is in the same workspace. It moves the agents to other schemas, and it renames the IoC table from oss_iocs to iocs.
• azure is in another workspace, where the same data is in other catalogs: the OCSF tables are in lw_aottdb_ws.gold, the CVE table is in cybersecurity.datasets, and the IoC table is cybersecurity.threatintel.iocs.
• prod uses the names from the files without changes.

The profiles, catalogs, and schemas in the example are from my environments. To try it in your workspace, change them in a target to point to your tables (the README explains what to change).

The project has the following layout:

example/
├── databricks.yml
├── pyproject.toml
├── resources/
│   ├── analysis_of_cves.genie_space.yml
│   ├── analysis_of_indicators_of_compromise.genie_space.yml
│   └── lakewatch_security_operations.genie_space.yml
└── src/
    ├── analysis_of_cves.geniespace.json
    ├── analysis_of_indicators_of_compromise.geniespace.json
    └── lakewatch_security_operations.geniespace.json

The files have the same format as the output of the databricks bundle generate genie-space command. The original JSON files weren't changed. In the YAML files, we only need to replace two values that bundle generate writes as constants: warehouse_id (now ${var.warehouse_id}) and parent_path. All other configuration is in databricks.yml:

bundle:
  name: genie-agent-retarget-example
  engine: direct

python:
  venv_path: .venv
  mutators:
    - 'genie_agent_retarget:retarget_genie_spaces'
include:
  - resources/*.yml
variables:
  ocsf_schema:
    default: fe_lakewatch_catalog.gold
  cve_schema:
    default: fe_lakewatch_catalog.lakewatch_field
  iocs_table:
    default: fe_lakewatch_catalog.lakewatch_field.oss_iocs
  warehouse_name:
    default: "Lakewatch Warehouse"
  warehouse_id:
    lookup:
      warehouse: ${var.warehouse_name}
  genie_table_mapping:
    type: complex
    default:
      fe_lakewatch_catalog.gold: ${var.ocsf_schema}
      fe_lakewatch_catalog.lakewatch_field: ${var.cve_schema}
      fe_lakewatch_catalog.lakewatch_field.oss_iocs: ${var.iocs_table}
targets:
  dev:
    default: true
    mode: development
    workspace:
      profile: lw-field
    variables:
      ocsf_schema: fe_lakewatch_catalog.genie_demo_ocsf
      cve_schema: fe_lakewatch_catalog.genie_demo_intel
      iocs_table: fe_lakewatch_catalog.genie_demo_intel.iocs
  azure:
    mode: development
    workspace:
      profile: az-alexott
    variables:
      ocsf_schema: lw_aottdb_ws.gold
      cve_schema: cybersecurity.datasets
      iocs_table: cybersecurity.threatintel.iocs
  prod:
    mode: production
    workspace:
      profile: lw-field

There are two things to note about this configuration:

• The keys of genie_table_mapping are the table names used in the JSON files, so they don't change between targets. The values use normal bundle variables, and DABs resolves them before it calls the mutator. We use one variable for the location of each data source (a schema or a table), so a target only sets these variables, just as for jobs and pipelines.
• A complex variable is replaced as a whole when a target overrides it. If one target needs a different set of rules, it is better to override the simple variables that the rules use.

The mutator must be available in the bundle’s virtual environment. With uv, just add it to pyproject.toml (you can also copy genie_agent_retarget.py next to databricks.yml, because it has no other dependencies):

[dependency-groups]
dev = [
    "databricks-bundles==1.19.0",
    "pydabs-genie-agent-retarget",
]

The test results

databricks bundle validate shows the result of applying the mutator, so you can check the rewritten names before you deploy:

databricks bundle validate -o json | jq -r \
    '.resources.genie_spaces[].serialized_space | fromjson
     | .data_sources.tables[0].identifier'
fe_lakewatch_catalog.genie_demo_intel.cves_v5
fe_lakewatch_catalog.genie_demo_intel.iocs
fe_lakewatch_catalog.genie_demo_ocsf.account_change

After performing the databricks bundle deploy, we read the agent back via the Genie API. All 94 references across the 3 agents (11 + 11 + 72) pointed to the new schemas, and there were no references left to the original schemas. We also validated that Genie Agent worked by asking each agent a question. I.e., the IoC agent wrote this SQL for “How many IoCs are in the dataset, broken down by type?”:

SELECT `ioc_type`, COUNT(*) AS `ioc_count`
FROM `fe_lakewatch_catalog`.`genie_demo_intel`.`iocs`
WHERE `ioc_type` IS NOT NULL
GROUP BY `ioc_type`
ORDER BY `ioc_count` DESC

The implementation was also tested in other workspaces with different combinations of catalogs and schemas.

Limitations

There are some limitations that you need to know about:

• The rules work only with 3-part names. If the SQL in an agent uses unqualified table names (FROM authentication), the mutator does not change them, and the query uses the session's default catalog and schema. Genie Agent by default writes fully-qualified names into serialized content, so this is mainly a problem for SQL that was written by hand.
• A catalog-level rule rewrites any 3-part name that starts with this catalog, in any field. If your catalog has the same name as a struct column (for example, a catalog actor), a struct path such as actor.user.name is also rewritten. In this case, use schema-level rules to avoid this problem.
• The Genie API verifies that all the agent’s tables exist when it creates or updates the agent. If a table does not exist, the deployment fails with an error such as Table 'main.gold.authentication' does not exist. So in a new environment, the tables must exist before you deploy the agent. If the same bundle includes the jobs that create these tables, keep the agents in a separate bundle and deploy it after the jobs run for the first time.
• The mutator only changes table references. Other values in the definition, such as the warehouse ID, are specified as fields in the resource declaration, and you set them with bundle variables (as with warehouse_id above).
• Right now we retarget only tables/schemas/catalogs, and there is no support for other data sources (i.e., UC Volumes) yet.
• bundle generate genie-space --watch downloads the deployed definition. If you edit the agent in a target that uses other table names, the downloaded file contains those names. Either edit the agent in the target whose names match the original JSON files, or convert the file back with the command-line version of the same code:

python -m genie_agent_retarget src/x.geniespace.json --in-place \
  -m fe_lakewatch_catalog.genie_demo_ocsf=fe_lakewatch_catalog.gold

Conclusion

The serialized Genie agent allows us to store an agent in Git, but it binds the agent to the catalog and schemas where you created it. A Python mutator for DABs can rewrite these names for each target during deployment. With genie_agent_retarget, you register the mutator once, describe the differences between environments using table/schema/catalog rules, and keep the files from bundle generate unchanged. The code and the example bundle are in the pydabs-genie-agent-retarget repository. This package is also published to PyPI, so you can just refer to it in your Python project.

Crosspost from blogspot…

How the code is organized

The exporter is a single Go package in the exporter/ directory of the Databricks Terraform provider. The files are split per specific part of work: command-line handling, the resource model, the per-service logic, and code generation. Such a split makes it easier to find the right place for a change, so this section provides a map for anyone who wants to contribute to the exporter.

The entry point and the shared state are in the following files:

• command.go holds the exported Run() function that is called by the provider. It parses the command-line flags, sets up the client, and starts the export.
• context.go defines the importContext structure, which is the central state object for an export run. Almost every function takes it as the ic argument or struct instance. It implements the two-pass model (described in the references post): the worker goroutines that list and import objects, the Emit and Add methods that queue resources for processing, and the code that reads an object's state through either framework.
• ui.go has the small helpers for the interactive prompts (askFor, askFlag) used when you run the exporter interactively.

The resource model and the registry of supported types are separate:

• model.go defines the core data types: the importable structure described below, the resource type that represents a single discovered object, the reference type for dependencies, and the import-command generation.
• importables.go is the registry. It holds one importable entry per supported Databricks resource type in a large map. This is the first file you need to change when you add a resource.
• impl_<service>.go holds the per-service functions that the importable entries point at: the functions List, Import, Name, and other required functions for a service, such as impl_uc.go for Unity Catalog, impl_compute.go for compute, or impl_jobs.go for jobs. Keeping these out of importables.go keeps the registry readable: each entry stays close to a declaration, and the implementation lives next to its service.

Code generation is implemented in two files:

• codegen.go is the HCL generation pass, including unifiedDataToHcl(), the field rules, the variable generation, and the code that writes the .tf files.
• abstractions.go holds the framework-abstraction layer: the ResourceDataWrapper and SchemaWrapper interfaces and the FieldType enumeration. This is what hides whether a resource is SDKv2 or Plugin Framework, so the generation pass can treat both the same way. The “Two frameworks, one generator” section below describes how this is used.

The rest of the files are supporting functions:

• util.go, util_compute.go, util_scim.go, and util_workspace.go hold shared helpers, such as the service-enablement checks, name matching, and the effective fields helper.
• cloud_utils.go and node_type_mapping.go implement the -targetCloud conversions, which rewrite cloud-specific attributes and node types for a different cloud.
• logging.go sets up the log-level filtering and formatting.

Tests follow a fixed naming pattern: each *.go file has a matching *_test.go file, the per-service tests are in impl_<service>_test.go, and exporter_test.go holds the larger integration-level tests. A few patterns also have their own Markdown notes in the directory, for example, EFFECTIVE_FIELDS_PATTERN.md and COMPLEX_TYPES_HANDLING.md, which are worth reading before you work on the pattern they describe.

One structure per resource type

The exporter keeps the logic for importing a resource type separate from the logic that generates the HCL (HashiCorp Configuration Language) code. Each resource type is described by an importable structure that is tied to a specific Databricks Terraform resource. For most resource types, you set only a few fields: the Service, the List function (also optional), a Name function, an Import function, and whether the resource is WorkspaceLevel or AccountLevel, and the Depends references.

The full set of fields:

• Service is the logical group the resource belongs to (compute, notebooks, access, and so on). These are the values you pass to the -services and -listing command-line options.
• List (optional) lists the objects of the resource type. It emits each object's ID; the actual read happens later.
• Search (optional) finds the ID of an object referred to by a non-ID attribute, for example a user referenced by display name (users/groups/service principals are identified by SCIM ID).
• Name generates the Terraform resource name for one instance (the SDKv2 signature). NameUnified does the same for both frameworks through the ResourceDataWrapper abstraction described below.
• Import runs after the object's state is read, and emits additional objects: references to permissions, writes files to disk for init scripts, normalizes data, and so on.
• Depends declares references to other resources, which is what keeps the generated code referentially consistent. A separate post, Inside the Databricks Terraform exporter: referential integrity, describes this field in detail.
• Body customizes the generated HCL block. It is rarely needed, only for a few complex cases such as databricks_mount, where nested blocks depend on the storage URL.
• Ignore (optional) decides whether an object should be skipped.
• ShouldOmitField (optional) decides whether a field should be left out of the generated code, usually to drop computed attributes or default values. ShouldGenerateField (optional) forces a field to be generated regardless of its value (sometimes we need to generate zero values even for optional fields). Both have unified variants (ShouldOmitFieldUnified, ShouldGenerateFieldUnified) that work with both frameworks.
• ApiVersion (optional) sets the REST API version for the resource, for example, version 2.1 for databricks_job.
• AccountLevel and WorkspaceLevel mark where the resource can be exported.
• PluginFramework marks a resource that is implemented with the Terraform Plugin Framework rather than SDKv2.

This declarative model is what makes the exporter straightforward to extend: a new resource is mostly a new importable entry, not new code in the generator.

Adding a new resource

Adding support for a resource type follows a fixed set of steps:

1. Define a new importable in importables.go, and mark it as workspace-level, account-level, or both.
2. Assign it to a service. Reuse an existing one if it fits, or add a new service. Put the per-service functions in impl_<service>.go and the tests in impl_<service>_test.go.
3. Implement List to discover and emit instances (prefer the Go SDK's List method over ListAll).
4. Implement Import to emit the resource's dependencies, such as permissions or grants.
5. Add Depends to describe the references between this resource's fields and the resources they point at.
6. Add ShouldOmitField if some fields need to be omitted conditionally.
7. Add a unit test that checks the generated code. For resources that use the Go SDK, use qa.MockWorkspaceApply or qa.MockAccountsApply.
8. Update the support matrix and the service list in the documentation.

Emission between resources can go in both directions. A newly added resource can emit the existing resources it references: for example, databricks_data_quality_monitor emits the databricks_sql_table it monitors, and that table's own import then emits the table's schema and catalog. An existing resource can also be updated to emit a newly added one once the exporter supports it. I.e., databricks_job could emit a new resource that wasn't supported before.

References can also be ambiguous, for example, a table or schema name that exists in several catalogs — in these cases, you need to implement an IsValidApproximation function on the reference (again, see the references post).

From resource data to HCL

Code generation runs as a separate pass, after listing and import have finished for every resource (the two-pass model described in the references post). In the code generation pass, we iterate over the imported resources and, for each one, first check the resource’s Ignore function: a resource that Ignore rejects is skipped, and nothing is generated for it. For a resource that is kept, the exporter creates its block (a resource block, or a data block for a data source) and fills it in.

Filling in a block is the job of unifiedDataToHcl(), which is executed once per resource and iterates over the fields recursively. It's a two-step process: first, it decides which fields to write, then it writes each one.

The first step iterates over the resource schema and applies a fixed set of rules to every field. Fields are visited in reverse alphabetical order; the exporter keeps this order for a consistent and readable layout. For each field, the exporter reads the current value and decides whether to write it:

• Omission comes first. A resource can ShouldOmitField or ShouldOmitFieldUnified to drop a field; without one, the default rule removes computed attributes and values that are equal to the schema default.
• An unset or zero value is skipped, unless the field is required or holds a value that differs from its default. For Plugin Framework resources, an optional primitive that is still at its zero value is also skipped.
• ShouldGenerateField can force a field back in when the rules above would drop it (this is typically a workaround for fields that are incorrectly marked as optional in the OpenAPI spec and propagated to the Databricks Go SDK).

The second step writes each remaining field as HCL. Most of this logic is shared between the two provider frameworks; the one framework-specific difference is described in the next section. The output is shaped by the field type:

• Strings and integers pass through the reference step (described below) and are written as raw tokens, so a raw identifier can become a Terraform reference.
• Booleans and floating-point numbers are written directly as values.
• A map becomes an HCL object with its keys sorted.
• A list or a set recurses into a nested structure: each element is written one by one, and the same field rules apply against individual values.

The reference step is a single call, ic.reference(), executed for each string and number value. This is the one place where a raw identifier can be replaced with a Terraform reference to another resource, a path to a generated file, or a variable. How that replacement is resolved is the subject of the references post.

After the fields, a top-level resource can also get its depends_on list, built from the explicit dependencies that the import step recorded.

Two frameworks, one generator

The generation loop described above is shared, but the provider has two kinds of resources in it. The Databricks provider is moving its resources from the older Terraform SDKv2 to the newer Terraform Plugin Framework, and both kinds exist in the provider at the same time. The exporter generates code for both through the single unifiedDataToHcl() function.

The unifiedDataToHcl() function reads resource data and schema through two interfaces, ResourceDataWrapper and SchemaWrapper, which hide whether the underlying resource is SDKv2 or Plugin Framework. The field walk, omission rules, variable references, and depends_on handling is all shared - only the step that writes a single field is framework-specific.

There is one difference you can see in the generated code. SDKv2 writes a nested object as a block, and the Plugin Framework writes it as an attribute:

# SDKv2: nested structure as a block
evaluation {
  # ...
}

# Plugin Framework: nested structure as an attribute
evaluation = {
  # ...
}

When you add a Plugin Framework resource, you set PluginFramework: true, use NameUnified instead of Name, and use the unified ShouldOmitFieldUnified and ShouldGenerateFieldUnified callbacks for any custom field logic.

Effective fields and complex types

Some Plugin Framework resources have a specific problem. For example, the Lakebase database instance API uses an “effective fields” pattern: you configure an input field such as node_count, but the API's read operation does not return node_count. Instead, it returns effective_node_count. The exporter reads the resource through that read operation, so it sees only the effective_* fields, while the generated configuration needs the input fields. This is typically needed to support default values for some attributes - we can't return a new value for an attribute that wasn't provided by the user, otherwise we'll get an error.

The exporter handles this by copying each effective_* value back to its input field during import. A resource enables this by calling a helper from its Import function, and by using a ShouldOmitFieldUnified callback that omits an input field when its value is zero or equal to the default, so the generated code stays minimal. The zero check uses reflection rather than the schema's “is set” check, because a field set to false or 0 is still set, and would otherwise be written out unnecessarily. Required fields are never omitted, even when their value is zero.

Simple types (strings, numbers, booleans) copy directly. Complex types, such as a list of objects like custom_tags, do not: the API returns native Go SDK types, while the Plugin Framework expects its own type system, and there is no direct conversion between the two. For these, the exporter uses a converter-based helper (copyEffectiveFieldsToInputFieldsWithConverters) that converts the Terraform state to a Go SDK struct, copies all Effective* fields by reflection and converts back through the provider's existing type converters. This one call handles both simple and complex types, and it is the recommended approach for Plugin Framework resources that use effective fields.

References

• Introduction to the Databricks Terraform exporter is the overview: what the exporter does and how to run it.
• Inside the Databricks Terraform exporter: referential integrity describes the Depends field and reference resolution in detail.
• Speeding up the Databricks Terraform exporter describes the parallel and incremental export.
• Exporter-specific AGENTS.md also provides quite a lot of implementation details, so you can read it as well.

Cross-post from blogspot…

Why generation of correct references is hard

Databricks resources may point to other. A group has members, a job triggers a dashboard update, a secret belongs to a secret scope, and a cluster policy can embed several secrets. When you read these objects through the API, each reference is a raw identifier: a group SCIM ID, a dashboard unique ID, or a secret scope name.

If the exporter wrote those raw identifiers into the generated code, the code would not be portable. The identifiers are often unique to the source workspace, and applying the configuration in another workspace would either fail or point at the wrong object. What you want instead is code in which one resource refers to another via a Terraform reference, for example, databricks_group.my_group.id. Terraform then resolves the reference at apply time, in the target workspace, and the dependency stays correct. Plus, when you create resources, you need to create them in the correct order, like the dashboard should be created before a job - if you don't have references between resources, Terraform will attempt to create many of them in parallel, leading to creation errors.

Referential integrity is the property that makes this work: the generated configuration correctly represents the state of the source, and applying it produces an equivalent workspace object. The rest of this post describes how the exporter does it.

Declaring dependencies

Each resource type in the exporter is described by an importable structure. Its Depends field lists the references that the resource has to other resources. For example, databricks_group_member connects a group to one of its members (it could be a user, service principal, or another group), so it declares references for both fields:

"databricks_group_member": {
    Service: "groups",
    Depends: []reference{
        {Path: "group_id", Resource: "databricks_group"},
        {Path: "member_id", Resource: "databricks_user"},
        {Path: "member_id", Resource: "databricks_group"},
        {Path: "member_id", Resource: "databricks_service_principal"},
    },
},

Each reference says which field holds the value (Path), which resource type the value points at (Resource), and, optionally, how to match it. By default, the match is based on the target resource's ID (the id attribute), but you can match on another attribute using the Match field and choose a match strategy with MatchType (see below). A reference can also point to a generated file or a variable instead of another resource.

The same path can appear more than once. member_id points to a user, a group, or a service principal, so the exporter declares one reference per possible type; the resolver tries them in order until one matches. This is common because a Databricks field often accepts several kinds of objects (but all of them should have the same type).

Most resources define only a few references, but some require many more. The dependency list for databricks_job is about 200 lines with more than 100 individual references, covering task notebooks, libraries, clusters, cluster policies, warehouses, notification destinations, referenced jobs, etc. That list doesn't even include the for_each task variants: the exporter generates those from the base list by rewriting each task path, rather than writing them out by hand. So while the mechanism is relatively simple, the reference definitions for some resources can be complex.

Two passes: approximate, then resolve

The exporter cannot resolve a reference while it imports a single resource, because the target resource may not have been imported yet. The reference is resolved in two passes.

During listing and import, the exporter reads each resource and adds it to an in-memory “state approximation”. This approximation is the set of resources it has already seen, grouped by resource type and indexed by attribute value, so that a lookup by resource type, attribute, and value is fast. All listing and import must finish before code generation starts, because reference resolution needs the complete set of resources. This is the same barrier described in the performance post.

During code generation, the exporter walks each resource’s fields. For a field that has a matching reference in Depends, it takes the field value, looks up a matching resource in the state approximation (not always only there), and replaces the raw value with a Terraform reference to that resource.

With the initial pass, the intermediate state holds resources with raw identifiers, approximately like this (really, admins is generated as a data source, not as a resource):

resource "databricks_group" "admins" { // ID: abc
  display_name = "admins"
}

resource "databricks_user" "jane" { // ID: def
  user_name = "jane@example.com"
}
resource "databricks_group_member" "admins_jane" {
  group_id  = "abc"
  member_id = "def"
}

After the resolution pass, the raw identifiers are replaced with references:

resource "databricks_group_member" "admins_jane" {
  group_id  = databricks_group.admins.id
  member_id = databricks_user.jane.id
}

How the lookup works

The state approximation keeps, for each resource type, both a list of resources and a map from an attribute-and-value pair to the resource that has it. This map gives a direct lookup for most cases. When the exporter resolves a reference with an exact, case-insensitive, or regular-expression match, it first tries this direct lookup by the wanted attribute and value. If the direct lookup misses or the match is a prefix match, it falls back to iterating over all resources of the target type and comparing the attribute (which can be very slow for large workspaces with many notebooks, etc.).

Two conditions can reject a candidate that otherwise matches:

• The candidate resource is ignored (for example, filtered out of the export).
• The reference defines an IsValidApproximation function that returns true or false. This is used when a match on one attribute is not enough. For example, each Unity Catalog catalog has a default schema, and matching only on the schema name could point at the wrong catalog, so the corresponding IsValidApproximation function in databricks_schema implementation checks the parent catalog as well.

During an iterative search, the resolver continues to the next candidate after either rejection. An exact or regular-expression lookup normally uses the direct map and has only one candidate. Exact references that need to inspect several candidate sets SkipDirectLookup to true, which forces the iterative search.

When a match is found, the exporter emits a Terraform traversal for it, such as databricks_group.my_group.id. If the matched resource was generated as a data source rather than a managed resource, the reference points at the data source instead. This happens, for example, when Identity Federation is enabled, and account-level users, service principals, and groups are generated as data sources.

Match strategies

The default match compares the whole value against the target attribute. Some references need a different comparison, selected with MatchType:

• exact (the default): the value must match the attribute as-is.
• caseinsensitive: the value must match, ignoring character case (i.e., a user name).
• prefix: the attribute is a prefix of the value. A Databricks Git Folder path, for example, is a prefix for the notebook paths inside it.
• longestprefix: like prefix, but when several candidates match, the one with the longest matching prefix is chosen.
• regexp: a regular expression (in the Regexp field) extracts a match group from the value, and that match group is used for the lookup.

By default, all strategies except longestprefix stop at the first valid candidate. The search usually continues past a candidate only when it is ignored or when IsValidApproximation rejects it. Or there could be multiple references in a single value, as described in the next section.

In some cases, we even need to transform a value before the match. I.e., if we have two directories with the same prefix, i.e., /Shared/test and /Shared/test2, and the value to search is /Shared/test2/** (i.e., in SDP configuration) - we can't stop after a first prefix match because it will be wrong - we need to make sure that we match the second variant. To support such relatively rare cases, we allow specifying a transformation function as part of the reference struct. The name of the function is specified by the SearchValueTransformFunc field.

References embedded in a larger value

A reference is not always the whole value of a field. With a prefix or regexp match, only part of the string is a reference, and the rest is literal text. In that case, the exporter substitutes only the matched part and keeps the surrounding text, producing an interpolation such as "${databricks_repo.my_repo.path}/notebook".

Two reference options handle values that contain more than one reference:

• ContinueMatch tells the resolver not to stop after the first match, so multiple references to the same field are evaluated, and their substitutions are combined into a single value. This is used, for example, for a value that embeds both a variable for account ID and a resource reference.
• MultiMatch, together with a regexp match, substitutes every occurrence of the expression in the value, resolving each captured group to its own target. This is used for values that contain multiple references, such as multiple {{secrets/scope/key}} traversals within a cluster policy definition.

References to files

Some fields point at a file rather than another resource, which is common for notebooks, workspace files, libraries, init scripts, and some other resources. The resource’s Import function downloads the file into the export directory. A reference with File: true then rewrites the field to point at that local copy. For example, databricks_file declares:

"databricks_file": {
    Service: "storage",
    // ...
    Depends: []reference{
        {Path: "source", File: true},
        {Path: "path", Resource: "databricks_volume", Match: "volume_path", 
            MatchType: MatchLongestPrefix},
    },
},

and the exporter downloads the file and generates:

resource "databricks_file" "main_default_init_scripts_empty_init_script_sh_866a123d" {
  source = "${path.module}/uc_files/main/default/init-scripts/empty_init_script.sh"
  path   = "${databricks_volume.main_default_init_scripts.volume_path}/empty_init_script.sh"
}

The ${path.module}/ part stays a real Terraform interpolation, and the file name, which comes from the object path, is escaped so that it cannot break out of the string.

References to variables

Some values should not be hard-coded in the generated code, most importantly, secrets, which the API doesn’t return by default. A reference with Variable: true makes the exporter declare a Terraform variable for the field and reference that variable from the resource. For example, databricks_secret declares:

Depends: []reference{
    {Path: "string_value", Variable: true},
    {Path: "scope", Resource: "databricks_secret_scope"},
},

The exporter generates a variable declaration in the vars.tf:

variable "string_value_builtin_test1_dfd372d494" {
  description = ""
}

and put a reference to it into the resource:

resource "databricks_secret" "builtin_test1_dfd372d494" {
  string_value = var.string_value_builtin_test1_dfd372d494
  scope        = databricks_secret_scope.builtin_748a336cc8.id
  key          = "test1"
}

You should set the variable value before you apply, or, in the case of secrets, if you pass -export-secrets, the exporter writes the current values to terraform.tfvars file.

By default, each variable reference gets a unique name derived from the field. A reference can set VariableName to share a single variable across multiple resources. The account ID is a good example: account-level exports declare a single databricks_account_id variable and reference it from the provider block and from every account-scoped attribute, so you change the target account by editing a single value.

When this matters

For some exports, you may not notice any reference resolution: the generated code just works when applied to the same workspace. It becomes important when you move resources between workspaces, merge exports from several workspaces, or run repeated exports for a migration or a disaster recovery. In those cases, the difference between a raw identifier and a proper reference is the difference between a configuration that applies cleanly in the target workspace and a configuration that does not. The exporter resolves the references so that the generated code stays portable.

Cross-post from blogspot…

We need reproducible environments for our data and ML projects. We want to keep the platform available (with high availability and disaster recovery), promote data and ML pipelines safely across stages (dev/staging/prod), and let teams create new workspaces and resources without manual setup when needed.

Such environments are usually described with an Infrastructure-as-Code (IaC) tool, and Terraform is one of the widely adopted tools in that class. It uses the same language to describe the cloud infrastructure and to integrate it with other systems, including Databricks objects such as clusters, users, jobs, notebooks, and almost everything else in a workspace. (Yes, we have Databricks Automation Bundles (DABs) now, and they could be more convenient to data and ML engineers, but Terraform isn’t going away. I wrote about my position on Terraform vs. DABs almost two years ago.

The Databricks Terraform provider is used by many customers for various tasks: implementing disaster recovery (before managed disaster recovery becomes available), building reusable project templates for mesh product teams, promoting projects across environments, etc. Many customers who set up Databricks through the UI understand the benefits of IaC, but they worry they will have to re-implement everything in Terraform. That is a large task when there are many teams and many workspaces, and they have many objects inside them.

This post describes the Terraform exporter included in the Databricks Terraform provider. The exporter generates Terraform code for the resources that already exist in a workspace or a Databricks account, so you can start using Terraform without writing everything by hand. You can use it to manage an existing workspace, perform a full or partial migration between workspaces, or just back up the definitions of your deployed assets.

The exporter is built and maintained by the field team rather than by product engineering, and although it’s marked as experimental, many customers use it, sometimes at extreme scale (see Working with huge Terraform states). However, its interface or output can change or be removed in a future provider version, so we keep the experimental label.

Quickstart

Getting started with the exporter takes a few steps:

• Get the Databricks Terraform provider binary. Download the latest version for your operating system from the releases page, or reuse the binary that Terraform already downloaded for one of your projects (check the ~/.terraform.d/plugin-cache/registry.terraform.io/databricks directory, but make sure you use the latest version).
• Set environment variables for authentication. Exporter picks any supported environment variable. The simplest way is to use the DATABRICKS_CONFIG_PROFILE variable with the name of the Databricks CLI profile. For account-level operations, you may need to set the DATABRICKS_ACCOUNT_ID variable as well.
• You can run it with just the terraform-provider-databricks exporter command and select the resources to export interactively. Or you can skip the interactive selection (most suitable for regular exports), by passing -skip-interactive and specifying the resource types through the -listing and -services options (in recent versions, the exporter also switches to non-interactive mode automatically when you pass -listing or -services, so you don't need to specify that option).
• Wait for the export to finish. The time depends on the number of resources (you can tune parallelism, but for most workspaces, the defaults are good enough). If you want to see what the exporter is doing, add the -debug option (or -trace for even more detail). These options include Databricks SDK HTTP logs, so review where you store and share the output, as it may contain sensitive data from API requests or responses.
• Look for the generated files in the output directory (see screenshot). If necessary, set values for the variables declared in vars.tf, for example, secrets in the secret scope or secrets for mounts (mounts are deprecated anyway).

So now you can start using the generated code:

• It’s recommended to review all generated files before you use them.
• If you export resources to start managing the same workspace, use the same identity for the export and the terraform apply. Otherwise, Terraform can change the ownership of some objects. The exporter has been tested most extensively with administrator permissions (workspace, metastore and and account) - exports made with more limited permissions can be incomplete.
• To productionize the generated files, apply your own coding standards and use the correct repository structure. The exporter aims for readable output (although resource names may contain UUIDs or numbers, making them less readable), so you may want to apply final touches — the VSCode or IntelliJ Terraform extensions can help with refactoring of the generated code. This is not required if you want to perform periodic synchronization between different workspaces.
• To manage the existing workspace with the generated code, it’s recommended to run the exporter with the -native-import option. After the export, run terraform init, and then plan and apply. This imports the resources into the Terraform state through native import {} blocks (which need Terraform 1.5 or later). You can also run the generated ./import.sh script (i.e., you have an old Terraform version), but it is much slower than using the import blocks.

Selecting what to export

By default, the exporter processes all services, which is rarely what you want for a large workspace. There are two options to control the scope, and it helps to keep them apart:

• -listing selects the services whose objects are discovered and imported. This is the option to reach for most of the time. Please note that some resources aren't listable directly as they are attached to other objects - i.e., permissions on notebooks, clusters, and other workspace objects, or schemas in UC catalogs, or tables/volumes/… in those schemas. Such objects are typically emitted as dependencies of their parent objects.
• -services controls which transitive dependencies are processed, for example, the permissions of a listed object, or a notebook/dashboard/alert referenced by a job.

Both options take a comma-separated list of service names. (The full list of available services can be found in the documentation.) You can start with all (that's a default), and add a service with +service (it's the same as service), or remove one with -service. There is a special alias uc that selects all Unity Catalog-related services, including vector-search.

For example, -listing all,-uc lists everything except Unity Catalog, and -listing compute,jobs lists only clusters and jobs, and exports them together with all their dependencies.

By default, -services include every service, so all discovered dependencies are generated as portable references. When you restrict -services, the exporter still lists the objects selected by -listing, but it only emits resources for the services you explicitly specified. References to the services you drop could be written as hard-coded IDs instead (i.e., job or cluster IDs), which may not be portable between workspaces. This is useful when part of the environment is managed separately. For example:

• -listing compute,jobs -services all,-users,-groups exports clusters and jobs with their dependencies, but does not generate databricks_user or databricks_group resources, because the identities are managed elsewhere.
• -listing jobs -services notebooks,storage,uc-volumes exports jobs together with only their notebooks and the files on storage, and leaves other dependencies (SQL objects, permissions, and so on) as hard-coded IDs.

You can also filter by resource name during the listing:

• -match keeps resources whose name contains a given string (case insensitive).
• -matchRegex keeps resources whose name matches a regular expression.
• -excludeRegex drops resources whose name matches a regular expression.

These name filters apply during listing to most (but not all) resource types, so combine them with -listing. If you want the dependencies of a single cluster, list only the compute service (-listing compute) and match that cluster by name. -excludeRegex has higher priority than -match and -matchRegex, so anything matching it will be skipped. Directory names are not filtered by default during the workspace tree walk; pass -filterDirectoriesDuringWorkspaceWalking if you want the filters to apply to directories too (be careful, because it then applies to all directories, so you may need to allow parent paths such as /Users in the regex).

The -prefix option is not a filter: it adds a prefix to the names of all exported resources. This is useful when you merge exports from several workspaces into a single export and want to avoid name collisions.

Commonly used options

The exporter has more options than most runs need. These are the ones you are most likely to use:

The exporter has more options than this, for example, to control the notebook export format or how to handle the user name when generating resource names. See the guide for the full list.

A few examples of command lines to run the exporter:

# Export only jobs and clusters and their dependencies into ./export
terraform-provider-databricks exporter -skip-interactive \
  -listing compute,jobs -directory ./export
# Export jobs and clusters, but skip user and group resources
terraform-provider-databricks exporter -skip-interactive \
  -listing compute,jobs -services all,-users,-groups
# List everything except Unity Catalog, with native import blocks.
# UC dependencies could be still exported if they are dependencies
# of other resources.  To skip them as well, use `-services all,-uc`
terraform-provider-databricks exporter -skip-interactive \
  -listing all,-uc -native-import
# Export one team's resources, matched by a name prefix, for a merge
# with other exports
terraform-provider-databricks exporter -skip-interactive \
  -matchRegex '^team_a_' -prefix team_a

What the exporter produces

The exporter writes several files into the output directory (see the screenshot above as well):

• databricks.tf holds the provider declaration.
• One .tf file per service, for example, compute.tf, jobs.tf, and storage.tf, storing the generated resources.
• vars.tf declares the variables (if they are needed) used by the generated resources, and terraform.tfvars holds secret values when you pass -export-secrets.
• import.sh contains terraform import commands, and import.tf contains native import {} blocks when you pass -native-import.
• exporter-run-stats.json records the run time and resource counts, and is also used to drive incremental exports.
• ws_objects.json is a snapshot of workspace objects - it's used to detect deletions in incremental mode.
• ignored_resources.txt lists the resources that were skipped during code generation, one per line.

Handling secrets and variables

Normal Terraform resource reads do not expose Databricks secrets in plaintext, so the generated configuration declares a variable for each secret and references that variable in the resource. By default, the variable has no value, and you must fill it in before you run terraform apply. If you pass -export-secrets, the exporter makes a separate Secrets API call and writes the current secret values into terraform.tfvars. Treat that file as sensitive and keep it out of version control.

The same variable mechanism is used for other values that should not be hard-coded, such as the secret scope and secret names referenced by a mount.

Migrating between clouds

When you move a workspace between clouds, cloud-specific compute attributes differ. The -targetCloud option (with a value of aws, azure, or gcp) converts compatible attributes for clusters, jobs (both new_cluster in tasks and job clusters), pipelines, instance pools, and cluster policies. These include the availability setting, first_on_demand, and selected disk attributes. For zone_id, only the value auto is portable between AWS and GCP. Incompatible attributes, such as an AWS instance profile ARN, are omitted. Review the generated configuration and manually add any required cloud-specific settings.

Instance types cannot be converted completely automatically because clouds do not share node type names, so we need to provide a mapping. Pass -nodeTypeMappingFile with a JSON file that maps node types between clouds, and the exporter rewrites node_type_id accordingly. This option can only be used together with -targetCloud.

Exporting at the account level

The exporter runs in three stages. First, it lists the objects of each selected resource type. Then it imports each object: it reads the object’s current state through the API and, when necessary, emits its dependencies (permissions, a referenced notebook, etc.) and downloads external files such as libraries, init scripts, and notebooks. Finally, it generates the Terraform (HCL) code and writes it to per-service files. The performance improvement post has a diagram of this flow in its parallel form, where listing, import, and code generation run in worker pools.

The logic for importing a resource type is kept separate from the logic that generates the actual Terraform code. Each resource type is described by a small declarative structure, which makes the exporter easy to extend as new resources are added to the provider. A separate post will describe that structure, how to add a new resource, and other implementation details.

One of the harder parts of code generation is referential integrity: making sure that a reference in one resource points to another generated resource, so that applying the code reproduces the source workspace, and doesn’t try to use a hardcoded ID that is unique inside the source workspace. The exporter resolves these references in a separate pass and supports different match strategies (exact, case-insensitive, prefix, and regular expression). A separate post will describe this mechanism. For this post, it’s enough to know that the generated code uses proper Terraform references between resources rather than hard-coded IDs (which is the most common problem in similar projects).

How it works under the hood

The exporter runs in three stages. First, it lists the objects of each selected resource type. Then it imports each object: it reads the object’s current state through the API and, when necessary, emits its dependencies (permissions, a referenced notebook, etc.) and downloads external files such as libraries, init scripts, and notebooks. Finally, it generates the Terraform (HCL) code and writes it to per-service files.

The performance improvement post has a bit more detailed diagram of this flow in its parallel form, where listing, import, and code generation run in worker pools.

The logic for importing a resource type is kept separate from the logic that generates the actual Terraform code. Each resource type is described by a small declarative structure, which makes the exporter easy to extend as new resources are added to the provider. A separate post will describe that structure, how to add a new resource, and other implementation details.

One of the harder parts of code generation is referential integrity: making sure that a reference in one resource points to another generated resource, so that applying the code reproduces the source workspace, and doesn’t try to use a hardcoded ID that is unique inside the source workspace. The exporter resolves these references in a separate pass and supports different match strategies (exact, case-insensitive, prefix, and regular expression). A separate post will describe this mechanism. For this post, it’s enough to know that the generated code uses proper Terraform references between resources rather than hard-coded IDs (which is the most common problem in similar projects).

Performance and incremental export

The first versions of the exporter were single-threaded, and a large workspace with tens of thousands of notebooks could take hours or days to export, mostly waiting on the network. The current implementation lists, imports, and generates code in parallel using worker pools, and it supports incremental export that processes only the resources changed since the previous run. Both are important for repeated exports such as migration cutovers and disaster-recovery synchronization runs. The design, tuning parameters, and the problems introduced by parallelism are described in a separate post, Speeding up the Databricks Terraform exporter.

Incremental mode is enabled with -incremental, optionally with -updated-since to set the cutoff timestamp. For resource types whose APIs report a modification time, the exporter lists and processes only resources modified after the cutoff. The caveat is that their dependencies are always exported. Resource types that do not report a modification time are always exported. The exporter tracks the previous run time in exporter-run-stats.json and merges new code into the previous run. See the support matrix for the resource types that support incremental export. For most resource types, the API does not report deletions, so you need to run a full export from time to time. The workspace objects (notebooks, workspace files, and directories) are the exception, because the exporter tracks them in ws_objects.json and can detect deletions.

What the exporter covers

The exporter supports resources across compute, jobs, notebooks, and other workspace objects, SQL, Unity Catalog, model serving, and account-level MWS services. The support matrix in the documentation lists the current set.

Some resources are intentionally not exported because they do not fit the discovery-and-import model or should not be copied between environments. These include short-lived or credential-like resources such as personal access tokens, service principal secrets, and git credentials, and grants that are better managed together with the objects they apply to. The exporter writes supported resources that it discovers, but aren’t generated during a run to ignored_resources.txt.

Similar projects

Similar open source projects generate Terraform code for existing infrastructure, but they target public cloud providers rather than Databricks:

• Azure Export for Terraform (aztfexport) is Azure-focused, actively maintained, and offers a good introduction to this kind of tooling.
• TerraCognita, supports AWS, Google Cloud, Azure, and vSphere.
• Terraformer, a multi-cloud tool archived in March 2026 and no longer maintained.
• Terraforming, an AWS-only tool that was archived in 2022.

None of these tools supports Databricks resources. Shipping the Databricks exporter inside the provider was a good fit because the provider already holds the schemas for all supported resources.

When this is useful

The exporter is most useful when you want to adopt Terraform for infrastructure you built by hand, or when you migrate resources between workspaces, including a self-built disaster-recovery setup (now prefer to use managed disaster recovery). You can generate Terraform-managed resources in a few minutes instead of writing them manually, and then manage them with the usual best practices: pull requests, review, and CI/CD with approvals. For large workspaces and repeated exports, combine parallel and incremental export as described in the performance post.

Related blogs

• Databricks Terraform Provider Is Now Generally Available
• illimity Bank’s Disaster Recovery Strategy on Databricks Lakehouse
• Working with huge Terraform states

Cross-post from the blogspot…

Terraform exporter was originally created by Serge Smertin, but it’s mostly supported by me in the last ~4 years.

The initial implementation was single-threaded. It listed each resource type one after another, and for every resource, it did the API calls to read the data, and then generated the Terraform code (HCL) at the end. This was very simple to reason about in terms of existing functionality and code correctness, but it didn’t scale. A large workspace can contain tens of thousands of notebooks, hundreds of jobs, many SQL queries, dashboards, their permission, etc. For such workspaces, the export ran for hours or even days, and most of that time was spent waiting on the network, not on the CPU (plus, we need to account for rate limiting on the underlying Databricks REST APIs).

This post describes two things we did to speed up the exporter. The first is the parallelism: we reworked the listing, the import (reading resource data), and the code generation to run concurrently. The second is incremental export: for repeated exports of the same workspace, such as migration cutovers or self-made disaster recovery, the exporter can process only the resources that were created or changed since the last run. I’ll also touch on the problems introduced by parallelism and incremental export, and how you can tune the exporter’s behavior for a specific run to workaround them.

The single-threaded version was slow

The TF exporter does three things:

• Listing: for every resource type, query the API to find which resources of a given type exist and emit a resource instance (this will trigger an Import operation if the resource isn’t imported yet).
• Import: for each resource identified by the listing operation (or emitted by another resource), read its full definition through one or more API calls (usually it’s a call to a corresponding ReadContext of the Terraform resource), and emit references to other resources if they exist (i.e., a job may refer to a dashboard, users in notifications, notebook, etc.).
• Code generation: turn the collected state into HCL and write it to files.

The first two are dominated by REST API latency. A single request to read a notebook or a job takes a fraction of a second, but the exporter does one or more requests per resource, and it does them one after another. With 20000 notebooks, even 100 milliseconds per notebook adds up to more than 30 minutes for that resource type alone, and the CPU is idle the whole time. And we should take into account that the Databricks platform has a limit on the number of REST API calls per second, and it may vary per specific service — i.e., SCIM API v1 had a very small limit, like 2–3 calls per second only.

The listing of workspace objects was the worst case. Notebooks, directories, and workspace files live in a tree, and the exporter should walk that tree recursively, listing one directory at a time. A deep folder structure turned into a long chain of sequential API calls.

Three years ago, more and more large migrations and DR implementations were coming, so it was decided to invest time in speeding up the exporter by focusing on two major areas: incremental export of resources (very useful for DR implementations) and parallelization of all operations (all written manually). I’ll start the description with parallelization.

The design: channels and worker pools

The rework switched the exporter to a producer-consumer model built on Go goroutines and channels. The idea is standard for Go: producers put work items on a channel, and a fixed pool of worker goroutines takes items off the channel and processes them concurrently. The number of workers bounds how many REST API calls run at the same time, which is important because the REST API will throttle a client that sends too many requests.

The exporter has two heavy phases, and each has its own pool-based implementation.

The top half of the diagram is the listing and import phase. The bottom half is the code generation phase. The two phases are separated by a barrier: all listing and import operations must finish before code generation starts, because the code generator needs the complete set of resources to resolve references between them.

Step 1: parallel listing of workspace objects

The first change (PR #2691, September 2023) started with improving the recursive tree walk. We added a parallel listing routine for workspace objects. A buffered channel holds directories that still need to be listed. A pool of worker goroutines takes a directory off the channel, lists its contents, appends the results to a shared slice guarded by a mutex, and pushes any child directories back onto the channel. A sync.WaitGroup counts the directories that are still in flight, so the pool knows when the whole tree has been walked, and it can stop.

Two parameters were introduced here (overridable via environment variables):

• EXPORTER_WS_LIST_PARALLELISM (default 5) sets the number of listing workers.
• EXPORTER_DIRECTORIES_CHANNEL_SIZE (300k by default) sets the channel capacity. The channel must be able to hold every directory in the workspace. If it fills up, a worker who tries to push a child directory blocks, and because all workers can block at the same time, the export deadlocks. A large buffer avoids this.

This step also added retries. A directory listing that fails is re-enqueued and retried up to 3 times, with a short pause between submissions to avoid hitting the API too hard. Retries matter more when the client sends many requests simultaneously, and previously transient errors become more frequent under load.

Step 2: parallel import of resources

The next change (PR #2742) made the import of individual resources parallel. Before this, the code that listed a resource type also read each resource inline, in the same loop.

We split this into two operations. Listing now only discovers resource IDs and emits them. The emit step puts each resource on a channel — it no longer reads the resource data itself. A separate function, ImportResource, does the reading - the search by non-ID attribute (if required), the ReadContext call, and any custom import logic. This function runs inside the worker goroutines that drain the resource channels.

Different resource types need different amounts of concurrency. Reading a notebook is cheap and safe to do many at a time; while reading users and service principals should stay at a low concurrency to avoid throttling that API. So the number of workers is set per resource type, through a map in the code (goroutinesNumber), with a default of 2 workers when a resource type is not explicitly specified. You can override any of these with an environment variable named after the resource, for example:

export EXPORTER_PARALLELISM_databricks_notebook=10
export EXPORTER_PARALLELISM_databricks_permissions=11

The current defaults reflect what we found safe in practice: 10 workers for notebooks, 11 for permissions, 5 for directories and workspace files, and 1 for users and service principals.

Step 3: routing to dedicated and shared channels

Giving each resource type its own channel and pool proved being wasteful. Many resource types have just a few instances, so a dedicated pool for each adds overhead without improving performance. A later change (PR #3167) introduced the routing step shown in the diagram.

Only a few high-volume resource types get a dedicated channel. By default, these are databricks_user, databricks_service_principal, and databricks_group, because their APIs throttle aggressively and benefit from a controlled, separate queue. Every other resource goes to a single shared channel, called the default channel, served by its own pool of workers (EXPORTER_PARALLELISM_default, default 15). You can change which resources get a dedicated channel with the EXPORTER_DEDICATED_RESOURCES_CHANNELS environment variable by providing a comma-separated list of resources.

The same PR also made code generation parallel, which is the bottom half of the diagram.

Step 4: parallel code generation

Code generation had the same problem as import: it processed resources one at a time and wrote all files at the end. Generating the HCL for one resource includes resolving references to other resources, which could be quite expensive, i.e., for workspace objects, so this stage was also slow on large exports.

We replaced it with another pool. A configurable number of worker goroutines (EXPORTER_RESOURCE_GENERATORS, default 50) each take a resource, generate its HCL body, and send the result to a writer. Writing is separated by a service: one writer goroutine per service owns one output file (for example, clusters.tf or jobs.tf) and is the only goroutine that writes to it. This avoids locking on the file while still generating many resources at once. A separate goroutine writes the shell script with terraform import commands.

A later change (PR #3293) added a second import writer. With the -native-import option, the exporter also generates Terraform native import {} blocks (needs Terraform 1.5 and later), and these are written into import.tf file by their own goroutine, reading from a separate channel, next to the shell-script writer.

The problems parallelism exposed

Parallelism gets code executed faster, but it exposed issues that a single thread never had. Three of them shaped the final design.

Apply contention in the provider. Even when notebooks were applied in parallel, the terraform apply was still slow because the databricks_notebook resource held a mutex around directory creation, which serialized the work again. PR #2902 fixed this by creating the parent directory only when a notebook or workspace file import fails because the directory is missing, instead of always locking and calling the create-directory API. This is a fix in the provider resource, not in the exporter, but the exporter is what made the problem visible.

Channel deadlocks. A bounded channel can fill up. When the listing produces resources faster than the workers can drain them, the producer blocks on a full channel. If enough goroutines block this way, the export stops making progress. We handle this in two ways: the shared channel has a large buffer (EXPORTER_DEFAULT_HANDLER_CHANNEL_SIZE), and when the channel is more than 80 percent full, the producer pauses briefly before emitting more resources. This is simple backpressure: it slows the producer instead of letting the pipeline deadlock.

API throttling. Higher concurrency means more requests per second, and the Databricks REST API will reject requests when a client sends too many of them. The Go SDK automatically retries on HTTP status codes 429, 5xx, etc., but there were still cases where the REST API returned non-standard responses that the Go SDK didn’t handle. In PR #3202, we added additional retries around the search, read, and import operations, using the same retry approach as the directory listing. The per-resource worker limits were first the line of defense — retries handled the transient errors that still occur.

Duplicate emits under concurrency. The Emit function serves as the routing step, and several goroutines call it simultaneously. It marks a resource as “already seen” in a shared map, so the same resource is not imported twice. Large-scale testing found a race: two goroutines could check the map, both find the resource missing, and both enqueue it, resulting in a small number of duplicate resources in the output. PR #4163 fixed this by making the check-and-mark one atomic operation under the mutex, so the first caller wins and the rest skip the resource.

Tuning parameters

The existing defaults work for most workspaces. For a very large workspace, or one where you see throttling, you can control the behavior with the following environment variables:

A word of caution: do not raise the per-resource worker counts too high. The limit that helps in one workspace can cause throttling in another, because the API limits depend on the workspace and the account. Increase the values gradually and watch for throttling errors in the log. In addition to exporter environment variables, you can tune the DATABRICKS_RATE_LIMIT environment variable. But be careful, as the throttling affects not only direct API calls, but also all users of the workspace who use the same APIs when using UI!

Doing less work: incremental export

Parallelism makes a single export faster, but for migrations and disaster recovery projects, you run the export against the same workspace multiple times. In that case, the largest speedup is to not export unchanged resources at all. This was the goal of incremental export. It was added in PR #2563 (August 2023), about a month before the parallel work started, so the two speedups were built independently and can be used together.

You enable it with two command-line options:

• -incremental turns on incremental mode. The exporter merges the newly generated code with the code from the previous run instead of writing everything from scratch, and it merges the import.sh script, import.tf, and the variables file the same way.
• -updated-since takes an ISO8601 timestamp (for example, 2023-07-24T00:00:00Z) and limits the export to resources modified after that time. If you omit it, the exporter reads the timestamp of the previous run from the exporter-run-stats.json file in the output directory, so a scheduled run needs only -incremental.

The mechanism relies on a last-modified date, and this is where the limitations come from. Only some resource types report when they were created or modified: notebooks, SQL queries, dashboards, and alerts, among others. For these, the exporter lists them and skips the ones that were not modified since the cutoff. Resource types that do not expose a modification time (jobs, clusters, and many others) are re-exported every time, so incremental mode reduces the work but does not eliminate it for these resource types.

Deletions are the harder case. For most resource types, the API cannot tell the exporter that a resource was deleted since the last run, so a sequence of incremental runs keeps resources that no longer exist. You must run a full export periodically to catch deletions. Workspace objects (notebooks, workspace files, and directories) are the exception: on each full export, the exporter writes the complete list of workspace objects to a ws_objects.json file, compares against it on the next run to detect deletions, and removes the deleted objects from the generated code.

Another problem is the renaming of a workspace object that is currently handled as a deletion of the old resource and a creation of a new one. This leads to a situation where in the target workspace, the same resource will get another ID, so resource URLs will change. Potentially, it could be implemented as well by tracking the original resource ID (that doesn’t change on the rename) plus resource name in the generated TF code, and then generating Terraform moved blocks.

Incremental and parallel export combine well. A daily disaster-recovery export, for example, can run incrementally to skip unchanged notebooks and SQL objects, and still use the parallel worker pools for the resources it processes.

When this is useful

The parallel exporter implementation was worth the extra complexity when the workspace is large enough that API latency, not CPU, defines the execution time. On a small workspace, the difference is minor, and the defaults are fine. In a workspace with thousands of notebooks, jobs, or queries, parallel listing and import can turn an export that took hours into one that finishes in minutes, and tuning parameters give you room to adapt to a specific workspace’s API limits. For repeated exports, such as migration cutovers and disaster-recovery runs, combine it with incremental mode, which skips resources that have not changed since the previous run.

The exporter is still marked as experimental (because it’s a tool built by the field team, not engineering), but it’s heavily used by Databricks customers. The design outlined in the diagram has been stable since early 2024: parallel listing that emits resources onto channels, a routing step that separates high-volume resources from the shared queue, per-resource worker pools that read the data, and a code-generation pool that writes per-service files.

Cross-post from blogspot…

A process chain: the malicious document pattern

A classic endpoint intrusion starts when someone opens a malicious document (maldoc): an office application executes code when the file is opened, and that downloads a malware payload via a scripting tool like PowerShell. The signal is in the parent process, so this recipe reads process.parent_process (we extract a field using the : operator because this column has a VARIANT type). Pre-filter to office-spawned processes and script hosts so the two stages sit side by side per host, then match them in order. We use two CTEs: the first flattens the fields into columns, the second filters on those columns:

WITH launched AS (
  SELECT time,
         device.hostname AS host,
         process.name AS pname,
         process.parent_process:name::string AS parent_name
  FROM process_activity
  WHERE activity_name = 'Launch'
    AND time >= current_timestamp() - INTERVAL 1 DAY
),
proc AS (
  SELECT * FROM launched
  WHERE parent_name ILIKE '%outlook.exe' OR parent_name ILIKE '%winword.exe'
     OR parent_name ILIKE '%excel.exe'
     OR pname IN ('powershell.exe', 'cmd.exe', 'wscript.exe', 'cscript.exe')
)
SELECT *
FROM proc
MATCH_RECOGNIZE (
  PARTITION BY host
  ORDER BY time
  MEASURES FIRST(time) AS started,
           LAST(time)  AS ended,
           COUNT(*)    AS steps
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (office_spawn script+)
  DEFINE office_spawn AS parent_name ILIKE '%outlook.exe'
                        OR parent_name ILIKE '%winword.exe'
                        OR parent_name ILIKE '%excel.exe',
         script       AS pname IN ('powershell.exe', 'cmd.exe', 'wscript.exe', 'cscript.exe')
) AS m;

office_spawn matches the process the office application launched (the dropped payload), and script+ matches the script host it runs next. If you want the stages in any order rather than a fixed sequence, PATTERN (PERMUTE(office_spawn, script)) matches the two variables in either order. The same shape works on cloud audit data (api_activity), where you can look for a run of read operations followed by a role assignment write by the same principal.

Matching an event that is missing

It’s not always only about events that occurred. Some useful signals are about an expected event that is missing: a login that is never confirmed by a step-up, a secret exposed in a repository that no one quarantines, or an administrator role granted without approval. MATCH_RECOGNIZE can match the absence of a follow-up, because NEXT() reads the next row in order, and a DEFINE can require that it is not the event you were hoping for.

Take multi-factor authentication that a user turns off and is supposed to turn back on for a short period. Read a per-user stream of the two operations (off and on), ordered by time, and match a disable operation where the next event for that user is not a re-enable within an hour:

WITH mfa AS (
  SELECT time,
         user.name     AS user_name,
         activity_name AS action
  FROM account_change
  WHERE activity_name IN ('MFA Factor Disable', 'MFA Factor Enable')
    AND time >= current_timestamp() - INTERVAL 2 DAY
  QUALIFY lag(action) OVER (PARTITION BY user_name ORDER BY time)
          IS DISTINCT FROM action
)
SELECT *
FROM mfa
MATCH_RECOGNIZE (
  PARTITION BY user_name
  ORDER BY time
  MEASURES FIRST(time) AS disabled_at
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (left_disabled)
  DEFINE left_disabled AS action = 'MFA Factor Disable'
     AND (NEXT(action) IS NULL
          OR NEXT(action) <> 'MFA Factor Enable'
          OR unix_timestamp(NEXT(time)) - unix_timestamp(time) > 60 * 60)
) AS m;

left_disabled matches a disabled event where the next event for that user is missing, is not a re-enable, or is a re-enable that arrived too late.

The WHERE filter is an important part of this query. It limits data to only two operations, so any other user action performed between disable and enable is removed. That is what allows NEXT() to go from the disabled to the next relevant event. Keep this filter if you reuse the pattern. On an unfiltered activity, NEXT() would read whatever action came next and raise a false alert. To express "ignore everything that is not one of these events", just remove those rows.

The QUALIFY line is also part of the work. MFA is a binary switch, not a count: any run of disables in a row leaves it in a one-off state, and a single enable after them turns it back on. So five disables followed by one enable is safe, not five separate problems. The lag(action) IS DISTINCT FROM action check keeps a row only when the action differs from the one before it for that user, which collapses repeated actions into a single row and leaves a clean stream of real state changes. Without it, the first disable in a Disable, Disable, Enable run would see another disable as its next event, not the enable, and raise a false alert even though the account ended enabled. On our test data, omitting this turned 0 real findings into 9 false ones.

The MFA rule so far tests two things: the order of the events, and the gap between them. Absence has a third part that the pattern cannot see on its own: how long a user account was in a bad state. When a disable is the last event for a user (NEXT(action) IS NULL), you know nothing follows it. You do not know if it happened two minutes ago or two days ago. Wall-clock time is not a row in the stream, so DEFINE cannot test it: DEFINE only compares rows to each other, and there is no row for "one hour of nothing".

So you keep the time of the last event as a measure and compare it to current_timestamp() outside the match:

SELECT *
FROM (
  SELECT * FROM mfa
  MATCH_RECOGNIZE (
    PARTITION BY user_name
    ORDER BY time
    MEASURES LAST(time) AS disabled_at
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (left_disabled)
    DEFINE left_disabled AS action = 'MFA Factor Disable'
       AND NEXT(action) IS NULL
  ) AS m
)
WHERE disabled_at < current_timestamp() - INTERVAL 1 HOUR;

Use the same mfa CTE as before, so the stream contains only the two operations. The pattern shows that the state is still open because the disable is the user's most recent relevant event. The outer filter proves it has stayed open long enough to matter. You cannot move that filter inside the match, because a match sees only the rows in the partition, not the current clock.

We use two checks here. The first checks whether B follows A within a specified time. The second checks whether A occurred, no relevant event followed, and enough time has passed. A privileged role granted and never used, a secret exposed and never quarantined, a session that starts and then goes quiet: you match what happened, keep the time of the last event, and let NEXT() and a clock decides what should have come next.

Correlating your own detections

You can run matches against any data, not necessarily the cybersecurity data. If you run detections in Lakewatch, it could be your own notables: the signals produced by your rules. That gives you a second-order detection, one that runs over the output of the other rules instead of over the events underneath them.

Currently, notables are stored in a table (<lakewatch_catalog>.internal.notables). Each row has a time, a rule_display_name, a severity, and an observables array that links the notable to the extracted entities (an IP address, an asset, an email address, a file hash). Lakewatch already groups notables inside a case, so the similar signals land together. That groups them by identity or entity. It does not look at the order in which the notables arrived.

Order is what a kill chain is about. A host that fires a discovery alert, then a suspicious-script alert, then a beaconing alert, then a data-staging alert, in that order within a day, is a stronger signal than any one of those alerts alone, and stronger than the same four in a different order. That ordered progression is what MATCH_RECOGNIZE adds on top of the case grouping.

The observables column is an array, and PARTITION BY takes a plain column, so first explode it and keep only one entity type, so that every notable in a partition is used for the same object. The available observables determine which kind you can use as the key. A rule attaches only the observables it has in its definition, and different rules attach different kinds of observables: the endpoint rules typically use an asset ID (the host), the authentication rules key on a user identity, i.e., email address, and the beaconing rules use an IP address. A chain has to stay inside one of these keys. If you want to cross them, you resolve the observable kinds to a common entity first (for example, map an IP to a host through an inventory table or DHCP leasing history).

The endpoint rules all carry an asset ID, so align the chain on the host and match the process sequence a hands-on intrusion leaves behind:

WITH signals AS (
  SELECT n.time,
         o.value             AS host,
         n.rule_display_name AS rule_name,
         n.severity
  FROM fe_lakewatch_catalog.internal.notables n
  LATERAL VIEW explode(n.observables) t AS o
  WHERE o.kind = 'Asset ID'
    AND n.time >= current_timestamp() - INTERVAL 7 DAY
)
SELECT *
FROM signals
MATCH_RECOGNIZE (
  PARTITION BY host
  ORDER BY time
  MEASURES FIRST(time) AS started,
           LAST(time)  AS ended,
           COUNT(*)    AS stages
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (discovery exec+ exfil)
  DEFINE discovery AS rule_name = 'Discovery Command Execution via Direct or Wrapped Process',
         exec      AS rule_name = 'suspicious_powershell_scriptblock',
         exfil     AS rule_name = 'Suspicious File Staging and Potential Data Exfiltration'
                      AND unix_timestamp(time) - unix_timestamp(FIRST(time)) <= 24 * 60 * 60
) AS m;

Every notable in a partition belongs to the same host, because the explode kept only the asset ID observable. Each row variable is one stage of the chain, named by the rule that produced it, and the window check on the last stage keeps the whole match inside 24 hours. The output is one row per host that matched the full sequence, with the start and end times and the number of stages. In other cases, you would write this as a Splunk ES correlation search or a chain of self-joins in Databricks SQL — here it is one clause. A user-centric chain has the same shape: key on o.kind = 'Email Address' and match a sequence of the authentication rules (an off-hours login, then a novel-country login, then data staging by the same user). The rule names in both cases are only examples: use the names of your own rules.

This works because the whole chain sits within a single entity: one partition, one ordered path. A correlation that crosses entities, a notable on a host tied to a notable on a different user, is a path between partitions, which is more like a graph problem that we described in another post.

When to use GraphFrames instead

If you have used graph analytics on security data (see the graph analytics post), the examples in these two posts may look like graph problems. Some of them are, and some are not, and the difference is worth being clear about.

MATCH_RECOGNIZE works for an ordered path inside a partition. It cannot follow an edge into another partition. So it fits any detection where the "graph" is really a per-entity timeline: the events of a single identity, entity, or connection, read in a specific order. Those cases become plain SQL that runs in a warehouse or a scheduled detection rule, with no separate graph library and no PySpark.

Real graph traversal is a different problem. Classic lateral movement, where an account moves from host A to host B to host C and each hop is a different pair of machines, is a path across entities, so you cannot express it with a single partition. Blast-radius questions (“what can this compromised host reach?”) and community detection (“which accounts and hosts cluster together?”) are also graph work. Graph frameworks and algorithms are the better fit for those cases.

But they both work well together. Use MATCH_RECOGNIZE for the temporal reduction: turn raw events into sessions, into edges, or into "A then B" facts. Then hand only the multi-hop part to GraphFrames. This keeps most of the logic in SQL and gives the graph engine a much smaller graph to work on.

Wrap up

How much MATCH_RECOGNIZE improves depends on the pattern. The simplest ones have a standard SQL equivalent: impossible travel (part 1) is a single LAG() comparison, and a query such as fail{5,} success can be rewritten with window functions and a gaps-and-islands trick. Once a pattern has several variable-length stages, an alternation, or any-order PERMUTE, the plain-SQL version needs recursive CTEs or procedural code, because window functions are not recursive and cannot follow an arbitrary-length sequence of stages. In these cases, MATCH_RECOGNIZE expresses the sequence more easily than nested joins. For fixed two-row comparisons, a window function can remain a simpler alternative. Use row pattern matching when the order, repetition, or optional stages are important in the detection.

References

• Databricks docs: MATCH_RECOGNIZE clause and the PATTERN sub-clause.
• Part 1: the parts of the clause, beaconing, and impossible travel.
• Graph analytics on cybersecurity data.
• Lakewatch documentation.

• A brute-force attempt followed by a successful login can indicate an account takeover. Failed attempts without a later success are a different, usually lower-priority signal.
• A beacon is a set of connections at a near-constant interval.
• A hands-on-keyboard intrusion is a chain: an office document spawns a script, and the script reaches out to the network.
• MFA was disabled and not enabled again during a given time window.

In each case the signal is the sequence, and the order of the events defines the meaning.

Standard SQL isn’t a good fit for this. Counting events in a window is easy (a GROUP BY with a HAVING filter), but "event A, then B, then C, in this order, for one user" needs self-joins, chains of LAG()/LEAD(), or gaps-and-islands tricks. These queries are hard to write and to read a few months later.

MATCH_RECOGNIZE provides a direct way to describe such sequences. It's a row pattern matching clause from SQL:2016, available in some databases and streaming engines. And now it's available in Databricks SQL.

It is a regular expression engine over time-ordered rows inside a partition. For cybersecurity, you partition the data by an entity (a user, a source IP, a host), order rows by time, and match a pattern against that ordered sequence.

This post and the next one cover detections over OCSF data. This post covers the basics of the usage and two single-entity patterns: beaconing and impossible travel.

Preview note. At the time of writing, MATCH_RECOGNIZE is in Beta on Databricks Runtime 19.0 and above, and a workspace admin should enable it on the Previews page. Every query in this post and in part 2 was tested on a live Lakewatch workspace with the preview enabled. The field paths follow the Lakewatch OCSF "gold" schema, so adjust them if your data has a different layout.

The parts of a match

We will start with the brute-force example because it shows every part of the MATCH_RECOGNIZE clause. The example from the pipe-syntax post finds users with 5 or more failed logins during the last hour:

FROM authentication
|> WHERE time >= current_timestamp() - INTERVAL 1 HOUR
     AND status <> 'Success'
|> AGGREGATE COUNT(*) AS failures
   GROUP BY user.name AS user_name, src_endpoint.ip AS src_ip
|> WHERE failures >= 5

This is a good triage signal, but it cannot say whether any of those attempts succeeded. The more interesting case is 5 or more failures followed by a success from the same user and source IP: the pattern of a guessed password. With MATCH_RECOGNIZE, that is one line in the PATTERN clause. Because PARTITION BY takes plain columns, first flatten the nested OCSF fields into columns (a small CTE, or a pipe SELECT) and filter to the last day, then match:

WITH auth AS (
  SELECT time,
         user.name        AS user_name,
         src_endpoint.ip  AS src_ip,
         status
  FROM authentication
  WHERE time >= current_timestamp() - INTERVAL 1 DAY
)
SELECT *
FROM auth
MATCH_RECOGNIZE (
  PARTITION BY user_name, src_ip
  ORDER BY time
  MEASURES FIRST(time)  AS first_fail,
           LAST(time)   AS success_time,
           COUNT(*)     AS attempts
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (fail{5,} success)
  DEFINE fail    AS status <> 'Success',
         success AS status =  'Success'
) AS m;

Read it from top to bottom:

• PARTITION BY user_name, src_ip runs the match once per user-and-source-ip pair, the same as a GROUP BY.
• ORDER BY time puts the rows of each partition in time order. The pattern-matching and navigation functions use this order.
• MEASURES builds the output columns for each match, using FIRST() and LAST(), and aggregates over the matched rows.
• ONE ROW PER MATCH returns a single summary row per match.
• AFTER MATCH SKIP PAST LAST ROW tells the engine to continue after the last matched row, so matches do not overlap.
• PATTERN (fail{5,} success) is the regular expression: 5 or more fail rows, then one success row, all consecutive within the partition.
• DEFINE names the row variables and gives each a boolean rule. Here, a fail is any row whose status is not success, and success is the opposite. A variable with no DEFINE entry matches any row.

The query that just produces counts, and this one looks similar, but they answer different questions. The first query counts failures and stops there. The second query needs failures and then success in that order, so it fires on the takeover pattern and stays quiet on only failures.

One thing the example leaves out is the time between events. Success can arrive at any point after failures, so a failed burst on Monday and then a success on Friday would still be counted as one match (if you have a long interval). Real detections bound the gap between steps, which you can implement by adding a PREV() check in the DEFINE:

DEFINE fail    AS status <> 'Success',
       success AS status =  'Success'
                  AND (PREV(time) IS NULL
                       OR unix_timestamp(time) - unix_timestamp(PREV(time)) <= 30 * 60)

Now, success is matched only when it occurs within 30 minutes of the previous attempt. Fifteen, 30, 60, or 90 minutes are the typical time windows you see most often in real rules.

The pattern language

The PATTERN clause is a small regular-expression language over the row variables you define. Databricks supports the full set of operators:

Inside DEFINE and MEASURES, you have navigation functions to look across rows: PREV() and NEXT() read the previous or next row in ORDER BY order, FIRST() and LAST() read the first or last row of a variable, CLASSIFIER() returns which variable matched a row, and MATCH_NUMBER() numbers the matches inside a partition. A variable-qualified reference, such as fail.src_ip reads a column from the rows that matched a specific variable. PREV() and NEXT() take an optional offset, so PREV(time, 2) reads two rows back, but you cannot nest them: Databricks rejects PREV(PREV(time)), so use the offset form instead.

Beaconing: connections at a near-constant interval

Command-and-control beacons call back on a schedule, so the gap between one connection and the next stays about the same. That regularity is a sequence property: each interval is close to the one before it. PREV() gives you the previous row, so you can compare the current gap to the last gap and match a run of steady intervals.

WITH flows AS (
  SELECT time,
         src_endpoint.ip   AS src_ip,
         dst_endpoint.ip   AS dst_ip,
         dst_endpoint.port AS dst_port
  FROM network_activity
  WHERE connection_info.direction = 'Outbound'
    AND time >= current_timestamp() - INTERVAL 1 DAY
)
SELECT *
FROM flows
MATCH_RECOGNIZE (
  PARTITION BY src_ip, dst_ip, dst_port
  ORDER BY time
  MEASURES FIRST(time) AS first_beat,
           LAST(time)  AS last_beat,
           COUNT(*)    AS beats
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (start regular{4,})
  DEFINE regular AS abs(
           (unix_timestamp(time)       - unix_timestamp(PREV(time)))
         - (unix_timestamp(PREV(time)) - unix_timestamp(PREV(time, 2)))
       ) <= 2
) AS m;

start has no DEFINE rule, so it matches any starting row and anchors the run. regular holds when the current interval is within 2 seconds of the previous interval. PREV(time, 2) reads two rows back. regular{4,} requires at least 4 such steps in a row, so the match is a source-to-destination pair that kept a steady rhythm. Tune the tolerance and the count to your data, and add a filter on beats to drop short sessions (you also need to exclude some services like infrastructure monitoring, etc.).

Impossible travel

Impossible travel is a two-row pattern: a successful login in one place, followed by another login from the same user so far away that the implied travel speed is physically impossible. Because MATCH_RECOGNIZE orders the rows by time and PREV() reads the earlier one, the whole test fits in a single DEFINE.

WITH logins AS (
  SELECT time,
         user.name              AS user_name,
         src_endpoint.ip        AS src_ip,
         dst_endpoint.location.lat  AS lat,
         dst_endpoint.location.long AS lon
  FROM authentication
  WHERE status = 'Success'
    AND dst_endpoint.location.lat IS NOT NULL
    AND time >= current_timestamp() - INTERVAL 7 DAY
)
SELECT *
FROM logins
MATCH_RECOGNIZE (
  PARTITION BY user_name
  ORDER BY time
  MEASURES FIRST(time)   AS from_time,
           LAST(time)    AS to_time,
           FIRST(src_ip) AS from_ip,
           LAST(src_ip)  AS to_ip
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (a jump)
  DEFINE jump AS
    (
      6371 * acos(
        cos(radians(PREV(lat))) * cos(radians(lat))
          * cos(radians(lon) - radians(PREV(lon)))
        + sin(radians(PREV(lat))) * sin(radians(lat))
      )
    )
    / (greatest(unix_timestamp(time) - unix_timestamp(PREV(time)), 1) / 3600.0)
    > 900
) AS m;

a matches any successful login, and jump matches the next one when the great-circle distance from the previous location (calculated with the spherical law of cosines, in kilometers) divided by the hours between the two logins is above 900 km/h. The greatest(..., 1) guards against a divide-by-zero when two events share a timestamp.

Before you jump to use it

There are a few limits on how you write these queries:

• Non-overlapping matches. Databricks supports only one skip mode, AFTER MATCH SKIP PAST LAST ROW, so once a match finishes, the engine continues after its last row, and matches do not overlap. This works for most detections (find the pattern, report it, move on), but it means you cannot count every overlapping window.
• Greedy quantifiers only. There are no non-greedy quantifiers (A+?). When you need to skip filler rows between stages, pre-filter to the rows of interest first so the stages sit next to each other. We use this in the process-chain example in part 2.
• PARTITION BY takes columns only. You cannot partition by an expression, so flatten nested OCSF fields into columns or apply functions first. That's why every example starts with a CTE.
• Anchors only on boundaries. ^ and $ are allowed only at the start or end of the top-level pattern, not in the middle.
• Watch for partition skew. Partitioning and ordering shuffle and sort the data, so a single source IP or user with a lot of data will result in one large partition to sort. This is the same problem as with window functions.

Conclusion

These examples show where MATCH_RECOGNIZE is useful. A run of failures followed by a success, a steady beacon interval, or a two-login location change can each be expressed as a short pattern instead of a chain of self-joins or window functions. This can make detection or threat-hunting queries easier to read and maintain. For a two-row comparison, such as impossible travel, a standard LAG() query can still be sufficient. Row pattern matching becomes more useful as the number and variability of the stages increase.

We’ll continue with more examples in Part 2.

References

• Databricks docs: MATCH_RECOGNIZE clause and the PATTERN sub-clause.
• Why Databricks SQL pipe syntax is great for cybersecurity work — the counted brute-force example and the OCSF field mapping.
• Lakewatch documentation.