DEV Community: castnutt

BigQuery: query and table optimization to save some money

castnutt — Mon, 17 Nov 2025 09:57:04 +0000

What is BigQuery?

BigQuery belongs to the Google Cloud Platform suite and it is one of the most used platforms for data warehousing in the data world. You can host tables and query them to transform data and provide useful insights.

The way users are charged is based on the bytes processed by each query. The cost will depend on:

The number of columns scanned in each row
The total amount of data read, not the number of rows returned

To help understand this post, let's assume we have a table with a list of orders with the following columns:customer_id, product_id, store_id, product_value, product_name, purchase_date

For a query like the one below, we are retrieving all columns from all rows. This translates to scanning the entire table, which may be expensive depending on its size:

SELECT
  *
FROM orders

One way to reduce the cost of the above query is to select just the fields that are required. For example, if we want to know which products were bought by which customers and on which date, we are only interested in customer_id, product_id, and purchase_date. There's no need to load fields like store_id or product_value:

SELECT
  customer_id,
  product_id,
  purchase_date
FROM orders

That is how we can optimize queries during development. However, there are two other ways to optimize costs at the table design level, by preparing the table for more efficient querying.

Partitioning

Partitioning breaks the table into logical segments based on a date or timestamp column. For example, if we select daily partitioning, the table is split into partitions for each day.

Using the orders table example, imagine we want to retrieve product purchases made during 2024. The query below will only scan the partitions that fall within that date range, in this case 365 partitions (one per day). This helps reduce the amount of data processed by skipping older data.

SELECT
  customer_id,
  product_id,
  purchase_date
FROM orders
WHERE DATE(partition_field) BETWEEN '2024-01-01' AND '2024-12-31'

Note: To take advantage of partition pruning, make sure you filter directly on the partition field without wrapping it in functions like DATE() unless the field itself is a timestamp. If the field is already a DATE type, use it as-is.

Clustering

Clustering breaks the data further within each partition by organizing it based on the values of selected fields. These are usually the fields most frequently used in WHERE clauses.

When a table is partitioned, clustering happens within each partition. This helps BigQuery scan only the blocks relevant to your query.

In our example, if the table is clustered by store_id, and we want to retrieve products purchased by customers in a specific store, we can write:

SELECT 
  customer_id,
  product_id,
  purchase_date
FROM orders
WHERE DATE(partition_field) BETWEEN '2024-01-01' AND '2024-12-31'
  AND store_id = 'store_xyz'

Thanks to partitioning and clustering, this query will skip over irrelevant partitions and blocks, scanning much less data.

See below an image of how a table would look without any partitioning or clustering, and how it looks after adding these optimizations.

Are you aware of any other optimizations? Share them in the comments

Git basics - cheatsheet

castnutt — Fri, 06 Jun 2025 15:12:28 +0000

Git general knowledge

Git is a distributed version control system that tracks changes in source code. It helps enabling collaboration and history management for projects.
The name "origin" is a shorthand name for the remote repository that a project was originally cloned from. It is a standard convention used instead of that original repository's URL, making referencing much easier.
A .gitignore file is used to exclude files in the repository. These files wont be part of a commit. Used with personal, configuration or library files.
It is best practises to use branches for your own development and merge later instead of committing directly to main.
Default branch for a repository is called main. Before it was called master, but that has now been deprecated (can be found in old repositories)

Useful sequences

Standard dev sequence

Checkout the main branch and pull any updates so that your local copy of the repository is up to date.
```
  git checkout main
  git pull
```
Create a new branch and start development
```
  git checkout -b new_dev_branch
```
Add any modified or new files into a stage so that they can be committed.
```
  git add new_files
```

Commit files and push to the remote repository

  git commit -m 'commit message' 
  git push

Merge with/without conflicts

Rebase your branch to main - download any updates from main so that your branch is up to date

git fetch origin/main
git merge origin/main

If no conflicts, then push the changes

git push

If conflicts are found, then resolve the conflicts and push the changes

git add any_files
git commit -m 'updating my branch'
git push

Top used git commands

git config

Set username and email for global config

git config --global user.name "username"
git config --global user.email "email"

Set username and email for local config

git config --local user.name "username"
git config --local user.email "email"

git pull

Get the latest version of the repository for the current active branch

 git pull

Get the latest version of the repository for a specific branch (main in this case)

git pull origin main

git checkout

Create a branch for work

git checkout -b work_branch

Change to a different branch (main for the default repository branch)

git checkout main

Get just a single file from another branch (if origin/main used, then it will reset the file status to the repository's main branch)

git checkout origin/master -- filename

git branch

Delete branch locally

git branch -d work_branch

Delete branch remotely

git push origin --delete work_branch

Rename branch from main

git branch -m old_name new_name

Rename branch from the same branch

git branch -m new_name

Rename the local branch to the new name

git branch -m $old_name $new_name

Delete the old branch on remote

git push $remote --delete $old_name

Prevent git from using the old name when pushing in the next step

git branch --unset-upstream $new_name

Push the new branch to remote

git push $remote $new_name

Reset the upstream branch for the new_name local branch

git push $remote -u $new_name

git status

See the status of the repository. Which branch you are in, what files have been modified and what files have been created ad new.

git status

git add

Stage all changes

git add . --all

git diff

Differences between commits

git diff --name-only commit_hash commit_hash

See changes between branches (diff)

git diff master...branch

Export the differences between two branches into an external file.

git diff branch1..branch2 -- file.txt

git reset

Discard local changes as only indexed files

git reset –hard

Discard all the file

git clean -fxd

Reset branch to the same status as origin

git fetch origin
git reset --hard origin/main

git commit

Commit changes

git commit -m 'message'

git log

Commit history/log

git log

Summarized log to one line

git log --oneline

List the modified file names in each commit

git log --name-only

Compact summary with commit message plus filenames with a note whether they are gone

git log --compact-summary

git revert

Find a previous commit and revert to it

git log --oneline
git revert to_commit_hash

git push

Push changes to repo

git push origin 'work_branch'

Push a new branch to remote

git push --set-upstream origin new-branch

git stash

Save current changes into a dirty directory (push) and then retrieve them later (pop)

git stash push/pop

Stash work with a message

git stash save 'message'

Retrieve a specific stash

git stash pop --index n

List of all stashes

git stash list

Delete specific stash

git stash drop index

Delete all stashes

git stash clear

git merge

Git merge while resolving conflicts (our changes or their changes)

git merge branch --strategy-option ours/theirs

Abort a merge command

git merge --abort

As always, if you think anything is missing, wrong or can be improved, add your comments.

Google Cloud Associate Cloud Engineer - My Experience

castnutt — Thu, 05 Jun 2025 16:04:59 +0000

In February 2025, I took the Google Cloud Associate Cloud Engineer certification exam, and it turned out to be quite a journey. I'd like to share my experience, including what worked, what didn't, and a few tips I wish I had from the start.

I decided to pursue the certification in November 2024. While the Associate Cloud Engineer exam isn't the most technical or specialized, I found it to be a great entry point into the Google Cloud ecosystem. It didn't disappoint. It provided a solid foundation and broad overview of GCP services.

I enrolled in Google's Get Certified program and completed it in 10 weeks. The program includes hands-on labs and instructor-led sessions, which helped reinforce concepts and answer questions. Some of the test-style examples they walked through were particularly useful.

That said, I still faced several challenges along the way.

From the beginning, I found the scope of the exam difficult to pin down. Google offers a study guide, but it felt overly broad, and the services it referenced were scattered across multiple sections. What really helped was building a clear, structured understanding of the platform and its major components.

The official documentation is comprehensive, but that's part of the problem. It's so detailed and dense that it quickly becomes overwhelming. And while the labs are great for hands-on practice, they don't always align with what's needed to actually pass the exam.

The three resources that made the biggest difference for me:

Dan Sullivan's book: it might be a bit outdated, but it was still the key for building a structured approach and getting a solid conceptual overview of the platform.
Aldovelio Castremonte's practice questions: these were incredibly helpful for getting used to the exam format and spotting gaps in my understanding. Whenever I came across a question I couldn't confidently answer, I'd dig into the topic, add it to my notes, and make sure I fully understood it.
My own summarized notes: after working through books, videos, practice exams, and asking ChatGPT a hundred questions, I created a condensed set of notes focused on what truly matters for the exam. This became my go-to resource for review. You can check it out here

If there is anything missing or that can be improved, please add your comments