I've been using UNPIVOT a lot, it is great for many of my use cases. But somehow it doesn't work when I use the same column in the UNPIVOT and WHERE sections. Is this as expected (like a hard limitation for all database solutions implementing UNPIVOT) or just a current limitation as UNPIVOT is still experimental?

It looks like the UNPIVOT may be processing before the outer level WHERE clause. I was able to reproduce your issue on Wikipedia ... I was able to get around it by using a nested select (or CTE) to apply the where clause, e.g.:

select ...
  from (select * from xxx where feature_age > 30)
  unpivot ...

Thanks for the reply, John. I've suggested both the potential problem cause and this solution with coworkers, but isnt this subquery solution move potentially billions of records from historicals to be processed in the broker?