BigQuery For Data Warehouse Practitioners

into a bat table structure. For example, for sales transactions, you would write each fact to a record,along with the accompanying dimensions, such as order and customer information.In contrast, the preferred method for denormalizing data takes advantage of BigQuery's nativesupport for nested and repeated structures in JSON or Avro input data. Expressing records usingnested and repeated structures can provide a more natural representation of the underlying data. Inthe case of the sales order, the outer part of a JSON structure contains the order and customerinformation, and the inner part of the structure contains the individual line items of the order, whichare represented as nested, repeated elements."orderID": "ORDER","custID": "EMAIL","custName": "NAME","timestamp": "TIME","location": "LOCATION","purchasedItems": [{"sku": "SKU","description": "DESCRIPTION","quantity": "QTY","price": "PRICE"},{"sku": "SKU","description": "DESCRIPTION","quantity": "QTY","price": "PRICE"}Expressing records by using nested and repeated elds simpli es data load using JSON or Avro les.After you've created such a schema, you can perform SELECT, INSERT, UPDATE, and DELETE operationson any individual elds using a dot notation, for example, Order.Item.SKU. For examples, see theBigQuery documentation (/bigquery/docs/data).Advantages of denormalizationBigQuery is essentially an analytical engine. It supports DML

(https://wikipedia.org/wiki/Data manipulation language) actions, but it isn't meant to be used as an onlinetransaction processing (OLTP) store. The discussion about Changing data (#handling change) providesguidelines for dealing with changes while maintaining zero analysis downtime and delivering optimalonline analytical processing (OLAP) performance. While normalized or partially normalized datastructures, such as star schema or snowbake, are suitable for update/delete operations, they aren'toptimal for OLAP workloads. When performing OLAP operations on normalized tables, multiple tableshave to be JOINed to perform the required aggregations. JOINs (/bigquery/query-reference#joins) arepossible with BigQuery and sometimes recommended on small tables. However, they are typically notas performant as denormalized structures.The following graph compares query performance using JOINs to simple lters in relation to tablesize. Query performance shows a much steeper decay in presence of JOINs.Disadvantages of denormalizationDenormalized schemas aren't storage-optimal, but BigQuery's low cost of storage addressesconcerns about storage inemciency. You can contrast costs against gains in query speed to see whystorage isn't a signi cant factor.

One challenge when you work with denormalized schema is maintaining data integrity. Depending onthe frequency of change and how widespread it is, maintaining data integrity can require increasedmachine time and sometimes human time for testing and veri cation.Pa"itioning tablesBigQuery supports partitioning tables by date (/bigquery/docs/creating-partitioned-tables). You enablepartitioning during the table-creation process. BigQuery creates new date-based partitionsautomatically, with no need for additional maintenance. In addition, you can specify an expiration timefor data in the partitions.New data that is inserted into a partitioned table is written to the raw partition at the time of insert. Toexplicitly control which partition the data is loaded to, your load job can specify a particular datepartition.Loading dataBefore data can be loaded into BigQuery for analytical workloads, it is typically stored in a CloudStorage product (/products/storage) and in a format that is native to its origin. During early stages ofmigration to Google Cloud, the common pattern is to use existing extract, transform, and load (ETL)tools to transform data into the ideal schema for BigQuery. After data is transformed, it is transferredto Cloud Storage as CSV, JSON, or Avro les, and from there loaded into BigQuery by using load jobs(/bigquery/loading-data) or streaming (/bigquery/streaming-data-into-bigquery). Alternatively, you cantransfer les to Cloud Storage in the schema that is native to the existing on-premises data storage,loaded into a set of staging tables in BigQuery and then transformed into the ideal schema forBigQuery by using BigQuery SQL commands. These two approaches are visualized here:

As you expand your footprint in Google Cloud, you will probably capture your source data directly inBigtable (/bigtable), Datastore (/datastore), or Cloud Spanner (/spanner) and use Databow (/databow) toETL data into BigQuery in batch or streams.Using load jobsThis section assumes that your data is in Cloud Storage as a collection of les in a supported leformat. For more information about each data format, as well as speci c requirements and featuresto consider when choosing a format, see BigQuery data formats (/bigquery/data-formats).In addition to CSV, you can also use data les with delimiters other than commas by using the -field delimiter bag. For details, see bq load bags uery supports loading gzip ading compressed and uncompressed data) les. However, loadingcompressed les isn't as fast as loading uncompressed les. For time-sensitive scenarios orscenarios in which transferring uncompressed les to Cloud Storage is bandwidth- or timeconstrained, conduct a quick loading test to see which alternative works best.Because load jobs are asynchronous, you don't need to maintain a client connection while the job isbeing executed. More importantly, load jobs don't affect your other BigQuery resources.

A load job creates a destination table if one doesn't already exist.BigQuery determines the data schema as follows:If your data is in Avro format, which is self-describing, BigQuery can determine the schemadirectly.If the data is in JSON or CSV format, BigQuery can auto-detect the schema(/bigquery/bq-command-line-tool#autodetect), but manual veri cation(/bigquery/bq-command-line-tool#autodetect) is recommended.You can specify a schema explicitly by passing the schema as an argument to the load job(/bigquery/docs/loading-data-cloud-storage). Ongoing load jobs can append to the same table using thesame procedure as the initial load, but do not require the schema to be passed with each job.If your CSV les always contain a header row that needs to be ignored after the initial load and tablecreation, you can use the --skip leading rows bag to ignore the row. For details, see bq load BigQuery sets daily limits on the number and size of load jobs that you can perform per project andper table. In addition, BigQuery sets limits on the sizes of individual load les and records. For details,see Quota policy (/bigquery/quota-policy#import).Because tables are set to a hard limit of 1,000 load jobs per day, micro-batching isn't advised. To achieve emcient high-lume or real-time loading of data, use streaming inserts in place of micro-batching.You can launch load jobs through the BigQuery web UI. To automate the process, you can set up aCloud Functions (/functions) to listen to a Cloud Storage event (/functions/docs/calling/storage) that isassociated with arriving new les in a given bucket and launch the BigQuery load job.Using streaming insertsFor an alternate and complementary approach, you can also stream data directly into BigQuery.Streamed data is made available immediately and can be queried alongside existing table data inreal-time.For situations that can bene t from real-time information, such as fraud detection or monitoringsystem metrics, streaming can be a signi cant differentiator. However, unlike load jobs, which are freein BigQuery, there is a charge for streaming data. Therefore, it's important to use streaming insituations where the bene ts outweigh the costs.

When you stream data to the BigQuery tables, you send your records directly to BigQuery(/bigquery/streaming-data-into-bigquery) by using the BigQuery API. If you use Cloud Logging, you canalso stream your Google Cloud project's logs directly into BigQuery(/logging/docs/export/con gure export), including request logs from App Engine and custom loginformation sent to Cloud Logging.Handling changeMany data warehouses operate under strict Service Level Agreements (SLAs), demanding little to nodowntime. While Google handles BigQuery's uptime, you control the availability and responsivenessof your datasets with your approach to rebecting change in the data.All table modi cations in BigQuery are ACID (https://wikipedia.org/wiki/ACID) compliant. This applies toDML operations, queries with destination tables, and load jobs. A table that goes through inserts,updates, and deletes while serving user queries handles the concurrency gracefully and transitionsfrom one state to the next in an atomic fashion. Therefore, modifying a table doesn't requiredowntime. However, your internal process might require a testing and validation phase before makingnewly refreshed data available for analysis. Also, because DML operations compete against analyticalworkload over slots, you might prefer to isolate them. For these reasons, you might introducedowntime. This article uses the term "analysis downtime" to avoid confusion with BigQuery servicedowntime.You can apply most of the old and proven techniques for handling analysis downtime. This sectionexpands on some of the known challenges and remedies.Using BigQuery as an OLTP store is considered an anti-pattern. Because OLTP stores have a high volume of updates andletes, they are a mismatch for the data warehouse use case. To decide which storage option best ts your use case, review thoud storage products (/products/storage) table.Sliding time windowA traditional data warehouse, unlike a data lake, retains data only for a xed amount of time, forexample, the last 5 years. On each update cycle, new data is added to the warehouse and the oldestdata rolls off, keeping the duration xed. For the most part, this concept was employed to workaround the limitations of older technologies.BigQuery is built for scale and can scale out as the size of the warehouse grows, so there is no needto delete older data. By keeping the entire history, you can deliver more insight on your business. If the

storage cost is a concern, you can take advantage of BigQuery's long term storage pricing(/bigquery/pricing#long-term-storage) by archiving older data and using it for special analysis when theneed arises. If you still have good reasons for dropping older data, you can use BigQuery's nativesupport for date-partitioned tables (/bigquery/docs/creating-partitioned-tables) and partition les#partition-expiration). In other words, BigQuery canautomatically delete older data.Changing schemasWhile a data warehouse is designed and developed, it is typical to tweak table schemas by adding,updating, or dropping columns or even adding or dropping whole tables. Unless the change is in theform of an added column or table, it could break saved queries and reports that reference a deletedtable, a renamed column, and so on.After the data warehouse is in production, such changes go through strict change control. You mightdecide to handle minor schema changes during an analysis downtime, but for the most part rebectingschema changes are scheduled as version upgrades. You design, develop, and test the upgrade inparallel while the previous version of the data warehouse is serving the analysis workloads. Youfollow the same approach in applying schema changes to a BigQuery data warehouse.Slowly changing dimensionsA normalized data schema minimizes the impact of Slowly Changing Dimensions (SCD)(https://wikipedia.org/wiki/Slowly changing dimension) by isolating the change in the dimension tables. Itis generally favorable over a denormalized schema, where SCD can cause widespread updates to thebat fact table. However, as discussed in the schema design section, use normalization carefully forBigQuery.When it comes to SCD, there is no one-size- ts-all solution. It is important to understand the nature ofthe change and apply the most relevant solution or combinations of solutions to your problem. Theremainder of this section outlines a few solutions and how to apply them to SCD types.It is important to address slowly changing dimensions in the context of ideal schema for BigQuery. Often you mustcri ce emcient SCD handling in exchange for optimized query performance or the opposite.Technique 1: view switching

This technique is based on two views of the data: "main" vs. "shadow". The trick is to hide the actualtable and expose the "main" view to the users. On update cycles, the "shadow" view iscreated/updated and goes through data correctness tests while the users work against the "main"view. At switchover time, the "main" view is swapped with "shadow." The old "main" and now "shadow"could be torn down until the next update cycle or kept around for some workbows depending on therules and processes de ned by the organization.The two views could be based on a common table and differentiated by a column, for example,"view type," or based on distinct tables. The former method is not recommended, because DMLoperations against the "shadow" view of the table could slow down user queries against the "main"view without offering any real bene ts.While view switching offers zero analysis downtime, it has a higher cost because during the updatecycle, two copies of the data exist. More importantly, if update cycles happen at a higher rate than 90days, this approach could prevent your organization from taking advantage of long-term storagepricing (/bigquery/pricing#long-term-storage). Ninety days is based on the pricing policy at the time of thiswriting. Be sure to check the latest policy.Sometimes different segments of data change at their own pace. For instance, sales data in NorthAmerica is updated on a daily basis, while data for Asia Paci c is updated on a biweekly basis. Insuch situations, it is best to partition the table based on the driving factor for the change, Country inthis example. View switching is then applied to the impacted partitions and not the entire datawarehouse. At the time of this writing, you can only partition based on a custom data attribute, suchas Country, by explicitly splitting the data into multiple tables.Technique 2: in-place partition loadingWhen the change in data can be isolated by a partition and brief analysis downtime is tolerated, viewswitching might be overkill. Instead, data for the affected partitions can be staged in other BigQuerytables or exported to les in Cloud Storage, where they can be replaced during analysis downtime.To replace data in a target partition with data from a query of another table:query --use legacy sql false --replace \--destination table 'flight data.fact flights part 20140910' \'select * from ods.load flights 20140910 To replace data in a target partition by loading from Cloud Storage:

load--replace \--source format NEWLINE DELIMITED JSON'flight data.fact flights part 20140910' \gs://{bucket}/load flights 20140910.jsonTechnique 3: update data maskingA small and frequently changing dimension is a prime candidate for normalization. In this technique,updates to such a dimension are staged in an isolated table or view that is conditionally joined withthe rest of the data:LECT f.order id as order id, f.customer id as customer id,IFNULL(u.customer first name, f.customer first name) as customer first name,IFNULL(u.customer last name, f.customer last name) as customer last nameOM fact table fFT OUTER JOIN pending customer updates uf.customer id u.customer idSCD Type 1: overwrite attribute valueType 1 SCD overwrites the value of an attribute with new data without maintaining the history. Forexample, if the product "awesome moisturizer cream" was part of the "health and beauty" categoryand is now categorized as "cosmetics", the change looks like this:Before:PRD SKPRD IDPRD DESCPRD CATEGORY123ABCawesome moisturizer cream - 100 ozhealth and beautyPRD SKPRD IDPRD DESCPRD CATEGORY123ABCawesome moisturizer cream - 100 ozhealth and beautycosmeticsAfter:

If the attribute is in a normalized dimension table, the change is very isolated. You simply update theimpacted row in the dimension table. For smaller dimension tables with frequent type 1 updates, useTechnique 3: update data masking. (#technique 3)If the attribute is embedded in the fact table in a denormalized fashion, the change is ratherwidespread. You will have to update all fact rows where the attribute is repeated. In this case, useeither Technique 2: in-place partition loading (#technique 2), or Technique 1: view switching.(#technique 1)SCD Type 2: change attribute value and maintain historyThis method tracks unlimited historical data by creating multiple records for a given natural key(https://wikipedia.org/wiki/Natural key) with separate surrogate keys(https://wikipedia.org/wiki/Surrogate key). For example, the same change that is illustrated in SCD type 1would be handled as below:Before:PRD SK PRD ID PRD DESCPRD CATEGORYSTART DATEEND DATE123health and beauty31-Jan-2009NULLABCawesome moisturizer cream - 100 ozAfter:PRD SK PRD ID PRD DESCPRD CATEGORYSTART DATEEND DATE123ABCawesome moisturizer cream - 100 ozhealth and beauty31-Jan-200918-JUL-2017124ABCawesome moisturizer cream - 100 ozcosmetics19-JUL-2017NULLIf the attribute is in a normalized dimension table, the change is isolated. You simply update theprevious row and add a new one in the dimension table. For smaller dimension tables with frequenttype 1 updates, use Technique 3: update data masking (#technique 3).If the attribute is embedded in the fact table in a denormalized fashion, the situation can be morefavorable, as long as you don't maintain explicit start and end dates for the value and instead rely onthe transaction dates. Because the previous value remains true for the date and time the previoustransactions occurred, you don't need to change previous fact table rows. The fact table would looklike this:

TRANSACTION DATE PRD SK PRD ID PRD DESCPRD CATEGORYUNITS AMOUNT18-JUL-2017123ABCawesome moisturizer cream - 100 ozhe

The following table maps standard data-warehouse concepts to those in BigQuery: Data warehouse BigQuery Data warehouse The BigQuery service replaces the typical hardware setup for a traditional data warehouse. That is, it serves as a collective home for all analytical data in an organization. Data mart Datasets are collections of tables that .