Dataform core sample scripts

This document shows example Dataform core and JavaScript scripts that you can use to create a workflow in Dataform.

Creating tables

Creating a view with Dataform core

The following code sample shows definition of a view called new_view in the definitions/new_view.sqlx file:

config { type: "view" }

SELECT * FROM source_data

Creating a materialized view with Dataform core

The following code sample shows definition of a materialized view called new_materialized_view in the definitions/new_materialized_view.sqlx file:

config {
  type: "view",
  materialized: true
}

SELECT * FROM source_data

Creating a table with Dataform core

The following code sample shows definition of a table called new_table in the definitions/new_table.sqlx file:

config { type: "table" }

SELECT * FROM source_data

Creating an incremental table with Dataform core

The following code sample shows an incremental table that incrementally processes rows of the productiondb.logs table:

config { type: "incremental" }

SELECT timestamp, message FROM ${ref("productiondb", "logs")}

${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Using the ref function to reference tables with Dataform core

The following code sample shows the ref function used to reference the source_data table in the definitions/new_table_with_ref.sqlx table definition file:

config { type: "table" }

SELECT * FROM ${ref("source_data")}

Adding documentation to a table, view, or declaration with Dataform core

The following code sample shows table and columns descriptions in the definitions/documented_table.sqlx table definition file:

config { type: "table",
         description: "This table is an example",
         columns:{
             user_name: "Name of the user",
             user_id: "ID of the user"
      }
  }

SELECT user_name, user_id FROM ${ref("source_data")}

Configuring incremental tables

Adding new table rows for new dates in source data with Dataform core

The following code sample shows a configuration of an incremental table in the definitions/incremental_table.sqlx file. In this configuration, Dataform appends a new row to the incremental_table for each new date:

config { type: "incremental" }

SELECT date(timestamp) AS date, action
FROM weblogs.user_actions

${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)

Taking a snapshot of a table periodically with Dataform core

The following code sample shows a configuration of an incremental table in the definitions/snapshots_table.sqlx file. In this configuration, Dataform creates snapshots_table with a snapshot of productiondb.customers at the specified date:

config { type: "incremental" }

SELECT current_date() AS snapshot_date, customer_id, name, account_settings FROM productiondb.customers

${ when(incremental(), `WHERE snapshot_date > (SELECT max(snapshot_date) FROM ${self()})`) }

Building a rolling 30-days table that updates incrementally with Dataform core

The following code sample shows a configuration of an incremental table in the definitions/incremental_example.sqlx file. In this configuration, Dataform creates a temporary incremental_example that updates incrementally, and deletes the table after 30 days from its creation:

config {type: "incremental"}

post_operations {
  delete FROM ${self()} WHERE date < (date_add(Day, -30, CURRENT_DATE))
}

SELECT
 date(timestamp) AS date,
 order_id,
FROM source_table
  ${ when(incremental(), `WHERE timestamp > (SELECT max(date) FROM ${self()})`) }

Creating custom SQL operations

Running several SQL operations in a SQLX file with Dataform core

The following code sample shows ; used to separate multiple SQL operations defined in definitions/operations.sqlx:

config { type: "operations" }

DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';

Running custom SQL before creating a table with Dataform core

The following code sample shows a custom SQL operation defined in the pre_operations block of the definitions/table_with_preops.sqlx table definition file:

config {type: "table"}

SELECT * FROM ...

pre_operations {
  INSERT INTO table ...
}

Running custom SQL after creating a table with Dataform core

The following code sample shows a custom SQL operation defined in the post_operations block of the definitions/table_with_postops.sqlx table definition file:

config {type: "table"}

SELECT * FROM ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:allusers@example.com", "user:otheruser@example.com"
}

Validating tables

Adding assertions to a table, view, or declaration with Dataform core

The following code sample shows uniqueKey, nonNull, and rowConditions assertions added to the definitions/tested_table.sqlx table definition file:

config {
  type: "table",
  assertions: {
    uniqueKey: ["user_id"],
    nonNull: ["user_id", "customer_id"],
    rowConditions: [
      'signup_date is null or signup_date > "2022-01-01"',
      'email like "%@%.%"'
    ]
  }
}
SELECT ...

Adding a custom assertion with Dataform core

The following code sample shows a custom assertion in a table definition file that validates if columns a, or b, or c from source_data are null:

config { type: "assertion" }

SELECT
  *
FROM
  ${ref("source_data")}
WHERE
  a is null
  or b is null
  or c is null

Developing with JavaScript

Using inline variables and functions with JavaScript

The following code sample shows the foo variable defined in a js block and then used inline in a SQLX file:

js {
 const foo = 1;
 function bar(number){
     return number+1;
 }
}

SELECT
 ${foo} AS one,
 ${bar(foo)} AS two

Generating one table per country with JavaScript

The following code sample shows the use of the forEach function to generate one table per each country defined in countries in the definitions/one_table_per_country.js file:

const countries = ["GB", "US", "FR", "TH", "NG"];

countries.forEach(country => {
  publish("reporting_" + country)
    .dependencies(["source_table"])
    .query(
      ctx => `
      SELECT '${country}' AS country
      `
    );
});

Declaring multiple sources within one file with JavaScript

The following code sample shows declaration of multiple data sources in the definitions/external_dependencies.js file:

declare({
  schema: "stripe",
  name: "charges"
});

declare({
  schema: "shopify",
  name: "orders"
});

declare({
  schema: "salesforce",
  name: "accounts"
});

Declaring multiple sources within one file using forEach

The following code sample shows declaration of multiple data sources with the forEach function in the definitions/external_dependencies.js file:

["charges", "subscriptions", "line_items", "invoices"]
  .forEach(source => declare({
      schema: "stripe",
      name: source
    })
  );

Deleting sensitive information in all tables containing PII with JavaScript

The following code sample shows a function in the definitions/delete_pii.js file that deletes selected information in all tables that contain personal identifiable information (PII):

const pii_tables = ["users", "customers", "leads"];
pii_tables.forEach(table =>
  operate(`gdpr_cleanup: ${table}`,
    ctx => `
      DELETE FROM raw_data.${table}
      WHERE user_id in (SELECT * FROM users_who_requested_deletion)`)
      .tags(["gdpr_deletion"]))
);

Adding preOps and postOps with JavaScript

The following code sample shows the publish function used to create a query with preOps and postOps in the definitions/pre_and_post_ops_example.js table:

publish("example")
  .preOps(ctx => `GRANT \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)
  .query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
  .postOps(ctx => `REVOKE \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)

Creating incremental tables with JavaScript

The following code sample shows the publish function used to create an incremental table in the definitions/incremental_example.js file:

publish("incremental_example", {
  type: "incremental"
}).query(ctx =>