An excellent question. The Germans are often seen as this military juggernaut marching into the USSR against a disorganized enemy. While it's true that they did outnumber the Russians at first, it didn't take long until the latter started to mobilize its reserves and grind the Germans down.

Barbarossa as a lightning war is technically a myth. Since the Soviet Union was so vast, the Wehrmacht couldn't have the mobility to rush across short distances as they had done in Western Europe. In fact, much of the army deployed to the Eastern Front were not motorized and had to depend on walking and horse-drawn transport to get to their objectives. Combined with the USSR's harsh terrain and increasing difficulty to get supplies (especially oil) to the front lines, you pretty much have the German army that is gradually running out of steam. It was only a matter of time before the Germans exhausted themselves, and Russians could reorganize and counterattack with its mass resources.

As for Stalingrad, it put the German Army in an uncomfortable position and it forced them to fight in closer quarters, something that the Red Army was more proficient at. Artillery and aerial support didn't help the situation much, as it created more rubble and encouraged the Russians to hide underground and harass the German ground troops. By late 1942, the Germans still failed to pacify Stalingrad and a Soviet counterattack broke the flanks (which were held by less experienced troops like the Italians/Romanians/Bulgarians) and surrounded the city. Combined with dwindling supplies and of course, the Russian winter, it was an absolute disaster. Hitler's obsession with Stalingrad cost him the entire German 6th Army, but most importantly, he failed to secure the oil fields in the Caucasus mountains, something that the Wehrmacht desperately needed to continue the fight.

To answer your last question, there's no definite answer, but I would say it was Stalingrad, as the Germans could no longer start large-scale offensive operations from then on. German forces on the Eastern Front were exhausted and facing an enemy that never seemed to give up. It wasn't so much tactics that turned things around, but simply a matter of resources, in which the Russians had the upper hand.

The failure of the German attempt to conquer the Soviet Union is a tale of hubris, poor planning, and the enduring military cliche that “the enemy gets a vote too.” It is also the subject of innumerable books, indeed even entire careers, so this answer will by necessity be but a summary. However, before addressing the failure of the invasion as a whole, we should ask “Did Barbarossa fail?” Operation Barbarossa itself, the initial invasion, could by some metrics be considered a resounding success. From June 22 to the end of 1941, the Wehrmacht inflicted some 3.6 million irrecoverable casualties on the Red Army, including 2.8 million prisoners. One hundred and seventy seven Soviet divisions were destroyed during this process - either destroyed or captured in the great encirclements at Bialystok, Minsk, Smolensk, and Viaz’ma-Bryansk, or mauled so severely that they had to be almost entirely rebuilt behind the lines. The prewar combat power of the Red Army evaporated during these six months - the Red Army Air Force (VVS) was bled white, losing nearly its entire complement of attack aircraft and nearly 70% of its bombers, and more tanks were lost than Soviet industry replaced during this period. Wehrmacht reconnaissance units advanced to within 20 miles of Moscow proper, with 7th Panzer Division’s maneuver forces getting within 22 miles.

And yet, Barbarossa was definitively a failure, because it failed to achieve the strategic aims of the German high command. These aims were:

1. Quickly destroy the bulk of the Red Army’s forces in encirclement battles (a grand Vernichtungsschlacht, a battle of annihilation) west of the Dnepr river
2. Once the Vernichtungsschlacht was achieved, the Wehrmacht would advance largely unopposed to the map line running from Arkhangelsk in the north to Astrakhan in the south, capturing Moscow in the process. The Arkhangelsk-Astrakhan line represented an advance of nearly a thousand miles. This optimistic line was chosen because it was viewed as a sufficient distance to prevent Soviet bombers from threatening the German heartland.
3. Having occupied Ukraine, Belarus, the Baltics, and Western Russia, the Germans could make full use of the oil and grain of these regions to fuel their war machine and feed their people (at the detriment of the Slavic populations of these territories, who were in the long run to be expelled or exterminated anyway, so the mass starvation from this policy was viewed as a feature and not a bug by German high command).

The German high command estimated that it would take approximately seventeen weeks to attain the Arkhangelsk-Astrakhan line. This, as you may guess, was not a terribly realistic plan. For one, the German intelligence assessments of Soviet strength were too optimistic. They believed the Red Army possessed 147 divisions and 40 independent brigades in the western districts, plus an additional 33 divisions and 5 brigades further east. In fact, there were 186 divisions in the western districts alone, and a total of 303 divisions throughout the USSR, although many of these were still in the process of being formed at the time of the invasion. The unpredicted reserves threw a major wrench in the entire Vernichtungsschlacht plan of operations. Wehrmacht officer memoirs describe feelings of frustration as they kept advancing into fresh forces and getting bogged down in counterattacks. This goes beyond the claim that the Soviets won by numbers; as you noted in your question, the correlation of forces at the start of the invasion was in fact in the Reich’s favor. However, the unanticipated reserves gave the Soviet resistance the depth and tenacity to erode German combat power and create the conditions for counteroffensives.

Furthermore, the logistics situation facing the Wehrmacht was unlike anything they had experienced before. The advance into France was 250 miles deep at its farthest extent, whereas the Dnepr river alone is over 300 miles east of the German starting point, with Moscow another 350 miles beyond that. A staff study based on the experience in France had indicated that supply trucks had a maximum round-trip range of about 180 miles, beyond which point they would have to set up relays of multiple trucks. This point was largely swept under the rug during planning, but supply difficulties would dog the Wehrmacht throughout their advance, giving the Red Army time to regroup, establish new defensive lines, and prepare for counterattacks. For instance, Army Group B’s advance on Stalingrad ran out of steam before assaulting the city and had to wait for refueling and resupply, giving Soviet 1st and 4th Tank Armies time for an admittedly indecisive counterattack. Lack of supplies meant that German offensives lacked combat power, as well, due to decreased artillery ammunition loads and insufficient numbers of working tanks - keeping the panzer fleet operational required not only large amounts of fuel, but adequate spare parts.

However, the outcome of the first period of the war (the period from the invasion to the Operation Uranus counterattack at Stalingrad) was not merely a Nazi failure, but a Soviet victory. A hard-won victory, a victory marred by missteps, hard lessons, and at times appalling callousness, but a victory nonetheless. The Red Army was caught with its pants down in June 1941, for a variety of reasons which may be beyond the scope of this answer (but which I can address if you’d like), but even the disastrous early days were marked by gallant attempts at counterattacks. These attempts, generally lacking adequate mass and preparation, failed in their aim of destroying Wehrmacht forces in the field and going over to a full offensive. However, as David Stahel has argued, these counterattacks wore down the Wehrmacht and inflicted losses from which the Wehrmacht could not recover in time for the next year’s fighting season. As David Glantz has further argued, the battle of Smolensk in particular blunted Army Group Center so thoroughly that from that point onwards the Wehrmacht both could not maintain the tempo and lacked the combat power required to attain its original goals. At Moscow, the Wehrmacht’s growing weaknesses met mounting Soviet skill, determination, and ability to marshal well-equipped forces. The result was that Army Group Center had its nose bloodied quite badly, and the Soviets went over to their first proper offensive of the war. This offensive was overambitious, given how battered Soviet forces were after absorbing Army Group Center’s offensive, but it did inflict serious casualties and push German lines back to a less advantageous starting point for their next offensives.

The premature offensive spirit still dogged the Red Army through 1942, most notably at the disastrous Second Battle of Khar’kov. Stalin and the General Staff’s insistence on launching offensives before they were fully prepared and adequately supported by reserves resulted in some messy, ineffectual operations. However, each failure brought lessons learned, and the Red Army brought both its force structure and its actual practice of war more in line with their prewar doctrine, which had been abandoned during the purge years. The lessons learned were implemented in full force by Operation Uranus: breakthrough sectors were supported by heavy artillery preparation, and troop densities were adequate to force the breakthrough. The end result? The encirclement of 6th Army, and the mauling of several Axis-ally supporting forces in Army Group B.

As /u/rendersen25 notes in their answer, from this point on the Wehrmacht found itself increasingly unable to contest and hold the strategic initiative. However, in closing I do want to heavily note that this was emphatically not “simply a matter of resources” but indeed a matter of tactics, operational art, strategy, and the other immaterial aspects of combat performance. The Red Army improved its combat performance immensely between 1941 and 1943, which is discussed in much detail in this thread. In short, the Red Army was still digesting the lessons of the Winter War when Barbarossa began, and by 1943 it had mostly digested those lessons as well as the new lessons of Barbarossa (mostly a better understanding of the force ratios needed for counteroffensives, as compared to the hasty counterattacks of 1941).