Yes, this is a big deal.

SCOTUS is going to create precedent no matter what. There are a few outcomes.

First, and easiest to understand, they could rule that Trump is disqualified from serving as President (or any office) per section 3 of the 14th amendment. Congress could remove the restriction, but that would be almost impossible given the current Congressional climate. In that situation, Trump is off the ballot entirely, and while he and his followers will violently thrash about for a while, he won't be President under any circumstances.

I don't think that's likely, but one can hope.

Second, the court could rule that Trump is not disqualified, either because Article 14 Section 3 doesn't apply to President (an incredibly stupid argument that ignores all evidence and rational thought) or because Trump didn't support an insurrection. Could be that they say it wasn't proven to be an insurrection (which isn't true, several participants have been convicted of seditious conspiracy) or that he didn't support it (which isn't true, he gave a speech at the rally and was tweeting his support, not to mention the eyewitnesses at the White House). Maybe the court finds he cannot be penalized because he hasn't been convicted yet, or because the impeachment vote went his way (also stupid arguments that ignore the wording of the amendment).

I think one of these is possible, because the court is a fraud and there is no justice. Any of these findings would require twisting the law and ignoring objective truth in favor of a political win, and these justices have already demonstrated that's something they are capable of.

Third, they could rule narrowly that the Colorado Court and/or the state of Maine can keep him off the ballot, but make no ruling regarding whether he is actually disqualified from holding office. This would be interesting, because they would basically be saying that it's up to each state to make their own determination for at least the primary. The issue could come back to the court later if he ends up with the nomination after being disqualified in some states, at which point they would have to rule on the issue.

I doubt they punt like that.

Last option is they narrowly find Colorado and Maine overstepped their authority, but make no ruling on the larger issue related to whether he is or is not disqualified by the 14th amendment. Could be any number of procedural issues. This split-the-baby attempt puts him back on the ballots everywhere and makes it harder for states to keep him off primary ballots. Like the previous possibility, this would likely end up back before the court assuming Trump is the nominee.

This one seems likely to me, because I can imagine a few of the justices are hoping that the electorate will take the heat by nominating someone else. If Trump loses to Haley, his supporters won't be angry with the Court, nobody can cry that their rights have been violated. And if he does receive the nomination, then they get a second bite at the apple to disqualify him should the justices' owners decide they don't want Trump to be President.