Feature Request: Support select all * (wildcard) for unfiltered activity stream

[hugo-vanthournhout]

Problem
I am in a case where I need a single activity fact table containing all activity types with pre-computed temporal flags. The goal is a semantic layer build for self service analytics. I feel it might be useful for others as well, as agent querying semantic layer will only increase.

Why pre-computed flags?

• MetricFlow/Cube have limited query-time join capabilities for large dataset
• Reduces compute at runtime (e.g. agent calling a lot for insight)
• Ensures metric consistency across queries

Why dataset_column?
The pattern is ideal for reusable temporal flags, but currently requires filtering to a specific activity type:

sql```
select all order (...) -- ✅ Works, but only orders
select all pageview (...) -- ✅ Works, but only pageviews
select all * (...) -- ❌ Not supported

We need temporal flags computed across the entire stream (or null where not applicable), not just filtered subsets.
Proposed syntax

sql```
{% set aql %}
using activity_stream

select all * (
    ts,
    entity_key,
    activity_id,
    activity,
    entity2_join_key
    ...
)

include (
    first_order_date,
    is_active_at_date,
    total_events_before_at_date,
    total_events_last_month_at_date,
)
{% endset %}

{{ dbt_activity_schema.dataset(aql) }}

Not sure if my case is too specific (then I can just build window function for my flags), or it might be usefull for the package to be future ready.

[bcodell]

Hey @hugo-vanthournhout! Thanks for opening this. Going to address this a couple of ways:

1. A couple workarounds to roughly accomplish your goal
2. Commentary on adding this (and similar) syntax as a feature

1. Workarounds to achieve this:

If you're skipping the stream (skip_stream: true in project config), you could create an activity called customer__completed_activity (or whatever you want to name it) that is a union of all the activities you've defined in your project. activity_name becomes a feature in the feature json (need to re-alias to activity_type or similar). materialization can be a view or a table/incremental - whatever you see fit for your project. then from there the following aql should work:

{% set aql %}
using activity_stream

select all completed_activity (
    ts,
    entity_key,
    activity_id,
    activity_type, -- this is re-aliased activity_name that's now a feature
    entity2_join_key
    ...
)

include (
    first_order_date,
    is_active_at_date,
    total_events_before_at_date,
    total_events_last_month_at_date,
)
{% endset %}

{{ dbt_activity_schema.dataset(aql) }}

If you're actually building the full stream (skip_stream: false in project config), then I'd recommend leveraging some jinja for-loops - enrich each activity with the common dataset columns, then union the whole thing:

{% set activities = [
  'activity_1',
  'activity_2',
  ...
] %}

{% set aql_dict = {} %}

{% for activity in activities %}
{% set aql %}
using activity_stream
select all {{activity}} (
    ts,
    entity_key,
    activity_id,
    activity,
    entity2_join_key
    ...
)
include (
    first_order_date,
    is_active_at_date,
    total_events_before_at_date,
    total_events_last_month_at_date,
)
{% endset %}
{% do aql_dict.update({activity: aql}) %}
{% endfor %}

{% for activity, aql in aql_dict.items() %}
with {{activity}} as (
{{ dbt_activity_schema.aql(aql) }}
){% if not loop.last %},{% endif %}{% endfor %}
{% for activity in aql_dict.keys() %}
select *
from {{activity}}
{% if not loop.last %}union all{%endif %}{% endfor %}

Some possible gotchas:

• not sure what activity total_events_before_at_date and total_events_last_month_at_date are pinned to (since every dataset column is just a joined activity, and an activity needs to be explicitly defined in the dataset column model's aql expression). if you need dataset columns that span across the entire stream, then the first option (creating the stream as its own activity) is the way to go.
• in general i'd recommend skipping the stream build by default - simplifies the data pipeline and keeps warehouse costs down. the stream is convenient for manual querying but since the macros in this project abstract away that part of development, i find the convenience isn't worth the maintenance cost

Regardless of the approach you choose here, compute costs should be taken into consideration as running across every activity will get expensive, even with cluster keys defined. so for optimizations there, i'd split the dataset columns into two categories:

• columns defined with first/last ever (i.e. don't actually rely on a timestamp in the join - like your first_order_date and is_active_at_date examples)
• columns defined with before/between/after (i.e. join is dependent on primary activity record's timestamp - like your total_events_before_at_date and total_events_last_month_at_date examples)

For the former group, I'd create a dataset using an activity that every entity has done at least once (e.g. select first/all customer__created_account), then include each of the aforementioned columns in the dataset (basically a precompute). Then rather than recomputing for each row in each activity in your full enriched stream dataset model, just join the final select/union statement to that "customer dimension" table. For the latter group... no workarounds there - they just need to be recomputed each time.

2. Commentary on adding this (and similar) syntax as a feature:

Definitely open to it! But on-the-fly activity definitions are a bigger topic that are worth a separate discussion, and candidly with other initiatives I have going on, I likely won't add this anytime soon. Of course, contributions are welcome, but if you don't have time for that, hopefully one of the aforementioned workarounds serves you for now.

This was verbose 😅 so let me know what questions you have. Happy to jump on a call if helpful. Thanks for using the package!