Next, let's discuss your data storage and extract, transform, and load options on Google Cloud. Your options on Google Cloud for building a data lake are your storage solutions you saw earlier. You've got Cloud Storage as a great catch-all, Cloud SQL and Cloud Spanner for relational data, and Firestore and Cloud Bigtable for NoSQL data. Choosing which option or options to use depends heavily on your use case and what you're trying to build. In this lesson, we will focus on Cloud Storage and Cloud SQL, but you will see the NoSQL options like cloud Bigtable later on in the course when we talk about very high-throughput streaming. How do you decide on which path to take for your lake? The final destination for where your data lands on the Cloud and the paths that you take to get your data to the Cloud depends on where your data is now. How big your data is, this is the volume component of the three V's of big data, and ultimately where does it have to go. In architecture diagrams, the ending point for the data is called a data sink. A common data sink after a data lake is your data warehouse. Don't forget a critical thing to consider is how much processing and transformation your data needs before it is useful to your business. Now you may ask do I complete the processing before I load it into my data lake or afterwards before it gets shipped off somewhere else. Let's talk about these patterns. The method that you use to load the data into the Cloud depends on how much transformation is needed from the raw data that you have, to the final format you want it in. In this lesson, we will look at some for the considerations for the final format that you want it in. The simplest case might be that you have data in a format that is readily ingested by the Cloud products that you want to store it in. Let's say, for example, you have your data in Avro format and you want to store the data in BigQuery because your case fits what BigQuery is good at. Then what you do is simply EL or extract and load. BigQuery will directly load Avro files. EL refers to when data can be imported as is into a system. Examples include importing data from a database where the source and the target have the same schema. One of the features that makes BigQuery unique is that as you saw before with the federated query example, you may end up not even loading the data into BigQuery and still can query off of it. Avro, ORC, and Parquet files are all now supported for federated querying. The T in ELT is transform. That's when the data loaded into the Cloud product isn't in the final format you want it in. You may want to clean it up or maybe you want to transform the data in some way, for example, if data needs to be corrected. In other words, you would extract from your on-premise system, load into the Cloud product, and then do the transformation. That's an extract, load, and transform, or ELT. You tend to do this when the amount of transformation that's needed is not very high and the transformation will not greatly reduce the amount of data that you have. ELT allows raw data to be loaded directly into the target and transforms there. For example, in BigQuery, you could use SQL to transform the data and write a new table. The third option is extract, transform, and load, or ETL. This is the case when you want to extract the data, apply a bunch of processing to it, and then load it into the Cloud product. This is usually what you pick when this transformation is essential or if this transformation greatly reduces the data size, so by transforming the data before loading it into the Cloud, you might be able to greatly reduce the network bandwidth that you need. If you have your data in some binary proprietary format and you need to convert it before loading, then you need ETL as well. ETL is a data integration process in which transformation takes place in an intermediate service before it is loaded into the target. For example, the data might be transformed in a data pipeline like dataflow before being loaded into BigQuery.
