Datawarehouse and BigQuery

        Data warehouse is a repository where all data from multiple sources are stored —raw data, metadata, and summary data— in a structural approach. The data will be used for various user from data analyst, data scientist, and business analyst. Data may need to be cleaned to ensure data quality before using. Data in a data warehouse can be aggregated for specific need (i.e., marketing, sales, inventory) are called data marts.

What is Google BigQuery?

        Google BigQuery is a part of Google Cloud Platform (GCP). It is the autonomous data to AI platform, automating the entire data life cycle, from ingestion to AI-driven insights, so you can go from data to AI to action faster [1]. BigQuery is serverless, meaning users don't have to  manage infrastructure. BigQuery separates storage and compute engines. It can automatically scale to handle large datasets and run complex, petabyte-scale queries quickly. BigQuery supports standard ANSI SQL (GoogleSQL). This makes it accessible to data analysts and engineers who are already familiar with SQL.

Ways to Minimize Cost

        The cost and time for querying depends on amount of data being scanned by BigQuery (don't run SELECT * over millions rows of data). However, we can optimize query time and cost of data tables using partitioning and clustering. These 2 approaches let BiqQuery to scan only relevant rows that matched with the filter condition in a column.

Partitioning

Partitioning segment a table into groups of rows based on numeric values in a column (i.e., integer, date). The query cost can be estimated before running. BigQuery skip rows that do not match the query's filter on the partition column, the process is called data pruning. The limitation of partitions is 4,000.

Clustering

Clustering provides additional performance from partitioning. Clustering organizes, sorts, data blocks within a single table or within each partition of a partitioned table. It further improve performance within the scanned data by ensuring rows with similar values are stored physically close together in storage blocks. We can specify up to 4 clustering columns. However, querying cost can only after the query runs (the initial estimate may be an upper bound), as the data pruning happens during query execution. Note that table size less than 1 GB will not receive significant improvement from clustering.

The image shows an a query to retrieve the distinct VendorIDs between tpep_dropoff_datetime 2024-03-01 and 2024-03-15 from NYC yellow taxi dataset without partitioning and clustering.

Let's see the performance gain from partitioning and clustering. The SQL below partition the NYC yellow taxi data by tpep_dropoff_datetime and cluster it by VendorID.

CREATE OR REPLACE TABLE `de-zoomcamp-487008.de_zoomcamp.yellow_tripdata_partitioned_clustered`
PARTITION BY DATE(tpep_dropoff_datetime)
CLUSTER BY VendorID AS
SELECT * FROM `de-zoomcamp-487008.de_zoomcamp.yellow_tripdata_non_partitioned`;

Then we can run the previous query to see the performance.

With partitioning and clustering, we can see the the data can is reduced to 26.84 MB (about 91% decreased in data scanned by BigQuery).

References

  1. From data warehouse to autonomous data and AI platform https://cloud.google.com/bigquery?hl=en

Comments

Post a Comment

Popular posts from this blog

Basics Dockers for Data Engineering

Image
On this page What is Docker Docker Volume Multiple Containers Hello there! I have started the Data Engineering Zoomcamp 2026 cohort led by Alexey Grigorev. I want to share what I am learning as I go. My goal is to explain how things work and why we use them. Codes of each learning modules are in  my GitHub . Knowing what to do as a data engineer Data engineer builds and maintain what so called "data pipeline" a program that ingest data from sources, clean, transform and put them in a database. The goal is to make sure the pipeline works whatever it takes. Now, imagine that you build a pipeline using Python 3.11 and the production server is on 3.8: your pipeline cannot run on the server. What we need is a software that allow us to run the same version and dependencies on every machine. That is what docker do. What is Docker Docker is a containerization software; it isolates an environment from the ...
Read more

Using Secrets as Environment Variables in GitHub Codespace

My Problem with Secret Management           In the second module of Data Engineering Zoomcamp , I've learned about Kestra, an open source workflow orchestrator, to automate and provision Data Engineering tasks. Today, it is inevitable for data engineers to work with cloud platform like Google Cloud Platform (GCP), Microsoft Azure, Amazon Web Service (AWS), etc. Thus, we need to pass our credential into Kestra, as well as other workflow orchestrator, to streamline our works. I use GitHub for version control and coding via its Codespaces. I think it is not a good decision to put my credential in an public repository. I following  Manage Secrets in Kestra | How-to Guide from Kestra to keep my credential secret and use it as a variable in my workflows. Since I use an open-source version, I can not directly add my credential as a secret via Kestra UI. Thus, I opt another method to store my credentials in a  .env file. Then, convert them to base-64 an...
Read more