A waterproof membrane (like Gore-Tex) in cycling pants gives you full rain protection, but it's only worth it if you regularly ride in wet conditions. Commuters who face rainy climates, bikepackers, and touring cyclists are the ones who benefit most. For most road riders or fair-weather cyclists, waterproof pants are unnecessary—you'd be better off with standard shorts and a separate pair of rain pants you can pull on when needed.
The big trade-off is breathability. Waterproof membranes block rain, but they also trap sweat, which can leave you feeling clammy during harder efforts. They add weight and bulk too, so they're not something you'd want to wear on a hot, dry day. That's why they're typically designed as an outer layer you throw over your regular cycling shorts when the weather turns.
If you only occasionally get caught in the rain, a DWR-coated pant might be a better middle ground. But if you're committed to riding through downpours—especially for commuting or multi-day tours—a waterproof membrane is the right call.