Architectural Zen in Dataform: Building a Seamless Incremental Pipeline

Chapter 1. Where It All Began: Pain and Optimization

The fundamental reason behind launching any Cloud Data Engineering project almost always comes down to finances. Running a daily analytical script that does a basic SELECT * from raw logs and entirely overwrites the final data mart (a classic Full Refresh) works flawlessly. That is, until your data volume grows beyond a negligible size.

BigQuery is a highly pragmatic and cold machine. Google charges you for every gigabyte scanned. By default (On-Demand pricing), you pay around $6.25 for every terabyte of data your query processes. Updating an entire 10 TB table every single day will cost you over $60 just to refresh yesterday’s metrics. Doing this daily across years of historical data is essentially a form of voluntary and completely pointless charity to Google.

Our primary architectural goal is to surgically optimize this algorithm. We need to teach the database to update exclusively yesterday’s data (an incremental update), and then hand this routine over to Dataform’s automation so the process runs without human intervention.

Chapter 2. Under the Hood Logic: MERGE vs. Transactions (DELETE + INSERT)

To avoid scanning the entire history, we use Partitioning—physically slicing a massive table into isolated daily chunks. BigQuery allows you to read only the necessary partitions (Partition Pruning), ignoring the rest of the array. But what is the right way to update data strictly for yesterday?

An engineer has two paths:

Option A: MERGE (Native Incremental) It looks elegant and “textbook”. The command looks for matches using a unique key: if the key already exists, it updates the row (UPDATE); if not, it inserts a new one (INSERT). The catch? To find these matches, the database query planner has to perform a JOIN operation under the hood, scanning both the target table and the source. When dealing with hundreds of millions of rows, this requires a massive amount of compute slots, takes an unjustifiably long time, and burns through your budget.

Option B: Transactions (DELETE + INSERT) This is an architecturally brutal but mathematically perfect method for web analytics and event logs. We open a transaction, strictly delete yesterday’s data from the target partition (just in case there is garbage left from a previously failed run or duplicates), and insert a fresh slice strictly for yesterday. It takes seconds and costs pennies.

Here is what this production-ready code looks like inside Dataform:

SQL

config {
  type: "operations",
  hasOutput: true,
  schema: "analytics_mart",
  name: "daily_events_partitioned"
}

BEGIN TRANSACTION;

-- Step 1: Clean up yesterday's partition to eliminate the risk of duplication
DELETE FROM ${self()}
WHERE event_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Step 2: Stream in fresh data strictly for yesterday
INSERT INTO ${self()} (event_date, user_id, category, hits)
SELECT 
  event_date, 
  user_id, 
  category, 
  COUNT(hit_id)
FROM `raw_project.raw_dataset.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY 1, 2, 3;

COMMIT TRANSACTION;

Chapter 3. Architectural Trade-offs: The Problem of Late Arriving Data

Engineering is the science of trade-offs. Before blindly copying the code above, we must face reality: the real world is not perfect. The DELETE + INSERT method with a 1-day window (INTERVAL 1 DAY) works flawlessly only in a vacuum.

What if a user makes a purchase in a mobile app while riding the subway without an internet connection, and their phone sends the raw log to the server 48 hours later? Systems like Google Analytics 4 officially process “late-arriving hits” for up to 72 hours. If our pipeline only looks at “yesterday”, we permanently lose these conversions. The data in our analytics will start to mismatch the actual revenue.

The Solution: Shifting the load window. We modify our transaction so that it daily overwrites not one, but the last 3 days. This operation is idempotent—no matter how many times you run the script, the result remains consistent and correct.

SQL

DELETE FROM ${self()}
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY);

INSERT INTO ${self()} (...)
SELECT ...
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))

Yes, we scan slightly more raw logs, but we guarantee Enterprise-grade data consistency.

Chapter 4. The Illusion of Power and Access (IAM Configuration)

The algorithm is ready. Now it needs to be hosted in the cloud. The first rule of cloud engineering: never use Project Owner rights to configure pipelines. Giving global admin privileges to a developer just to run a SQL script is like giving a barista a nuclear briefcase to brew an espresso. Google Cloud strictly follows the Principle of Least Privilege.

To make the automation take off, a cloud administrator does two surgically precise things:

  1. Creates a Service Account (the robot) and grants it permission to edit data strictly within BigQuery (BigQuery Data Editor, BigQuery Job User).
  2. Grants your personal email the Service Account User role specifically applied to that robot.

This allows you to tell the system: “Allow me to link this specific robot to my Dataform.” You control the schedule, and the robot controls the database. A perfect and secure symbiosis.

Chapter 5. The Vault and the Desk (Repository & Workspace)

In Dataform, you cannot just log in and start writing code directly into production. The infrastructure is strictly divided into two entities:

  • Repository (The Vault): Your main storage. Under the hood, this is a fully functional Git repository. It holds the flawless production code, but it lacks a text editor for direct intervention.
  • Development Workspace (The Desk): Your personal sandbox. You create it to pull an empty draft from the vault onto your desk, write your code, test it, and make sure a random typo will not crash the company’s live infrastructure.

Chapter 6. The Hidden Engine and Environment Isolation (Dev vs Prod)

This is where 90% of beginners stumble. Dataform runs on a Node.js engine. If you just write your beautiful SQL file and try to trigger the automation, the compiler will instantly crash with a Can't find package.json error.

Before writing any analytics, you must lay a foundation on your “desk”. You need two system files:

  1. package.json in the root directory, which explicitly tells the system which engine version to use:JSON{ "dependencies": { "@dataform/core": "3.0.0" } }
  2. workflow_settings.yaml — the project configurator. And this is exactly where the magic of environment isolation lies. Deploying raw code directly into the production dataset is a path to getting fired. In this file, we define the default dataset, but Dataform is so smart that when you run code from your Workspace, it automatically adds a suffix (e.g., _dev) to the database name. Your draft compiles in analytics_mart_dev, keeping the production analytics_mart completely untouched.

Chapter 7. True Zen: JavaScript Meta-Programming

Writing SQL manually for a single table is easy. But what if you work in an international corporation and have 50 identical data marts for different brands or countries (from events_de to events_jp)? Writing 50 SQL files and maintaining them when logic changes is a failure.

Dataform’s true killer feature is the ability to generate SQL code on the fly using JavaScript loops. Instead of creating dozens of files, we create a single build_pipelines.js file:

JavaScript

const countries = ['us', 'uk', 'de', 'fr', 'jp'];

countries.forEach(country => {
  operate(`daily_events_${country}`)
    .schema("analytics_mart")
    .queries(ctx => `
      BEGIN TRANSACTION;
      DELETE FROM ${ctx.self()} WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY);
      
      INSERT INTO ${ctx.self()} (...)
      SELECT ... FROM \`raw_dataset.events_${country}_*\`
      WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY));
      
      COMMIT TRANSACTION;
    `);
});

This compact script instantly generates 50 perfect SQL queries. If you need to adjust the metric calculation logic, you change one line in JS, and Dataform immediately rolls out the updates across all regions. This is the definition of Senior Data Engineer level.

Chapter 8. Pipeline Safeguards (Data Quality & Assertions)

Our script runs at lightning speed, but it runs blind. If a flawed mobile app release causes duplicate transactions or missing user_ids in the backend, our perfect incremental code will obediently pour this garbage into the financial data mart.

To prevent disasters, we use Assertions. These are SQL tests that Dataform runs before or after building the data.

We create an assert_unique_events.sqlx file:

SQL

config {
  type: "assertion"
}

-- The query must return 0 rows. If it returns even one, the test fails.
SELECT
  hit_id,
  COUNT(1) as duplicates
FROM ${ref("daily_events_partitioned")}
GROUP BY hit_id
HAVING duplicates > 1

If the test fails, Dataform immediately blocks all subsequent steps in the Directed Acyclic Graph (DAG), sends an alert to your email or Slack, and stops the bad data from infecting the rest of your business dashboards.

Chapter 9. Code Transportation and Automation (Snapshots & Alarm Clocks)

Once the SQL, JS, and system files are ready, they must be moved from the desk back into the vault. We click Commit (saving the draft), and then we absolutely must click Push to default branch. Only after this step will the code physically land in the main branch of your repository and become available for automation.

Dataform cannot execute code directly from a Git branch in real-time. The process is divided into two phases:

  1. Release Configuration (The Snapshot): You command the system: every morning at 06:00, grab the fresh code from the main branch, read the package.json, and compile all of it into a working build.
  2. Workflow Configuration (The Alarm Clock): You link a timer to the created Release. You set the start time for 07:00 (when the code is definitely compiled) and select the Service Account as the executor.

A Critical Detail at the Finish Line: In the Workflow settings, there is a checkbox called “Run with full refresh”. Never check it if you are using transactional logic in operations blocks. If your hand slips, Dataform will ignore all our elegant incremental magic, attempt to physically drop the entire table, and recalculate data from the beginning of time. This will cause either a fatal pipeline error or a massive cloud bill at the end of the month.

Leave only Execute as interactive job with high priority active. Save it, manually click Execute for a Smoke test, wait for the green success checkmark to appear in the logs, and calmly go grab a coffee. The pipeline is running.

Chapter 10. Backfilling: The Pain of Historical Recalculations

Our pipeline flawlessly updates the last 3 days. But what if the business logic changes (for example, the marketing team invents a new attribution model), and the CTO demands a recalculation of the data mart for the last 6 months?

You cannot run a Full Refresh—it will destroy the partitioning logic. This is where Tags and the Dataform CLI come to the rescue. We temporarily add a tag like tags: ["historical_rebuild"] to the script config, modify the code (e.g., substituting variables instead of CURRENT_DATE()), and use the console to run a loop that carefully, day by day, executes our DELETE + INSERT for each historical window. The data is updated safely without blocking the execution of other data marts.

Similar Posts