I was in San Francisco for my honeymoon in November and heard the sirens go off while we were there. If you aren't near a siren, it isn't that loud, but we were also in the areas with tall buildings when they did the test. There are plenty of areas without taller buildings in SF so YMMV. Anyway, this is not my video, but it looks to be the newest SF siren video. We drove past this particular siren several times on tour buses, but we didn't actually hear this one go off.
https://www.youtube.com/watch?v=rT0iKdqTyx4