A dual-piston design is less prone to pulsation in the airflow and can provide a higher, more consitent output pressure compared to a diaphragm type.
You will get pulsation in the airflow of a diaphragm compressor because by its very nature, (for want of better terms) there is a "compression" (up) stroke and a "return" (down) stroke. The diaphragm must return to its starting position and while this happens, it's not pumping air.
In a dual piston system, one piston is "compressing" while the other is "returning", so it is able to maintain a consistent output
Due to the flexible nature of a diaphragm, it can have a lower maximum output pressure (though it's still sufficient for most airbrushing purposes).
In extreme cases, excessive backpressure in a diaphragm compressor can rupture the diaphragm. (hence the need for a "bleeder" valve in a diaphragm compressor).