Google\u2019s BigQuery serverless data warehouse makes life so much easier and so much safer in terms of having all the data in one location however, the house-keeping and data governance are left for you to administer and manage.<\/p>\n
At Making Science we work with numerous Google BigQuery customers, some small, some large and some very large corporate groups. \u00a0Most of our experience shows that customers are rolling out data governance tools but most of our customers are typically still offering the \u201cSQL knowledgeable\/connected\u201d departments direct access to BigQuery. This all in the spirit of self-service analytics moving their business to be more data-driven.<\/p>\n
Even with the advances in Google\u2019s data catalog and data governance capabilities, the data administrators are left with challenges keeping data lakes and data warehouses operating efficiently.<\/p>\n
The Making Science teams are always looking for ways to make themselves more effective at performing their work with the construction of methodologies and tools. \u00a0Making Science has tools specifically for Data Quality projects, Data Discovery and a most recent addition is BigQuery Intelligence. \u00a0The tool offers value to the roles presented in the data governance model below:[image_with_animation image_url=”48047″ animation=”Fade In” hover_animation=”none” alignment=”center” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]The BigQuery Intelligence tool was built to help the data warehouse\/lake architects\/administrators\/custodians manage house-keeping activities regarding datasets, tables and views and optimize these same objects. \u00a0Many of our larger and very large customers have 100\u2019s of datasets and 100\u2019s of tables and 100s of views within each dataset making it very challenging to keep on-top of house-keeping activities.<\/p>\n
BigQuery provides data lake administrators with a simple visual interface for listing datasets, tables, and views. \u00a0The BigQuery explorer panel interface allows you to collapse tables and views under their given dataset simplifying the visualization.[image_with_animation image_url=”48053″ animation=”Fade In” hover_animation=”none” alignment=”center” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]But the BigQuery Explorer does not offer data lake administrators a simple visual interface to understand usage statistics regarding datasets, tables and views; this is where BigQuery intelligence collects<\/strong> underlying data previously collected by the GCP + BigQuery environment and offers visualizations to understand the usage patterns across the nodes in the data warehouse.<\/p>\n The Data Scientists and Data Engineers at Making Science had a look and found that GCP offers a tremendous amount of data lake usage statistics. But, there was no easy way to retrieve the data and visualize the usage patterns in the data lake. The team chose to use Sankey visualizations, built with Python and D3, to better understand the usage patterns between the nodes (datasets, tables, views and jobs\/queries) in the data warehouse. [image_with_animation image_url=”48059″ animation=”Fade In” hover_animation=”none” alignment=”center” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]As the team worked with some very large data lakes they quickly found it necessary to allow data custodians to apply filters to limit views of the data lake to make the Sankey diagram more readable. \u00a0Most typically they would select logical groupings of datasets to give a complete view but without making the visualization undecipherable. \u00a0BigQuery Intelligence actually allows filtering both on datasets and tables.[image_with_animation image_url=”48104″ animation=”Fade In” hover_animation=”none” alignment=”center” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]The team also found that by including the possibility to define color based on criteria; by default, the color criteria is “dataset” so that each dataset is distinguished by a specific color in the Sankey visualization.[image_with_animation image_url=”48073″ animation=”Fade In” hover_animation=”none” alignment=”” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]The team decided to include a “change color criteria” filter, where we can choose between additional criteria including bytes, jobs, inherited jobs, recency, and inherited recency. With this kind of plot, we can check if there exist inefficient nodes in the data lake.[image_with_animation image_url=”48079″ animation=”Fade In” hover_animation=”none” alignment=”center” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]<\/p>\n A couple of use cases helped by changing the color criteria is the location of “hot spots”, i.e. those nodes which process too many bytes or have too many jobs, and \u201ccold spots\u201d or those nodes that have not been used in a long time and hence could be removed\/deleted from the data lake. \u00a0In the following screenshot, we can see that the color indicates the volume of data each node has processed. Red indicates too much, and green indicates a reasonable volume. Grey indicates that we don’t have information about this particular node. It is interesting to see nodes that have a deep level and are very red. This usually means that those nodes could be optimized by creating aggregated\/intermediate tables.[image_with_animation image_url=”48085″ animation=”Fade In” hover_animation=”none” alignment=”center” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]<\/p>\n If we change the color criteria to “inherited recency”, we can search for nodes that have not received any query\/job in a long time. The meaning of “inherited” is that we take the smallest recency among one node and its children, given that if one child node receives a job, in some way, the parent node also receives that job. Here, red indicates very high inherited recency and green indicates very small inherited recency. Grey, indicates that we have no info about the particular node. We could analyze the plot to search for unused nodes (red and grey) and clean the data lake.[image_with_animation image_url=”48091″ animation=”Fade In” hover_animation=”none” alignment=”center” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]<\/p>\n The team included a mouse-over capability to display relevant metrics of a node, as you can see in the following screenshot:[image_with_animation image_url=”48097″ animation=”Fade In” hover_animation=”none” alignment=”center” border_radius=”none” box_shadow=”none” image_loading=”default” max_width=”100%” max_width_mobile=”default”]<\/p>\n Making Science teams, acting as Data Custodians for some of our customers, have found that conversations with Data Business Owners\/Stewards regarding optimizations and housekeeping are greatly facilitated by the use of the BigQuery Intelligence tool. \u00a0Sitting down with the Data Steward with data and visualizations in hand enables a transparent, data-driven conversation.<\/p>\n The value to the business from a tool such as BigQuery Intelligence comes in the form of improved performance for systems accessing\/consuming the data and ensures a reduced GCP operating costs.<\/p>\n The Making Science team members have feature additions already in the works and we will share some of the use cases and the corresponding benefits as they are available.<\/p>\n","protected":false},"excerpt":{"rendered":" Google\u2019s BigQuery serverless data warehouse makes life so much easier and so much safer in terms of having all the data in one location however, the house-keeping and data governance are left for you to administer and manage. At Making Science we work with numerous Google BigQuery customers, some small, some large and some very […]<\/p>\n","protected":false},"author":21,"featured_media":28028,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[550],"tags":[552,393,554],"class_list":["post-48046","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-marketing-analytics","tag-bigquery-2","tag-cloud-2","tag-data-visualization"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/posts\/48046","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/users\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/comments?post=48046"}],"version-history":[{"count":0,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/posts\/48046\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/media\/28028"}],"wp:attachment":[{"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/media?parent=48046"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/categories?post=48046"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/tags?post=48046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}HotSpots<\/h4>\n
Unused nodes<\/h4>\n
Metrics<\/h4>\n
The Conclusion<\/h3>\n