Scaling Analytics Data Platform with dbt and BigQuery

dbt has become a key component of modern analytics engineering, enabling data teams to apply software engineering principles to data transformation workflows. In this article, we share how dbt was adopted within the Analytics Data Platform and the impact it had.

The challenge of scale

The Analytics Data Platform ingests large volumes of IoT device telemetry, shadow and firmware updates, mobile application and user activity data from multiple sources. As the project revolves around mobile app and IoT connectivity, much of the Platform's workload is driven by data generated by mobile devices and connected home appliances. In the early stages of the Data Platform, data transformations were relatively straightforward. A limited number of datasets, stakeholders and reporting requirements meant that traditional SQL development was sufficient for delivering insights quickly. As the platform matured, however, both the volume and complexity of the data increased significantly. New source systems were onboarded and more business teams began relying on analytical datasets for reporting and decision making.

Logic duplication, similar calculations, filtering rules and KPI definitions began appearing in multiple queries across different parts of the platform. This increased development effort and introduced the risk of inconsistencies, where different reports could return different results for what was intended to be the same metric. Also, dependencies became increasingly difficult to understand. When transformations are developed as independent SQL scripts, the relationship between datasets is not always obvious. Onboarding new engineers became another challenge. New contributors needed to understand not only the SQL logic itself but also the sequence in which transformations were executed and the assumptions embedded within them. Much of this knowledge existed only in the experience of existing team members rather than in documentation or tooling.

As data volumes grew, scalability and cost efficiency became equally important considerations. Scalability was primarily required because of the ever-growing IoT telemetry datasets, where millions of records are generated by connected appliances and continuously flow into the Analytics Data Platform. In BigQuery, querying these large IoT datasets directly translates into increased processing costs. To address this, we adopted incremental processing patterns as part of our dbt implementation. This approach significantly reduces the amount of data scanned during each run and shortens execution times for large telemetry workloads.

Why dbt became necessary

dbt introduced software engineering practices into analytics development and changed the way we approached data transformations. Instead of managing large, standalone SQL scripts, transformation logic was organized into standardized models with clearly defined responsibilities. This made the project easier to navigate, provided lineage, reduced logic duplication and encouraged reuse across different business domains.

Version control became a core part of the development process. Changes could be reviewed through pull requests, tested before deployment and tracked over time, bringing the same governance practices commonly used in software engineering into the analytics space. Another major benefit was the ability to treat data quality as part of the development lifecycle rather than a downstream concern. dbt tests allowed us to validate key assumptions such as uniqueness, referential integrity and mandatory fields directly within the transformation pipeline. Issues could be identified during execution instead of being discovered days later in dashboards or reports.

Automatic lineage provided visibility that had previously been difficult to achieve. Engineers could quickly understand how datasets were connected, identify upstream dependencies and assess the impact of proposed changes. As the Analytics Data Platform continues to grow, this transparency becomes increasingly valuable.

How dbt changed Analytics Data Platform

Layered architecture & reusable models

A layered architecture consisting of source (Bronze Layer), staging, intermediate (Silver Layer) and business-facing (Gold Layer) models significantly improved consistency across the Analytics Data Platform. While the concept itself is not unique to dbt, dbt provided the tooling and structure needed to enforce this approach at scale.

At the source layer, raw data from IoT appliances, telemetry, mobile applications and backend systems was ingested into BigQuery with minimal transformation, just to be gathered in the same project. The staging layer then standardized naming conventions, data types, source-specific inconsistencies and deduplicated records. This created a single, reliable representation of source data that could be reused throughout the project.

For example, instead of repeatedly applying the same business logic across multiple reporting models, common transformations were centralized in reusable Silver Layer models:

SELECT 
      event_name 
    , event_ts 
    , DATE(event_ts)                                    AS event_date 
    , bq_inserted_ts 
    , user_id 
    , {{ get_param_string('firebase_screen') }}         AS firebase_screen 
    , {{ get_param_int('sessionId') }}                  AS session_id 
    , {{ get_param_string('id') }}                      AS content_uuid 
    , {{ get_param_date_from_string('publishDate') }}   AS content_publish_date 
    , {{ get_param_string('contentSource') }}           AS content_source 
FROM 
    {{ ref('stg_events') }} 
WHERE 
    event_name = 'my_model'

Since we had a high number of scheduled queries extracting telemetry and IoT data from the same sources, logic duplication was common across the platform. Similar extraction, enrichment and filtering rules were frequently reimplemented in multiple models, increasing maintenance effort and creating a risk of inconsistent business metrics. By centralizing these operations in reusable Silver Layer models and macro functions, we established a single source of truth for common transformations. This improved consistency across downstream datasets while making the platform significantly easier to maintain and extend as data volumes and reporting requirements continued to grow.

Incremental processing

One of the biggest optimizations came from incremental processing. As IoT telemetry datasets grew into billions of records, rebuilding analytical tables from scratch became increasingly expensive and time consuming. Rather than processing the entire dataset during every execution, dbt incremental models allowed us to process only new data while preserving previously calculated results:

{{ config( 
    materialized='incremental' 
) }} 

SELECT 
      primary_key 
    , version 
    , datatype                              AS data_type 
    , data                                  AS telemetry_data 
    , {{ int_to_timestamp('created_at') }}  AS event_ts
FROM 
    {{ source('data_platform', 'telemetry') }} 
WHERE 
    {{ incremental_where_clause() }} 
QUALIFY 
    ROW_NUMBER() OVER(PARTITION BY primary_key ORDER BY version DESC) = 1 

With a dedicated macro function incremental_where_clause() which is filtering only fresh data when incremental flag is set:

{% macro incremental_where_clause() %} 
    {% if is_incremental() %} 
        created_at >= ( SELECT MAX(event_ts) FROM {{ this }} ) 
    {% else %} 
        1 = 1 
    {% endif %} 
{% endmacro %} 

For datasets where records arrive strictly in order, processing only newly ingested data was sufficient. However, some mobile app workloads produced late-arriving data, where events generated earlier could still be delivered days after their original timestamp. For these cases, we implemented dedicated incremental macros that reprocessed not only newly arrived records, but also a configurable number of recent partitions. This ensured that delayed updates were captured without requiring a full historical rebuild:

{% macro incremental_where_clause_firebase(lookback_days=3) %} 
    {% if is_incremental() %}
        {# Another macro function can be called from within a macro #}
        {% set suffix = get_firebase_incremental_suffix(lookback_days) %}
        _TABLE_SUFFIX >= '{{ suffix }}'
    {% else %} 
        1 = 1 
    {% endif %} 
{% endmacro %} 

dbt provides several incremental strategies for BigQuery:

• merge: Inserts new records and updates existing records based on a configured unique_key
• insert_overwrite: Refreshes entire partitions instead of updating individual rows, making it well suited for partitioned datasets
• microbatch: Incremental strategy designed for processing large time-series datasets by splitting the data into time-based batches (for example, daily or hourly)

Even though our Silver Layer is partitioned, we adopted the merge strategy in combination with incremental predicates. The use of incremental predicates allowed us to limit the portion of the destination table participating in the merge operation, typically restricting it to the most recent partitions. By adjusting these predicates, we could support both true merge-based processing and append-only style workloads while minimizing the amount of historical data scanned. This provided a practical balance between data accuracy and processing efficiency, particularly for IoT telemetry datasets where duplicate records, data corrections and late-arriving Firebase mobile events were common.

When combined with BigQuery partitioning, this approach significantly reduced the amount of data scanned during each run. Processing only recent or new partitions allowed models to scale alongside growing telemetry volumes without creating a proportional increase in execution time or warehouse costs. At the same time, selectively reprocessing the most recent partitions provided confidence that late-arriving data was incorporated into analytical datasets without sacrificing the performance benefits of incremental processing.

Macros & self-documenting transformations

Another area where dbt delivered significant value was through the use of reusable macros and centralized model documentation. As the number of models increased, maintaining consistency across hundreds of transformations became increasingly important. Rather than duplicating the same SQL logic throughout the project, common operations were abstracted into macros and reused across multiple domains.

Functions such as get_param_string(), get_param_int(), get_param_date_from_string() and incremental_where_clause() allowed engineering teams to standardize data extraction and transformation logic while reducing the risk of implementation differences between models. When business rules or transformation requirements change, updates can often be performed in a single macro instead of modifying dozens of individual models. Another example is deduplication. Since many models are derived from Firebase event data, they share identical deduplication requirements. Rather than implementing the same windowing logic repeatedly, we encapsulated it within a reusable macro that could be applied consistently across all event-based models:

{% macro firebase_deduplicate(partition_by, order_by) %}
QUALIFY
    ROW_NUMBER() OVER ( PARTITION BY {{ partition_by }} ORDER BY {{ order_by }} ) = 1
{% endmacro %}

The benefits extended beyond code reuse. dbt's YAML-based configuration enabled teams to document datasets, columns, business definitions and transformation logic directly alongside the implementation itself. For example, model-level descriptions could explain the purpose of a dataset, while column-level descriptions documented calculation methodologies, ownership and business meaning:

models: 
  - name: fct_content_update 
    description: > 
      Contains content consumption events enriched with user, device and content attributes. 

    columns: 
      - name: content_uuid 
        description: Unique identifier of the consumed content. 
        data_type: string 

      - name: content_publish_date 
        description: Original publication date of the content. 
        data_type: date

This information was automatically incorporated into the generated dbt documentation website, creating a centralized knowledge repository for the Analytics Data Platform. Engineers, analysts and business stakeholders could easily search for datasets, inspect column definitions and understand the purpose of models without needing to read SQL code directly.

The generated documentation also provided an intuitive browser-based interface for exploring the platform. Users could navigate upstream and downstream dependencies, inspect model metadata, review tests and understand how business metrics were derived. As the project continued to grow, this capability reduced onboarding time for new team members and improved confidence that analytical outputs were being interpreted correctly.

Combined with automatic lineage generation, documentation transformed the project from a collection of SQL files into a discoverable and maintainable analytics ecosystem where both technical implementation details and business context were readily accessible.

Data lineage

Perhaps the most underrated benefit was lineage. With more than a hundred interconnected models, understanding dependencies manually would have been nearly impossible. dbt automatically generated a dependency graph showing exactly how data flowed from raw ingestion all the way to business reporting. During development, this allowed engineers to assess the impact of proposed changes before deployment. During incidents, it significantly reduced troubleshooting time by making upstream and downstream relationships immediately visible.

Data quality as code

Data quality was another area where dbt provided immediate value. Instead of relying solely on downstream validation or dashboard checks, critical assumptions became part of the pipeline itself. For key business entities such as users, devices and subscriptions, automated tests ensured that important identifiers remained populated and unique:

columns: 
  - name: device_id 
    tests: 
      - not_null 
      - unique 

This helped identify issues earlier in the development lifecycle and increased confidence in production deployments.

Lessons learned and trade-offs

While dbt brought significant improvements to the Analytics Data Platform, we quickly learned that it is not a silver bullet. One of the most important lessons was that dbt amplifies both good and bad engineering practices. Well-designed models become easier to scale and maintain, while poorly designed transformations can still create performance, cost and maintenance challenges.

For example, adopting incremental models reduced processing costs and execution times, but only when combined with thoughtful partitioning, clustering and filtering strategies in BigQuery. A poorly designed incremental model can still scan large volumes of historical data and negate many of the expected benefits. Performance optimization therefore remained an important responsibility of the engineering team rather than something automatically handled by the framework. Within the Analytics Data Platform, these optimizations reduced processing costs for Firebase and IoT telemetry workloads by approximately 66%. Because BigQuery costs are largely driven by the amount of data scanned, this translated directly into processing the same analytical workloads while scanning only one-third of the data previously required.

We also discovered that governance becomes increasingly important as projects grow. Naming conventions, folder structures, model ownership and documentation standards have a limited impact when a project contains only a few models. However, once the number of models grows into hundreds, consistency becomes essential for maintainability and onboarding.

Finally, there is an initial learning curve. Concepts such as macros, materializations, lineage, testing and environment management require developers to adopt new ways of working. While this investment pays off over time, teams should account for the effort required to establish standards, educate contributors and continuously improve development practices.