<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Rohan Paithankar on Medium]]></title>
        <description><![CDATA[Stories by Rohan Paithankar on Medium]]></description>
        <link>https://medium.com/@rohan_paithankar?source=rss-2165b853c675------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*jXyaZPoNVAFaIlVZgTUIPQ.jpeg</url>
            <title>Stories by Rohan Paithankar on Medium</title>
            <link>https://medium.com/@rohan_paithankar?source=rss-2165b853c675------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 18:19:44 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rohan_paithankar/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[A Prequel to Data Mesh]]></title>
            <link>https://medium.com/data-science/a-prequel-to-data-mesh-2233c0c56311?source=rss-2165b853c675------2</link>
            <guid isPermaLink="false">https://medium.com/p/2233c0c56311</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-mesh]]></category>
            <category><![CDATA[data-architecture]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Rohan Paithankar]]></dc:creator>
            <pubDate>Sun, 03 Dec 2023 02:35:49 GMT</pubDate>
            <atom:updated>2024-01-21T07:47:50.782Z</atom:updated>
            <content:encoded><![CDATA[<h4>My personal take on justifying the existence of Data Mesh</h4><p>A senior stakeholder at one my projects mentioned that they wanted to decentralise their data platform architecture and democratise data across the organisation.</p><p>When I heard the words ‘decentralised data architecture’, I was left utterly confused at first! In my then limited experience as a Data Engineer, I had only come across centralised data architectures and they seemed to be working very well. So, I was left wondering what was it that we wanted to solve using a decentralised data architecture? Or were we creating a new problem that did not ever exist in the first place?</p><h3>Where did I look?</h3><p>The obvious answer — Data Mesh by Zhamak Dehghani.</p><p>A great book that takes you on a journey of an organisation that implements this concept and overcomes some unique challenges. A highly recommended read for those who may be keen to learn more about it.</p><p>But in order to justify why this concept came into existence, I thought it’d be great to look back in time and understand the evolution of the data landscape. So here goes my overly-simplified take.</p><h3><strong>Evolution of the data landscape</strong></h3><h4><strong>1980s — Inception</strong></h4><ul><li>Relational databases came into existence.</li><li>Organizations began to use relational databases for ‘everything’.</li><li>Databases were overwhelmed with transactional and analytical workloads.</li></ul><p>Result:</p><ul><li>Data warehouse was born.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q1SO5tjim3ODPD8gthCv5Q.jpeg" /><figcaption>Image by the author</figcaption></figure><h4><strong>Early 1990s — Scale</strong></h4><ul><li>Analytical workloads started to get complex.</li><li>Data volumes started to grow.</li><li>Performance needed improvement.</li></ul><p>Result:</p><ul><li>The concept of Massively Parallel Processing (MPP) was introduced — data distributed across clusters.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jHjSrC3QfnQFhUCYwJEYPw.jpeg" /><figcaption>Image by the author</figcaption></figure><h4><strong>Late 1990s to Early 2000s - Productize</strong></h4><ul><li>Demand for reporting kept growing.</li><li>Architectures became complex.</li><li>Business units required data relevant to their analysis.</li></ul><p>Result:</p><ul><li>Companies started to sell pre-configured data warehouses as products.</li><li>The concept of `Data Marts` was introduced.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sKLuwgcAleiK6nFGlUFqAg.jpeg" /><figcaption>Image by the author</figcaption></figure><h4><strong>2004 to 2010 — The elephant enters the room</strong></h4><ul><li>New wave of applications emerged — Social Media, Software observability, etc.</li><li>New data formats emerged — JSON, Avro, Parquet, XML etc.</li></ul><p>Result:</p><ul><li>Hadoop &amp; NoSQL frameworks emerged.</li><li>Data lakes were introduced to store the new data formats.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*76fMKfuv9oP9Qa0gnaQtyw.jpeg" /><figcaption>Image by the author</figcaption></figure><h4><strong>2010 to 2020 - The Cloud Data Warehouse</strong></h4><ul><li>Enterprises now wanted quick data analytics without yesterday’s constraints of flexibility, processing power and scale.</li></ul><p>Result:</p><ul><li>Cloud data warehouse offerings emerged as preferred solutions for relational and semi-structured data.</li><li>Examples include: Amazon Redshift, Google BigQuery, Snowflake, Azure Synapse Analytics, Databricks etc.</li></ul><h3>So what was missing?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aLVjmxJ3NFOzcGrs4hs8tA.jpeg" /><figcaption>Image by the author</figcaption></figure><p>If we look at this generic flow of data in an organisation using a centralised data architecture, we realise that there are 3 touch points for the data:</p><ul><li>Data Producers</li><li>Central Data Team</li><li>Data Consumers</li></ul><p>Now let’s ask ourselves a few questions to start with:</p><ul><li>Who manages the data warehouse?</li><li>Which team responds to data requests?</li><li>Which team is responsible for ensuring data quality?</li><li>Which team is expected to be the SME for data?</li></ul><p>When I asked these questions to a bunch of people, I got one common answer across all questions (in combination with others)— option B, Central Data Team.</p><p>So we can infer that the central data team needs to:</p><ul><li>Manage data warehouse</li><li>Serve data requests</li><li>Ensure data quality</li><li>Be SMEs for the data in the data warehouse</li></ul><p>And the list goes on.</p><p><strong>So what was missing?</strong></p><p>As an enterprise continues to grow, the central data team tends to become the bottleneck in gaining actionable insights from data.</p><p>Central data teams end up having high knowledge burden and an ever increasing pressure of delivery.</p><p>This builds my case to justify the existence of the decentralised data architecture popularly known as the Data Mesh.</p><blockquote>Data Mesh is a type of analytical architecture but most importantly an operating model that shifts the ownership of analytical data to teams that most intimately know and own the data — the data producers and consumers.</blockquote><p>This image shows a high-level view of the Data Mesh Architecture: <br><a href="https://martinfowler.com/articles/data-monolith-to-mesh/data-mesh.png">https://martinfowler.com/articles/data-monolith-to-mesh/data-mesh.png</a></p><p>I won’t get into the principles or logical architecture of Data Mesh as there are many articles out there that do justice to it. Here are a few of my favorites:</p><ul><li><a href="https://martinfowler.com/articles/data-mesh-principles.html">https://martinfowler.com/articles/data-mesh-principles.html</a></li><li><a href="https://www.datamesh-architecture.com/">https://www.datamesh-architecture.com/</a></li></ul><h3><strong>References:</strong></h3><ul><li><a href="https://www.oreilly.com/library/view/data-mesh/9781492092384/">https://www.oreilly.com/library/view/data-mesh/9781492092384/</a></li><li><a href="https://martinfowler.com/articles/data-monolith-to-mesh.html">https://martinfowler.com/articles/data-monolith-to-mesh.html</a></li><li><a href="https://www.snowflake.com/wp-content/uploads/2017/09/Past-Present-Future-DW-FINAL.pdf">https://www.snowflake.com/wp-content/uploads/2017/09/Past-Present-Future-DW-FINAL.pdf</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2233c0c56311" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/a-prequel-to-data-mesh-2233c0c56311">A Prequel to Data Mesh</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exporting GCP Projects to Terraform]]></title>
            <link>https://medium.com/google-cloud/exporting-gcp-projects-to-terraform-c7b3b48525a0?source=rss-2165b853c675------2</link>
            <guid isPermaLink="false">https://medium.com/p/c7b3b48525a0</guid>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[infrastructure-as-code]]></category>
            <category><![CDATA[gcp-security-operations]]></category>
            <category><![CDATA[terraform]]></category>
            <dc:creator><![CDATA[Rohan Paithankar]]></dc:creator>
            <pubDate>Wed, 29 Nov 2023 11:30:54 GMT</pubDate>
            <atom:updated>2023-11-30T00:51:18.874Z</atom:updated>
            <content:encoded><![CDATA[<p>Have you found yourself in a situation where you went on creating resources in a project through ClickOps and that eventually became your production environment? If yes, this article is for you!</p><p>In my consulting journey, I have come across some very unique challenges that organisations face. One such challenge was that of a client that had created a ‘production environment’ through ClickOps and they found themselves in a pickle trying to maintain these resources. In an environment like this, engineers often feared touching anything because it could even mean that it was their last day at the job. Added to this were the rising concerns of costs incurred by the many unknown resources running somewhere in the project.</p><blockquote>You see, psychological safety is often under-appreciated but it is an important ingredient for building high performing teams.</blockquote><p>Watching the team’s turmoil with this so called ‘production environment’ forced me to explore ways to bring the situation under control. And voila! Terraform-ing the resources was the most favourable solution. Why?</p><ul><li>Infrastructure-as-code(IaC) would first help us audit the resources that had been deployed and thereby monitor costs incurred.</li><li>Infrastructure-as-code(IaC) would help us re-build the infrastructure if needed.</li><li>Infrastructure-as-code(IaC) would help the team deploy newer resources and most importantly, move away from ClickOps.</li></ul><p>Having said that, I was now left with only half the answer. The second part of this challenge was to export the resources into Terraform. I had only seen this uni-directional flow:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9i0LzbguQObE0g-cF3KFbg.jpeg" /></figure><p>But what I wanted was:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Hh4abFZYZn9wAqn7OSiudA.jpeg" /></figure><h3>How did I achieve it?</h3><p><strong>Note:</strong> This process only works for Linux/MacOS. The user exporting the resources requires elevated permissions in the project. Alternatively, service account impersonation can be used.</p><p>Here are the steps:</p><ul><li>Setup a new git repository and clone it to your local machine.</li><li>In the repository, setup Terraform backend file <strong>backend.tf</strong> with remote state file in a GCS bucket.</li></ul><pre>terraform {<br>  backend &quot;gcs&quot; {<br>    bucket  = &quot;BUCKET NAME&quot;<br>    prefix  = &quot;PATH/TO/STATE/FILE&quot;<br>  }<br>}</pre><ul><li>Authenticate with GCP and configure the target project. Additionally, service account impersonation can also be used.</li></ul><pre>gcloud auth login<br><br>gcloud config set project PROJECT_ID</pre><ul><li>As per the steps mentioned in the <a href="https://cloud.google.com/docs/terraform/resource-management/export#write_the_output_to_a_directory_structure">GCP documentation</a>, install GCP’s config-connector and run the gcloud cli command to export the required resources to Terraform scripts.</li></ul><pre>gcloud components install config-connector<br><br>gcloud beta resource-config bulk-export \<br> --path=OUTPUT_DIRECTORY \<br> --project=PROJECT_ID \<br> --resource-format=terraform</pre><p>The export is arranged in the format:</p><pre>OUTPUT_DIRECTORY/projects/PROJECT_ID/RESOURCE_TYPE</pre><p>Along with the bulk export of resources, it also provides the ‘terraform import’ commands to import the resources into the state file. Once the scripts are exported, run ‘terraform init’ followed by the ‘terraform import’ command.</p><h3>The Catch-22</h3><p>The time taken to export is linearly proportional to the number of resources running in the project. In my case, I had a ‘huge’ number of BigQuery Datasets and Tables that took ages to export if combined with all other resources. To solve this challenge, I first listed out all the resource types supported for the export using:</p><pre>gcloud beta resource-config list-resource-types &gt;&gt; resources.txt</pre><p>I then created two separate lists, one for BigQuery resources and another for non-BigQuery resources.</p><p><strong>BigQuery resources: </strong>(resourceTypes_BQ.txt)</p><pre>BigQueryDataset<br>BigQueryJob<br>BigQueryTable<br>BigtableAppProfile<br>BigtableGCPolicy<br>BigtableInstance<br>BigtableTable</pre><p><strong>Non-BigQuery resources: </strong>(resourceTypes_exceptBQ.txt)</p><pre>AccessContextManagerAccessLevel<br>AccessContextManagerAccessPolicy<br>AccessContextManagerServicePerimeter<br>ArtifactRegistryRepository<br>CloudBuildTrigger<br>CloudIdentityGroup<br>ComputeAddress<br>ComputeBackendBucket<br>ComputeBackendService<br>ComputeDisk<br>ComputeExternalVPNGateway<br>ComputeFirewall<br>ComputeForwardingRule<br>ComputeHTTPHealthCheck<br>ComputeHTTPSHealthCheck<br>ComputeHealthCheck<br>ComputeHealthCheck<br>ComputeImage<br>ComputeInstance<br>ComputeInstanceGroup<br>ComputeInstanceTemplate<br>ComputeInterconnectAttachment<br>ComputeNetwork<br>ComputeNetworkEndpointGroup<br>ComputeNetworkPeering<br>ComputeNodeGroup<br>ComputeNodeTemplate<br>ComputeProjectMetadata<br>ComputeRegionNetworkEndpointGroup<br>ComputeReservation<br>ComputeResourcePolicy<br>ComputeRoute<br>ComputeRouterInterface<br>ComputeRouterNAT<br>ComputeRouterPeer<br>ComputeSSLCertificate<br>ComputeSSLPolicy<br>ComputeSecurityPolicy<br>ComputeSharedVPCHostProject<br>ComputeSharedVPCServiceProject<br>ComputeSnapshot<br>ComputeSubnetwork<br>ComputeTargetGRPCProxy<br>ComputeTargetHTTPProxy<br>ComputeTargetHTTPSProxy<br>ComputeTargetHTTPSProxy<br>ComputeTargetInstance<br>ComputeTargetPool<br>ComputeTargetSSLProxy<br>ComputeTargetTCPProxy<br>ComputeTargetVPNGateway<br>ComputeURLMap<br>ComputeVPNGateway<br>ComputeVPNTunnel<br>ContainerCluster<br>ContainerNodePool<br>DataflowFlexTemplateJob<br>DataflowJob<br>DNSManagedZone<br>DNSPolicy<br>DNSRecordSet<br>FirestoreIndex<br>IAMCustomRole<br>IAMServiceAccount<br>IAMServiceAccountKey<br>KMSCryptoKey<br>KMSKeyRing<br>LoggingLogSink<br>MemcacheInstance<br>MonitoringAlertPolicy<br>MonitoringNotificationChannel<br>PubSubSubscription<br>PubSubTopic<br>RedisInstance<br>Folder<br>Project<br>ResourceManagerLien<br>ResourceManagerPolicy<br>SecretManagerSecret<br>SecretManagerSecretVersion<br>ServiceDirectoryEndpoint<br>ServiceDirectoryNamespace<br>ServiceDirectoryService<br>ServiceNetworkingConnection<br>Service<br>SourceRepoRepository<br>SpannerDatabase<br>SpannerInstance<br>SQLDatabase<br>SQLInstance<br>SQLSSLCert<br>SQLUser<br>StorageBucket<br>StorageBucketAccessControl<br>StorageDefaultObjectAccessControl<br>StorageNotification<br>StorageTransferJob</pre><p>I then wrote a simple parameterised bash script to export BigQuery &amp; Non-BigQuery resources.</p><pre>#!/bin/bash<br><br>sudo apt-get install google-cloud-sdk-config-connector<br><br>if [$2 == &quot;&quot;]; then<br>    input=&quot;resourceTypes_exceptBQ.txt&quot;<br>    while read -r line<br>        do<br>            echo &quot;Exporting resource - $line&quot;<br>            mkdir -p ../$1/$line<br>            gcloud beta resource-config bulk-export  --resource-types=$line  --path=../$1/$line  --project=$1  --resource-format=terraform<br>            echo &quot;Deleting resource folder if empty&quot;<br>            rm -d ../$1/$line<br>        done &lt; &quot;$input&quot;<br>else<br>    input_bq=&quot;resourceTypes_BQ.txt&quot;<br>    while read -r line<br>    do<br>        echo &quot;Exporting resource - $line&quot;<br>        mkdir -p ../$1/$line<br>        gcloud beta resource-config bulk-export  --resource-types=$line  --path=../$1/$line  --project=$1  --resource-format=terraform<br>        echo &quot;Deleting resource folder if empty&quot;<br>        rm -d ../$1/$line<br>    done &lt; &quot;$input_bq&quot;<br>fi</pre><p>The script accepts the GCP Project ID as command line argument. The script can be executed as follows:</p><pre>./export_resources [project-id] [Optional argument:bq]</pre><p>The second argument ‘bq’ is optional. If specified, it exports the BigQuery resources. If not present, the script exports all Non-BigQuery resources.</p><h4>References:</h4><ul><li><a href="https://cloud.google.com/docs/terraform/resource-management/export#before-you-begin">https://cloud.google.com/docs/terraform/resource-management/export</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c7b3b48525a0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/exporting-gcp-projects-to-terraform-c7b3b48525a0">Exporting GCP Projects to Terraform</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery Stored Procedures to Automate Snapshots at Dataset Level]]></title>
            <link>https://medium.com/google-cloud/bigquery-stored-procedures-to-automate-snapshots-at-dataset-level-ae963d5e745d?source=rss-2165b853c675------2</link>
            <guid isPermaLink="false">https://medium.com/p/ae963d5e745d</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Rohan Paithankar]]></dc:creator>
            <pubDate>Mon, 30 Oct 2023 03:36:17 GMT</pubDate>
            <atom:updated>2023-11-02T15:21:35.493Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Written by </em><a href="https://medium.com/u/2165b853c675"><em>Rohan Paithankar</em></a><em> &amp; </em><a href="https://medium.com/u/40f2f886f04f"><em>Bihag Kashikar</em></a></p><p>With the growing use of data warehouse automation tools, the focus of data engineers is shifting towards DataOps. This includes designing robust pipelines to deliver enhancements without the fear of breaking downstream analytics workflows. An important aspect of this process is the snapshot and restore process while pushing changes to datasets.</p><blockquote>Google’s white-paper on <a href="https://cloud.google.com/bigquery/docs/continuous-integration-of-data-in-bigquery#how_to_use_table_clones_and_table_snapshots_to_allow_isolation">Continuous Data Integration</a> introduces snapshot/clone as one of the best practices in the deployment workflow.</blockquote><p>With the above as the motivation, we began work on one such use-case in BigQuery but found that it only offers <a href="https://cloud.google.com/bigquery/docs/table-snapshots-intro">snapshots at Table-level</a>. Our challenge was to create snapshots for a defined number of Datasets with a varying number of tables with the option to automate it as a part of the deployment workflow. To overcome this challenge, we leveraged stored procedures in BigQuery.</p><h3>Solution</h3><p>BigQuery provides system-defined metadata views called INFORMATION_SCHEMA for every dataset. We utilise it to obtain the list of tables in a particular dataset and assign it to a variable tables. Refer to the query below:</p><pre>SET tables = (SELECT ARRAY_AGG(table_name) FROM `project_id.dataset_id.INFORMATION_SCHEMA.TABLES`);</pre><p>We then iterate through the array returned by the above query using a simple for-loop:</p><pre>FOR table IN (SELECT * FROM UNNEST(tables) as value)<br> <br> DO<br> <br> <br>END FOR;</pre><p>For every value in the list of tables, we</p><ul><li>Create a fully qualified snapshot table name snapshot_name with a target snapshot_dataset_id.</li></ul><pre>SET snapshot_name=CONCAT(&quot;project_id.snapshots_dataset_id.&quot;,table.value,&quot;_SNAPSHOT_&quot;,STRING(CURRENT_DATE()));</pre><ul><li>Obtain the fully qualified table name table_name from the loop variable table.value.</li></ul><pre>SET table_name=CONCAT(&quot;project_id.dataset_id.&quot;,table.value);</pre><ul><li>Run a dynamic SQL query using EXECUTE_IMMEDIATE to pass the above variables as parameters.</li></ul><pre>EXECUTE IMMEDIATE format(&quot;&quot;&quot;<br> CREATE SNAPSHOT TABLE `%s`<br> CLONE `%s`<br> OPTIONS (<br> expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP, INTERVAL 14 DAY))&quot;&quot;&quot;,snapshot_name,table_name);</pre><p><strong>Note:</strong> The expiration timestamp has been set to 14 days from the snapshot creation timestamp but can be modified as per the use case.</p><h4>Final stored procedure:</h4><pre>CREATE OR REPLACE PROCEDURE `project_id.dataset_id.snapshot_procedure_name`<br>BEGIN<br><br> DECLARE tables ARRAY&lt;STRING&gt;;<br> DECLARE snapshot_name STRING;<br> DECLARE recovery_table_name STRING;<br><br> -- Get the list of tables in the dataset as an array<br> SET tables = (SELECT ARRAY_AGG(table_name) FROM `project_id.dataset_id.INFORMATION_SCHEMA.TABLES`);<br> <br> -- Iterate through all the tables in the dataset<br> FOR table IN (SELECT * FROM UNNEST(tables) as value)<br> <br> DO<br> <br> -- Set table &amp; snapshot names<br> SET snapshot_name=CONCAT(“project_id.snapshots_dataset_id.”,table.value,”_SNAPSHOT_”,STRING(CURRENT_DATE()));<br> SET table_name=CONCAT(“project_id.dataset_id.”,table.value);<br> <br> -- Create Snapshot<br> EXECUTE IMMEDIATE format(&quot;&quot;&quot;<br> CREATE SNAPSHOT TABLE `%s`<br> CLONE `%s`<br> OPTIONS (<br> expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP, INTERVAL 14 DAY))&quot;&quot;&quot;,snapshot_name,table_name);<br> <br>END FOR;<br><br>END;</pre><p>We can also parameterise the stored proc to make it generalised and callable from a CI/CD pipeline, for instance. Here is the parameterised version:</p><pre>CREATE OR REPLACE PROCEDURE `project_id.dataset_id.snapshot_procedure_name`(project_id STRING, dataset_id STRING, snapshot_dataset_id STRING)<br>BEGIN<br>  DECLARE tables ARRAY&lt;STRING&gt;;<br>  DECLARE snapshot_name STRING;<br>  DECLARE info_schema STRING;<br>  DECLARE table_name STRING;<br>  -- Create the fully qualified name<br>  SET info_schema = CONCAT(project_id,&#39;.&#39;,dataset_id,&#39;.&#39;,&#39;INFORMATION_SCHEMA.TABLES&#39;);<br>  -- Get the list of tables in the dataset as an array<br>  EXECUTE IMMEDIATE FORMAT (&quot;&quot;&quot;SELECT ARRAY_AGG(CONCAT(table_name)) FROM `%s`&quot;&quot;&quot;,info_schema)<br>  INTO tables;<br><br>  FOR table IN (SELECT * FROM UNNEST(tables) as value)<br>  DO<br>    -- Create fully qualified names for snapshot and table<br>    SET snapshot_name=CONCAT(project_id,&#39;.&#39;,dataset_id,&#39;.&#39;,table.value,&#39;_&#39;,STRING(CURRENT_DATE()));<br>    SET table_name=CONCAT(project_id,&#39;.&#39;,dataset_id,&#39;.&#39;,table.value);<br>    -- Create Snapshot<br>    EXECUTE IMMEDIATE format(&quot;&quot;&quot;<br>    CREATE SNAPSHOT TABLE `%s`<br>    CLONE `%s`<br>    OPTIONS (<br>      expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP, INTERVAL 14 DAY))&quot;&quot;&quot;,snapshot_name,table_name);<br>  <br>  END FOR;<br>END</pre><p>A parameterised stored procedure to restore the tables from snapshots would look something like this:</p><pre>CREATE OR REPLACE PROCEDURE `project_id.dataset_id.restore_procedure_name`(project_id STRING, dataset_id STRING, snapshot_dataset_id STRING)<br>BEGIN<br>  DECLARE tables ARRAY&lt;STRING&gt;;<br>  DECLARE snapshot_name STRING;<br>  DECLARE table_name STRING;<br>  DECLARE latest_snapshot_date STRING;<br>  DECLARE info_schema STRING;<br>  DECLARE snapshot_info_schema STRING;<br>  -- Create the fully qualified name<br>  SET info_schema = CONCAT(project_id,&#39;.&#39;,dataset_id,&#39;.&#39;,&#39;INFORMATION_SCHEMA.TABLES&#39;);<br>  -- Create the fully qualified name<br>  SET snapshot_info_schema = CONCAT(project_id,&#39;.&#39;,snapshot_dataset_id,&#39;.&#39;,&#39;INFORMATION_SCHEMA.TABLE_SNAPSHOTS&#39;);<br>  -- Find date of latest snapshot<br>  EXECUTE IMMEDIATE FORMAT (&quot;&quot;&quot;SELECT STRING(MAX(DATE(snapshot_time))) FROM `%s`&quot;&quot;&quot;,snapshot_info_schema)<br>  INTO latest_snapshot_date;<br>  -- Get the list of tables in the main dataset as an array<br>  EXECUTE IMMEDIATE FORMAT (&quot;&quot;&quot;SELECT ARRAY_AGG(CONCAT(table_name)) FROM `%s` WHERE table_type=&#39;BASE TABLE&#39;&quot;&quot;&quot;,info_schema)<br>  INTO tables;<br>  FOR table IN (SELECT * FROM UNNEST(tables) as value)<br>  DO<br>    -- Create fully qualified names for snapshot and table<br>    SET snapshot_name=CONCAT(project_id,&#39;.&#39;,snapshot_dataset_id,&#39;.&#39;,table.value,&quot;_&quot;,latest_snapshot_date);<br>    SET table_name=CONCAT(project_id,&#39;.&#39;,dataset_id,&#39;.&#39;,table.value);<br>    <br>    -- Restore from Snapshot<br>    EXECUTE IMMEDIATE format(&quot;&quot;&quot;<br>    CREATE OR REPLACE TABLE `%s`<br>    CLONE `%s`&quot;&quot;&quot;,table_name,snapshot_name);<br><br>  END FOR;<br>END</pre><p>This is just one of the solutions to automate Dataset-level snapshots and it works well for:</p><ul><li>Limited number of datasets with increasing number of tables.</li><li>Teams that are SQL-oriented.</li><li>Scheduled execution of snapshot and restore.</li></ul><p>To conclude, the above solution only utilises a single service, i.e, BigQuery and hence has lesser overhead. It won’t be a surprise if Google Cloud soon releases Dataset-level snapshots out of the box.</p><p>A shoutout to <a href="https://medium.com/u/2ff7a615e3d0">Franklin White</a> &amp; <a href="https://medium.com/u/67f11f195daf">Preetika Bhateja</a> who have proposed a more loosely-coupled event-driven solution using Pub/Sub and Cloud Functions which can be referred in this article-</p><p><a href="https://medium.com/google-cloud/automate-bigquery-snapshots-at-dataset-level-5ea886d9c1bc">Automate BigQuery Snapshots at Dataset Level</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ae963d5e745d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/bigquery-stored-procedures-to-automate-snapshots-at-dataset-level-ae963d5e745d">BigQuery Stored Procedures to Automate Snapshots at Dataset Level</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>