Comparison Between Using Dbt + (Athena vs Redshift or Snowflake) as a Data Warehouse - Which Path Should I Take?
I'm Currently Using Dbt and Athena as a Data Warehouse, and It's Able to Do Transformations and Write Data Back to S3. We Don't Do Any Inserts/Updates/Deletes...
I'm currently using DBT and Athena as a data warehouse, and it's able to do transformations and write data back to S3. We don't do any inserts/updates/deletes.
I should say that the amount of data I currently have is quite small, but I imagine Athena can scale well considering it was developed for Facebook.
My question then, is what are the reasons for using Redshift or Snowflake considering how much more expensive they are? What am I missing?
If I had more data, in terabytes perhaps, would this affect the choice of warehouse?
I have not tried Snowflake or Redshift, so perhaps I am missing some context. Happy to learn from you folks!
1 Answer
Reporting systems need a means of running SQL against data.
Traditionally, this meant that a Database was required, and all databases (at the time) consisted of both Storage and Compute. There was no capability to separate these two components because the database stored its data in a proprietary format and the Compute component was required access that data.
As data volumes increased, traditional databases struggled to provide fast performance. This led to a new class of Data Warehouse systems that specialise in querying tables with billions of rows and Terabytes of data. These systems typically use parallel infrastructure and columnar storage split across multiple storage nodes to provide fast performance. Examples are: Amazon Redshift, Snowflake.
The next evolution came from Presto (and can be traced back to Hadoop), which was the idea of completely separating the Compute and Storage components of databases. Optimized for querying, Presto could query data stored in cloud services (eg Amazon S3) without having to load the data into the database (known as a 'query engine'). This was not only a mind-blowing concept, but depending upon the data format (eg Snappy-compressed Parquet) could actually rival the speed of Data Warehouses. Plus, the fact that they are cloud-native, it was easy to scale Compute as needed for short periods of time.
The main thing to understand about Query Engines is that data is not 'loaded' into them. Rather, when a query runs, they go to the storage service, look at data stored in whatever format and then calculate the answer to the query. Data can be added by simply adding another file in the storage location.
Examples of Query Engines are: Presto, Amazon Athena, Amazon Redshift Spectrum
The downside of using Query Engines is that they are not good at inserting/updating data. This has been addressed by the Delta Lake file format that uses a combination of Parquet files and logs files to allow data to be inserted, updated and deleted. This is the main focus of Databricks.
Of course, if your data needs are small, it is quite acceptable to use a traditional database (eg PostgreSQL) as a Data Warehouse.
The best approach is to start with something small until it no longer meets your needs. Then, move to something more powerful. If Amazon Athena is meeting your needs, then there is no need to move to anything else.
(My apologies for not including Google and other services as examples. My knowledge is mostly limited to AWS services.)
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
