Wildly speculative and totally without any knowledge whatsoever, my predictions for the season finale of Picard:
NSFBC
CBS made it clear before this season started that not everyone is going to make it to the end. I expect that there will be two of the main cast not to make it: Picard and Data. Picard because this will be his big sign off from the show and Data because he has been killed more times than Sean Bean (and resurrected more times than Phil Coulson).
Also biting the big one will be the Enterprise-D. This is because it makes sense in the grand scheme, but also because everyone was dissing the “fat” Enterprise early in the series, so she will show them all that this fat lady can still sing.
Picard is going to use his residual Borgification to tap into the collective and connect with Jack. They will use the same technique that was used in The Best of Both Worlds, where Jack implants a shut down command into the collective, and the Enterprise destroys the Borg ship with the Queen aboard. With the loss of the signal that triggered the kids to assimilate, they will all return to normal. Later, they will use the transporter to strip the Borg code from their DNA - and they might even do this to Geordi’s kids at some point during the fight.
With the Borg cube sitting way in the backfield. Our heroes will have to fight on two fronts: at Earth against the assimilated fleet and against the cube wherever it is (Jack leads them to it either willingly or unwittingly through his connection to Picard). This will mean separating the saucer section - also done in BOBW - with all the associated emotional drama of the crew splitting up, knowing that this will likely be the last time any of them see each other.
Picard and Data will take the battle section - with the warp drive - and go after the Borg cube. The others will use the saucer section to occupy the fleet for as long as they can - maybe somehow mucking the Borg signal to gain some support. Eventually, though, the saucer will be damaged to the point that it is dead in space and about to be destroyed by the fleet when Picard and Data destroy the Borg cube. They both lose their lives in this effort - perhaps by blowing up the warp core to take out the cube - but Jack somehow escapes in his shuttle to begin the adventures of Picard Jr.
Or…
Variation on a Theme
As above but reversed. Dr. Crusher et al take the battle section to fight the Borg and save Jack. Picard, Data and Worf take the saucer section to fight the fleet, with Data piloting, Worf at the weapons station and Picard trying to muck the Borg signal to the kids. Worf will declare that it is a good day to die and, this time, he’s right.
I’m now thinking that this is the more likely scenario, with Jack killing the Borg Queen and destroying the cube from the inside before escaping in his shuttle.