---

Understanding Dataform

The Dataform BigQuery integration is great for increasingly streamlined and efficient data management.

This integration allows teams to leverage Google BigQuery with Dataform’s capabilities to optimize SQL workflows and enhance data operations.

In the blog below we’re providing a detailed introduction to Dataform. We explains SQL basics, and demonstrate how Dataform can upgrade your BigQuery usage.

Dataform and its Impact on BigQuery

This integration facilitates simplified SQL developments, improving data governance and speeding up insight generation.

What I mostly find, is that the tool is used by organizations to transform data from (amongst other data) the GA4 export to BigQuery.

For example, you could transform the event-based data model into a session-based data model. Or, if you track customer_type as a user_property, you could transform the dataset to a user_pseudo_id based dataset design. This would allow you to assign the customer_type over multiple sessions of a user, even the sessions where customer_type was unknown due to not being logged-in during that session.

Let’s take a look at the Dataform-BigQuery integration and how it transforms data analytics workflows.

Key Features of Dataform

SQL-based Workflow

Utilizing Dataform, teams can write and manage SQL code directly in BigQuery, empowering those familiar with SQL to contribute effectively without additional programming skills.

As SQL forms the basis of Dataform, let’s shortly dive in what it is and how it works.

Introduction to SQL

Structured Query Language (SQL) is the cornerstone of modern database management. Developed initially in the 1970s, SQL has become ubiquitous in the data management world. This is due to its powerful, yet straightforward approach to querying and manipulating data stored in relational database systems.

Key features of SQL:

• Versatility: SQL is used to query, update, insert, and modify data, making it indispensable for a wide range of data tasks.
• Simplicity: Despite its powerful features, SQL syntax is relatively easy to learn and understand, even for those new to programming.
• Interactivity: SQL commands are executed in real-time, allowing users to interact directly with the database and see immediate results.
• Standardization: SQL is standardized by both ANSI and ISO, ensuring compatibility and consistency across different database systems.

Examples of basic SQL operations:

• SELECT: Retrieve data from one or more tables.
• INSERT: Add new rows of data to a table.
• UPDATE: Modify existing data in a table.
• DELETE: Remove data from a table.
• JOIN: Combine rows from two or more tables based on a related column.

SQL(X) in Dataform

In Dataform, SQL is not just a means of querying data but a way to structure entire data transformation pipelines.

Example of a simple SQLX script in Dataform:

-- Example script
config {
  type: "table"
}

SELECT
  user_pseudo_id,
  SUM(purchase_revenue) AS total_revenue
FROM
  sales_data
GROUP BY
  user_pseudo_id

This script demonstrates creating a table that aggregates total spending per customer, showcasing how SQL is used within Dataform to perform data aggregation tasks efficiently.

Version Control and Collaboration

The Dataform-BigQuery integration also has a Github integration.

Automation and Scheduling

Dataform streamlines the execution of data workflows by automating and scheduling SQL queries. It helps with ensuring that your data warehouse remains up-to-date.

This automation is crucial for maintaining freshness in the data, which in turn supports accurate and timely analytics for strategic decision-making.

CRON Scheduling

To manage when and how often these workflows are executed, Dataform utilizes CRON expressions.

This is a flexible way to define time and frequency for jobs in a Unix-like scheduler syntax.

A CRON expression is a string comprised of five or six fields separated by white space that represents a set of times, normally as a schedule to execute some routine.

Here’s a breakdown of what each field in a standard five-field CRON expression represents:

1. Minute (0 – 59)
2. Hour (0 – 23)
3. Day of the month (1 – 31)
4. Month (1 – 12)
5. Day of the week (0 – 7) (where both 0 and 7 represent Sunday, or use names)

An example of a CRON expression is 0 12 * * 1-5, which means “at 12:00 PM (noon) every day Monday through Friday.”

This kind of scheduling is extremely useful in business environments where reports need to be generated during weekdays or data refreshes are required before business hours.

Utilizing CRON in Dataform

In the Dataform-BigQuery integration, CRON expressions are used to schedule SQL scripts for execution. As u user, you can specify these expressions in the scheduling settings. In turn, it allows you to automate data transformations, data checks, or updates according to your operational requirements.

This ensures that all stakeholders have access to the latest data at the needed frequency. Also, it alleviates the need for manual intervention. Hence, it reduces the risk of human error and increases the efficiency of data operations.

Testing and Documentation

Dataform includes robust tools for ensuring the accuracy and reliability of data models. And of course, these are highly valuable for any data-driven organization.

These tools facilitate not only the verification of data integrity but also the creation of accessible and automated documentation which can be seen as crucial for governance and compliance.

Assertions: Ensuring data quality

One of the key features of testing in Dataform is the use of assertions.

Assertions are conditions that you expect to be true within your data environment. These can be used to automatically verify the quality of the data after transformations are applied.

By defining assertions, teams can proactively catch issues and inconsistencies. This in turn ensures that data pipelines produce reliable, high-quality data.

For example, you might use assertions to ensure:

• No Duplicate Records: An assertion can be set to verify that there are no duplicate entries in a table, which is crucial for accurate reporting and analysis.
• Data Completeness: Ensure that important fields, such as customer IDs or transaction dates, are not null.
• Data Range Validity: Check that values fall within expected ranges, such as positive quantities for inventory counts or realistic date ranges in time series data.

Applying Assertions in Dataform

In Dataform, assertions are implemented as SQL checks against specific conditions in your data tables.

If an assertion fails, it indicates a potential issue in the data. This issue can then be addressed before it affects downstream processes or analytics.

For instance, if an assertion checks for null values in a primary key column and fails, it prompts an investigation into data collection or processing stages that might be introducing errors.

Here’s a simple example of how an assertion might be written in Dataform in the case of a GA4 data connection with BigQuery dataset:

config {
  type: "assertion"
}

select
  user_pseudo_id
from
  marketing_data_hub.ga4_dataset
where
  user_pseudo_id is null

This assertion checks for null values in the customer_id column. If the query returns any rows, the assertion fails, signaling a violation of data integrity expectations.

Automated Documentation

Dataform also automates the generation of documentation.

As code is developed and datasets are defined, Dataform generates documentation detailing tables, their schemas, and the relationships between them.

This documentation is updated in real-time as changes are made, ensuring that all team members have access to the latest data definitions and structures.

This real-time documentation supports better understanding and utilization of the data assets by all parts of the organization.

Additionally, a cool example of automated insights in the data structure and accompanied dependencies is created in the shape of a ‘compiled graph’. It’s presented in what’s called a ‘Directed Acyclic Graph (DAG)’.

Here you can see an example of a DAG currently present in my Dataform environment in the Google Cloud:

Enhancing BigQuery with Dataform

The integration of Dataform with BigQuery represents another leap forward in managing and leveraging large data sets within a business environment.

This integration not only streamlines data operations but also enhances strategic decision-making capabilities.

Streamlining Data Transformation

Dataform’s integration with BigQuery revolutionizes the way businesses handle data transformation by eliminating the reliance on separate ETL (Extract, Transform, Load) tools.

By allowing transformations to be scripted, tested, and executed directly within BigQuery, Dataform simplifies the data management process.

This direct approach minimizes the complexity traditionally associated with transferring data between different tools and platforms.

Hence, it speeds up data workflows and allows businesses to react more swiftly to market changes or internal demands.

Optimizing Query Performance

Efficient data querying is critical in today’s data-driven business environment.

Dataform enhances BigQuery’s capabilities by organizing and structuring data transformations efficiently.

This optimization results in faster query execution and lower processing loads. Both are crucial for reducing the computational costs typically associated with large-scale data analytics.

Faster data processing means quicker access to insights, and we all know: ‘Time is money’. Hence, more timely and informed business decisions, which helps the revenue streams.

Improving Collaboration

Dataform leverages Git integration to enhance collaborative efforts across teams.

This integration ensures that changes to data scripts are tracked, merged, and maintained with transparency and ease. It effectively mirrors the collaborative workflows found in software development.

Such functionality is vital for maintaining data consistency and reliability. This holds especially true with large-scale projects or in environments where multiple analysts and data scientists are working on the same data sets.

This collaboration focus fosters a culture of shared responsibility and continuous improvement.

Cost-efficient Data Management

The operational cost savings from using Dataform with BigQuery are substantial.

By optimizing query performance and streamlining data transformations, Dataform reduces the need for additional computational resources. This in turn, lowers the costs associated with running complex queries and maintaining large data warehouses.

Furthermore, the efficiency gained translates into less time spent on managing data processes and more time on strategic activities that add direct value to the business. Think of time for data analysis and interpretation for market insights.

Conclusion: make use of Dataform

The Dataform BigQuery integration is a powerful tool for any web analytics professional looking to improve data management and analytics efficiency.

By streamlining SQL workflows and enhancing data operations, this integration not only simplifies data processes but also provides a competitive edge in deriving actionable insights.

Thanks!

If you found this guide helpful, subscribe to our LinkedIn to receive updates whenever we post a new blog!

Share your experiences or questions about the Facebook Pixel in the comments below!