Scenario E: Product and clickstream events¶

Part of Streaming Transformations › Scenarios.

Goal¶

Product analytics needs every event linked to the account’s tier and region, with bot and internal traffic removed, and event counts per tier and region that stay current.

Transformations used¶

1. Stream filtering on stream and dimension columns: drop bot traffic, and exclude internal and test accounts.
2. Stream enrichment: map the user to the account’s tier and region. The dimension source can be a dynamic table.
3. Custom scalar logic: JavaScript UDFs parse the user agent into a device and the URL into a campaign.
4. Insert-triggered materialized views: maintain event counts per tier and region.

Source data¶

Stream¶

The example uses these seven click events:

{"ts": "2026-10-09 09:15:00", "account_id": "acc-100", "event": "page_view", "url": "https://example.com/pricing?utm_campaign=fall_launch", "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) Chrome/128.0"}
{"ts": "2026-10-09 09:15:04", "account_id": "acc-200", "event": "click", "url": "https://example.com/signup", "user_agent": "Mozilla/5.0 (iPhone; CPU iPhone OS 17_0 like Mac OS X) Mobile/15E148"}
{"ts": "2026-10-09 09:15:06", "account_id": "acc-100", "event": "page_view", "url": "https://example.com/docs", "user_agent": "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"}
{"ts": "2026-10-09 09:15:07", "account_id": "acc-900", "event": "page_view", "url": "https://example.com/admin", "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) Chrome/128.0"}
{"ts": "2026-10-09 09:15:08", "account_id": "acc-950", "event": "click", "url": "https://example.com/signup", "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) Chrome/128.0"}
{"ts": "2026-10-09 09:15:09", "account_id": "acc-300", "event": "page_view", "url": "https://example.com/docs?utm_campaign=newsletter&ref=mail", "user_agent": "Mozilla/5.0 (Linux; Android 14; Pixel 8) Chrome/128.0 Mobile"}
{"ts": "2026-10-09 09:15:12", "account_id": "acc-100", "event": "click", "url": "https://example.com/checkout", "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) Chrome/128.0"}

Dimension table¶

The PIPE joins the following dimension table, refreshed periodically:

-- Accounts dimension: tier, region, and internal or test flags per account.
CREATE OR REPLACE TABLE dim_accounts (
  account_id STRING PRIMARY KEY RELY,
  tier STRING,
  region STRING,
  is_internal BOOLEAN,
  is_test BOOLEAN);

INSERT INTO dim_accounts VALUES
  ('acc-100', 'enterprise', 'emea', FALSE, FALSE),
  ('acc-200', 'standard', 'amer', FALSE, FALSE),
  ('acc-300', 'standard', 'apac', FALSE, FALSE),
  ('acc-900', 'enterprise', 'amer', TRUE, FALSE),
  ('acc-950', 'free', 'emea', FALSE, TRUE);

Target table¶

The PIPE lands transformed and filtered click events in this target table:

CREATE OR REPLACE TABLE click_events (
  event_ts TIMESTAMP_NTZ, account_id STRING, event_name STRING,
  page_url STRING, device STRING, campaign STRING, tier STRING, region STRING);

Transformation¶

The PIPE lands click events in the target table. Accounts are mapped to their tier and region. JavaScript UDFs parse the user agent into a device and the URL into a campaign. Bot traffic and events from internal and test accounts are dropped by filtering on stream and dimension columns. Event counts per tier and region are computed synchronously via an insert-triggered materialized view.

-- JavaScript UDF: user agent to bot, mobile, or desktop. Argument names reach the body UPPERCASED.
CREATE OR REPLACE FUNCTION parse_device(ua STRING) RETURNS STRING LANGUAGE JAVASCRIPT AS
  $$ if (/bot|crawler|spider/i.test(UA)) return 'bot';
     if (/Mobile|Android|iPhone/.test(UA)) return 'mobile';
     return 'desktop'; $$;

-- JavaScript UDF: utm_campaign from the URL query string, or NULL.
CREATE OR REPLACE FUNCTION parse_campaign(page_url STRING) RETURNS STRING LANGUAGE JAVASCRIPT AS
  $$ var m = /[?&]utm_campaign=([^&#]+)/.exec(PAGE_URL); return m ? m[1] : null; $$;

-- Insert-triggered materialized view. Create it before the pipe.
CREATE OR REPLACE MATERIALIZED VIEW click_events_by_tier_region
  REFRESH USING INSERT ONLY
  AS
  SELECT tier, region, COUNT(*) AS event_count
  FROM click_events
  GROUP BY tier, region;

CREATE OR REPLACE PIPE click_events_pipe AS
  COPY INTO click_events (event_ts, account_id, event_name, page_url, device, campaign, tier, region) FROM (
    SELECT
      e.$1:ts::TIMESTAMP_NTZ,
      e.$1:account_id::STRING,
      e.$1:event::STRING,
      e.$1:url::STRING,
      parse_device(e.$1:user_agent::STRING),
      parse_campaign(e.$1:url::STRING),
      a.tier,
      a.region
    FROM TABLE(DATA_SOURCE(TYPE => 'STREAMING')) e
    LEFT OUTER JOIN dim_accounts a
      ON e.$1:account_id::STRING = a.account_id
    WHERE parse_device(e.$1:user_agent::STRING) <> 'bot'
      AND a.is_internal = FALSE
      AND a.is_test = FALSE);

Result¶

Target table¶

Querying the target table returns these rows:

The Googlebot event is dropped as bot traffic, and the events from the internal account acc-900 and the test account acc-950 are dropped by the dimension filters. The acc-200 and second acc-100 events have no utm_campaign parameter, so their campaign is NULL.

Insert-triggered materialized views¶

The PIPE keeps the view synchronously up to date as it lands rows. Querying click_events_by_tier_region returns the number of landed events per tier and region:

The two acc-100 events land in the same tier and region, so they’re counted in one row.

Considerations¶

1. Dimension data is a snapshot that refreshes periodically, so updates can take several minutes to appear. This works well for slow-moving reference data. Dynamic tables can be used as dimension tables.
2. In a JavaScript UDF, argument names are uppercase inside the function body.
3. Keep UDFs fast. A slow UDF reduces throughput and increases latency.
4. Create the materialized views before the pipe. Rows ingested before a view exists aren’t backfilled into it.

See also¶

See Stream filtering, Stream enrichment, Custom scalar logic, and Insert-triggered materialized views for more information on the transformations used in this scenario.

Previous: Scenario D: Security events · Next: Scenario F: Privacy-safe ingestion