I’ve got some - how long it takes to model your domain, how quickly you need an answer, how good the quality of your raw data is, whether your data is append only and/or all of it already exists, and lastly, for how long the solution needs to last.
S3 + SQL is good for huge log/machine data, exploratory use cases that are not yet productionized, ELT (to get data from raw files into SQL, used as a feed to later layers), quick and dirty SQL against a directory of similarly structured files. I tend to think of it as a utility layer.
For long term analytics use, that involves a domain model, I’d still stick with dimensionally modeled (or snowflake) data warehouse techniques. Getting data into such a model can take weeks to months, so sometimes it might be better to do something quick and dirty in a data lake to prove a dataset or get a quick answer, vs. slow down the business waiting for a perfect model.
Lastly, I see storage + SQL as being the same conceptually as any RDBMS, with different performance, cost, and functionality. For example, SQL Server proprietary disk format + SQL Server query engine is somewhat analogous to Parquet + PrestoDB. In fact many proprietary vendors integrate with HDFS as a distributed storage layer for their proprietary formats which can be queried alongside open source storage formats by proprietary SQL query engines too.