Dataform: The ELT Game Changer for GCP

If you’re just starting out with data transformation and using Google Cloud Platform (GCP), Dataform might be the perfect tool for you, especially if you’re working with BigQuery. Dataform is a service designed to help you manage SQL-based data operations within BigQuery, making it easy to turn raw data into clean, usable tables or views for analysis.
It’s part of GCP, so it works seamlessly within this ecosystem, offering features like version control and collaboration through Git. For beginners, think of it as a simple way to organize and clean up your data in BigQuery without needing deep programming knowledge.

What is Dataform?

Dataform is a service designed to manage SQL-based data operations within BigQuery, a part of Google Cloud Platform. It serves as a framework for data teams to develop, test, version control, and schedule complex workflows for data transformation. For beginners, think of Dataform as a tool that helps organize and clean up data in BigQuery, making it ready for analysis or reporting. It is particularly useful for creating scalable data pipelines, leveraging software engineering best practices like version control with Git and automated testing.

Key Features:

• SQL-First Approach: Write your transformations using SQL, making it simple for analysts and data engineers.
• Integrated with BigQuery: Designed to work seamlessly with BigQuery’s powerful processing capabilities.
• Collaboration: Supports version control and testing, so you can ensure data quality as your team evolves your data pipelines.

Components of Dataform:

1. Repositories
   1. A workspace to store transformation logic with Git integration for version control.
2. SQLX Files (Scripts)
   1. Extended SQL format for defining transformations:
   2. Tables (.sqlx) - Creates transformed tables.
   3. Views (.sqlx) - Defines virtual tables.
   4. Incremental Tables - Processes only new data.
3. Dependencies & DAG
   1. Auto-detects dependencies and builds a Directed Acyclic Graph (DAG) to run queries in order.
4. Assertions (Data Quality Checks)
   1. Ensures data correctness (e.g., checks for negative revenue).
5. Scheduling & Orchestration
   1. Automates transformation runs using Cloud Workflows/Scheduler.
6. Testing & Documentation
   1. Supports unit tests and auto-generates documentation with data lineage.

SQLX file format:

1
// ✅ Declaration: Define table/view/incremental settings
2
config {
3
  type: "table",
4
    description: "This table joins t1 and t2",
5
  columns: {
6
    column_1: "column1 description",
7
    column_2: "column1 description",
8
  },
9
    assertions: {
10
    uniqueKey: ["id"]
11
  }
12
}
13

14
---
15

16
// ✅ Transformation: The core SQL logic for data processing
17
SELECT
18
  <column_1>,
19
  <column_2>,
20
  <aggregations/calculations>
21
FROM <source_table>
22
JOIN <another_table>
23
ON <join_condition>
24
WHERE <filter_conditions>
25
GROUP BY <grouping_columns>;
26

27
---
28

29
// ✅ Assertion: Data quality check conditions
30
config {
31
  type: "table",
32
  assertions: {
33
    nonNull: ["column_1"]
34
  }
35
}

Why Dataform Excels in ELT Workflows?

Before diving into why Dataform is better for ELT, let’s clarify the workflow types

• ETL (Extract, Transform, Load): This traditional approach involves extracting data from sources, transforming it outside the data warehouse (e.g., using separate tools or servers), and then loading the transformed data into the warehouse.
• ELT (Extract, Load, Transform): A modern approach where data is first extracted and loaded into the data warehouse in its raw form, then transformed within the warehouse.
• Dataform is explicitly designed for the transformation part of ELT workflows, as evidenced by its documentation, which states it helps “transform data already loaded in your warehouse”. This alignment makes it a natural fit for ELT, where transformations occur after loading, utilizing BigQuery’s powerful query engine.

Here’s why ELT with Dataform is better:

• Optimized for BigQuery: Direct integration with GCP for easier management and scalability.
• Modular & Scalable: Breaks down transformations into reusable parts, leveraging BigQuery’s processing power.
• Automated Dependencies & Scheduling: Automatically detects dependencies, manages execution order, and supports reliable automation with Cloud Scheduler.
• Data Quality & Version Control: Built-in assertions for data integrity and Git integration for versioning and collaboration.
• Cost-Efficient ELT: Optimizes compute costs by using BigQuery’s on-demand processing, eliminating the need for intermediate ETL steps.

Dataform vs. dbt: A Beginner’s Take

You might have heard of dbt (Data Build Tool), another big name in data transformation. Both Dataform and dbt help you write SQL to transform data, but they’re a little different. Here’s a quick comparison:

• Where They Work: Dataform is made for BigQuery and GCP—it’s like a native citizen of that world. dbt, on the other hand, works with lots of data warehouses (BigQuery, Snowflake, Redshift, etc.), making it more of a global citizen.
• Setup: With Dataform, you’re ready to go in GCP’s web interface—no extra installs. dbt has a free version (dbt Core) that needs some setup on your computer, or a paid dbt Cloud option with a web UI.
• Coding Style: Dataform uses SQL with a sprinkle of JavaScript for extra flexibility. dbt uses SQL with Jinja (a Python-like tool), which might feel trickier if you’re new to coding.
• Community: dbt has a bigger community with tons of tutorials and plugins. Dataform’s community is smaller but growing, especially among GCP fans.

PS: Dataform is part of BigQuery.