{"id":11640,"date":"2022-09-21T12:05:29","date_gmt":"2022-09-21T12:05:29","guid":{"rendered":"https:\/\/predictly.se\/save-money-with-bigquery-and-dataform\/"},"modified":"2024-05-08T11:46:40","modified_gmt":"2024-05-08T11:46:40","slug":"save-money-with-bigquery-and-dataform","status":"publish","type":"post","link":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/","title":{"rendered":"Save money with BigQuery and Dataform"},"content":{"rendered":"

As we are starting to build transformations with Dataform, we looked into utilizing native BigQuery functionality to not only save money but also gain performance. Let me show you how.<\/p>\n

What you are paying for<\/h6>\n

Google BigQuery is an enterprise data warehouse integrated into GCP. It provides enormous processing power while minimizing the administrative effort required by its users.<\/p>\n

Dataform is a platform to build and manage data workflows in cloud data warehouses. It was recently acquired by Google<\/a> and it’s likely that the tight integration with BigQuery will only grow stronger.<\/p>\n

While Dataform is free of charge, BigQuery is not. Pricing is mainly based on 2 components: Storage and Analysis. There’s not much you can do about the amount of data stored but it’s quite interesting to take a closer look at how Analysis is billed and how you as a developer can work with that. (note that the following is valid for the on-demand pricing model)<\/p>\n

How to act intelligently in BigQuery…<\/h6>\n

Ad-hoc queries are billed by the number of bytes processed by BigQuery; at the time of writing Google charges $5.00 per terabyte. Not supporting indexes, the usual way BigQuery handles growing tables is by adding compute resources to keep computing times at an acceptable level. This is a great approach for the developer as there’s no headache about the optimal indexing strategy but sooner or later the finance department might give you a call…<\/p>\n

This is where partitioning comes into play. The technique has been known in traditional databases for over 20 years – as an example, Oracle introduced it in 1997 with version 8.0 – and is available out of the box in BigQuery as well. It means that all data is saved in “segments” or “partitions” based on a criteria. If a query filters on that criteria, vast parts of the data can be ignored rather than filtered, a technique called pruning<\/em>.<\/p>\n

Partitioning customer orders<\/h6>\n

Let’s look at an example to get a better grasp of the concept: we start with a table containing orders that’s partitioned daily on the order-ts-column. You can think of this as many small tables, each of which containing the orders of exactly one day. If you’re now looking for last weeks’ orders, BigQuery will understand that there are 7 partitions you’re interested in – and that it can ignore all others. Assuming you keep data over a period of 5 years and that the amount of orders is pretty stable over time, you just saved BigQuery scanning through 1820 days’ worth of data – or 99.6 % of all orders. Spin this thought further: you also saved 99.6 % of your money. An offer you can’t resist, right?<\/p>\n

…and how to do the same thing in Dataform<\/h6>\n

Dataform goes its own way in some respects. For example, required columns don’t exist and you’re supposed to ensure their integrity with an assertion instead. This makes it extremely easy for anyone with basic knowledge in SQL to create transformations but it sometimes isn’t obvious how to use certain more advanced features.<\/p>\n

And that’s the case with partitioning as it’s hidden behind the BigQuery configuration parameter<\/a> which provides us with the partitionBy<\/em> option. So you can define a table as follows:<\/p>\n

\nconfig {\n  type: 'table',\n  bigquery: {\n  partitionBy: \"TIMESTAMP_TRUNC(order_ts, DAY)\"\n  }\n}\n\nSELECT order_id, order_ts, order_no, order_reference,\n  payment_method, transportation_method\nFROM ${ref(\"orders_deduplicated\")}\n<\/pre>\n
This will partition the table on the order_ts<\/em> column and create one partition per day. After executing the script it can be confirmed in the Google Cloud Console:<\/p>\n
\"Google<\/p>\n
Partitioning comes without any real downside and should be considered as a standard technique in every bigger data warehouse as it both helps performance and saves money.<\/p>\n
I hope you found this article useful. Stay tuned for more tips and tricks around BigQuery, Dataform and much more!<\/p>\n","protected":false},"excerpt":{"rendered":"
An easy way to cut costs and increase performance in Data Warehouses – learn how to implement Partitioning with Dataform!<\/p>\n","protected":false},"author":8,"featured_media":11462,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"cybocfi_hide_featured_image":"","footnotes":""},"categories":[116,110,111,106],"tags":[],"class_list":["post-11640","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-big-query-en","category-data-en","category-dataform-en","category-google-cloud-en"],"yoast_head":"\nSave money with BigQuery and Dataform – Predictly<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Save money with BigQuery and Dataform – Predictly\" \/>\n<meta property=\"og:description\" content=\"An easy way to cut costs and increase performance in Data Warehouses - learn how to implement Partitioning with Dataform!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/\" \/>\n<meta property=\"og:site_name\" content=\"Predictly\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/predictly.se\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-21T12:05:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-05-08T11:46:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money-1024x636.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"636\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Daniel Hommrich\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@predictly_se\" \/>\n<meta name=\"twitter:site\" content=\"@predictly_se\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Hommrich\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/\"},\"author\":{\"name\":\"Daniel Hommrich\",\"@id\":\"https:\/\/predictly.se\/en\/#\/schema\/person\/f2b3064f1d3930252aa7dbf59c04d992\"},\"headline\":\"Save money with BigQuery and Dataform\",\"datePublished\":\"2022-09-21T12:05:29+00:00\",\"dateModified\":\"2024-05-08T11:46:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/\"},\"wordCount\":653,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/predictly.se\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money.png\",\"articleSection\":[\"Big Query\",\"Data\",\"Dataform\",\"Google Cloud\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/\",\"url\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/\",\"name\":\"Save money with BigQuery and Dataform – Predictly\",\"isPartOf\":{\"@id\":\"https:\/\/predictly.se\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money.png\",\"datePublished\":\"2022-09-21T12:05:29+00:00\",\"dateModified\":\"2024-05-08T11:46:40+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#primaryimage\",\"url\":\"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money.png\",\"contentUrl\":\"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money.png\",\"width\":1429,\"height\":888},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/predictly.se\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data\",\"item\":\"https:\/\/predictly.se\/en\/insikter\/data-en\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Save money with BigQuery and Dataform\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/predictly.se\/en\/#website\",\"url\":\"https:\/\/predictly.se\/en\/\",\"name\":\"Predictly\",\"description\":\"Professional IT services\",\"publisher\":{\"@id\":\"https:\/\/predictly.se\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/predictly.se\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/predictly.se\/en\/#organization\",\"name\":\"Predictly\",\"url\":\"https:\/\/predictly.se\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/predictly.se\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/predictly.se\/wp-content\/uploads\/2022\/10\/Logotype1-mobil.svg\",\"contentUrl\":\"https:\/\/predictly.se\/wp-content\/uploads\/2022\/10\/Logotype1-mobil.svg\",\"width\":532,\"height\":96,\"caption\":\"Predictly\"},\"image\":{\"@id\":\"https:\/\/predictly.se\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/predictly.se\",\"https:\/\/x.com\/predictly_se\",\"https:\/\/www.linkedin.com\/company\/predictly\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/predictly.se\/en\/#\/schema\/person\/f2b3064f1d3930252aa7dbf59c04d992\",\"name\":\"Daniel Hommrich\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/predictly.se\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/65916cb75efae5810937b164c1721b4ec6b27863bf190d6d5ea734a935569984?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/65916cb75efae5810937b164c1721b4ec6b27863bf190d6d5ea734a935569984?s=96&d=mm&r=g\",\"caption\":\"Daniel Hommrich\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Save money with BigQuery and Dataform – Predictly","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/","og_locale":"en_US","og_type":"article","og_title":"Save money with BigQuery and Dataform – Predictly","og_description":"An easy way to cut costs and increase performance in Data Warehouses - learn how to implement Partitioning with Dataform!","og_url":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/","og_site_name":"Predictly","article_publisher":"https:\/\/www.facebook.com\/predictly.se","article_published_time":"2022-09-21T12:05:29+00:00","article_modified_time":"2024-05-08T11:46:40+00:00","og_image":[{"width":1024,"height":636,"url":"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money-1024x636.png","type":"image\/png"}],"author":"Daniel Hommrich","twitter_card":"summary_large_image","twitter_creator":"@predictly_se","twitter_site":"@predictly_se","twitter_misc":{"Written by":"Daniel Hommrich","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#article","isPartOf":{"@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/"},"author":{"name":"Daniel Hommrich","@id":"https:\/\/predictly.se\/en\/#\/schema\/person\/f2b3064f1d3930252aa7dbf59c04d992"},"headline":"Save money with BigQuery and Dataform","datePublished":"2022-09-21T12:05:29+00:00","dateModified":"2024-05-08T11:46:40+00:00","mainEntityOfPage":{"@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/"},"wordCount":653,"commentCount":0,"publisher":{"@id":"https:\/\/predictly.se\/en\/#organization"},"image":{"@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#primaryimage"},"thumbnailUrl":"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money.png","articleSection":["Big Query","Data","Dataform","Google Cloud"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/","url":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/","name":"Save money with BigQuery and Dataform – Predictly","isPartOf":{"@id":"https:\/\/predictly.se\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#primaryimage"},"image":{"@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#primaryimage"},"thumbnailUrl":"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money.png","datePublished":"2022-09-21T12:05:29+00:00","dateModified":"2024-05-08T11:46:40+00:00","breadcrumb":{"@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#primaryimage","url":"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money.png","contentUrl":"https:\/\/predictly.se\/wp-content\/uploads\/2022\/09\/money.png","width":1429,"height":888},{"@type":"BreadcrumbList","@id":"https:\/\/predictly.se\/en\/save-money-with-bigquery-and-dataform\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/predictly.se\/en\/"},{"@type":"ListItem","position":2,"name":"Data","item":"https:\/\/predictly.se\/en\/insikter\/data-en\/"},{"@type":"ListItem","position":3,"name":"Save money with BigQuery and Dataform"}]},{"@type":"WebSite","@id":"https:\/\/predictly.se\/en\/#website","url":"https:\/\/predictly.se\/en\/","name":"Predictly","description":"Professional IT services","publisher":{"@id":"https:\/\/predictly.se\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/predictly.se\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/predictly.se\/en\/#organization","name":"Predictly","url":"https:\/\/predictly.se\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/predictly.se\/en\/#\/schema\/logo\/image\/","url":"https:\/\/predictly.se\/wp-content\/uploads\/2022\/10\/Logotype1-mobil.svg","contentUrl":"https:\/\/predictly.se\/wp-content\/uploads\/2022\/10\/Logotype1-mobil.svg","width":532,"height":96,"caption":"Predictly"},"image":{"@id":"https:\/\/predictly.se\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/predictly.se","https:\/\/x.com\/predictly_se","https:\/\/www.linkedin.com\/company\/predictly\/"]},{"@type":"Person","@id":"https:\/\/predictly.se\/en\/#\/schema\/person\/f2b3064f1d3930252aa7dbf59c04d992","name":"Daniel Hommrich","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/predictly.se\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/65916cb75efae5810937b164c1721b4ec6b27863bf190d6d5ea734a935569984?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/65916cb75efae5810937b164c1721b4ec6b27863bf190d6d5ea734a935569984?s=96&d=mm&r=g","caption":"Daniel Hommrich"}}]}},"_links":{"self":[{"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/posts\/11640","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/comments?post=11640"}],"version-history":[{"count":1,"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/posts\/11640\/revisions"}],"predecessor-version":[{"id":11648,"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/posts\/11640\/revisions\/11648"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/media\/11462"}],"wp:attachment":[{"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/media?parent=11640"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/categories?post=11640"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/predictly.se\/en\/wp-json\/wp\/v2\/tags?post=11640"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}