A Heist attempt is only considered a failure if you die after the lockdown and lose your contraband. If you die after the lockdown is in effect, you won’t be able to return to the Heist and collect those items (but they can be collected by remaining party members). If you die prior to the lockdown, you can return to the Heist.
You can somewhat consider it a failed state if you don’t reach the Artifact (because you can’t turn it in for a profit, advance your Rogue’s experience or gather intel from the Heist), but if you make it out, you will still get to keep your other rewards.
In a Grand Heist, each wing has its own alert level to manage, so if you fail at one wing you’ll still be able to attempt the others. You can also Stash your items between wings to ‘lock them in’ in case you die in one of the other wings.