Some are just hardware pipeline to compute 2d/3d convolutions + activation function (Rockchip RK3588 has one like that). You give it the memory address + dimensions of the input matrix, the memory address + dimensions of the weights, memory address + dimensions of the output, which activation function you want (there are only like 4 supported), then you tell it RUN, you wait for a bit, and you have the result at the output memory address.
(I took Rockchip example to show that even in one microcosm it can change a lot)
And then you can imagine any architecture in-between.
AFAIK they all work with some RAM as input and some RAM as output, but some may have its own RAM, some may share RAM with the system, some might have mixed usage (RK3588 has some SRAM, and when you ask it to compute the convolution, you can tell it either to write to SRAM or system RAM)
It's possible that there are some components that are border line between ISP (Image Signal Porcessing) and a NPU, where the input is the direct camera stream, but my guess is that they do some very small processing on the camera stream, then dump it to RAM, then do all the heavy work from RAM to RAM. I think that Pixel 4-5 had something like that.