What's the difference? Why do professional cameras support CF but amateur models don't?
Both are flash based, both support FAT filesystems — what's the difference?
Fundamentally they are the same thing in a different package but they work differently.
SD cards use their own protocol which was extended to go beyond 2 GB up to 32 GB with the introduction of SDHC (there were a few 4GB SD cards but not very compatible) and then to support up to 2 TB with the introduction of SDXC. The SD to SDHC transition if you remember was particularly painful as it took years for most other devices (Readers, Picture Frames, Card Readers, Laptops, etc) to catch-up.
CF cards use the IDE protocol which can index large volumes by using pseudo head, track, sector coordinates. That they just kept working as capacities grew, although FAT32 support is used above 2 GB. That makes the protocol more stable and extensible although the next revision is CFast (Compact-Fast) which is based on the SATA protocol.
The larger physical size of Compact Flash also gives them an edge in capacity and they still have a lead in terms of maximum speed as well. This has historically been significant but the gap is so narrow now that it is mostly a case of legacy.
In terms of camera grades, there are high-end models which each type of memory. The Pentax 645D Digital Medium-Format camera uses SDXC cards, Canon top-of-the-line models accept both CF and SD. This leaves only Nikon to exclusively use CF cards in their high-end models.
Five extra years of adoption helps explain why higher-end cameras support CF over SD (and vice versa). Pros tend to standardize; they buy a lot at once, have supporting equipment to match, and don't want to switch frequently. Thus professional standards have higher market inertia, and end up lasting longer than consumer ones.
Also, pro cameras tend to be bigger (especially relative to point-and-shoot and smaller "amateur" DSLRs). CompactFlash is a much larger card than Secure Digital, so the smaller camera form factor forces CF out (in favour of SD and the rest) earlier.
The primary difference, as far as I can tell, is the size of the card. I have pro cameras that support both CF and SD so it's not based on your "seriousness" level. In practice, SD cards are smaller in a crowded camera bag, so you have to be extra careful to track where you put them. Otherwise, I don't see a diff. I've been shooting on a 32 gig SD card for a while (about a year) and it's been as reliable as any CF card I've used.
One thing you'll find is that if you are using an external reader and fast connectivity (i.e., Firewire) is important to you, it's very hard to find a Firewire SD card reader. Fortunately, many laptops come with built-in SD readers.
Hope this helps.