If you always cruise with the same person, it's probably better to play on only one card. That being said, traditionnally it has been known that on your sail @ sign card 1 point = 2.50$ played (not lost) with Carnival. The only real thing that is known, however, with Carnival, is you get free drinks in the Casino when you get to 1500 points on a 7-day cruise.
Other than that, your guess is as good as mine. For years, we were told the offers you would get were, in part, based on you rank in money spent in the casino. In other words, some times you could have spend 2000$ during your cruise, but only be ranked 500th on your sail & sign card. Other times, you could "only" spend 1000$ but be ranked 400th, therefore get better offer because of it. Carnival, contrary to some other companies, is only counting one cruise at a time.
It is also known that since the cruises have been back, Carnival has been cutting a lot on those offers. Also, nowadays, it is more and more believed that if you have not cruised a lot yet with Carnival (say you have a red sail & sign card), you will get better offers than those with Platinum or Diamond levels.
Back about a decade ago or so, once, we got our sail & sign card to about 10 000 points on a cruise (we like to create "themes" when we cruise and that year the theme was "The Gamblers"). We have not paid for a single cruise since then.
Of course, if you start playing enough in the casino, expect to get A LOT of cookies, chocolate covered strawberries and things like that in your room. And also steakhouse meals as well (at least once on every cruise).