please create a case study section based on this
please add to the previous case study these details

Sequencing:
1.1:	Create a new AWS S3 bucket with storage class S3 Standard to store parquet files.
1.2:	Create the same tree structure on the filesystem to support partitioning.
1.3:	Create a process that will read data from each source twice an hour and create one parquet file. Thus, you get one big parquet file per hour instead of many small *.csv.gz files.
Benefits:
a)	Reducing the total amount of data, (approximately 300 Gb).
b)	Reducing the cost of data storage, (approximately $7 per month).
c)	The amount of scanned and transmitted data is reduced.
d)	Increase the speed of reading data several times.
e)	The format is great for Data Science calculations. 
WHAT IS PARQUET?
Apache Parquet is an open source, column-oriented data file format designed for efficient data storage and retrieval.
Characteristics of Parquet
•	Free and open source file format.
•	Language agnostic.
•	Column-based format - files are organized by column, rather than by row, which saves storage space and speeds up analytics queries.
•	Used for analytics (OLAP) use cases, typically in conjunction with traditional OLTP databases.
•	Highly efficient data compression and decompression.
•	Supports complex data types and advanced nested data structures.
Benefits of Parquet
•	Good for storing big data of any kind (structured data tables, images, videos, documents).
•	Saves on cloud storage space by using highly efficient column-wise compression, and flexible encoding schemes for columns with different data types.
•	Increased data throughput and performance using techniques like data skipping, whereby queries that fetch specific column values need not read the entire row of data. 
3 MANAGING OPTIONS AVAILABLE FOR THE RAW LAYER
STAGE 1. STEP 2.
Option 1. Let it be as it is.
Advantages:
•	Constant and unlimited fast access to data.
•	There is no need to change anything.
•	Stores historical data in its original form.
Disadvantages:
Additional data storage costs: 2,3 Tb; approximately $53 per month.
Option 2. Set up automatic archiving by changing the storage class to S3 Glacier Instant Retrieval
Advantages:
•	Reducing the cost of storage (approximately $10 per month).
•	The effect will become noticeable only in the future when a huge amount of data is reached.
•	Stores historical data in its original form.
Disadvantages:
•	It is necessary to develop a process that supports automatic archiving of source data.
•	Suitable for long-lived archive data accessed once a quarter with instant retrieval in milliseconds. 
3 MANAGING OPTIONS AVAILABLE FOR THE RAW LAYER
STAGE 1. STEP 2.
Option 3. Delete original CSV files after conversion into parquet files.
Advantages:
•	Reducing the cost of data storage.
•	Partially solves the problem of storing users' personal data.
Disadvantages:
Denies access to historical data in case of emergencies. 
DATA ORCHESTRATION
STAGE 1. STEP 3.
Sequencing:
3.1:	Launch an instance of AWS EC2: 4CPU; 16Gb RAM; 30GB HDD.
3.2:	Configure environment: run Linux based OS; install Apache Airflow; install Python; install PostgreSQL (for keeping metadata).
3.3:	Create ETL jobs in Airflow in order to transform and moving data sets.
3.4:	Set up orchestration to run your data pipeline automatically.
Benefits:
a)	Apache Airflow is a free and open-source ETL tool.
b)	AWS EC2 has a predictable low cost when it is used 24/7 for long periods of time.
(Prices will be described on another slide).
c)	The data pipeline will run fully automatically.
d)	Apache Airflow is flexible, scalable, versatile and widely used tool suitable for both Data Engineers and Data Scientists.
e) It is possible to set up notifications.	0 EC2
1		1	*
t t	/v Airflow	1
J
		1 I
Extract ‘	Transform	Load
 

 
WHY APACHE AIRFLOW?
Apache
Airflow


 
STAGE 1. STEP 3. 

 
We have compared the following ETL tools:
1.	AWS Glue
It has a primitive GUI and limited features.
The ratio of price to quality is worse than that of competitors.
2.	MatillionETL
It has Snowflake partner connect.
The emphasis is shifted towards using ANSI SQL and a GUI, making it difficult to add custom functionality.
Lack of specialists familiar with this tool.
3.	Shipyard
It has Snowflake partner connect.
The emphasis is shifted towards using ANSI SQL and a GUI, making it difficult to add custom functionality.
Lack of specialists familiar with this tool.
4.	Fivetran
It has Snowflake partner connect.
It is difficult to calculate the final cost of the platform.
5.	Amazon MWAA
Almost the same as Airflow, but much more expensive.
There is a limit on the number of running jobs.
6.	Apache Airflow
The most popular and widespread tool among data engineers. This application is based on Python.
It has a simple and intuitive GUI. Easy to learn and manage. 

 
WHAT IS APACHE AIRFLOW?
STAGE 1. STEP 3.
Airflow DAGs Datasets Security- Browse Admin Docs-
DAGs
I AISQ
Active
O DAG C
dg_orchestraticui_eommon_dwh orchestration
cifl„orche5if3tionclm dm DTchestrabon
dg_orcti e strati onods ods orchestratiofi
<ifl_orctie5tration_sa orchestration sa
	X orchestration		®'	L
Owner C	Runs 0		Schedule	Last Run
alifldw	©	©	01 ■" @	2022-11-13, 01:00:00 ®
airflow			gone* <0	2022-11-14.01:17:33 Q
airflow	©		©oiwe O	2022-11-14, 01:00:22 O
aifflow ■	00		@CHice	2022-11-14, 01:14:29 0

Searct) DAGs
Next Run C 0
2022-11-14.01:00:00 ®



















 
WHAT IS APACHE AIRFLOW?
STAGE 1. STEP 3.
Airflow DAGS Datasets Securtty Browse Admin Doos
prchestration^sa 

 
Send
u Graph
Q Calendar
2 Task Duration
S Task Tries
Landing Times Gantt A Details
< > Code Audit Log
Q 2022--1-14T01'4 29Z
Runs 25	w|| Run manuai_2022-ii-i4Tai-i4:294&9&a5+oo;oo « Layout _sTt>RigtTt w^nZpoatT
I Find Task.

 

I flfcteireJ I paliea| [queaiKlj [mnwrig]	[■slappedj	|up ftir ig»elieil|ile|	] upstreaiH taBed^ na^itSus 
WHAT IS APACHE AIRFLOW?
STAGE 1. STEP 3



 

Sequencing:
4.1:	Prepare the Snowflake environment.
4.2:	Create Stage Object to connect to the S3 bucket with the parquet files.
4.3:	Create a new database in Snowflake.
4.4:	Create a new Schema in Snowflake - "Operational Data Storage" (ODS).
4.5:	Create External Tables in the ODS schema.
Benefits:
a)	No data duplication.
b)	No physical data storage.
c)	Free data transfer to same region, same cloud.
d)	Ability to work with parquet files using the SQL.
e)	GET, SELECT, and all other requests operations in S3 will cost you 0.0004$ per 1,000 requests. (It's 4$ per 10 millions requests). 
WHY SNOWFLAKE?
We have compared the following ETL tools:
1.	Postgre SQL
•	It's possible to improve the overall performance of dashboards by creating a DWH and after SQL performance tuning.
•	But it is not suitable for storing huge amounts of raw data.
•	It is not possible to update datasets twice an hour.
2.	Amazon Athena
•	Is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL.
•	Not suitable for creating a full-fledged DWH.
3.	Amazon Redshift
It has many limitations and features in use, therefore it is not suitable for use by an unprepared user.
4.	Google BigQuery
•	One of the best cloud solutions for big data analytics.
 

5H>^ snowflake’
•	It requires the transfer of a large amount of data over the network from one cloud to another when working with S3, so it degrades the overall performance. It can only be resolved by duplicating the data in GCP.
•	The price for storing and processing data is significantly higher than that of competitors.
5.	Snowflake
•	One of the best cloud solutions for big data analytics.
It is not an independent solution, but is based on one of the cloud platforms including AWS.
•	Supports ANSI SQL and various types of stored procedures.
•	The cost of storing and processing data is the cheapest among competitors.
•	You only pay for the resources actually used.
•	It is possible to run several virtual warehouses of different capacities in parallel. 
SNOWFLAKE BENEFITS
STAGE 1. STEP 4.
Snowflake supports a wide range of virtual warehouse sizes: X-Small, Small, Medium, Large, X-Large, 2X-Large, 3X-Large, and 4X-Large. The size of the virtual warehouse determines how fast queries will run. When a virtual warehouse is not running (that is, when it is set to sleep mode), it does not consume any Snowflake credits. The different sizes of virtual warehouses consume Snowflake credits at the following rates, billed by the second with a one-minute minimum.
VIRTUAL WAREHOUSE SIZE
L XL 2XL 3XL 4XL 5XL* EXL* 

 
Credits
consumed 1
per hour
8	16	32	64 128 256 512



 
SNOWFLAKE BENEFITS
STAGE 1. STEP 4.
•	The easiest and most flexible way to purchase the Snowflake Service is On Demand. Similar to Amazon AWS, customers are charged a fixed rate for the services that are consumed and are billed in arrears every month.
•	$40 per Terabyte per month for customers deployed in AWS - US West (Oregon).
ON DEMAND	Price per Snowflake Credit
	
	
	

•	Free data transfer from AWS S3 to same region, same cloud.
•	Provides the ability to quickly migrate to another cloud. There is no vendor lock.
•	Snowpipe is Snowflake's continuous data ingestion service.
Snowpipe loads data within minutes after files are added to a stage and submitted for ingestion but incurs an additional cost. 
STAGE 1. BUSINESS USAGE
  
CREATION OF STAGING AREA