Data will eventually end up there. Our product and business teams are heavy users of Redshift.
As mentioned in another comment I’ve found having Dynamo snapshots in Athena really useful as an oncall to sanity check snapshots (what was the state of Harry Potter 3 months ago compared to now?) and to answer product questions that can only be answered from the raw production data.
This is the first time I've come across the approach of storing database snapshots and saving them in a data lake. Do you find those snapshots are useful/used for analytics or data science end-uses, or are they more there for debugging and answering one-off questions?
I have personally only used them for debugging one off questions. That was my original intent. We do have teams that are considering using the snapshots for ML problems.
For a lot of teams, S3 is a data warehouse, and you can treat it just like HDFS for the most part with most things in the big-data ecosystem. Presto works well for letting you access it from these locations without having to explicitly import it (assuming it's in a traditional data warehouse or a common SQL DB).
I wonder if anyone here has a good heuristic for identifying the conditions under which using S3 + SQL layer as a data warehouse is a better choice than a SQL database?
I've been exploring the former and it seems to only make sense if the size of your data is at a scale that is beyond what a single SQL database instance can handle, and even then, you can continue to scale out with systems like Citus so the limit isn't a hard one. SQL gives one so much (data mutability, consistency, indexes, etc.) that I am hesitant to give it up unless the tradeoffs make sense.
I've worked with a S3 + SQL system. It was used for serving data for a reporting dashboard where the stored data was in the 0.1-10 TB range. As the use case was only semi-interactive (users didn't mind waiting 1-10 seconds for a report), and all the queries were pre-defined, this solution was a good fit.
I think it makes sense when there's no in-place updates; either querying write-once data like logs or the output of batch data processing roll-ups that replace the previous data. The less you need the relational model (like joins), the better, but some of those needs can be met through careful design of the storage schema and denormalization.
I wouldn't advocate this sort of solution if your requirements include in-place updates of existing data, frequent/granular updates of new data, expressive ad-hoc queries that use the full capability of relational algebra, or tight latency requirements. You also lose the safety net of referential integrity and table-level constraints, as those are now enforced in custom code that can have bugs.
I would say maintaining this system cost about a half-engineer for ongoing maintenance and new functionality.
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.
Having been back in SQL land for a bit (vanilla MySQL on RDS) I have to say that I _love_ a well designed SQL database. I forgot how much I had given up in NoSQL land.
Goodreads hit scaling issues a while ago with Active Record and a single database so we broke up the data into separate MySQL servers. At that point joining data across DB servers is impossible so we went with Redshift for BI. Nowadays we would probably go with a datalake on S3.
The decision to add SQL on top of S3 probably had a lot to do with a very common use case: people had structured data in S3 but no way to query it.
However, it is also very useful if the following two things are true: 1. You have a very large stream of incoming structured data that is mostly write-once-read-never, like logs. 2. Your query use cases are relatively simple and static. If those fit your use case, then S3 + parquet + Athena is very easy and very cheap.
The serverless capabilities is the big plus IMO. You pay for query. If you go with a typical OLAP system like redshift you need a cluster with a minimum number of machines I believe.
I think it compares more with something like BigQuery but if you already have your data in S3 maybe you get a more well integrated system if you stick with AWS tools.
:this: A concept that's underlying the move to a datalake architecture (read: keeping your data in its rawest form, and its transforms in S3 or HDFS) is decoupling your compute from storage.
Motivating example: you have huge tables in Redshift that are either infrequently accessed or the usefulness of the data decays over time (website logs, customer order information). In this scenario you're paying a lot just to keep data in Redshift (storage) but a large subset of the data is laying dormant (no compute).
If you're bought into the Redshift ecosystem this is where Redshift Spectrum comes in. If you're a smaller company you could just store the data in S3 and "spin up" the compute when you need it (Athena, Glue jobs, or Elastic Map Reduce clusters).
For those of us not actually working at aws redshift gets insanely expensive when your data set grows into the terabytes. Analytics on s3 is much more cost effective using athena snowflake or old fashioned emr as yourdata grows
