SQL audience builder

Overview

SQL audiences let analysts define cohorts directly against the warehouse-backed semantic layer. Results materialize as segment memberships with freshness governed by your ETL cadence.

Steps

1. Grant TrackLayer read access to approved views—never raw PII tables without tokenization.
2. Author SQL returning user_id and optional trait_patch columns.
3. Schedule the query hourly or daily depending on cost envelopes.

SELECT
  user_id,
  to_json(struct(
    last_cart_value AS ltv_hint,
    loyalty_tier AS warehouse_tier
  )) AS trait_patch
FROM analytics.user_features
WHERE last_seen_at > current_timestamp - interval 30 day;

4. Map outputs to downstream sync templates like any other segment.

Troubleshooting

• Row explosion: enforce uniqueness on user_id with QUALIFY ROW_NUMBER patterns.
• Latency: move heavy joins to upstream dbt models; keep the segment SQL thin.
• Governance: tag the query with owners and SLAs; TrackLayer surfaces staleness badges when jobs miss.