From Raw Data to Rich Insights: Building a Serverless ELT Pipeline with Supabase, Google Cloud, and Terraform In today’s data-driven world, the ability to derive insights from operational data is not just an advantage; it’s a necessity. However, running complex analytical queries directly on your production database is a recipe for disaster, leading to performance degradation and potential downtime. The solution is to create a dedicated analytics environment—a data warehouse.
This post walks through the end-to-end process of building a professional-grade, automated data pipeline. We’ll extract data from a Supabase (PostgreSQL) production database, load it into Google BigQuery, and transform it into analytics-ready models. The entire process is orchestrated by a serverless Python application and defined declaratively using Terraform, ensuring a robust, scalable, and maintainable solution.
The Architecture: A Modern Data Stack At its core, our goal is to separate the system that runs the business (the operational database) from the system that analyzes the business (the data warehouse). We achieve this using the ELT (Extract, Load, Transform) methodology.
Extract: A serverless Python function pulls raw data (orders, products, customers) from our Supabase database.
Load: The raw, unchanged data is loaded directly into a staging area in Google BigQuery. This approach leverages BigQuery’s immense storage and processing power.
Transform: Once the data is in the warehouse, we run SQL queries to clean, join, and aggregate it into clean, performant models ready for analysis.
This entire process is automated, serverless, and built for scale.
The Foundation: Infrastructure as Code with Terraform Manually clicking through a cloud provider’s UI to set up infrastructure is slow, error-prone, and impossible to version control. We use Terraform to define our entire cloud architecture in code. This gives us repeatability, automation, and a single source of truth for our setup.
Our infrastructure is defined across several logical files, including iam.tf for security and scheduler.tf to create the cron job that triggers our pipeline automatically. This declarative approach means we can spin up or tear down our entire pipeline with a single command.
[A screenshot of the terraform/iam.tf file, highlighting the resource block for granting the Cloud Function access to Secret Manager.]
The Core Logic: A Modular Python Pipeline The heart of the operation is a Python application designed for clarity and maintainability. Instead of a single, monolithic script, the logic is broken into distinct modules: extract.py, load.py, and transform.py.
This modularity makes the pipeline easy to test, debug, and extend. But it also introduces common development challenges that provide valuable lessons.
Learning Moment #1: Mastering Python Imports and Dependencies A ModuleNotFoundError is a rite of passage for any developer moving an application to a serverless environment. We encountered two key issues:
Local vs. Cloud Paths: Our code was structured in a /src directory. Running python src/main.py locally failed because Python couldn’t resolve imports like import config. The solution was to run the application as a module from the project root (python -m src.main), which correctly adds the directory to Python’s path.
The requirements.txt File: Our Cloud Function initially failed with ModuleNotFoundError: No module named ‘sqlalchemy’. The cause was twofold: first, a subtle typo in a package name (google-cloud-secret-manager instead of google-cloud-secretmanager) caused the entire dependency installation to fail. Second, we learned that the requirements.txt file must be placed inside the source directory (/src) to be included in the deployment package.
These experiences underscore a critical lesson: a robust local testing strategy and a meticulously maintained requirements.txt file are non-negotiable.
[A screenshot of the src/load.py file, highlighting the _convert_unsupported_types function that handles UUID and JSON conversion.]
Learning Moment #2: Adopting Existing Resources with Terraform What happens when your infrastructure doesn’t perfectly match your code? In our development process, we manually created the BigQuery dataset before running Terraform. When we tried to apply our configuration, Terraform failed, complaining that the dataset already existed.
This is a common scenario, and destroying the existing resource isn’t always an option. The solution is the powerful terraform import command.
By running the following command, we instructed Terraform to “adopt” the existing, manually-created dataset and bring it under its management:
terraform import google_bigquery_dataset.analytics_dataset projects/your-project-id/datasets/pos_analytics
This command doesn’t create or destroy anything; it simply updates Terraform’s internal state file, aligning the code with reality. It’s an indispensable tool for managing real-world, evolving infrastructure.
[A screenshot of a terminal window showing the successful output of the ‘terraform import’ command.]
The Final Result: Automated Insight With our Terraform configuration applied and our code hardened by real-world debugging, we have a fully automated, production-ready pipeline. Every Monday at 2 AM AEST, Cloud Scheduler triggers our Cloud Function. The function extracts the latest data from Supabase, loads it into BigQuery, and rebuilds our analytical models.
When the team comes online Monday morning, their dashboards in tools like Looker Studio or Tableau are already populated with fresh, reliable data, ready to inform the next business decision. By embracing automation, serverless computing, and Infrastructure as Code—and learning from the inevitable bumps along the way—we’ve built a solution that is not only powerful but also cost-effective and easy to maintain.
Helpful Resources Terraform: Google Cloud Provider Documentation https://registry.terraform.io/providers/hashicorp/google/latest/docs
Google Cloud: Cloud Functions Python Quickstart | BigQuery Documentation https://www.google.com/search?q=https://cloud.google.com/functions/docs/quickstart-python | https://cloud.google.com/bigquery/docs Supabase: Connecting to your Database | Connection Pooling https://supabase.com/docs/guides/database/connecting-to-postgres | https://www.google.com/search?q=https://supabase.com/docs/guides/database/connecting-to-postgres%23connection-pooler Python: SQLAlchemy 2.0 Documentation | https://docs.sqlalchemy.org/en/20/