Access Historic data as of time travel in BigQuery

As a data professional, it's a common requirement to understand the state of data as of a specific date, such as identifying changes made since a particular point in time. While creating a change log or implementing Change Data Capture (CDC) is a typical approach, it's often impractical to maintain such logs for all tables, especially in systems with a significant number of tables. In modern data warehouses or data systems, which often contain more than 50+ tables, maintaining change logs for many of them can become a substantial overhead. Data evolves constantly, and sometimes these changes are extensive, particularly in data warehouse environments. When debugging data issues, having the ability to view the state of the data as of a specific date can be invaluable.

BigQuery has added this great feature to solve this problem with minimal effort, with almost no additional development. 

Bigquery has FOR SYSTEM_TIME AS OF that help us access data as of a particular date.

Step - 1:

SELECT

  *

FROM

  `poc.bq-api-test`

LIMIT

  100;

Step - 2 : Update records 

UPDATE

  `poc.bq-api-test`

SET

  name ='test2'

WHERE

  id=1;

Finally : 

We can view the data as it existed prior to any changes. This is just a simple example, showcasing the capability to access the state of the data up to 7 days in the past by default. This feature can also be used to restore a table to its state on a specific past date, among other use cases.

SELECT

  *

FROM

  `poc.bq-api-test` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

Conclusion: Using FOR SYSTEM_TIME AS OF We can travel to any past time and can see the state of data. Obviously it has some limitations; we can see the details on GCP documentation here.

Access historical data using time travel  |  BigQuery  |  Google Cloud

Here is how to restore accidently deleted Bigquery Dataset 
How to restore a deleted BigQuery Dataset (derrickqin.com)

Using chatGPT with Python

 

Now days ChatGPT is hot cake that everyone want to test it, Its rely a great to ask any type of question. Most of time it gives me better and faster answer than Google.  I tried using UI available in market but its not stable. Finally I have started using my favorite python code to call openai and ask questions.

I am calling chatGPT  in my python code, here is basic code snippet.

Lets create a function first, before that you might need to generate a key openai.api_key. 

# -*- coding: utf-8 -*-

"""

Created on Fri Mar 24 13:17:57 2023

@author: p.vikas

"""

import openai

# import speech_recognition as sr

def call_chatGPT(ask  =" "):

# Define OpenAI API key 

    openai.api_key = "<key>"

    

    # Set up the model and prompt

    model_engine = "text-davinci-003"

    prompt = ask

    completion = openai.Completion.create(

        engine=model_engine,

        prompt=prompt,

        max_tokens=1024,

        n=1,

        stop=None,

        temperature=0.5,

    )

  

    response = completion.choices[0].text

    return response

--------------------------

Now call that function

 # -*- coding: utf-8 -*-

"""

Created on Fri Feb 10 16:55:12 2023

@author: p.vikas

"""

from  functions import call_chatGPT,voice_to_text

import subprocess

voice_file_path="recording.wav" 

# Ask a question 

print("Please ask a question")

# subprocess.call(["C:\\Program Files\\MyApp\\MyApp.exe"])

# question = input("Please ask a question..")

question=f"""how to Develop Your Emotional Intelligence """

# question_text=voice_to_text(voice_file_path)

answer=call_chatGPT(question)

print(answer)

Now call this function in another program and you all set to asking question to chatGPT.

This code is much stable the UI available in the market. Even it give me more flexibility to pass question from various sources and write back answer and handle answer programmatically.

 

Data Pipeline for SaaS Application

Introduction

Nowadays all the software applications are moving towards the SaaS(Software as Service) model

where it has a single application that serves multiple tenants with multiple data stores either

separated by schema or database instance. At the same time data separation between tenants

is a overcritical part for all the SaaS platform. I still believe many clients have tough to understand

and believe that their data is completely secure and separated from other tenant data on that SaaS

application and it will never mess with other tenants data. 

Also its great challenge for data engineer and data architect to plan and design Data Pipelines for

SaaS Platform.

How Data pipeline for SaaS is different than normal application pipeline: 

Solution 

Apache Airflow provides a solution with GCP Composer to manage data pipelines for SaaS applications.

Here is detail on GCP documentation Google Cloud Composer Operators — apache-airflow-providers-google Documentation

Cloud Composer is a fully managed workflow orchestration service, enabling you to create, schedule, monitor, and manage workflows that span across clouds and on-premises data centers.

Cloud Composer is built on the popular Apache Airflow open source project and operates using the Python programming language.

By using Cloud Composer instead of a local instance of Apache Airflow, you can benefit from the best of Airflow with no installation or management overhead. Cloud Composer helps you create Airflow environments quickly and use Airflow-native tools, such as the powerful Airflow web interface and command-line tools, so you can focus on your workflows and not your infrastructure.I am assuming the reader has an idea or learns about airflow basics DAG creation, Now I will explain

how to make it for SaaS.

  

How to make Airflow for SaaS

1. Write your most of logic in separate .py file and create as function 

2. Function should accept tenant as parameter

3. Create a list of tenants in DAG or you can read the tenant list from either airflow variable or configuration. I recommend reading from airflow variable 

4. Call that function directly in DAG and pass the tenant parameter 

5. Import task from airflow.decorators import task

6. Use @task in DAG just before calling the function.

7.    Use  this code to create task at runtime for each of the tenant result=data_load.expand(tenant=tenant_list)

    all_sucess(result)

In the next blog I will write all the working code for this solution.

I hope you have enjoyed learning, your feedback or comment will be highly appreciated.

Saying NO is not Leadership

 

 Never Say No 

It's my personal opinion and observation.  I have seen people getting influence with statement "A great leader must learn to say NO". But if you applying same on engineering and research will kill creativity, create fears, people start loosing enthusiast their mind start getting transform to sheep character. 

Lets take the example of all the top companies that grown and become world top companies from small startup like Google , MS, Apple has used their engineer brains 100%, its not that they have an ideas and build the solution and got popular instead they believe my each individual has unique great mind offered by GOD supper power and I just need to empower them, boost them and they used their ideas, they used their creativities, they welcomed their all initiatives. Instead of saying what to do they just say here is problem, You Mr. Engineer tell me how you want to solve it. Being engineer I can tell you that the moment when Engineers are most happy is when he solve some great problem. His happiness is proportional to problem complexity. Not when he get to know have less work in this quarter or knowing have some easy task to do this week.This is one of major reason engineers quit their employer.

 Are you a leader and leading great engineering mids? never say NO; every time you say NO, DONT DO you are loosing a chance to get one great idea and solution. Instead just empower them ask them why you think this solution work better than others. Give them enough freedom space to feel that they are not just employee for salary and work what they have been given instead they are pillar of organisation they are the creator that will get sold.

I believe thats one of the reason Agile process/ Dev-ops Development technique got more popular and much success than water fall. as in these technique we leave much on developer to take ownership and creativity.

Sometime I feel saying NO is all about limiting yourself and team from growth and fear of failing, fear of jumping on sea.