Collision damage waiver, also known as loss damage waiver, is technically not an insurance coverage, it's a waiver of responsibility. By purchasing CDW, you will be not be held accountable for any loss or damage to the rental car. This can be very helpful if you find yourself in an accident while driving a rental car.
Some fine print does exist on a CDW. It commonly states if you violate the rental agreement in any way the purchased waiver will not apply. Know the rules of your car rental agreement, and follow them to the T.