[{"title":"Querying BigQuery Tables with Serverless Spark SQL","category":"Data Engineering","tags":["gcp","bq","dataproc"],"created_at":"2026-11-11T09:44:43.945295Z","summary":"Dataproc Serverless","updated_at":"2026-11-11T09:44:43.945295Z","cover_image_url":"https://storage.googleapis.com/abatra-portfolio-assets-public/blog-img/video_thumbs/thumb-dataprocServerles.png","published":true,"read_time":8,"content":"\n
\n\nYouTube Video: - https://youtu.be/T27ZU0FxZAY\n
\n\n# Querying BigQuery Tables with Serverless Spark SQL\n
\n\nIf your data lives in BigQuery but your processing logic is written in Spark, you don't have to export the data before processing it. Serverless Spark SQL lets you query BigQuery tables directly using managed serverless Spark, with no infrastructure to set up. This post walks through the command, the required properties, the catalog naming convention, and a demo run.\n
\n\n## Prerequisites: IAM roles and APIs\n
\n\nBefore submitting a job, you need the right APIs enabled and a service account with the right permissions. The video's description lists the full set of APIs and IAM permissions. The main roles for the service account are:\n
\n\n- BigQuery User\n
\n\n- BigQuery Data Viewer\n
\n\n- Dataproc Worker\n
\n\nA naming note: Dataproc on Compute Engine and Serverless for Apache Spark are now called Managed Service for Apache Spark.\n
\n\n## The gcloud command\n
\n\nThe demo uses the `gcloud dataproc batches submit spark-sql` command. Each part of it matters:\n
\n\n- **`batches`**: the Dataproc batches command is what runs Spark in serverless mode on Google Cloud, so you don't manage any infrastructure.\n
\n\n- **`spark-sql`**: this is the type of workload you submit. If you were submitting a PySpark application, you would use `pyspark` here instead. In this demo, the job is a SQL query.\n
\n\n- **Project and region**: the project, and the region where you want the serverless Spark SQL job to run.\n
\n\n- **Runtime version**: the version of the serverless service.\n
\n\n- **Service account**: the service account described in the prerequisites.\n
\n\n- **Properties**: the settings that make BigQuery access work, covered in the next section.\n
\n\n## The two required properties\n
\n\nTwo settings are required to query BigQuery tables with serverless Spark.\n
\n\n1. **The Spark BigQuery connector version.** The command specifies the connector version and supplies the matching jars. This avoids a `ClassNotFoundException`. The connector version should be compatible with the Spark runtime version you are using.\n
\n\n2. **The BigQuery catalog.** Set the Spark SQL catalog property for the `bigquery` catalog to the default BigQuery catalog implementation.\n
\n\nTogether, the runtime version, the jars, and the catalog property are what make the job work.\n
\n\n## Writing the query: the catalog naming convention\n
\n\nThe most important thing to remember is how you reference tables. In an ordinary BigQuery query you might reference `project.dataset.table`. In Spark SQL running with the BigQuery catalog, the project part is replaced by the catalog name, so the reference becomes:\n
\n\n`bigquery.dataset.table`\n
\n\nIn the demo, the dataset was created in the presenter's project, and the table is America Health Rankings. The Spark SQL query is equivalent to the BigQuery query, with the project name swapped for the `bigquery` catalog. The query selects the maximum value for each state name from the table.\n
\n\n## Running the demo\n
\n\nWith the command ready, the demo proceeds as follows:\n
\n\n1. Paste the `gcloud` command into the terminal and run it.\n
\n\n2. The terminal confirms that the batch job has been submitted and created.\n
\n\n3. Open the serverless batches page in the managed Spark UI. The batch shows as running.\n
\n\n4. Wait for the job to finish. Once it completes, the terminal also confirms the job completed successfully.\n
\n\n## Reviewing the results\n
\n\nThe query returned the two expected columns: the state name and the maximum value for that state. It returned 52 rows, one per state-level entry, each with its maximum value. This confirms that Spark read the BigQuery table directly through the catalog and applied the aggregation, with no export step in between.\n
\n\n## Key takeaways\n
\n\n- You can query BigQuery tables directly from Spark SQL without exporting the data.\n
\n\n- Use `gcloud dataproc batches submit spark-sql` to run SQL on serverless Spark. Use `pyspark` instead for PySpark applications.\n
\n\n- You don't have to manage any infrastructure for serverless batches.\n
\n\n- The service account needs BigQuery User, BigQuery Data Viewer, and Dataproc Worker roles, along with the required APIs enabled.\n
\n\n- Two properties are required: the Spark BigQuery connector version (with matching jars, to avoid `ClassNotFoundException`) and the BigQuery catalog setting.\n
\n\n- The connector version must be compatible with your Spark runtime version.\n
\n\n- In queries, reference tables as `bigquery.dataset.table` instead of `project.dataset.table`.\n
\n\n- You can track a batch's progress in the managed Spark serverless batches UI and in the terminal.\n
\n\n## Conclusion\n
\n\nQuerying BigQuery from serverless Spark SQL comes down to a single `gcloud` command, a compatible connector version with its jars, the BigQuery catalog property, and the `bigquery.dataset.table` naming convention. Once those are in place, you can extend the approach from this simple aggregation to production-grade projects with more complex SQL, still without any infrastructure setup.\n
\n\n## Connect With Me\n
\n\n- š„ YouTube: Data Potion\n
\n\n- š¼ LinkedIn: https://www.linkedin.com/in/abhishek-batra-46053a70\n
\n\n- š Portfolio: https://abhishekbatra.me/\n
\n\n- š¬ Community: join the WhatsApp learning community linked in the video description\n
\n\n[](https://youtu.be/T27ZU0FxZAY)\n
\n","id":"Querying_BigQuery_Tables_with_Serverless_Spark_SQL"},{"title":"Jupyter Notebooks on Dataproc","category":"Data Engineering","updated_at":"2026-10-30T09:34:39.414261Z","created_at":"2026-10-30T09:34:39.414261Z","tags":["gcp","bq","Jupyter"],"summary":"Noebooks on Dataproc","cover_image_url":"https://storage.googleapis.com/abatra-portfolio-assets-public/blog-img/thumbnail.png","published":true,"read_time":8,"content":"\n
\n\nYouTube Video: - https://youtu.be/QRnzLPNxN0Y\n
\n\n# Running Jupyter Notebooks on Dataproc with the Optional Component\n
\n\nWhen you have a Spark cluster and want to explore data interactively, submitting a job every time you change a line of code gets tedious. On Google Cloud Dataproc, the answer is the Jupyter optional component. This post covers what it is, how to install it with the gcloud command, where your notebooks are stored, and the main limitation to keep in mind.\n
\n\n## What the Jupyter optional component is\n
\n\nJupyter is an optional component you add when you create a Dataproc cluster. It gives you a web-based notebook with the JupyterLab UI, running on the cluster's master node on port 8123. Two kernels are available by default: PySpark and Python 3.\n
\n\nThe request flow looks like this:\n
\n\n1. A user (for example, a data scientist) opens a web browser client.\n
\n\n2. The request goes to the Component Gateway, which runs on Dataproc.\n
\n\n3. Over an HTTPS access point, the request reaches the JupyterLab component on port 8123 on the master node.\n
\n\nIf your cluster has more than one worker node, you will see them alongside the master, and you can add more workers. The demo uses a single-node cluster, which is the best choice for saving cost and for demonstration purposes.\n
\n\n## Where your notebooks live\n
\n\nNotebooks are saved in your Dataproc staging bucket in Google Cloud Storage (GCS). A setting that is enabled by default points the Jupyter notebook directory at a GCS directory, so notebooks are kept in persistent storage without any extra work. You do not need to rely on the cluster's persistent disk.\n
\n\nFor data lake and larger-scale storage, you can keep your data in Cloud Storage as datasets in formats such as CSV, Parquet, ORC, Delta and Iceberg.\n
\n\n## Single-user by design\n
\n\nOne important point to understand up front: this component is a single-user setup. It is one notebook server for one person, not a multi-user environment. If a team uses it, the resources are shared. There is no per-user isolation. Notebooks can be shared and read by others, but everyone is working on the same server.\n
\n\n## Installing the component\n
\n\nThere are three ways to install it: the Google Cloud console, the gcloud command, and the REST API. The demo uses the gcloud command. The key elements of the cluster creation command are:\n
\n\n- The cluster name, with a single-node configuration for cost savings.\n
\n\n- The master machine type, `n1-standard-2` in the demo, along with the disk type.\n
\n\n- The optional component, `JUPYTER`.\n
\n\n- The Component Gateway, which is enabled as the main entry point to cluster components, whether that is the Spark UI, JupyterLab or the MapReduce UI.\n
\n\n- The region (Asia South 2, Delhi in the demo), a temp bucket and a service account.\n
\n\n- Automatic deletion of the cluster after about an hour, so the demo cluster does not linger.\n
\n\nThe command in outline form looks like this:\n
\n\n```bash\ngcloud dataproc clusters create \\\n --single-node \\\n --master-machine-type=n1-standard-2 \\\n --optional-components=JUPYTER \\\n --enable-component-gateway \\\n --region=asia-south2\n```\n\nThe exact flags for the disk type, temp bucket, service account and auto-deletion were shown on screen in the demo and are omitted here; add them as your environment requires.\n
\n\nRun the command in Cloud Shell or any terminal within your project. Cluster creation takes a while, roughly 10 minutes in the demo.\n
\n\n## Opening JupyterLab\n
\n\nOnce the cluster is created, open it in the console and look for the JupyterLab link in the Web Interfaces section, which is served through the Component Gateway. In the demo, the first attempt to open the link did not load. Refreshing, closing the tab and trying again worked. The setup takes a little time, so be patient if the page does not appear immediately.\n
\n\nOnce JupyterLab loads, you see the file browser and any notebooks that already exist, such as a demo notebook created earlier. From there you can create folders and notebooks. In the demo, a new folder called `notebooks-v2` was created, with a Python 3 notebook named `demo` inside it.\n
\n\n## Creating a Spark session and reading data\n
\n\nThe first step in any notebook is getting a Spark session. In the demo, this was done by importing `SparkSession` from `pyspark.sql` and creating the session. A few warnings appeared during startup, which is normal.\n
\n\nChecking `spark.version` returned 3.5.3 on this cluster.\n
\n\nNext, the notebook read a CSV file from a GCS bucket using `spark.read.csv` with the file's GCS path, then called `show()` to display the top 20 rows. This confirms the whole loop works: a Spark session in a notebook, reading directly from Cloud Storage, with results displayed immediately and no job submission.\n
\n\n```python\nfrom pyspark.sql import SparkSession\n\nspark = SparkSession.builder.getOrCreate()\nspark.version\n\ndf = spark.read.csv(\"gs:///\")\ndf.show()\n```\n\nThe builder call and the bucket path are placeholders; the demo used its own bucket and file path.\n
\n\n## Confirming notebooks are saved in GCS\n
\n\nAfter creating the notebook, the demo checked the staging bucket in the GCS console. The folder `notebooks-v2` appeared under the Jupyter notebooks directory, with the `demo` notebook inside. This confirms that notebooks persist in Cloud Storage rather than only on the cluster's disk, so they survive beyond the cluster itself.\n
\n\n## The main limitation and alternatives\n
\n\nThe big limitation is the single-user model. Because it is one shared notebook server with no per-user isolation, it is not a good fit for teams who need separate environments. Users can share and read notebooks, but they share the same resources.\n
\n\nFor team use, the demo suggests two alternatives:\n
\n\n- Create a Vertex AI Workbench instance with managed Spark enabled.\n
\n\n- Install the JupyterLab plugin on a VM.\n
\n\n## Key takeaways\n
\n\n- Jupyter is an optional component you enable at cluster creation time on Dataproc, giving you the JupyterLab UI for interactive Spark work.\n
\n\n- It runs on the master node on port 8123 and is reached through the Component Gateway.\n
\n\n- Two kernels are available by default: PySpark and Python 3.\n
\n\n- You can install it via the Google Cloud console, the gcloud command or the REST API.\n
\n\n- Enable the Component Gateway alongside the `JUPYTER` optional component so you can reach the UI.\n
\n\n- A single-node cluster is a cost-effective choice for demos and experimentation.\n
\n\n- Notebooks are saved by default in the Dataproc staging bucket in GCS, giving you persistent storage.\n
\n\n- The setup is single-user with shared resources and no per-user isolation.\n
\n\n- For multi-user teams, consider Vertex AI Workbench with managed Spark or JupyterLab on a VM.\n
\n\n## Conclusion\n
\n\nThe Jupyter optional component on Dataproc is a quick way to move from submitting jobs to exploring data interactively. One gcloud command with the `JUPYTER` component and the Component Gateway gives you a working JupyterLab with PySpark, direct access to data in Cloud Storage, and notebooks that persist in your staging bucket. Just remember that it is built for one user. If your team needs isolated environments, look at Vertex AI Workbench or a JupyterLab installation on a VM instead.\n
\n\n## Connect With Me\n
\n\n- š„ YouTube: Data Potion\n
\n\n- š¼ LinkedIn: https://www.linkedin.com/in/abhishek-batra-46053a70\n
\n\n- š Portfolio: https://abhishekbatra.me/\n
\n\n- š¬ Community: join the WhatsApp learning community linked in the video description\n
\n\n[](https://youtu.be/QRnzLPNxN0Y)\n
\n","id":"Jupyter_Notebooks_on_Dataproc"},{"title":"Loading Data into BigQuery with the Python Client Library and Load Jobs","category":"Data Engineering","summary":"This blog BigQuery Notebooks","created_at":"2026-10-23T10:54:06.198289Z","updated_at":"2026-10-23T10:54:06.198289Z","tags":["gcp","bq","notebooks"],"cover_image_url":"https://storage.googleapis.com/abatra-portfolio-assets-public/blog-img/video_thumbs/load_job_thumb.png","published":true,"read_time":8,"content":"[]\n
\n\nYouTube Video: - https://youtu.be/dL99MASaKQE\n
\n\n\nBigQuery client libraries let you interact with BigQuery programmatically: load data, run queries, list datasets, tables and running jobs. This post focuses on one task: loading data from Cloud Storage into a BigQuery table by creating a load job with a load job configuration. It is a production-grade approach that scales from a single file to thousands, and it fits easily into automation.\n
\n\n## What the BigQuery Client Can Do\n
\n\nThe BigQuery client library (Python in this demo) supports many operations beyond loading. You can query data, list datasets, list tables and inspect running jobs. For loading specifically, it offers several methods. The one covered here is creating a job and submitting a load job configuration.\n
\n\n## Job Types in BigQuery\n
\n\nBigQuery supports several job types, all documented in Google's BigQuery documentation:\n
\n\n- **Load job:** loads data into BigQuery. It can run asynchronously, and you can load any number of files at once.\n
\n\n- **Query job:** runs SELECT statements, update statements, stored procedure calls and other SQL statements.\n
\n\n- **Copy job:** copies data.\n
\n\n- **Extract job:** extracts data out of BigQuery.\n
\n\nIf the configuration matches none of these types, the client raises an error.\n
\n\n## Anatomy of a Load Job Configuration\n
\n\nThe load configuration is a REST-style payload. Because it is a REST configuration, you can also submit it through serverless options such as GCP Workflows by calling the API with the configuration. The payload tells BigQuery where and how to load:\n
\n\n- **Job type key:** marks the job as a load job.\n
\n\n- **Source URIs:** the Cloud Storage location of the data.\n
\n\n- **Schema fields:** optional. In the demo they are commented out because no schema is provided; the destination table already has one.\n
\n\n- **Destination table:** the project, dataset and table to load into.\n
\n\n- **Create disposition:** set to never create, since the table already exists.\n
\n\n- **Write disposition:** set to truncate, so each run replaces the table's contents.\n
\n\n- **Autodetect schema:** set to false.\n
\n\n- **Source format:** the file format. The demo uses CSV, and other formats such as Parquet and ORC are possible.\n
\n\n- **Skip leading rows:** set because the CSV file has a header row.\n
\n\nMany other load configuration options exist, and the full list is in the official documentation.\n
\n\n## Hive Partitioned Data\n
\n\nThe script also checks whether the data uses a Hive-style directory layout, for example:\n
\n\n```\ngs://my-bucket/year=2026/month=05/data.csv\n```\n\nFor files laid out this way, you enable the Hive partitioning option and provide a source URI prefix that covers everything up to the partition folders, along with the source URI pattern for the files. The demo does not use Hive mode, but the option is available through the same configuration.\n
\n\n## The Demo Setup\n
\n\nThe source is a file named `flipkart.csv` in Cloud Storage, and the target is a `flipkart` table in a demo dataset. The table already has a schema, so none is supplied during loading, though you can supply your own. Because the write disposition is truncate, running the load again replaces the existing data rather than appending to it.\n
\n\nThe script reads its inputs from command-line arguments: source URI, optional schema fields, target project, dataset, table name and file type. It then builds the configuration, gets a client for the project and calls a load function.\n
\n\n## How the Load Works: create_job\n
\n\nThe load function does one thing: it calls `create_job` on the BigQuery client. Looking inside the client library, `create_job` takes a configuration and inspects it. If the configuration contains a load section, it starts a load job. Other sections map to the other job types: copy, extract and query.\n
\n\nThe client also offers `load_table_from_uri`, which takes source URIs, a destination and a job config. It is a more direct option for plain loads, and the demo mentions it as a valid alternative. The demo uses `create_job` instead.\n
\n\nThe advantage of `create_job` is that it is generic. You can build abstraction layers on top of it and create job configurations in many ways:\n
\n\n- JSON configuration files\n
\n\n- YAML files\n
\n\n- Terraform generating configurations on the fly\n
\n\n- Other upstream processes\n
\n\nYour code simply consumes the configuration, which makes loading easy to automate.\n
\n\n## Asynchronous vs. Synchronous Execution\n
\n\n`create_job` returns a job object, which for this configuration is a load job. Until you call `result()` on it, the job is asynchronous: it is submitted and runs in BigQuery without your script waiting. Calling `result()` makes the script wait until the job completes, so the load becomes synchronous from the script's point of view.\n
\n\n## Running the Script\n
\n\nThe module is run with Python, passing the inputs: the source URI (the file path), no schema, the target project, the demo dataset, the `flipkart` table and the CSV type. The script prints the configuration and then reports that the data has loaded. Checking the table in the console confirms it: after a refresh, the table's details show the current time as the load time.\n
\n\n## Scaling Up\n
\n\nThe same approach works at much larger scale. A wildcard source URI (a path ending in a star) lets you load many files in a single job, and the demo mentions loading around 10,000 files at once. The job can stay asynchronous: another client, such as an Airflow task or other automation, can poll the job and check whether it has finished.\n
\n\n## Key takeaways\n
\n\n- BigQuery client libraries cover loading, querying and listing datasets, tables and jobs.\n
\n\n- A load job is a production-grade way to load data from Cloud Storage into BigQuery.\n
\n\n- BigQuery job types are load, query, copy and extract.\n
\n\n- The load configuration is a REST payload: source URIs, destination table, create and write dispositions, source format, schema or autodetect settings and options like skipped header rows.\n
\n\n- Create disposition \"never\" with write disposition \"truncate\" loads into an existing table and replaces its contents.\n
\n\n- Hive-partitioned files can be loaded by enabling the Hive partitioning option and providing a source URI prefix.\n
\n\n- `create_job` inspects the configuration and starts the matching job type; `load_table_from_uri` is a simpler alternative for plain loads.\n
\n\n- Jobs are asynchronous until you call `result()`.\n
\n\n- Configurations can come from JSON, YAML or Terraform, which makes the approach easy to automate.\n
\n\n- Wildcard source URIs allow loading very large numbers of files in one asynchronous job.\n
\n\n## Conclusion\n
\n\nSubmitting a load job configuration through the BigQuery client's `create_job` method gives you a flexible, scalable way to load data. The configuration is plain data, so it can be generated by many tools, and the asynchronous job model lets orchestration systems like Airflow track progress. Whether you load one CSV file or thousands of files, the pattern stays the same.\n
\n\n## Connect With Me\n
\n\n- š„ YouTube: Data Potion\n
\n\n- š¼ LinkedIn: https://www.linkedin.com/in/abhishek-batra-46053a70\n
\n\n- š Portfolio: https://abhishekbatra.me/\n
\n\n- š¬ Community: join the WhatsApp learning community linked in the video description\n
\n\n[](https://youtu.be/dL99MASaKQE)\n
\n","id":"Loading_Data_into_BigQuery_with_the_Python_Client_Library_and_Load_Jobs"},{"title":"Getting Started with BigQuery Notebooks: A Databricks-Style Spark Experience on Google Cloud","category":"Data Engineering","cover_image_url":"https://storage.googleapis.com/abatra-portfolio-content/blogs/getting-started-with-bigquery-notebooks-running-spark-on-goo.md","created_at":"2026-10-23T10:20:15.755803Z","updated_at":"2026-10-23T10:20:15.755803Z","summary":"This blog BigQuery Notebooks","tags":["gcp","bq","notebooks"],"published":true,"read_time":8,"content":"
\n\nYouTube video: https://youtu.be/r7UFVEZxlkc
\n\n\r\n
\n\nBigQuery notebooks are a lesser-known capability of BigQuery that looks and behaves much like Databricks.\n\nIf you have used Databricks, you know it as a platform popular with Spark users for processing large volumes of data.\r\n
\n\nBigQuery notebooks let you do similar work inside Google Cloud.\r\n
\n\nThis post walks through how to open a Spark notebook, read a CSV file from Cloud Storage, run a simple filter, and what to know about pricing before you try it.\r\n
\n\n## What Are BigQuery Notebooks?\r\n
\n\nBigQuery notebooks give you an interactive notebook environment inside Google Cloud.\r\n
\n\nThe experience is comparable to Databricks, which is known for processing large datasets with Spark.\r\n
\n\nBigQuery notebooks let you apply the same Spark-style workflow within Google Cloud.\r\n
\n\nThey are described in the BigQuery documentation as enterprise Colab notebooks.\r\n
\n\nThe presenter sees this as BigQuery moving into territory that Databricks already occupies, and as a sign that Google is competing in that space.\r\n
\n\n## Creating Your First Spark Notebook\r\n
\n\nGetting to a working notebook takes only a few steps:\r\n
\n\n1. Go to the home page in the Google Cloud console.\r\n
\n2. Click **Notebook**.\r\n
\n3. Choose **Spark notebook with Spark default session**.\r\n
\n4. Enable the APIs the console asks for.\r\n
\n5. Open the first notebook.\r\n
\n\nThe very first cell is pre-populated with code that creates a Spark session through Dataproc.\r\n
\n\nIt uses the `spark session builder` with `getOrCreate`.\r\n
\n\nRunning that cell gives you a Spark session by default, with no manual cluster setup.\r\n
\n\nIn the demo, checking the Spark version showed **4.0.2**.\r\n
\n\n## Reading a CSV File from Google Cloud Storage\r\n
\n\nOnce the session is running, you can read data as you would in any Spark environment.\r\n
\n\nIn the demo, the data is a CSV file stored in a Google Cloud Storage (GCS) bucket.\r\n
\n\nThe notebook reads the file into a DataFrame and displays some records.\r\n
\n\nThe columns include ID, title, rating, main category and platform.\r\n
\n\nFrom here, you can work with the data the same way you would in Databricks.\r\n
\n\nYou can also read BigQuery public datasets by using `spark.table` on them.\r\n
\n\n## Running a Simple Filter\r\n
\n\nTo try the notebook out, the presenter ran a basic filter, the DataFrame equivalent of a SQL `WHERE` clause.\r\n
\n\nThe goal was to show only rows where the rating is greater than three.\r\n
\n\nThe first attempt used the `col` function from `pyspark.sql.functions`.\r\n
\n\nIt failed with an error because the rating column had not been cast to a numeric type.\r\n
\n\nThe fix took a few iterations:\r\n
\n\n- Import `col` from `pyspark.sql.functions`.\r\n
\n- Import the type to cast to from `pyspark.sql.types`.\r\n
\n- Cast the rating column before filtering.\r\n
\n\nCasting to an integer type failed, because the values could not be converted to integers.\r\n
\n\nCasting to a double type worked, and the filter returned the rows with ratings greater than three.\r\n
\n\n```python\nfrom pyspark.sql.functions import col\nfrom pyspark.sql.types import DoubleType\n\ndf.filter(col(\"rating\").cast(DoubleType()) > 3).show()\n```\n\n\r\n
\n\nThe exact column handling in the video was assembled live and edited along the way.\r\n
\n\nThe snippet above shows the final approach: cast the rating to a double, then filter.\r\n
\n\nThe lesson is that CSV columns often arrive as strings, so you need to check types before filtering numerically.\r\n
\n\nIf a cast fails, try a different type that fits the actual values.\r\n
\n\n## Pricing: This Is Not Free\r\n
\n\nThe presenter points out an important caveat for newcomers: BigQuery notebooks are a paid feature.\r\n
\n\nYou can find the details in the BigQuery documentation under Colab Enterprise notebooks, in the pricing section on the right side of the page.\r\n
\n\nCompute is charged per hour for the notebook runtime, and the rate depends on the region where you run it.\r\n
\n\nThe presenter first mentioned a figure of around 0.16 dollars per hour for compute.\r\n
\n\nThe notebook runtime pricing table showed about **0.19 dollars per hour** for the Mumbai region, where the demo was running.\r\n
\n\nCheck the pricing table for your own region before you start experimenting, so there are no surprises on your bill.\r\n
\n\n## Key Takeaways\r\n
\n\n- BigQuery notebooks give you a Databricks-like notebook experience inside Google Cloud.\r\n
\n- You create one from the home page: **Notebook**, then **Spark notebook with Spark default session**, after enabling the required APIs.\r\n
\n- The first cell creates a Spark session automatically through Dataproc, and the demo ran Spark 4.0.2.\r\n
\n- You can read CSV files from a GCS bucket and display them as DataFrames.\r\n
\n- You can also query BigQuery public datasets with `spark.table`.\r\n
\n- Check column types before filtering. In the demo, the rating column had to be cast to a double before a numeric comparison would work.\r\n
\n- The service is paid. Compute is billed hourly, and the rate varies by region (about 0.19 dollars per hour in Mumbai).\r\n
\n\n## Conclusion\r\n
\n\nBigQuery notebooks offer a quick way to run Spark workloads in Google Cloud without leaving the BigQuery environment.\r\n
\n\nSetup is short, the Spark session is created for you, and the usual DataFrame operations work as expected.\r\n
\n\nBecause the runtime is billed by the hour, review the pricing for your region before you start.\r\n
\n\nWith that in mind, it is a good option for preparing and exploring data with Spark.\r\n
\n\n[](https://youtu.be/r7UFVEZxlkc)\n","id":"Getting_Started_with_BigQuery_Notebooks:_A_Databricks-Style_Spark_Experience_on_Google_Cloud"},{"title":"Unlock Your Data Potential: Top 5 Must-Know BigQuery Commands for Every Data Engineer","category":"Data Engineering","updated_at":"2026-10-18T11:47:27.088049Z","created_at":"2026-10-18T11:47:27.088049Z","cover_image_url":"https://storage.googleapis.com/abatra-portfolio-content/blogs/bigquery_top_5_commands_blog_readme.md","tags":["gcp","bq","piplines"],"summary":"This blog explains5 cool bq commands","published":true,"read_time":8,"content":"[](https://youtu.be/Ej751blU0jA)\nA comprehensive blog guide and reference summary based on the **Data Potion** video tutorial: *\"Unlock Your Data Potential: Top 5 Must-Know BigQuery Commands for Every Data Engineer!\"*
\n
\n\n
\n\n
\nHello Data folks! š
\n
\nIf you work with BigQuery daily, the console is great, but the `bq` command-line tool is what makes you fast, scriptable, and automation-ready.
\nIn this post, I walk through 5 `bq` commands that I use all the time in real data engineering work.
\n
\nš„ Watch the video: https://youtu.be/Ej751blU0jA
\n
\n\n## What You'll Learn
\n
\n\n- `bq load` ā load data from files into BigQuery tables
\n- `bq cp` ā copy tables within or across datasets and projects
\n- `bq query` ā run SQL from the terminal
\n- `bq mk` ā create datasets, tables, views and more
\n- `bq extract` ā export table data to Cloud Storage
\n
\n\n## Prerequisites
\n
\n\n- A Google Cloud project with BigQuery enabled
\n- Google Cloud SDK installed (or use Cloud Shell / Cloud Workstations)
\n- Authenticated session: `gcloud auth login`
\n- Default project set: `gcloud config set project `
\n- A Cloud Storage bucket for load and extract examples
\n
\n\n## 1. `bq load` ā Load Data into BigQuery
\n
\nLoads data from Cloud Storage (or a local file) into a BigQuery table.
\n
\n\n```bash\nbq load \\\n --source_format=CSV \\\n --skip_leading_rows=1 \\\n --autodetect \\\n my_dataset.customers \\\n gs://my-bucket/customers.csv\n```\n
\nKey points:
\n
\n\n- `--source_format` supports CSV, NEWLINE_DELIMITED_JSON, PARQUET, AVRO and ORC
\n- `--autodetect` infers the schema; for production pipelines, pass an explicit schema instead
\n- `--replace` overwrites the table; `--noreplace` appends
\n- Wildcards like `gs://my-bucket/data/*.csv` load multiple files in one job
\n
\nUse it when: you are doing quick ingestion, backfills, or one-off loads.
\n
\n\n## 2. `bq cp` ā Copy Tables
\n
\nCopies a table to another table, dataset or project.
\n
\n\n```bash\nbq cp my_dataset.customers my_dataset.customers_backup\n```\n
\nKey points:
\n
\n\n- `-f` overwrites the destination without prompting
\n- `-a` appends to the destination table
\n- Copy jobs are free of query charges and fast, since they are metadata-level operations
\n- Works across datasets and projects, which is handy for environment promotion (dev ā test ā prod)
\n
\nUse it when: you need backups, snapshots before risky changes, or table promotion across environments.
\n
\n\n## 3. `bq query` ā Run SQL from the Terminal
\n
\nRuns a query directly from the command line.
\n
\n\n```bash\nbq query \\\n --use_legacy_sql=false \\\n 'SELECT country, COUNT(*) AS total\n FROM my_dataset.customers\n GROUP BY country\n ORDER BY total DESC\n LIMIT 10'\n```\n
\nKey points:
\n
\n\n- Always use `--use_legacy_sql=false` for Standard SQL
\n- `--dry_run` shows how many bytes a query would process, without running it. Great for cost control
\n- `--destination_table` saves query results into a table
\n- `--parameter` enables parameterized queries
\n- `--format=prettyjson` or `--format=csv` helps with scripting
\n
\nUse it when: you are automating checks, validating loads, or embedding SQL in shell scripts and CI/CD.
\n
\n\n## 4. `bq mk` ā Create Resources
\n
\n\nCreates datasets, tables, views, and more.
\n
\nCreate a dataset:
\n
\n\n```bash\nbq mk --dataset \\\n --location=US \\\n --description=\"Sales data\" \\\n my_project:sales_dataset\n```\n
\nCreate a partitioned and clustered table:
\n
\n\n```bash\nbq mk --table \\\n --time_partitioning_field=order_date \\\n --clustering_fields=customer_id \\\n sales_dataset.orders \\\n order_id:INTEGER,customer_id:INTEGER,order_date:DATE,amount:FLOAT\n```\n
\nKey points:
\n
\n\n- Set `--location` carefully; a dataset's location cannot be changed later
\n- Partitioning and clustering at creation time cut query cost and improve performance
\n- You can also create views (`--view`) and set labels (`--label`)
\n
\nUse it when: you are bootstrapping environments or scripting repeatable infrastructure setup.
\n
\n\n## 5. `bq extract` ā Export Data to Cloud Storage
\n
\nExports a BigQuery table to files in Cloud Storage.
\n
\n\n```bash\nbq extract \\\n --destination_format=CSV \\\n --compression=GZIP \\\n my_dataset.customers \\\n gs://my-bucket/exports/customers-*.csv.gz\n```\n
\nKey points:
\n
\n\n- Supported formats: CSV, NEWLINE_DELIMITED_JSON, AVRO and PARQUET
\n- Use a wildcard (`*`) in the URI; exports over 1 GB must be sharded into multiple files
\n- Compression (GZIP, SNAPPY, etc.) reduces storage and transfer cost
\n- Parquet and Avro preserve schema and types better than CSV
\n
\nUse it when: you are sharing data with downstream systems, archiving, or feeding other tools.
\n
\n\n## Quick Cheat Sheet
\n
\n\n| Command | Purpose | Handy Flag |
\n\n| `bq load` | Load files into a table | `--autodetect` |
\n| `bq cp` | Copy a table | `-f` / `-a` |
\n| `bq query` | Run SQL | `--dry_run` |
\n| `bq mk` | Create resources | `--time_partitioning_field` |
\n| `bq extract` | Export to GCS | `--destination_format` |
\n
\n\n## Best Practices
\n
\n\n- Run `--dry_run` before expensive queries
\n- Prefer explicit schemas over autodetect in production
\n- Partition and cluster large tables from day one
\n- Wrap these commands in shell scripts or Cloud Composer/Airflow tasks for repeatable pipelines
\n- Keep datasets and buckets in the same region to avoid errors and egress costs
\n
\n\n## Conclusion
\n
\nMastering these five commands, `bq load`, `bq cp`, `bq query`, `bq mk` and `bq extract`, covers most day-to-day BigQuery operations from the terminal.
\nThey make your workflows faster, cheaper, and easy to automate.
\n
\nIf this helped, like the video, share it with a fellow data engineer, and subscribe to **Data Potion** for more GCP data engineering content. š
\n
\n\n## Connect With Me
\n
\n\n- š„ YouTube: Data Potion
\n- š¼ LinkedIn: https://www.linkedin.com/in/abhishek-batra-46053a70
\n- š Portfolio: https://abhishekbatra.me/
\n- š¬ Community: join the WhatsApp learning community linked in the video description
\n[](https://youtu.be/Ej751blU0jA)\n","id":"Unlock_Your_Data_Potential:_Top_5_Must-Know_BigQuery_Commands_for_Every_Data_Engineer"},{"title":"BigQuery Data Sharing: Mastering Authorized Views and Datasets","category":"Data Engineering","tags":["BigQuery","GCP","Google"],"created_at":"2026-09-03T11:55:12.257540Z","summary":"Control Data access using Auth Views","updated_at":"2026-09-03T11:55:12.257540Z","cover_image_url":"https://storage.googleapis.com/abatra-portfolio-assets-public/blog-img/video_thumbs/bq-notebooks-thimb.png","published":true,"read_time":8,"content":"
\n\n
\n\n*By Data Potion*\n\n
\nWhen working with Google Cloud BigQuery, balancing **data sharing** with **data security** can be a challenge. We all have security measures in place, but securing data shouldn't mean blocking users from deriving valuable insights.
\n
\nBigQuery provides a wide array of options for securing your data while keeping it accessible, such as network restrictions, row-level and column-level security, and authorized datasets. In this post, we will focus exclusively on **Authorized Views** and how they facilitate secure data sharing via SQL.
\n
\n\n## The Problem: Sharing Insights Without Exposing Source Data
\n
\nImagine a scenario where multiple users want to analyze data to gain insights. However, the source data contains sensitive informationāPersonally Identifiable Information (PII), financial records, or unmasked confidential data.
\n
\nIf these users get direct access to the source tables, they might inadvertently gain access to information they aren't supposed to see. As data engineers, our goal is to ensure people can query the data they need without ever exposing the raw source tables.
\n
\n\n## The Solution: Authorized Views
\n
\nThe most robust way to handle this in BigQuery is by creating a **View**.
\n
\n\nA view allows you to explicitly exclude sensitive columns (like PII or financial data) from the query definition. You can then distribute these views to the users.
\n
\n\nBut how does this work under the hood?
\n* An **Authorized View** is essentially a virtual table.
\n* The view itselfānot the userāis granted permission to access the source table.
\n* The user is granted the `BigQuery Data Viewer` role on the authorized dataset.
\n* The user queries the view, and the view fetches the records from the source table on their behalf.
\n\n
\nThis ensures that the end-user never interacts with the raw, potentially sensitive data.
\n
\n\n## The Vending Machine Analogy
\n\n
\nIf this sounds complex, think of an Authorized View like a **Vending Machine**:
\n
\n\n* **The Snack (Your Data):** The specific item the user wants.
\n* **The Glass & Metal Box (BigQuery Security):** The machine is locked. Nobody can just reach in and grab whatever they want.
\n* **The Inner Items (Source Tables):** The soft drinks, protein bars, and ice cream inside the machine. They are visible, but securely locked away.
\n\n* **The Keypad (Authorized View):** You input a specific request (your pin or code). The interface takes that input, processes it, and dispenses *exactly* what you asked forānothing more.
\n
\n\n## Scaling with Authorized Datasets
\n
\nCreating a single authorized view works great for small tasks. But what happens when you have a huge number of views to share across different workloads? Granting users access to each individual view would quickly become a cumbersome, complex nightmare.
\n
\n\n**The Answer: Authorized Datasets.**
\n\n
\nInstead of managing permissions on a per-view basis, you can club all relevant views into a single dataset. You then authorize the *dataset* itself. Once a user has access to the authorized dataset, they can seamlessly query any view inside it. In turn, those views are trusted to query the raw source tables. This makes managing permissions incredibly scalable and clean.
\n
\n\n## What's Next?
\n
\nAuthorized views and datasets are powerful tools for sharing data securely in any industryāwhether it's healthcare, telecommunications, or retail. Stay tuned for a future post where we will dive into a hands-on demo to see these concepts in action with an industry-specific scenario!\n
\n\n**YouTube Video**\n
\n[](https://www.youtube.com/watch?v=IpMerHSwxHM)\n","id":"BigQuery_Data_Sharing:_Mastering_Authorized_Views_and_Datasets"},{"title":"Scaling to Higher Loads: The Essentials of Distributed Data and Replication","category":"Data Engineering","summary":"Scaling is the only opton when your client wants to cater to fater requests processing with minimal lag","created_at":"2026-08-16T08:47:28.434123Z","tags":["distributed_data"," SQL"," postgres"],"updated_at":"2026-08-16T08:47:28.434123Z","cover_image_url":"https://storage.googleapis.com/abatra-portfolio-assets-public/blog-img/distributed_data.png","published":true,"read_time":8,"content":"\nAs modern applications scale to handle increasingly higher loads, the traditional approach of simply upgrading a single machineāknown as vertical scalingāquickly hits a wall of soaring costs and single points of failure[cite: 1].
\nTo build truly resilient, high-performing, and globally accessible systems, we must look toward distributed architectures and the power of shared-nothing horizontal scaling[cite: 1].
\nIn this post, we will explore the core concepts of scaling, the mechanics of keeping data replicated across multiple nodes, and the critical trade-offs involved in keeping our databases highly available and consistent[cite: 1].
\n\n
\nScaling to higher loads is a critical challenge in system design. Historically, the go-to solution was **Vertical Scaling**, also known as Scaling Up
\n\n
\n\n## Scaling Approaches \n\n
\n\n### Vertical Scaling (Shared Memory)
\n\nScaling up involves adding more RAM, CPUs, and disks to a single operating system.
\n* **Pros:** You can swap components anytime without a shutdown.
\n* **Cons:** Costs grow twice as fast, and it offers limited fault tolerance since it relies on a single geographic location.
\n\n
\n\n### Vertical Scaling (Shared Disk)
\n\nThis approach stores data in an array of disks while independent machines (with their own CPUs and RAM) connect via a fast network like NAS.
\n* **Pros:** Simplified data management, instant compute failover, independent elastic scaling, and strong ACID consistency.
\n* **Cons:** It requires complex locking mechanisms and presents a single point of failure for storage.
\n\n
\n\n### Horizontal Scaling (Shared Nothing)
\n\nHere, each node (machine or virtual machine) operates independently with its own CPU, RAM, and disk.
\n* Coordination happens at the software level over a conventional network, requiring no special hardware.
\n* It allows data distribution across multiple geographic regions, reducing latency for users and surviving entire datacenter losses.
\n\n
\n\n## Understanding Replication
\n
\n\nReplication involves keeping a copy of the same data on several different nodes connected via a network.
\nIt is useful when the dataset is small enough to fit on a single machine; otherwise, data partitioning is required.
\n\n
\n\n**Why Replicate?**
\n\n* To keep data geographically close to your users (reducing latency).
\n* To allow the system to continue working even if some of its parts have failed (increasing availability).
\n* To scale out the number of machines that can serve read queries (increasing read throughput).
\n\n
\n\nThe main difficulty in replication lies in handling changes to replicated data.
\n\n
\n\n### Leader and Follower Algorithm
\n\n
\nAlmost all distributed databases use single-leader, multi-leader, or leaderless algorithms for achieving replication.
\nIn a leader-based setup:
\n\n* One replica is designated the **leader** (primary or master). When clients want to write to the database, they must send requests to the leader, which writes new data to its local storage.
\n* Other replicas are **followers** (read replicas or slaves).
\n* The leader sending new data to local storage also sends the data change to all followers via a replication log or change stream.
\n* Followers update their local copy by applying all writes in the exact order processed by the leader database. Client read operations can be handled by either the leader or any of the followers.
\n\n
\n\n### Synchronous vs. Asynchronous Replication
\n\n
\n\n* **Synchronous:** The leader waits for the follower to confirm the write before reporting success to the user.
\n* **Asynchronous:** The leader doesn't wait for a response from the follower, and there is no guarantee of how long it might take.
\n* **Trade-offs:** Synchronous guarantees an up-to-date copy on the follower and data availability if the leader fails, but the disadvantage is that it blocks writes if a follower doesn't respond. \n\nAsynchronous doesn't block writes, but you cannot be sure data is available if the leader fails. In practice, databases usually enable synchronous replication on one follower, and keep the others asynchronous.
\n\n
\n\n## Operating a Replicated System
\n
\n\n### Setting Up New Followers
\n\n
\nSimply copying data files from one node to another isn't sufficient because clients are constantly writing to the database.
\n1. Take a consistent snapshot of the leaderās database (if possible, without taking a lock on the entire database).
\n2. Copy the snapshot to the new follower node.
\n3. The follower connects to the leader and requests all data changes since the snapshot was taken (using log sequence numbers in PostgreSQL or binlog coordinates in MySQL).
\n4. Once it has processed the backlog, it can now continue to process data changes from the leader as they happen.
\n\n
\n\n### Handling Node Outages
\n
\n\nThe goal is to achieve zero downtime.
\n* **Follower Failure (Catchup Recovery):** Easy and simple. The follower has logs for data changes on its local disk. In case of failure, it can recover from the last transaction recorded and ask the leader for data changes occurred after the last transaction.
\n* **Leader Failure (Failover):** Tricky and complex to achieve. Operations teams usually go for manual failovers over automatic ones. Automatic failover requires determining that the leader has failed, choosing a new leader, and reconfiguring the system. A lot of things may go wrong: in case of asynchronous replication, the new leader may not have received all writes; or a \"split-brain\" scenario could occur if both nodes believe they are the leader, resulting in conflicting writes. ","id":"Scaling_to_Higher_Loads:_The_Essentials_of_Distributed_Data_and_Replication<"},{"title":"Understanding OLTP Systems - Backbone of Transactional World & Robust Consistency","category":"Data Engineering","tags":["strong-consistency"," durable"," transactions"],"created_at":"2026-07-24T12:09:16.359737Z","summary":"Online Transaction Processing (OLTP) systems - designed for fast, interactive read and write database operations","updated_at":"2026-07-24T12:09:16.359737Z","cover_image_url":"https://storage.googleapis.com/abatra-portfolio-assets-public/blog-img/OLTP_Systems.png","published":true,"read_time":8,"content":"### ***History | What | Why***
\n\nIn early days, a write to the database typically corresponds to a commercial transaction taking place.Making a sale, placing an order with a supplier, paying an employeeās salary, etc.\nAs databases expanded into areas that didnāt involve money changing hands, the term *transaction* nevertheless stuck, referring to a group of reads and writes that form a logical unit.
\n\nTransaction processing just means allowing clients to make low-latency reads and writesāas opposed to *batch processing jobs*, which only run periodically (for example, once per day).\nEven though databases started being used for many kinds of dataācomments on blog posts, actions in a game, contacts in an address book, etc.āthe basic access pattern remained similar to processing business transactions.
\n\nAn application typically looks up a small number of records by some key, using an index. Records are inserted or updated based on the userās input. Because these applications are interactive, the access pattern became known as *online transaction processing (OLTP)*.
\n\nThese databases also started being increasingly used for data analytics, which has very different access patterns.
\nFor example, if your data is a table of sales transactions, then analytic queries might be:
\n* What was the total revenue of each of our stores in January?
\n* How many more devices than usual did we sell during our latest promotion?
\n* Which brand of baby food is most often purchased together with brand X diapers?
\n\nThese queries are often written by business analysts, and feed into reports that help the management of a company make better decisions (*business intelligence*).\nIn order to differentiate this pattern of using databases from transaction processing, it has been called *online analytic processing (OLAP)*.
\n\n
\n\n### ***OLTPs Row Based Storage Pattern***
\nTo Query this efficiently in OLTP systems indexes on `fact_sales.date_key` and/or `fact_sales.product_sk` that tell the storage engine where to find all the sales for a particular date or for a particular product.
\nBut then, a row-oriented storage engine still needs to load all of those rows (each consisting of over 100 attributes) from disk into memory, parse them, and filter out those that donāt meet the required conditions. That can take a long time.
\n\n```sql\nSELECT\n dim_date.weekday, dim_product.category,\n SUM(fact_sales.quantity) AS quantity_sold\nFROM fact_sales\nJOIN dim_date ON fact_sales.date_key = dim_date.date_key\nJOIN dim_product ON fact_sales.product_sk = dim_product.product_sk\nWHERE\n dim_date.year = 2013 AND\n dim_product.category IN ('Fresh fruit', 'Candy')\nGROUP BY\n dim_date.weekday, dim_product.category;\n```\n\n### ***Why ACID***
\n\nIn the harsh reality of data systems, many things can go wrong:
\n\n* The database software or hardware may fail at any time (including in the middle of a write operation).
\n* The application may crash at any time (including halfway through a series of operations).
\n* Interruptions in the network can unexpectedly cut off the application from the database, or one database node from another.
\n* Several clients may write to the database at the same time, overwriting each otherās changes.
\n* A client may read data that doesnāt make sense because it has only partially been updated.
\n* Race conditions between clients can cause surprising bugs.
\n\nTo be reliable, a system must deal with these faults and ensure that they donāt cause catastrophic failure of the entire system. However, implementing fault tolerance mechanisms is a lot of work.It requires a lot of careful thinking about all the things that can go wrong, and a lot of testing to ensure that the solution actually works.
\n\n### ***Transactions***
\n\nConceptually, all the reads and writes in a transaction are executed as one operation: either the entire transaction succeeds (*commit*) or it fails (*abort, rollback*). If it fails, the application can safely retry. With transactions, error handling becomes much simpler for an application.
\nBecause it doesnāt need to worry about partial failureāi.e., the case where some operations succeed and some fail (for whatever reason).
\n\n### ***Control and Locking Mechanisms***
\n\nConcurrency control is the mechanism used to ensure that multiple users or applications can interact with the database simultaneously without corrupting the data. Without concurrency control, simultaneous transactions can cause severe data anomalies, such as:
\n* *Dirty Reads:* Reading uncommitted data from another transaction that later rolls back.
\n* *Non-Repeatable Reads:* Reading the same row twice in a transaction and getting different values because another transaction updated it in between.
\n* *Phantom Reads:* Executing a query twice and seeing new \"phantom\" rows appear the second time because another transaction inserted them.
\n\nTo prevent this, relational databases primarily use *Locking Mechanisms* to serialize access to data.
\n\n### ***Lock Granularity***
\n\nDatabases don't just lock rows; they can lock data at various structural levels depending on the scope of the query.
\n* *Row-Level Locking:* Locks a single record. Excellent for concurrency (many users can edit different rows in the same table) but requires higher overhead for the database engine to manage thousands of tiny locks.
\n* *Page-Level Locking:* Locks a block of memory (usually 4KB or 8KB) that contains multiple rows. It's a middle ground between row and table locks.
\n* *Table-Level Locking:* Locks the entire table. Very low overhead, but terrible for concurrencyāif a bulk update is running, no one else can read or write to that table.
\n* *Database-Level Locking:* Locks the entire database, typically only used during major structural upgrades or restores.
\n\n### ***Lock Type***
\n\n| Lock Type | Also Known As | Behaviour |\n| :--- | :--- | :--- |\n| *Shared Lock (S)* | Read Lock | Allows a transaction to read a resource. Multiple transactions can hold S-locks on the same data simultaneously. |\n| *Exclusive Lock (X)* | Write Lock | Allows a transaction to update or delete a resource. Only one transaction can hold an X-lock on data at a time. |\n
\n\n### ***ACID - Atomicity***
\n\n*Atomic* - In general, atomic refers to something that cannot be broken down into smaller parts.
\nThe system can only be in the state it was before the operation or after the operation, not something in between.
\nNote - atomicity is not about concurrency. It does not describe what happens if several processes try to access the same data at the same time, because that is covered under the letter I, for isolation.
\n\nAtomicity describes what happens if a client wants to make several writes, but a fault occurs after some of the writes have been processed.
\nSo, if the writes are grouped together into an atomic transaction, and the transaction cannot be completed (committed) due to a fault, then the transaction is aborted and the database must discard or undo any writes it has made so far in that transaction.
\n\nWithout atomicity, if an error occurs partway through making multiple changes, itās difficult to know which changes have taken effect and which havenāt.
\nAtomicity simplifies this problem: if a transaction was aborted, the application can be sure that it didnāt change anything, so it can safely be retried.
\n\n### ***ACID - Consistency***
\n\nThe idea of ACID consistency is that you have certain statements about your data (invariants) that must always be true.
\nFor e.g., in an accounting system, credits and debits across all accounts must always be balanced.
\nHowever, this idea of consistency depends on the applicationās notion of invariants, and itās the applicationās responsibility to define its transactions correctly so that they preserve consistency.
\n\nThis is not something that the database can guarantee: if you write bad data that violates your invariants, the database canāt stop you.
\nIn general, the application defines what data is valid or invalidāthe database only stores it.
\nNOTE - The application may rely on the databaseās atomicity and isolation properties to achieve consistency.
\n\n### ***ACID - Isolation***
\nIsolation in the sense of ACID means that concurrently executing transactions are isolated from each other: they cannot step on each otherās toes.
\nThe classic database textbooks formalize isolation as *serializability*, which means that each transaction can pretend that it is the only transaction running on the entire database.
\nThe database ensures that when the transactions have committed, the result is the same as if they had run serially (one after another), even though they may have run concurrently.
\nTypes of Isolation levels ā *Read Uncommitted, Read Committed, Repeatable Reads, Serializable & Snapshot*
\n\n### ***ACID - Durability***
\nDurability is the promise that once a transaction has committed successfully, any data it has written will not be forgotten, even if there is a hardware fault or the database crashes.
\nIn a replicated database, durability may mean that the data has been successfully copied to some number of nodes.
\nIn order to provide a durability guarantee, a database must wait until these writes or replications are complete before reporting a transaction as successfully committed.
\nPerfect durability does not exist: if all your hard disks and all your backups are destroyed at the same time, thereās obviously nothing your database can do to save you.
\nIn practice, there is no one technique that can provide absolute guarantees. There are only various risk-reduction techniques, including writing to disk, replicating to remote machines, and backupsāand they can and should be used together.
\n\n### ***Normalization | What | Why | Types***
\nDatabase normalization is a systematic framework for organizing data in a relational database. Its primary goal is to reduce data redundancy and improve data integrity.
\nWhile modern data warehouses and analytics enginesālike BigQuery/RedShiftāoften favour heavily denormalized schemas (like flat tables or nested arrays) to speed up read-heavy analytical queries, the upstream transactional systems (OLTP) feeding those pipelines rely on strict normalization to maintain accuracy and prevent data corruption.
\n*WHY ? To Prevent Anomalies* ā If a database is not normalized, redundant data causes structural problems known as anomalies whenever data is modified.
\nFor e.g., Update Anomaly, Insert Anomaly, Deletion Anomaly.
\n\n**Example Unnormalized Data:**
\n| EmpID | EmpName | DeptName | DeptHead | ProjectID | ProjectRole |\n| :--- | :--- | :--- | :--- | :--- | :--- |\n| 101 | Rahul | Data | Anita | P1 | Engineer |\n| 101 | Rahul | Data | Anita | P2 | Lead |\n| 102 | Sneha | Data | Anita | P1 | Engineer |\n
\n\n* *Update Anomaly* - If Anita leaves and is replaced as the Data department head, you must update multiple rows. If one row is missed, the database becomes inconsistent (Rahul and Sneha would appear to have different department heads).
\n* *Insertion Anomaly*: You cannot add a new department (e.g., \"Security\" led by \"Vikram\") until you hire an employee to put in that department, because EmpID is required for a new row.
\n* *Deletion Anomaly*: If Rahul and Sneha both finish their projects and are temporarily unassigned, deleting their rows entirely wipes out the fact that the \"Data\" department exists and is led by \"Anita\".
\n\n## ***Normal Forms***
\n* Normalization solves these anomalies by decomposing large tables into smaller, tightly focused tables linked by relationships.
\n* Normal forms are progressive rules. To achieve a higher normal form, a database must first satisfy the rules of the lower ones.
\nTypes: *1NF, 2NF, 3NF*
\n\n## ***1 NF ā Unique Record & Atomic Values***
\n*The Rule:* Every column must contain atomic (indivisible) values, and each record must be unique. No repeating groups or arrays in a single column.
\n*1NF Solution:* Split the multi-valued attribute into separate rows (or a separate table, depending on the broader schema design).
\n\n**Before 1NF:**\n| EmpID | Name | Phone_Numbers |\n| :--- | :--- | :--- |\n| 101 | Rahul | 555-0101, 555-0102 |\n
\n\n**After 1NF:**\n| EmpID | Name | Phone_Number |\n| :--- | :--- | :--- |\n| 101 | Rahul | 555-0101 |\n| 101 | Rahul | 555-0102 |\n
\n\n## ***2 NF ā No Partial Dependencies***
\n*The Rule:* Must be in 1NF, and all non-key attributes must be fully functionally dependent on the entire primary key. (This only applies when a table has a composite primary key).
\nConsider a table with a composite primary key of (EmpID, ProjectID):
\n* Hours depends on both the Employee and the Project.
\n* EmpName only depends on EmpID (a partial dependency).
\n* ProjectName only depends on ProjectID (a partial dependency).
\n\n*2NF Solution:* Break the partial dependencies into their own tables.
\n\n
\n\n**Table 1: Employee**\n| EmpID (PK) | EmpName |\n| :--- | :--- |\n| 101 | Rahul |\n\n**Table 2: Project**\n| ProjectID (PK) | ProjectName |\n| :--- | :--- |\n| P1 | Migration |\n| P2 | API Setup |\n\n**Table 3: Employee_Project**\n| EmpID (PK) | ProjectID (PK) | Hours |\n| :--- | :--- | :--- |\n| 101 | P1 | 40 |\n| 101 | P2 | 20 |\n\n
\n\n## ***3 NF ā No Transitive Dependencies***
\n*The Rule:* Must be in 2NF, and there must be no transitive dependencies. This means non-key attributes must depend only on the primary key, and not on other non-key attributes.
\nHere, DeptLocation depends on DeptID, and DeptID depends on EmpID. Because DeptLocation relies on the primary key through another column, it's a transitive dependency. This causes the update anomaly mentioned earlier.
\n\n*3NF Solution:* Move the transitively dependent data into its own lookup table.
\n\n**Table 1: Employee**\n| EmpID (PK) | EmpName | DeptID (FK) |\n\n| 101 | Rahul | D1 |\n| 102 | Sneha | D1 |\n\n**Table 2: Department**\n| DeptID (PK) | DeptLocation |\n| :--- | :--- |\n| D1 | Pune |\n
\n\n## think. believe. grow","id":"Understanding_OLTP_Systems_-_Backbone_of_Transactional_World_&_Robust_Consistency"},{"title":"Building for the Future: The Three Pillars of Data-Intensive Applications","category":"data engineering","summary":"Reliability, Scalability, and Maintainability","created_at":"2026-06-19T12:34:52.717000Z","updated_at":"2026-06-19T12:34:52.717000Z","tags":["distributed computing","data intensinve","performance"],"cover_image_url":"https://storage.googleapis.com/abatra-portfolio-assets-public/blog-img/3_pillars.png","published":true,"read_time":8,"content":"As engineering leaders and architects, we are constantly challenged to build **unstoppable**, high-performance systems. Today's data-driven landscape demands robust architecture that doesn't just survive, but actively thrives under immense pressure. Let's strip away the industry buzzwords to focus on the absolute essentials: **Reliability, Scalability, and Maintainability**. Let's decode these three pillars and explore how they define the systems we engineer.
\n\n
\n\n### š„ ***Reliability: Expect the Unexpected***
\n\nWe often think of reliability in terms of uptime, but Kleppmann defines it more practically: *continuing to work correctly, even when things go wrong*. A truly resilient system anticipates faults so they don't cascade into total failures. \n\n* **Hardware Faults:** Disks crash and networks drop. We mitigate these highly probable events through hardware redundancy and software fault-tolerance.
\n* **Software Errors:** These are insidious because they are often correlated across nodes (e.g., a leap-second bug). Preventing them requires process isolation, measuring runtime behavior, and deliberately inducing faults (think Chaos Engineering) to exercise your recovery paths.
\n* **Human Errors:** We are the weakest link. The best defense is decoupling the places where people make mistakes from the places where they can cause outagesāpairing safe sandbox environments with rapid rollback mechanisms.
\n\n### š ***Scalability: Beyond \"Just Add More Servers\"***
\n\nScalability isn't a one-dimensional label; it's a dynamic capability to cope with increased load. But before you can scale, you must precisely *describe* your load.
\n\n* **Describing Load:** Consider Twitterās famous fan-out challenge. Handling 12,000 tweet writes per second is relatively trivial, but delivering a celebrity's tweet to 30 million followers in under 5 seconds is an entirely different architectural beast.
\n* **Describing Performance:** Forget averages; they are deceptive. As senior architects, we must obsess over **percentiles (p95, p99, p999)**. Queueing delays (head-of-line blocking) heavily influence these tail latencies, directly impacting your most valuable users.
\n* **Coping with Load:** The eternal debate of scaling up (vertical) versus scaling out (horizontal/shared-nothing). Pragmatic architectures often blend both, built entirely around the assumptions of your specific load parameters. There is no magic scaling sauce.
\n\n### š ļø ***Maintainability: Avoiding the \"Big Ball of Mud\"***
\n\nIt is a well-known industry secret that the majority of software cost is heavily weighted toward ongoing maintenance, not initial development.
\n\n* **Operability:** Make life easy for your operations team. Comprehensive telemetry, good default behaviors, and predictable runtime states are non-negotiable for modern data systems.
\n* **Simplicity:** Small systems are delightful; large systems easily become a \"big ball of mud.\" Your goal is to remove *accidental complexity* by creating clean abstractions that hide implementation details.
\n* **Evolvability:** Agile is an architectural necessity, not just a management process. Your data structures and system boundaries must be plastic enough to adapt to unforeseen use cases.
\n\n### š” ***The Architect's Takeaway***
\n\nAs Principal Engineers, it is incredibly tempting to over-engineer for hypothetical scale. However, premature optimization often locks you into inflexible designs. Most of a system's lifecycle is spent in maintenanceāprioritizing simplicity and robust operability from day one usually pays the highest dividends. You don't have to be Google or Amazon to build brilliant software; you just need to deeply understand the trade-offs of the tools in your hands.
","id":"DYSuqlTV2AT8ZFaeLqp7"},{"title":"Orchestrating ML Workflows with Airflow + MLflow","category":"mlops","tags":["airflow","mlflow","mlops"],"created_at":"2026-05-28T12:34:52.717401Z","summary":"End-to-end ML pipeline orchestration combining Airflow DAGs with MLflow tracking.","updated_at":"2026-05-28T12:34:52.717401Z","cover_image_url":"","published":true,"read_time":6,"content":"# Orchestrating ML Workflows\n\nContent goes here...","id":"rqUew3CJgjHKY9NNTTLz"},{"title":"Orchestrating Complex Microservices Environments","category":"Data Engineering","updated_at":"2026-05-28T12:34:52.717401Z","created_at":"2026-05-28T12:34:52.717401Z","tags":["llm","fine-tuning","pytorch"],"cover_image_url":"","summary":"Connect numerous services and agents, enabling them to communicate asynchronously and reliably, even across different event formats and schemas.","published":true,"read_time":11,"content":"# ***Eventarc Advanced: Orchestrating Complex Microservices Environments***\r\n\r\n\r\n---\r\n
\r\n\r\n## Overview\r\n\r\nModern distributed applications need more than simple event routing.\r\n\r\nOrganizations increasingly require:\r\n\r\n* Centralized governance\r\n* Event filtering\r\n* Event transformation\r\n* Security controls\r\n* Observability\r\n* Multi-service orchestration\r\n\r\nTo address these needs, Google introduced **Eventarc Advanced**, a serverless eventing platform that extends Eventarc beyond basic event routing.\r\n\r\n---\r\n
\r\n\r\n## Why Eventarc Advanced?\r\n\r\nTraditional event systems often become difficult to manage as:\r\n\r\n* Services multiply\r\n* Teams grow\r\n* Event sources diversify\r\n* Governance requirements increase\r\n\r\nEventarc Advanced provides a centralized eventing layer capable of:\r\n\r\n* Ingesting events\r\n* Filtering events\r\n* Transforming payloads\r\n* Routing messages\r\n* Managing security policies\r\n* Monitoring event flow\r\n\r\nAll from a unified platform.\r\n\r\n---\r\n
\r\n\r\n## Key Capabilities\r\n\r\n### 1. Publish API\r\n\r\nAllows:\r\n\r\n* Custom applications\r\n* External systems\r\n* Third-party services\r\n\r\nto publish events using the CloudEvents format.\r\n\r\n---\r\n
\r\n\r\n### 2. Central Message Bus\r\n\r\nActs as the backbone of the event-driven architecture.\r\n\r\nBenefits:\r\n\r\n* Centralized management\r\n* Security enforcement\r\n* Observability\r\n* Flexible routing\r\n\r\nThe message bus is built on technologies such as Envoy and leverages Google Cloud networking and policy capabilities.\r\n\r\n---\r\n
\r\n\r\n### 3. Event Mediation\r\n\r\nSupports:\r\n\r\n* Real-time filtering\r\n* Event transformation\r\n* Attribute modifications\r\n* Format conversions\r\n\r\nSupported payload formats include:\r\n\r\n* JSON\r\n* Avro\r\n* Protobuf\r\n\r\nThis enables communication between systems that use different schemas or message formats.\r\n\r\n---\r\n
\r\n\r\n### 4. Reliable Delivery\r\n\r\nProvides mechanisms for:\r\n\r\n* Error handling\r\n* Retry behavior\r\n* Recovery from transient failures\r\n\r\nto improve system resilience.\r\n\r\n---\r\n
\r\n\r\n## Developer Benefits\r\n\r\nEventarc Advanced simplifies event-driven development by:\r\n\r\n* Reducing custom routing logic\r\n* Providing a unified API experience\r\n* Supporting scalable and decoupled services\r\n* Enabling event transformation without modifying applications\r\n\r\nDevelopers can focus more on business logic and less on event infrastructure.\r\n\r\n---\r\n
\r\n\r\n## Operations Benefits\r\n\r\nPlatform teams gain:\r\n\r\n* Central governance\r\n* Security controls\r\n* Monitoring\r\n* Logging\r\n* Easier troubleshooting\r\n\r\nThis reduces operational complexity across projects and teams.\r\n\r\n---\r\n
\r\n\r\n## Example: Order Processing System\r\n\r\nImagine an e-commerce platform.\r\n\r\n### Event Sources\r\n\r\n* Order Created\r\n* Payment Confirmed\r\n* Shipment Updated\r\n\r\nAll events are published to a central message bus.\r\n\r\n---\r\n
\r\n\r\n### Flow\r\n\r\n```text\r\nOrder Service\r\n |\r\n v\r\n+---\r\n
---\r\n
---\r\n
---\r\n
---\r\n
-+\r\n| Message Bus |\r\n+---\r\n
---\r\n
---\r\n
---\r\n
---\r\n
-+\r\n |\r\n +---\r\n
---\r\n
---\r\n
---\r\n
---\r\n
-+\r\n | |\r\n v v\r\nNotification Fraud Detection\r\nService Service\r\n```\r\n\r\n---\r\n
\r\n\r\n### Example Routing Rules\r\n\r\n#### New Orders\r\n\r\nCondition:\r\n\r\n```text\r\nstatus = \"new\"\r\n```\r\n\r\nAction:\r\n\r\n```text\r\nSend confirmation email\r\n```\r\n\r\n---\r\n
\r\n\r\n#### High-Value Orders\r\n\r\nCondition:\r\n\r\n```text\r\namount > 1000\r\n```\r\n\r\nAction:\r\n\r\n```text\r\nRoute to fraud detection\r\n```\r\n\r\nEvent transformations can be applied before delivery.\r\n\r\n---\r\n
\r\n\r\n## Architecture Diagram (Mermaid)\r\n\r\n```mermaid\r\ngraph TD\r\n\r\nA[Order Service]\r\nB[Payment Service]\r\nC[Shipping Service]\r\n\r\nA --> D[Message Bus]\r\nB --> D\r\nC --> D\r\n\r\nD --> E[Notification Service]\r\nD --> F[Fraud Detection]\r\nD --> G[Analytics Platform]\r\n\r\nD --> H[Cloud Run]\r\nD --> I[Cloud Functions]\r\nD --> J[External Systems]\r\n```\r\n\r\n---\r\n
\r\n\r\n## Use Cases\r\n\r\n### Large-Scale Application Integration\r\n\r\nConnect many applications and services using asynchronous communication.\r\n\r\n### AI and Analytics Pipelines\r\n\r\nFilter and transform incoming data before processing.\r\n\r\n### Hybrid and Multi-Cloud\r\n\r\nExtend event-driven workflows beyond Google Cloud into:\r\n\r\n* On-premises systems\r\n* Other cloud providers\r\n\r\n---\r\n
\r\n\r\n## Looking Ahead\r\n\r\nGoogle highlights future integration opportunities involving:\r\n\r\n* Service Extensions\r\n* Agentic applications\r\n* Security controls\r\n* AI-related services such as Model Armor\r\n\r\nThe goal is to make Eventarc Advanced a central orchestration layer for increasingly distributed and intelligent applications.\r\n\r\n---\r\n
\r\n\r\n## Key Takeaway\r\n\r\nEventarc Advanced is not simply an event router.\r\n\r\nIt introduces a centralized eventing platform that combines:\r\n\r\n* Event ingestion\r\n* Governance\r\n* Security\r\n* Filtering\r\n* Transformation\r\n* Routing\r\n* Observability\r\n\r\nmaking it easier to build and operate large-scale event-driven systems.\r\n","id":"eHdWYceKq1R63C2GqMoj"}]