Most businesses are sitting on a goldmine of data that they're not using to make decisions. Sales data lives in the CRM. Operational data lives in the SaaS platform. Financial data lives in accounting software. Marketing data lives in Google Analytics. Each dataset, in isolation, tells a partial story. The job of data engineering is to bring all of that data together, clean and transform it, and surface it in dashboards that enable faster, more confident decisions.
Google Cloud BigQuery is my primary data warehouse for cloud-scale data engineering projects. Its serverless architecture, SQL-native query engine, and seamless integration with the Google Cloud ecosystem make it the right choice for analytics workloads ranging from small SaaS platforms to enterprise data pipelines.
BigQuery Architecture for SaaS Data
BigQuery is a serverless, columnar data warehouse that scales automatically. Unlike traditional databases, you don't provision servers or configure query engines — you write SQL, and BigQuery figures out the compute needed to run it efficiently. For analytics workloads (full table scans, aggregations, joins across millions of rows), BigQuery consistently outperforms row-oriented databases like PostgreSQL.
The standard BigQuery architecture for a SaaS analytics platform organizes data into three layers:
-- Layer 1: Raw (source-aligned, append-only, never modified)
dataset: `mavumium_raw`
tables: quotations_raw, organizations_raw, scan_events_raw
-- Layer 2: Staging (cleaned, typed, deduplicated)
dataset: `mavumium_staging`
tables: stg_quotations, stg_organizations, stg_scan_events
-- Layer 3: Warehouse (business-model tables, joined, aggregated)
dataset: `mavumium_warehouse`
tables: dim_organizations, dim_users, fct_quotations, fct_inventory
-- Layer 4: Analytics (report-ready views, metric tables)
dataset: `mavumium_analytics`
tables: daily_revenue, user_cohorts, quotation_funnel, inventory_healthThis layered architecture means that raw data is always preserved and reprocessable. If a business logic rule changes, you reprocess from the raw layer without losing historical data. Each layer has clear ownership and clear transformations — eliminating the "where did this number come from?" confusion that plagues unstructured data pipelines.
Data Modeling for Analytics
Analytics data modeling differs fundamentally from transactional database modeling. While operational databases are optimized for writing individual rows quickly (OLTP), analytics databases are optimized for reading large ranges of columns across many rows (OLAP). The right data model for analytics is dimensional modeling — organizing data into fact tables (what happened) and dimension tables (who/what/where/when).
For the Mavumium analytics layer, the core fact and dimension tables:
- fct_quotations — One row per quotation, with foreign keys to dimension tables and measures (total value, item count, acceptance probability)
- fct_scan_events — One row per inventory scan, with product details, quantity changes, and timing
- dim_organizations — Organization attributes: industry, plan tier, location, signup date, health score
- dim_products — Product catalog dimension for the Scan platform's inventory analytics
- dim_date — Calendar dimension enabling time-based analysis (fiscal year, quarters, weekdays vs weekends)
Modeling principle: Design your analytics data model from the business questions outward. Start with the decisions that executives need to make, identify the metrics that inform those decisions, and model the data to make those metrics easy to calculate. Don't model the data first and hope useful questions emerge.
Data Ingestion Patterns
Getting data from operational systems into BigQuery requires choosing the right ingestion pattern for each data source. The three patterns I use most frequently:
1. Batch loading from Cloud Storage: The operational database exports data to CSV or Parquet files in Google Cloud Storage on a schedule, and BigQuery loads those files into the raw layer. This pattern works for data that doesn't need real-time freshness — daily or hourly batch loads are appropriate for most reporting use cases.
2. Streaming inserts via Pub/Sub: Application events are published to a Pub/Sub topic, a Cloud Function consumes the topic and inserts rows into BigQuery in near-real-time. Used for Mavumium's scan event tracking where inventory levels need to be visible in dashboards within minutes of a scan.
3. BigQuery Data Transfer Service: For Google-native data sources (Google Analytics, Google Ads, Campaign Manager), the Data Transfer Service handles the ingestion automatically — no custom code required.
SQL Transformations & dbt
Raw data from operational systems needs cleaning, typing, deduplication, and business logic applied before it's useful for analytics. SQL is the right tool for these transformations, and dbt (data build tool) is the framework that makes SQL transformations maintainable, testable, and version-controlled.
-- dbt model: stg_quotations.sql (staging layer)
with source as (
select * from {{ source('raw', 'quotations_raw') }}
),
cleaned as (
select
id,
organization_id,
created_by as user_id,
client_name,
-- Normalize status values
lower(trim(status)) as status,
-- Cast and validate monetary values
cast(total as numeric) as total_amount,
cast(created_at as timestamp) as created_at,
-- Deduplicate: keep latest version of each quotation
row_number() over (
partition by id
order by _ingested_at desc
) as row_num
from source
where total > 0 -- Filter out test records
)
select * from cleaned
where row_num = 1dbt's testing framework lets you define data quality tests that run after every transformation: uniqueness tests ensure no duplicate IDs, not-null tests catch missing required fields, accepted-values tests enforce valid statuses, and referential integrity tests ensure foreign keys point to existing dimension records.
Partitioning & Clustering for Cost Control
BigQuery charges based on the bytes scanned by each query. Without partitioning, a query that asks "show me revenue for this month" might scan years of historical data unnecessarily. Partitioned tables limit scans to only the relevant time period, dramatically reducing both query cost and latency.
-- Create a partitioned, clustered fact table
CREATE TABLE `mavumium_warehouse.fct_quotations`
PARTITION BY DATE(created_at) -- Partition by day
CLUSTER BY organization_id, status -- Cluster for common filter patterns
OPTIONS (
partition_expiration_days = NULL, -- Keep all partitions
require_partition_filter = TRUE -- Force date filters on all queries
)
AS SELECT * FROM `mavumium_staging.stg_quotations`;Combining date partitioning with clustering by the most common filter columns (organization_id, status) reduces query costs by 80-95% for typical dashboard queries, which always filter by date range and often by organization.
Connecting Power BI to BigQuery
Power BI's native BigQuery connector enables live query mode and import mode connections. For executive dashboards at Mavumium, I use import mode for report-ready aggregate tables (refreshed hourly via scheduled refresh) and live query mode for operational dashboards where data freshness is critical.
The most important Power BI/BigQuery configuration for performance: create dedicated BigQuery views that pre-aggregate data for each dashboard use case. Power BI's query generation is not always optimal for BigQuery's columnar storage — pre-aggregated views ensure every dashboard query runs in under 2 seconds.
Apache Superset on BigQuery
Apache Superset is the open-source BI tool I use for internal technical dashboards and for dashboards embedded directly into the Mavumium SaaS platforms. Its SQL-native approach gives more control than Power BI and its embedding capabilities make it the right choice for customer-facing analytics features.
Superset connects to BigQuery via SQLAlchemy and supports the full range of BigQuery SQL syntax including analytical functions, STRUCT/ARRAY types, and ML functions. For Mavumium's farming platform, Superset provides crop performance dashboards that farmers can view directly in the application — powered by BigQuery queries running against their operational data.
Designing KPI Dashboards That Drive Decisions
The most common failure mode in business intelligence projects is building dashboards that display data but don't drive decisions. A dashboard full of interesting charts that no one acts on is wasted engineering effort. The KPI dashboards I design for Mavumium are structured around specific decisions and actions:
- Revenue dashboard: Answers "Are we on track for this month's revenue target, and if not, which sales reps or product lines need attention?"
- Quotation funnel: Answers "Where in the quotation process are we losing deals, and what's the win rate by client type and service?"
- Inventory health: Answers "Which products are at risk of stockout, and what's the reorder priority list for this week?"
- Customer growth: Answers "Which acquisition channels are producing the highest-value customers, and where should we invest marketing budget?"
Every KPI dashboard has a primary metric (the number that matters most), supporting metrics that explain the primary metric, and action-oriented alerts that tell someone what to do when a metric goes out of range. Data without action is just noise.
Google Cloud BigQuery provides the infrastructure foundation for the kind of analytics that transforms how organizations make decisions. Combined with Power BI and Apache Superset for visualization, and proper data modeling discipline in the transformation layer, BigQuery enables SaaS platforms to deliver the kind of business intelligence that was previously only available to large enterprises with dedicated data teams.