SQL Tutorial: Storing data

Key Takeaways

let's discuss the different ways you can store data data can be stored in three different levels the first is structured data which is usually defined by schemas data types and tables are not only defined but relationships between tables are also defined using concepts like foreign keys the second is unstructured data which is schema lists and data and its Raw's form meaning it's not clean most data in the world is unstructured examples include media files and raw text the third is semi structured data which does not follow a larger schema rather it has an ad hoc self-describing structure therefore it has some structure this is an inherently vague definition as there can be a lot of variation between structured and unstructured data examples include no sequel XML and JSON which is shown here on the right because it's clean and organized structured data is easier to analyze however it's not as flexible because it needs to follow a schema which makes it less scalable these are trade-offs to consider as you move between structured and unstructured data you should already be familiar with traditional databases they generally follow relational schemas operational databases which are used for OLTP are an example of traditional databases decades ago traditional databases used to be enough for data storage then as data analytics took off data warehouses were popularized for OLAP approaches and now in the age of big data we need to analyze and store even more data which is where the data Lake comes in I use the term traditional databases because many people consider data warehouses and lakes to be a type of database data warehouses are optimized for read-only analytics they combine data from multiple sources and use massively parallel processing for faster queries in their database design they typically use dimensional modeling and a denormalized schema we will walk through both of these terms later in the course Amazon Google and Microsoft all offered data warehouse solutions known as redshift bigquery and at Azure SQL data warehouse respectively a data Mart is a subset of a data warehouse dedicated to a specific topic data Mart's allowed departments have easier access to the data that matters to them technically traditional databases and warehouses can store unstructured data but not cost-effectively data Lake storage is cheaper because it uses object storage as opposed to the traditional block or file storage this allows massive amounts of data to be stored effectively of all types from streaming data to operational databases lakes are massive because they store all the data that might be used data lakes are often petabytes in size that's one thousand terabytes unstructured data is the most scalable which permits this size lakes are schema and red meaning the schema is created as data is read warehouses and traditional databases are classified as schema and right because the schema is predefined data lakes have to be organized and catalogued well otherwise it becomes an aptly named data swamp data lakes aren't only limited to storage it's becoming very popular to run analytics on data leaks this is especially true for tasks like deep learning and data discovery which needs a lot of data that doesn't need to be that clean again the big three cloud providers all offer a data Lake solution when we think about where to store data we have to think about how data will get there and in what form extract transform load and axe extract load transform are two different approaches for describing data flows they get into the intricacies of building data pipelines which we will not get into ETL is the more traditional approach for warehousing in smaller scale analytics but ELT has become more common with big data projects in ETL datas transformed before loading into storage usually to follow the storages schema as is the case with warehouses in ELT the data is stored in its native form in a storage solution like a data Lake the portions of data are transformed for different purposes from building a data warehouse to doing deep learning okay