How long does the countertop need to be in order to need a countertop bracket?

This really depends on the scenario but it is usually around 48" when the countertop needs extra support.

For a corner countertop that has no support underneath, you will need a 2x2 wall cleat and antyhing over 4 Ft countertop length should have a countertop bracket if there is nothing underneath.

Wall cleat can be pine wood.