Dataform Advanced: DWH Architecture, JS Macros, and Budget Protection in BigQuery (The No-BS Guide)

If you have already mastered the basic syntax of Dataform, learned how to write type: "table", and linked a couple of views using ref(), congratulations — you have passed the beginner’s tutorial. However, in a real Enterprise environment, when your Data Warehouse (DWH) digests tens of millions of events from daily logs, the naive approach will instantly burn through your cloud budget. Unstructured code will quickly turn your architecture into an unmanageable swamp.

In this guide, we will drop the marketing fluff and look at Dataform through the eyes of a data engineer. We will break down how the engine works under the hood, why default incremental loads are a straight path to bankruptcy, and how to force JavaScript to generate SQL for you.

1. Dataform vs dbt: Taking Off the Rose-Colored Glasses

Historically, dbt (data build tool) has been the market standard for analytics Infrastructure as Code (IaC). So why did Google acquire Dataform and integrate it so deeply into the GCP console? Should you even look at it if the whole world writes in dbt?

The answer lies in three distinct areas:

  • Pricing (The dbt Cloud Killer): dbt Cloud Enterprise can cost a team of data engineers tens of thousands of dollars a year. Dataform, as a managed service, is completely free. You pay absolutely nothing for orchestration and compilation inside GCP. Your only expenses are the slots (compute resources) and bytes that BigQuery consumes to execute the generated SQL code.
  • Vendor Lock-in vs. Native Ecosystem: dbt is multi-cloud and independent. Dataform is nailed to BigQuery. But if your DWH already lives entirely within the Google Cloud ecosystem, Dataform wins hands down. Native integration with IAM roles, Cloud Logging, Secret Manager, and triggers from Cloud Workflows can be configured in a few clicks without the need to set up proxy servers or push Docker images.
  • Macros: Jinja vs. JavaScript: In dbt, macros are written using the Jinja templating engine. This is tolerable for simple loops, but implementing complex algorithmic logic in Jinja is syntactically clunky and unreadable. Dataform uses Node.js under the hood. Writing a column generator, a recursive metadata traversal, or an array loop in pure JS is a five-minute task for any software engineer.

The Verdict: If you are building a multi-cloud architecture (Snowflake + Redshift + BQ) or using Python models in your pipelines, dbt is your choice. But if your foundation is exclusively BigQuery, Dataform delivers the exact same power for free, enhanced by native JavaScript support.

2. The Anatomy of SQLX and the Compilation Lifecycle

The biggest mistake beginners make is treating .sqlx files as standard top-to-bottom SQL scripts. A .sqlx file is actually a meta-template — an abstraction that goes through a complex pipeline before hitting the database.

The file structure is strictly segmented:

  1. config {}: A JSON block containing metadata. This is where you define the materialization type, orchestration tags, partition keys, and built-in tests (assertions).
  2. pre_operations {} / post_operations {}: DML/DDL statements that are executed strictly before or after the main transaction (e.g., creating a temporary function or issuing a GRANT SELECT ON...).
  3. Core Query: The SELECT statement itself, which Dataform will later wrap appropriately (depending on the materialization type, this will become a CREATE TABLE AS SELECT, CREATE VIEW, or MERGE statement).

How the Compilation Engine Works

When you click “Run” or trigger the API, Dataform does not immediately start sending queries to the database. The following sequence occurs:

  1. AST Assembly: The engine parses all ref() dependencies across all files and builds an Abstract Syntax Tree (AST). During this phase, it detects circular dependencies (e.g., if data mart A references B, and B references A for enrichment). If a loop exists, the compilation fails.
  2. Transpilation: JavaScript macros are executed, SQLX tags are replaced with absolute paths like `project_id.dataset.table_name`, and configurations are converted into valid DDL commands.
  3. Manifest Generation: The output is a massive JSON CompilationResult — a complete snapshot of your data warehouse containing ready-to-execute, valid BigQuery SQL for every single node.
  4. Execution: Only after the manifest is successfully compiled does the Dataform API begin dispatching batches of requests to the BigQuery Jobs API, strictly following the dependency graph.

Engineering Nuance: The Dataform API has hidden limits regarding the size of the compiled manifest and the execution time of the JS engine during compilation. If your monorepo contains thousands of complex files heavily reliant on JavaScript generation, you might hit a timeout before a single query is executed. In such cases, your DWH architecture must be logically partitioned.

Продолжаем. Вывожу вторую часть статьи на английском языке (B2). Здесь мы разбираем самую критичную для бюджета тему — инкременты и правильное использование JavaScript для генерации SQL.

3. Incremental Loads: How Not to Bankrupt Your Company

Materializations like table and view are only suitable for dimension tables and high-level aggregates. For fact tables (events, transactions, telemetry), you must use incremental loads. And this is exactly where Dataform’s biggest hidden traps lie.

Basic Incremental (Append-Only)

This approach is ideal for immutable logs, such as Google Analytics 4 or AppsFlyer streaming data, where historical records never change.

SQL

config {
  type: "incremental",
  schema: "dwh_core",
  name: "fct_events"
}

SELECT event_timestamp, event_name, user_id, params
FROM ${ref("stg_raw_events")}
${when(incremental(), `WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM ${self()})`)}

If the table does not exist, the incremental() macro evaluates to false, the WHERE clause is dropped, and a full load is executed. On subsequent runs, it generates a standard INSERT INTO statement.

Advanced Incremental: MERGE (Upsert) and Budget Protection

When order statuses update over time or data arrives late (late-arriving data), you need a MERGE operation. Dataform handles this automatically if you provide an array of unique identifiers in the uniqueKey parameter.

SQL

config {
  type: "incremental",
  schema: "dwh_core",
  name: "fct_orders",
  uniqueKey: ["order_id"] /* This triggers a MERGE operation */
}

The Critical Architectural Trap! BigQuery is a columnar database. A MERGE operation without limits forces BigQuery to scan the entire target table to find matches for order_id. If your target table is 10 TB, every hourly incremental run will scan 10 TB. Your monthly cloud budget will evaporate in less than a week.

The Solution: updatePartitionFilter This is arguably the most important parameter for incremental loads in Dataform. You must restrict the search area in the target table during a MERGE.

SQL

config {
  type: "incremental",
  schema: "dwh_core",
  name: "fct_orders",
  uniqueKey: ["order_id"],
  bigquery: {
    partitionBy: "DATE(updated_at)",
    /* Tells BQ to only scan the last 3 days of target partitions when looking for matches */
    updatePartitionFilter: "updated_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)"
  }
}

Now, Dataform generates SQL that tells BigQuery: “When searching for order_id to update records, only scan the partitions from the last three days.” The query cost instantly drops from hundreds of dollars to a few cents.

4. JavaScript Macro-Magic (The DRY Principle)

The true power of Dataform lies in its fully-fledged Node.js execution environment. You can encapsulate business logic in the includes/ folder and generate SQL dynamically, strictly adhering to the DRY (Don’t Repeat Yourself) principle.

Case Study: Unpacking EAV (Entity-Attribute-Value) Models

Custom parameters in Firebase and GA4 are stored in nested arrays (ARRAY<STRUCT>). Writing UNNEST statements manually for 50 different parameters is tedious and error-prone. Instead, we write a macro in includes/ga_helpers.js:

JavaScript

function extract_param(param_name, param_type = "string_value") {
  return `(SELECT value.${param_type} 
           FROM UNNEST(event_params) 
           WHERE key = '${param_name}') AS param_${param_name}`;
}
module.exports = { extract_param };

You then use it directly in your SQLX files:

SQL

SELECT
  event_name,
  user_pseudo_id,
  ${ga_helpers.extract_param("page_location")},
  ${ga_helpers.extract_param("engagement_time_msec", "int_value")}
FROM ${ref("events_raw")}

Case Study: Dynamic Table Generation (Data Marts)

Imagine you need to split one massive global sales table into 15 separate regional data marts. Instead of creating 15 separate SQLX files, you create a single JavaScript file in definitions/dynamic_marts.js:

JavaScript

const countries = ["UA", "US", "UK", "DE", "PL"];

countries.forEach(country => {
  publish(`dm_sales_${country}`, {
    type: "table",
    schema: "data_marts",
    description: `Regional data mart for ${country}`,
    tags: ["daily_marts"]
  }).query(ctx => `
    SELECT order_id, amount, customer_id 
    FROM ${ctx.ref("fct_global_sales")}
    WHERE country_code = '${country}'
  `);
});

During compilation, Dataform will automatically generate five independent nodes in the dependency graph, map them correctly, and deploy five distinct tables to BigQuery.

5. Storage Optimization (Partitioning & Clustering)

If a Data Warehouse lacks a partitioning strategy, it is not a DWH — it is an expensive data dump. You must configure these parameters at the core configuration level to ensure performance and cost efficiency.

SQL

config {
  type: "incremental",
  bigquery: {
    partitionBy: "DATE(created_at)",
    clusterBy: ["user_id", "event_type", "platform"],
    requirePartitionFilter: true
  }
}

Crucial Setup Nuances:

  1. requirePartitionFilter: true: This is your ultimate defense against junior analysts and rogue BI dashboards. If someone attempts to run SELECT * FROM table without specifying a WHERE created_at = ... clause, BigQuery will immediately reject the query before execution. It literally prevents accidental full table scans.
  2. Clustering Order Matters: The order of columns in the clusterBy array is critical. Always place high-cardinality fields that are frequently used in hard filters first (e.g., user_id), followed by fields used for grouping. You can specify a maximum of 4 columns.
  3. Partition Limits: BigQuery supports up to 4,000 partitions per table. If you partition by HOUR, your table will “break” in roughly 166 days (4000 / 24). For long-term logs, always use DAY or MONTH partitioning.

6. Data Quality Control (Data Observability & Assertions)

Data Observability is built natively into Dataform via Assertions. These are not just background tests; they are active nodes in your DAG. If an assertion fails, the execution of all downstream tables (tables that depend on the failed one) is halted, preventing corrupted data from reaching your BI dashboards.

Built-in Basic Checks: Dataform allows you to declare simple rules directly inside the table config:

SQL

config {
  type: "table",
  assertions: {
    uniqueKey: ["transaction_id"],
    nonNull: ["user_id", "amount"],
    rowConditions: [
      "amount >= 0",
      "currency IN ('UAH', 'USD', 'EUR')"
    ]
  }
}

Custom Checks (Business Logic Validation): Sometimes you need to validate complex logic across multiple tables. For example, ensuring that the sum of payments does not exceed the total order value. You can create a dedicated file tests/assert_payments.sqlx:

SQL

config { type: "assertion" }

/* Logic: The query must return 0 rows for the test to pass successfully */
SELECT 
  o.order_id, 
  o.order_total, 
  SUM(p.amount) as total_paid
FROM ${ref("fct_orders")} o
JOIN ${ref("fct_payments")} p USING(order_id)
GROUP BY o.order_id, o.order_total
HAVING SUM(p.amount) > o.order_total

If this query returns even a single “overpaid” order, the pipeline turns red and stops.

7. Orchestration and CI/CD: The Harsh Reality of Production

Forget the built-in Dataform UI Scheduler. It is a crutch designed for small teams and isolated projects. In an Enterprise DWH, Dataform is triggered externally — usually via Apache Airflow (Cloud Composer) or Cloud Workflows.

Why Airflow? A data pipeline rarely lives entirely inside BigQuery. A real-world DAG looks like this:

  1. Extract data from a PostgreSQL replica (via Airflow or Datastream) and load it into Cloud Storage.
  2. Load External Tables into BigQuery.
  3. Trigger the Dataform API to compile and execute the SQL transformations (Airflow).
  4. Upon success, push metrics to Prometheus and trigger a Tableau extract refresh.

In Airflow, you handle this seamlessly using the DataformCreateCompilationResultOperator and DataformCreateWorkflowInvocationOperator.

Environments (Dev/Prod) via Workspace Overrides: No sane engineer pushes code directly to production schemas. Environment separation is managed via the dataform.json file:

JSON

{
  "defaultSchema": "dwh_prod",
  "assertionSchema": "dwh_tests",
  "vars": {
    "env": "prod",
    "lookback_days": "3"
  }
}

During a release (via API or Release Configurations), you inject overrides: defaultSchema becomes dwh_dev_username. The SQLX code remains untouched, but the tables are materialized in isolated development datasets.

8. 5 Battle-Tested Gotchas from the Trenches

Gotcha #1: The Accidental Full Refresh (Catastrophe)

If a developer accidentally triggers an incremental table with the Run with full refresh flag, the table is dropped and recreated. If your source is a temporary streaming buffer (where old data is already purged), you will permanently lose historical data. Solution: Hard-block full refreshes using pre_operations:

SQL

pre_operations {
  /* Force a BigQuery syntax error if a full refresh is attempted */
  ${when(!incremental(), `SELECT ERROR("FULL REFRESH IS STRICTLY DISABLED FOR THIS TABLE.");`)}
}

Gotcha #2: Duplicates Blowing Up MERGE

If duplicates slip into your staging layer for a uniqueKey, the MERGE operation will crash with a database error: “UPDATE/MERGE must match at most one source row for each target row”. Solution: Always deduplicate your incremental SELECT statements using QUALIFY:

SQL

SELECT * FROM ${ref("stg_orders")}
${when(incremental(), `WHERE updated_at > ...`)}
QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY updated_at DESC) = 1

Gotcha #3: IAM Role Blind Spots

Dataform executes queries not under your personal Google account, but under its own Service Account (e.g., service-XXX@gcp-sa-dataform.iam.gserviceaccount.com). Problem: Code works perfectly in an analyst’s Workspace (using their user credentials), but fails with Access Denied on a scheduled run. Solution: Explicitly grant the Dataform Service Account the following roles: BigQuery Data Editor (on target datasets), BigQuery Data Viewer (on source datasets), and BigQuery Job User.

Gotcha #4: Spaghetti Dependencies (Circular Logic)

It is easy to abuse the ref() function and create an architecture where Data Mart A references Data Mart B, and B references A for enrichment. The Dataform engine will crash during the AST compilation phase. Solution: Enforce strict data layering. sources -> staging (cleaning) -> core/facts (incrementals) -> marts (business logic). Establish a strict team rule: staging can never reference marts.

Gotcha #5: Losing Control Over Billing

Every single action in Dataform creates a separate BigQuery Job. Hunting down the most expensive queries in INFORMATION_SCHEMA becomes a nightmare. Solution: Mandate the use of BigQuery labels at the config level:

SQL

config {
  type: "table",
  bigquery: { labels: { "domain": "marketing", "pipeline": "attribution_model" } }
}

With this setup, you can easily filter Billing Reports in the GCP Console to see exactly how much money the attribution_model pipeline consumes.

Conclusion: Dataform will not fix bad DWH logic. It simply automates it. Its true value unlocks only when you build dependency graphs with JavaScript, ruthlessly restrict data scanning via updatePartitionFilter, isolate environments, and control pipelines with external orchestrators like Airflow that react to events in your Data Lake.

Similar Posts