Google Dataform – data modeling directly in BigQuery
Dataform brings testing and version control to data modeling in BigQuery, so the numbers that reach your reports check out, for free and right inside your data warehouse.
Overview
When raw data collected in BigQuery is transformed for reporting using scattered, hand-maintained SQL scripts, tracing the impact of changes and finding errors gets harder as projects grow. Google Dataform solves this: it's a service that lets data analysts and engineers develop, test, version-control, and schedule data transformation workflows directly in BigQuery. It handles the last step of the ELT process, once raw data has already landed in your warehouse: Dataform transforms it into something ready for analysis and reporting using SQL and JavaScript.
Dataform started out as an independent startup founded in 2017, which Google Cloud acquired in 2020. After the acquisition, Dataform was made free for everyone and integrated directly into BigQuery and the Google Cloud console. In other words, you're choosing a tool that Google itself maintains and develops natively as part of BigQuery, not a separate third-party product whose future you'd need to worry about.
Projects are organized into repositories that contain SQLX, JSON, and JavaScript files. Development happens in a workspace, an editable copy of the repository. The SQLX file format combines configuration and a SQL query in the same file, extending SQL with dependency management, automated testing, and documentation.
Pricing
Dataform itself is free to use, so adopting it doesn't add a separate license cost. It runs its queries in BigQuery, so billing comes from the BigQuery capacity you use and the required Cloud Logging, meaning you only pay for what you're already using in your warehouse. Any service you use for scheduling, such as Cloud Composer, Cloud Scheduler, or Workflows, is billed separately. The solution is tied exclusively to the Google ecosystem, which limits you to Google's service offering and related tools.
Food for thought
Best used when you need an extra layer between your data warehouse and your reporting tool to make sure data quality and structure keep up with your reporting requirements as more models and metrics get added over time. Without this layer, transformation logic ends up scattered across your reporting tool or in individual analysts' own SQL scripts, which makes it harder to trace errors and onboard new team members. Works with BigQuery as your data warehouse, and native integration makes it QuickBI's preferred choice for BigQuery-based data warehouses.
Dataform's components
Repositories and workspaces : where projects are stored, containing SQLX, JSON, and JavaScript files; a workspace is an editable copy of the repository where the actual development work happens.
SQLX and Dataform core : a file format that extends SQL, combining configuration and a query in the same file and adding dependency management, automated testing, and documentation.
Compilation : Dataform compiles your definitions into a standard SQL workflow, checks for missing and circular dependencies, and always produces the same result from the same code.
Assertions : built-in data quality tests that can check whether values are unique, whether fields contain null values, or a custom rule you define, all as part of the dependency graph.
Version control : direct integration with GitHub, GitLab, Azure DevOps, and Bitbucket, so changes get committed, pulled, and pushed straight from the workspace.
Why choose Dataform?
Free and natively integrated with BigQuery
Dataform costs nothing to use and runs directly inside BigQuery as part of the Google Cloud console, so you don't need separate servers or licenses.
Dependency management with SQLX
The SQLX file format lets you define dependencies between tables, views, and sources directly within SQL, and Dataform resolves the run order automatically, so you don't have to plan or maintain it by hand.
Automated data quality testing
Built-in assertions catch things like missing values or duplicates as part of the dependency graph, before errors make it into your reports.
Version control with the Git services you already use
Direct integration with GitHub, GitLab, Azure DevOps, and Bitbucket brings code review and change tracking into your modeling work.
Code reuse with JavaScript
JavaScript lets you share logic within a single file, across a repository (includes), or across multiple repositories (packages), so you're not rewriting the same logic more than once.
Flexible scheduling
Runs can be scheduled natively with Dataform's own time-based workflow configurations, or orchestrated externally with Cloud Composer (Managed Apache Airflow), a Cloud Workflows workflow triggered by Cloud Scheduler, or a Cloud Build trigger tied to your Git repository, so runs fit into whatever orchestration your organization already has in place.
QuickBI uses Dataform as its go-to modeling tool
When your data models are tested and version-controlled directly inside BigQuery, you can trust the numbers in your reports, and you don't need to maintain a separate transformation platform or pay license fees for one. Kaivo, QuickBI's data warehouse product, uses Dataform as its go-to data transformation tool in customers' BigQuery data warehouses.
If your goal is reliable, well-tested data modeling in BigQuery, get in touch with QuickBI.
Want working reporting solutions?
Start by booking a 30-minute assessment with our consultant.