Google Cloud Certified Data Engineer: 7. Designing Databases for Reliability, Scalability, and Availability - Important Things To Know

1. Understand how to denormalize data in BigQuery using nested and repeated fields.
2. Denormalizing in BigQuery can be done with nested and repeated columns. A column that contains nested and repeated data is defined as a RECORD datatype and is accessed as a STRUCT in SQL. BigQuery supports up to 15 levels of nested STRUCTs.
3. Know when and why to use partitioning and clustering in BigQuery. Partitioning is the process of dividing tables into segments called partitions. BigQuery has three partition types: ingestion time partitioned tables, timestamp partitioned tables, and integer range partitioned tables. In BigQuery, clustering is the ordering of data in its stored format.
4. Clustering is supported only on partitioned tables and is used when filters or aggregations are frequently used.
5. Understand the different kinds of queries in BigQuery. BigQuery supports two types of queries: interactive and batch queries. Interactive queries are executed immediately, whereas batch queries are queued and run when resources are available. The advantage of using these batch queries is that resources are drawn from a shared resource pool and batch queries do not count toward the concurrent rate limit, which is 100 concurrent queries.
Queries are run as jobs, similar to jobs run to load and export data.
6. Know that BigQuery can access external data without you having to import it into BigQuery first. BigQuery can access data in external sources, known as federated sources. Instead of first loading data into BigQuery, you can create a reference to an external source. External sources can be Cloud Bigtable, Cloud Storage, and Google Drive.
7. When accessing external data, you can create either permanent or temporary external tables. Permanent tables are those created in a dataset and linked to an external source.
8. Temporary tables are useful for one-time operations, such as loading data into a data warehouse.
9. Know that BigQuery ML supports machine learning in BigQuery using SQL. BigQuery extends standard SQL with the addition of machine learning functionality. This allows BigQuery users to build machine learning models in BigQuery rather than programming models in Python, R, Java, or other programming languages outside of BigQuery.