I wouldn't go to Bempton to see Puffins (though you might get to see some), I would be going for the Gannet flight shots ... in some areas there the Gannets will glide close to the top of the cliffs as well as come into land and gather together. I have used a 400mm f2.8 there with great success but I would say that anything from 300-600mm would be able to get some decent shots.
There are various vantage points alongside the cliff-tops and if you take the time to watch you will see where they glide by and may also catch sight of a Fulmar or two