For this purpose I think it's fantastic. Write a PySpark script and press go (we have a package on pypi called etl_manager to facilitate this). It 'just works' for this use case, and there's a huge amount of value for us in not having to think at all about managing or configuring a Spark cluster.
Our biggest bugbear was slow job startup times and a lack of pip installs, but both of those are fixed with glue 2.0 which was released recently.
We don't use any of the visual/GUI based tools for our jobs, we just write our own Spark code and version control in Github. That's unlikely to change any time soon with products like Databrew. That said, the data profiling tool in Databrew does look like it could be useful as something to refer to when writing code.
(I realise this doesn't help with your specific issue, but i thought it was helpful to offer an example of a good experience)
This is spot on IMO. I use Glue internally (opinions are my own) and still believe that the best course of action is Glue should only run managed Spark. We provide an empty Scala "script" that does nothing, and load a compiled JAR file with the Scala code that actually runs our job as a library and have Glue exec into that.
We can version the ETL in git, run local tests outside of the Glue data plane, prototype in the Spark shell, and much more.
Super simple incremental loading. This also works when loading data from a relational database, by storing the greatest primary key value.
In general, AWS excels at a lot of core features (EC2, S3, the databases) but the higher-level services feel very thrown-together, with awful documentation. They are launching a large amount of half-baked services these days, (intending to capitalize on vendor lock-in?), but it's making the ecosystem start to look like a confusing mess. A lot of these wouldn't survive if they were marketed as standalone products.
Ended up going with an ETL as a service (Alooma and now transitioning to ETLWorks) to extract and load our data into Snowflake.
If not, would you be willing to share where some of the big pain points were?
I eventually got it set up, but for the amount of effort involved, I could have just wrote my own custom ETL solution in less time.
The scheduling jobs and triggers is nice once set up. I do hope AWS makes improvements because Glue has potential, but it doesn't feel like it is ready for prime time yet.
Another example is that some date/time columns got brought in and crawled as a string. That's a bummer because obviously you want to do native operations of these, datediff, datepart, etc. without having to cast all over the place. We manually set them to timestamp and they work perfect (awesome!), even in Athena, so we thought the problem was solved.
However, once we did anything with those columns in Glue ETL, those fields got set as nulls.
The problem can be fixed of course, but these types of issues happen fairly often and they quietly fail (no errors, just values set to null).