How many OS assumptions does it break to have a massively oversized capacity on a floppy drive?

Or is it just using 1.44mb of a multi-GB SD card?

The GoTEK (or similar) use several floppy images (each one 1.44 MB) and you can select manually which one to use.

I think it depends on the model, but should be able to select among up to 100 or 1000 such images.

Often, these present as multiple floppy sized chunks for exactly the reason you cite. I don't know about the Sony described, but the Gotek I have has a little front panel that let's you select a numerical floppy unit that presents as a: or whatever.

I'm assuming if it's a floppy emulator it just has floppy density, but you can emulate an IDE hard drive as well.

No, it is not a ATA/IDE (nor SATA) device, it is intended as a "direct replacement" for floppy drives (on embroidering machines, music instruments, CNC and similar), so it uses the 34 floppy connection (on PC-like hardware).

There is - besides Gotek - an alternative which is open source and (if one wants to) DIY, the HxC, a few different possibilities:

http://hxc2001.free.fr/floppy_drive_emulator/index.html

Oh, I may have been misunderstanding the parent comment but it sounded like they were saying it was an adapter that let you put flash memory devices of some sort into a regular floppy drive.

There are exactly such devices.

Neutron star density