Saying CDBI is more lightweight than Tangram, is like saying that DBI is more lightweight than CDBI.
Of course it's more lightweight, but it doesn't provide any real abstractions. CDBI is just another SQL macro library; it just happens to be the most actively developed. It's a very good alternative to raw DBI.
There is a large class of problems for which Tangram can solve easily, and CDBI just has no solution at all. For instance, with Tangram it is very simple to build up "complex" queries without resorting to hacking SQL. Likewise, if you're retrofitting to existing database schemas, Tangram can seem awkward and inept.
However, for building large scale database driven applications from scratch, my personal experience would say that using Tangram in concert with Class::Tangram gives you the most flexibility and adaptability of any currently available database abstraction for Perl 5.