How does this constant current sink actually work?
The circuit employs negative feedback and utilizes the very high gain of the op amp. The op amp will try to keep its non-inverting and inverting inputs at the same voltage \$V_{\text{set}}\$ due to its very high gain. Then by Ohm's law

$$I_{\text{set}} = \frac{V_{\text{set}}}{R_{\text{set}}}$$

Negative feedback causes the op amp to adjust the transistor base voltage so that \$I_{\text{set}}\$ is constant even with a varying load. If the varying load causes a temporary increase in \$I_{\text{set}}\$ then the voltage at the op amp's inverting input will temporarily rise above the non-inverting input's. This causes the op amp's output to decrease, which lowers the transistor's \$V_{BE}\$ and therefore its \$I_{C} \approx I_{\text{set}}\$.

Similarly, if the varying load causes a temporary decrease in \$I_{\text{set}}\$ then  the voltage at the op amp's inverting input will temporarily fall below the non-inverting input's. This causes the op amp output to increase, which increases the transistor's \$V_{BE}\$ and \$I_{C}\$.