When a card goes in, macOS reads its partition map and tries to mount each volume it finds with a file system it knows: APFS, HFS+, exFAT, FAT32, and NTFS read-only. A card with torn tables, or with a file system macOS does not have a driver for, mounts nothing, and the dialogue appears. The card has not been written to; the three buttons are what happen next. Initialize opens Disk Utility with the card selected and the Erase button a click away; Disk Utility's erase writes a new partition map and a new empty file system over the old, and on a card you want the pictures from it is the wrong click. Ignore leaves the card connected and unmounted, which is harmless but pointless. Eject releases it cleanly. Press Eject.
Disk Utility is still worth opening, because it shows what the Mac can see. The device, SanDisk SD Card Media or similar, appears in the sidebar with its capacity whether or not a volume mounts; a greyed-out volume beneath it means the Mac found a file system it could not mount, which is the format message by another name; no volume at all means it found no file system; a device at 0 bytes, or no device, means the controller has failed. First Aid is the button to leave alone: it checks the volume's structures and writes repairs back to the card, and a volume whose tables were torn mid-write is repaired by First Aid into something consistent and wrong. The bench does the same repair on an image, where a wrong guess costs nothing.
Two causes are peculiar to the Mac. A card from a Steam Deck, a Raspberry Pi or a Linux machine is ext4, which macOS does not read without third-party software; the card is fine and reads on any Linux machine. And older versions of macOS refused exFAT or mounted it badly, so a large card that reads on a current Mac and not on an old one is meeting the system, not a fault. The error codes some people see in Disk Utility, com.apple.DiskManagement.disenter and a number, are the Mac's way of saying the mount failed, and say little about why.
Photos adds a message of its own when a card mounts but its files are damaged: the file could not be imported, against each one. That is a different fault, a file cut short by a dead battery or torn by a bad block, and the file is rebuilt on the bench from the image rather than from the import.