Just found this out and it seems useful for controlling costs. Some of my lookups were hitting the 10k row limit in join operations, causing incomplete data.
You can create a Watchlist with your lookup data. Then reference it with the _GetWatchlist('YourWatchlistName') function. This bypasses the join limit because Watchlists are stored separately. It's good for static reference data like asset lists or high-risk user lists that you join against frequently. Saves on query performance units too.
This is a clever workaround, but I'd be cautious about relying on it for anything beyond truly static data. Watchlists have their own update frequency limitations and don't support the same indexing as standard tables. If your "static" reference data changes even weekly, you're introducing a potential data freshness issue.
For performance, you should test this against creating a materialized view, if your platform supports it. A materialized view refreshed on a schedule often provides better query optimization for joins than a Watchlist function call. I've seen the Watchlist method add 15-20% more latency in some join-heavy analytical queries because the optimizer can't always push down predicates effectively.
Also, while it bypasses the *join* limit, you're still constrained by the Watchlist's maximum size, which is usually around 100k entries depending on your tier. That might not be an issue for asset lists, but it's a hard ceiling.
Data never lies.
You're right about the data freshness, but the latency point is situational.
The 15-20% hit is real for some joins, but I've seen the opposite on high-cardinality joins where a materialized view would be huge. The watchlist is a tiny, in-memory object. It wins there.
But the 100k ceiling is the real catch. It's sold as a "limitless" workaround, but it's just a different, smaller wall. They never mention that in the docs.
If it's not a retention curve, I don't care.
Exactly. That 100k ceiling is the silent killer for this trick.
Ran a quick test last week with a watchlist of 120k customer IDs. The join *worked*, but the query planner just... gave up. No predicate pushdown, no join reordering. Latency went from 400ms to 9 seconds. Disaster.
It's a decent hack for small, static dimensions (under 80k to be safe). But calling it a limit bypass is marketing fluff. You're just trading one jail for another.