#4 - How to create and run your first SQL model on GA4 BigQuery export data in Dataform

In part 4 of our Dataform tutorial series, we delve into creating and running your first SQL model for GA4 BigQuery export data. This stage is pivotal for effectively utilizing your GA4 data in Dataform.

We guide you in setting up your model by creating a new 'staging' directory, craft a .sqlx file and run the SQL workflow for the first time. The end result is the automatic creation of a table in BigQuery, which contains a selection of recent GA4 events.

In the folder declarations we create another folder, next to source, called staging.

Every SQL workflow is comprised of various stages between the data source(s), in our case the GA4 export data, and a tool that consumes the results of our workflow, let's say a Looker Studio report.

GA4 data > staging > intermediate > mart > Looker Studio report

Opinions on best practices and naming may vary, but typically, it is advisable to have a phase where you perform basic transformations on top of your source data, avoiding any aggregations and joins at this point. This phase is what we refer to as staging in these tutorials.

Add the directory path definitions/staging and click Create directory.

Then create a new file in the staging folder: staging_events. The file path becomes definitions/staging/staging_events.sqlx. Click Create file.

Now you are looking at an empty .sqlx file.

Every .sqlx file starts with a config block, information that is written between {}. It is a good practice to always provide a table type, which can be table, view or incremental (we will explore this later).

Let's go for a 'normal' table this time:

config {
  type: "table"

In the config block above we intentionally left out the closing }. As you can see Dataform is constantly checking if the syntax is valid. In our case this is not the case, and it also indicates which files have an issue.