I don't fully understand the hostility to thinking of motives in terms of compatible systems. It's true that a motive is not just a compatible system of realizations (or else we would call it a "compatible system" and not a motive) but it's also true that studying compatible systems has always been a good way to get intuition for many aspects of the theory of motives. Maybe a better thing to say is that a motive should be a geometric recipe which produces a realization in any existing or future cohomology theory.
You asked how to reconcile this perspective on 1-motives with the definition of 1-motives. I guess this means how to derive a compatible system of realizations from a map $X \to G$.
The realization of the 1-motive $X \to G$ in any cohomology theory should be the extension of $H^1(G)$ by $H^1(X^\vee)$. (More properly I guess this is the dual of the realization).
You ask if this is an extension of $H^1(A)$. The group $G$ is always an extension of an abelian variety $A$ by a torus $T$, and $H^1(G)$ is an extension of $H^1(T)$ by $H^1(A)$. So we see that we are potentially extending $H^1(A)$ in both directions.
Which extension? The idea is we should morally get the $H^1$ of the quotient space $G/X$. Since this space is not very well-behaved, we can simulate this, in an arbitrary cohomology theory, as follows:
We observe that any class in $H^1(G)$ (ideally, completely represented by a torsor) is multiplicative in the sense that its pullback to $G \times G$ under the multiplication map is equal to the sum of its pullbacks under the two projections. For torsors, we can fix an isomorphism of torsors realizing this.
An element of $H^1( X \to G)$ is a class in $H^1(G)$ together with a trivialization of its pullback to $H^1(X)$ which is multiplicative in the sense that the pullback of this trivialization to $X \times X$ along the multiplication is equal to the sum of its pullbacks under the two projections (using the above isomorphism to define this sum).
You can follow this recipe in any theory and get a concrete description of the extension.
Furthermore, in every reasonable cohomology theory you will get a clue to the idea that this construction produces all the extensions with "geometric origin". For example, in $\ell$-adic cohomology theories, you will see that extensions of $H^1(G)$ by $X^\vee$ correspond to a Selmer group, and extensions arising from this construction are the image of the map from the group of rational points to the Selmer group. So as long as the Tate-Shafarevich group is finite, there will not be room to fit any more extensions.
Let $\mathfrak{a}:=(X_1-2X_2,X_1-2X_3,X_1)$ as ideal of $R$. Then the Koszul complex of the mentioned generating set of $\mathfrak{a}$ is not acyclic, because $X_1-2X_2,X_1-2X_3,X_1$ is not a regular sequence. However, $X_1-2X_2,X_1-2X_3,X_1$ forms a regular sequence in $R'$, thus the Koszul complex $K_\bullet(\mathbf{a};R)\otimes_RR'=K_\bullet(\mathbf{a};R')$ is acyclic. To see the regular sequence property in $R'$ one can compute by Macaulay; or by hand $(X_1-2X_2,X_1-2X_3,X_1)R'=(X_1,X_2,X_3)R'$! To see the non-regular sequence property in $R$:
We have $X_1(X_2-X_3)=(X_1-2X_2)(-X_3)+(X_1-2X_3)(X_2)$, thus $X_2-X_3\in (X_1-2X_2,X_1-2X_3):X_1$, while $X_2-X_3\notin (X_1-2X_2,X_1-2X_3)$. Note that $X_2-X_3$ will be in the $2$-generated ideal after inverting $2$.
For your new question, set $$M:=\mathbb{Z}[X_1,X_2,X_3]/(X_1-2X_2,X_1-2X_3,X_1,X_2^2,X_3^2).$$ Then $M$ has projective dimension $4$ over $$R=\mathbb{Z}[X_1,X_2,X_3];$$ because after localizing at $(2,X_1,X_2,X_3)$ the module $M$ has depth $0$ and then apply the Auslander-Buchsbaum Formula as well as Formula of Pdim and Localization. However, $M\otimes_RR'=\mathbb{Q}$ has projective dimension 3 over $R'$. Thus it is impossible to obtain a minimal resolution over $R'$ for $R'\otimes_RM$ (minimal in the sense that the length agrees with the projective dimension), by tensoring a free resolution of $M$ over $R$. It is also impossible to obtain a resolution of $M$ over $R$ from, simply, lifting to $R$ a minimal free resolution of $M\otimes_RR'$ over $R'$.
Best Answer
"Life is really worth living in a Noetherian ring $R$ when all the local rings have the property that every s.o.p. is an R-sequence. Such a ring is called Cohen–Macaulay (C–M for short).": Hochster, "Some applications of the Frobenius in characteristic 0", 1978.
Section 3 of that paper is devoted to explaining what it "really means" to be Cohen–Macaulay. It begins with a long subsection on invariant theory, but then gets to some algebraic geometry that will interest you.
In particular, he points out that if $R$ is a standard graded algebra over a field, then it is a module-finite algebra over a polynomial subring $S$, and that $R$ is Cohen–Macaulay if and only if it is free as an $S$-module. Equivalently, the scheme-theoretic fibers of the finite morphism $\operatorname{Spec} R \to \operatorname{Spec} S$ all have the same length.
At the end of section 3, Hochster explains that the CM condition is exactly what is required to make intersection multiplicity "work correctly": If $X$ and $Y$ are CM, then you can compute the intersection multiplicity of $X$ and $Y$ without all those higher $\operatorname{Tor}$s that Serre had to add to the definition.
He gives lots of examples and explains "where Cohen–Macaulayness comes from" (or doesn't) in each one. The whole thing is eminently readable and highly recommended.