Recommended screw fastener length
I will to try to give a few numbers you may find useful based on what I have found over my years of connecting one thing together or the other.
On Tapcons, Philcons or Con-certs or just good old concrete screws, 1" embedment into is the typical depth I see referred to. The screws are sold at those lengths with that in mind. 1 3/4" for 1X material. I checked the website and it is mentioned that the depth can be as deep as 1 3/4" bury into masonry. In my experience, in hard concrete, or brick, a screw will break after 1 1/4" into a hard surface. If you are setting it in the joints of the brick or perhaps cinder block or a soft brick like older handmade brick that are not real dark, 1 3/4" into the material is pretty easy to do. There are 2 different diameters to chose from too. That can be as simple as the judgment call of "this thing is really heavy and I don't want it to move so I will use the bigger diameter one" or, "This will not be too tough to set, so the small one will do." If the item you are going to secure is going to vibrate, I strongly advise against using concrete screws, unless glue can be used in some way to help keep it together. Wedge fasteners may be a better choice for that, or better still an anchor bolt with expansive concrete set into a drilled, cleaned hole will hold the most reliable if strength and resistance to vibration is what you are looking for.

When fastening wood, once the piece to be fastened is gone through, generally I find 1" into the second, or the piece you are fastening to is enough to hold. Of course, there are variations on this, depending on the circumstances. On deck boards, IMHO, 3" screws for a 5/4  deck board is way over kill, 2 1/2" is plenty for that. For 2X deck boards, 3" is a good fit. I have also seen 3/4" or less, work well in thin material that you may not want to go all the way through, but there may be need for many screws needed in a given area to have a strong hold. When screwing into oak or other hardwoods, 3/4" will hold very well. When fastening a piece to the end of another, as in making a corner for a box, I heard a long time ago the screws needed to be 3 times longer than the piece you are fastening, because end grain does not hold a screw as well as going into the wood perpendicular to the grain. The part about end grain not holding a screw well is true, but I find the screw does not need to be that long at all. A 4 1/2" screw to hold 2X together?? A bury of 2" works pretty good in most cases, in a joint that does not have much stress applied to it. If the screw can be slightly angled, instead of going straight into the end grain, what it can hold will be greatly increased. Whenever possible/practical, it is best in any case to use pilot holes in the piece to be fastened, to reduce or eliminate splitting. There will be cases where the pilot needs to be in the other piece as well to prevent splitting. Oak is a good example of needing properly sized pilot holes in each piece. I might add more to this when I get more time, or depending on the comments received, if clarification is needed.