Lots of science boils down to this:
Higher wt number on thread means it is skinnier and will kind of hide so the fabric is more emphasized. The lumps and bumps are more emohasized.
Lower wt thread is thicker thread so the thread shows more.
Choose your thread weight by the effect you want to get out of your quilting.
Of course, if you want to break the rule of thumb, then pile on and paint with the skinny thread to show up more like Libby Lehman does it.
And think about this, if your freemotion or longarm pattern requires a lot of traveling over previous stitches, a thinner thread won’t pile up as much as a thick one.