Reduce Table Size When Performing a Set Difference

Pre-intersect the larger table with a smaller table to avoid exceeding table size limits when using defineTable() with !match()

Query

logscale
defineTable(query={action=login}, name=A, include=[user], start=1h, end=now);
defineTable(query={action=login | match(A, field=user)}, name=B, include=[user], start=30d, end=-1h);
action=login
| !match(B, field=user)

Introduction

The defineTable() function can be used to define a named, in-memory table from a separate query and time range, which can then be looked up from the main query using match(). Because the table is materialized before the main query runs, it makes it possible to compare events across two different time intervals in a single query.

Tables created with defineTable() are subject to a maximum size. When the table is built from a long time range, it can exceed that limit and cause the query to fail. A set difference of the form A minus B can be rewritten as A minus (A intersect B), because the two expressions return the same result.

Note

This strategy has a caching trade-off: because table B depends on table A being materialized first, table B can likely not be served from cache.

In this example, the defineTable() function is used to find users who logged in within the last hour, but who did not log in during the preceding 30 days. The set of logins from the last hour is small and is defined as table A, while the set of logins from the last 30 days is large and is defined as table B. To keep table B small, it is built with an inner match() against table A, so that it only contains the intersection of the two sets.

Set difference A minus B rewritten as A minus (A intersect B)

For a simpler set difference without large-table constraints, see Filter For Items Not Part of Data Set Using defineTable().

Example incoming data might look like this:

@timestampuseraction
2026-07-30T08:14:22Zalicelogin
2026-08-05T09:02:11Zboblogin
2026-08-12T07:45:03Zalicelogin
2026-08-18T11:30:47Zboblogout
2026-08-20T13:12:09Zalicelogin
2026-08-24T15:22:58Zerinlogin
2026-08-25T08:00:00Zalicelogin
2026-08-25T09:00:00Zboblogin
2026-08-26T09:58:12Zfranklogout
2026-08-26T10:00:00Zalicelogin
2026-08-26T10:05:33Zboblogin
2026-08-26T10:12:41Zcarollogin
2026-08-26T10:30:00Zdavelogin
2026-08-26T10:41:19Zcarollogout
2026-08-26T10:52:07Zdavelogin

Step-by-Step

  1. Starting with the source repository events.

  2. logscale
    defineTable(query={action=login}, name=A, include=[user], start=1h, end=now);

    Defines the table named A from all events where the action field has the value login. The start and end parameters set the time range of the sub-query to the last hour, independently of the search interval of the main query. If these parameters are omitted, the sub-query uses the search interval of the main query.

    The include parameter restricts the table to the user column only, which keeps the table as small as possible. This is the small set that is used to pre-filter table B.

  3. logscale
    defineTable(query={action=login | match(A, field=user)}, name=B, include=[user], start=30d, end=-1h);

    Defines the table named B from login events in the 30 days that precede the last hour, as set by the start and end parameters.

    The inner match() function looks up the user field of each event in the previously defined table A and keeps only the events where a matching row exists. Table B therefore contains only the intersection of the two sets. Note that table A must be defined before it is referenced, as the tables are evaluated in the order they are declared.

  4. logscale
    action=login

    Filters the events of the main query, which runs over the last hour, so that only events where the action field has the value login are retained. These events represent set A in the main query.

  5. logscale
    | !match(B, field=user)

    Looks up the user field of each remaining event in table B and, because the function is negated with !, keeps only the events where no matching row is found. The field parameter names the field in the event to look up, and it is matched against the column of the same name in the table unless the column parameter specifies otherwise.

    The result is the events belonging to set A that are not in set B, in other words the users who logged in within the last hour but not during the preceding 30 days.

  6. Event Result set.

Summary and Results

The query is used to return the users who logged in within the last hour but who did not log in during the preceding 30 days, without exceeding the maximum size of a table defined with defineTable().

This query is useful, for example, to detect first-time or long-dormant accounts becoming active again, which can indicate onboarding activity, returning users, or the reuse of a stale credential. The same A minus (A intersect B) rewrite can be applied to any set difference where the historical set is too large to be materialized as a table.

Sample output from the incoming example data:

user
carol
dave

Note that the users alice and bob are excluded because they appear in table B, and that the user erin is never considered because that user does not appear in table A and is therefore filtered out of table B as well. Note also that the returned result set is identical to the result of a plain A minus B comparison, as the pre-intersection only reduces the size of table B.

For more information, see Query Joins and Lookups.