Guides to build your Dataform project.

Build your Dataform project

Learn how to configure, publish and document data tables in your warehouse.

Publish data tables and views

Learn how to publish tables and views in your warehouse.

Publish datasets

![Publishing tables](/static/images/publishing_tables.png)

## Create a view

Create a new `example.sqlx` file in your project under the `definitions/` folder:

```js
// example.sqlx
config { type: "view" }
SELECT 1 AS TEST
```

Upon running this query a **view** called `schema.example` will be created (where `schema` is the Dataform schema defined in your [`dataform.json`](../configuration#dataform.json) file).

Note that non-SQL statements are stripped during query compilation, so the final executable query in this case would be:

```js
SELECT 1 AS TEST
```

For a list of all configuration options for datasets, view the [`ITableConfig`](/reference#ITableConfig) reference documentation.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  Trailing semi-colons should be omitted from queries.
</div>

## Create a table

To create a table, i.e. a full independent copy of the query result, set the `type` of the query to `table`.

```js
config { type: "table" }
SELECT 1 AS TEST
```

For a list of all configuration options for datasets, view the [`ITableConfig`](/reference#ITableConfig) reference documentation.

## Referencing other datasets

Dataform provides methods that enable you to easily reference another dataset in your project without having to provide the full SQL dataset name.


```js
// definitions/source.sqlx
SELECT 1 AS sourcedata
```
<br />

```js
// definitions/ref_example.sqlx
SELECT * FROM ${ref("source")}
```

In order to reference the dataset created by a `source.sqlx` file, the value that should be passed to the `ref()` function is `"source"`, i.e. the name of the file defining the dataset (_without_ the file extension).

<div className="bp3-callout bp3-icon-info-sign bp3-intent-primary" markdown="1">
<h4 class="bp3-heading">The <code>ref()</code> function</h4>
The use the <code>ref()</code> function, pass it the database, schema, and name of the dataset you're referencing. Database and schema are optional: you'll only need to specify these if there are multiple datasets in your project with the same name.
Read more about the <code>ref()</code> function [here](/reference#ICommonContext).
</a></div>

The query will be compiled into the following SQL before it is run:

```js
SELECT * FROM "dataform_schema"."source"
```

Any dataset that is referenced by a query will automatically be added to that query's dependencies. Dependency queries are always executed before dependent queries to ensure pipeline correctness.

## Adding custom dependencies

If you want to manually add a dependency to a query - one that is not already explicitly referenced with `ref()` - you should configure the file's `dependencies`:

```js
config { dependencies: [ "some_table" ] }
SELECT * FROM ...
```

Multiple dependencies may be provided in a single invocation:

```js
config { dependencies: [ "some_table", "some_other_table" ] }
SELECT * FROM ...
```

## Disable a dataset

To stop a query being run, you can disable it. This will keep the dataset as part of your graph, but stop it from executing when you run your project.
This can be useful for example if the relevant query breaks for some reason and you don't want your pipeline to fail while it's being fixed.

```js
config { disabled: true}
SELECT * FROM ...
```

## Executing statements before or after dataset creation

You can specify `pre_operations { ... }` and `post_operations { ... }` to configure Dataform to execute one or more SQL statements before or after creating a dataset.

### Example: Granting dataset access with `post_operations`

The following example defines a post-query operation to configure dataset access permissions. It makes use of the built-in `self()` method which returns the fully-qualified name of the current dataset.

```js
SELECT * FROM ...

post_operations {
  GRANT SELECT ON ${self()} TO GROUP "allusers@dataform.co"
}
```

To specify multiple operations, separate them with `---` (using `---` to separate statements in pre- and post-operations ensures compatibility across all DB engines, while `;` only works with engines that support multiple statements in a single query):

```js
post_operations {
  GRANT SELECT ON ${self()} TO GROUP "allusers@dataform.co"
  ---
  GRANT SELECT ON ${self()} TO GROUP "otherusers@dataform.co"
}
```

## Overriding a dataset's schema or name

By default, a dataset's schema is set to the default schema chosen when initializing a project, usually `dataform`, and a dataset's name is set to the name of the corresponding file.
This can be overridden on a per-file basis by specifying the `schema` and/or `name` options. For example, to create a dataset called `example` in the schema `other_schema`:

```js
config {
  schema: "other_schema",
  name: "example"
}
SELECT * FROM ...
```

To reference datasets with overridden schemas, use the fully qualified name of the dataset:

```js
SELECT * FROM ${ref("example")}
```

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  If a dataset's <code>name</code> is overridden, you should use that name when calling the
  <code>ref()</code> function. For example, a dataset with
  <code>config &#123; name: "overridden_name" &#125;</code> would be referenced using
  <code>ref("overridden_name")</code>.
</div>

## Warehouse specific configuration

### BigQuery

For more information on configuring BigQuery datasets, such as enabling dataset partitioning, check out the [BigQuery guide](/warehouses/bigquery).

### Redshift

For more information on configuring Redshift datasets, such as sort keys and dist keys, check out the [Redshift guide](/warehouses/redshift).

### SQL Data Warehouse

For more information on configuring SQL Data Warehouse datasets, such as distribution settings, check out the [SQL Data Warehouse guide](/warehouses/sqldatawarehouse).


publish

Learn how to configure tables that update incrementally.

Incremental datasets

## Introduction

Incremental datasets aren't rebuilt from scratch every time they run. Instead, only new rows are inserted (or merged) into the dataset according to the conditions you provide when configuring the dataset.

Dataform takes care of managing state, creating datasets, and generating `INSERT` (or `MERGE`) statements for you.

### Example: Append only for performance

Web logs or analytics data are great use cases for incremental datasets. For these kinds of data sources you usually only want to process new records instead of having to reprocess old data.

### Example: Micro-batching for latency

A major benefit of incremental datasets is that pipelines complete more quickly and can therefore be run more frequently at lower cost, reducing downstream latency to consumers of these datasets.

### Example: Creating daily snapshots

A common incremental dataset use case is to create daily snapshots of an input dataset, for example to use in longitudinal analysis of user settings stored in a production database.

## Key concepts

Incremental datasets are inherently stateful and make use of `INSERT` statements. Thus, care must be taken to ensure that data is not erased or duplicated during insertion of new rows.

There are 2 parts to an incremental dataset that you must configure:

- The main query that selects all rows
- A `WHERE` clause that determines which subset of rows should be processed for each incremental run

## WHERE clause

The `WHERE` clause is applied to the main query and is used to ensure that only new data is added to the incremental dataset. For example:

```js
ts > (SELECT MAX(timestamp) FROM target_table)
```

## A simple example

Assuming there is a source dataset containing timestamped user action events called `weblogs.user_actions` which is streamed into our warehouse via some other data integration tool:

<table className="bp3-html-table bp3-html-table-striped .modifier" style="width: 100%;">
  <thead>
    <tr>
      <th>timestamp</th>
      <th>user_id</th>
      <th>action</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1502920304</td>
      <td>03940</td>
      <td>create_project</td>
    </tr>
    <tr>
      <td>1502930293</td>
      <td>20492</td>
      <td>logout</td>
    </tr>
    <tr>
      <td>1502940292</td>
      <td>30920</td>
      <td>login</td>
    </tr>
    <tr>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
  </tbody>
</table>

To make a simple incremental copy of this dataset, create a new file called `definitions/example_incremental.sqlx`:

```js
config { type: "incremental" }

SELECT timestamp, action
FROM weblogs.user_actions

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

First the script sets the type of the dataset to `incremental`.

It then specifies a `WHERE` clause using the `when()` and `incremental()` functions:

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

This ensures that only rows from the source dataset with a <b>timestamp greater than the latest timestamp that has been processed so far</b> are selected in the incremental query.

Note that `self()` is used here in order to get the name of the current dataset. Thus the compiled `WHERE` clause will be expanded to:

```sql
timestamp > (SELECT MAX(timestamp) FROM default_schema.example_incremental)
```
<br />

<div className="bp3-callout bp3-icon-info-sign" markdown="1">
  Note that for BigQuery, incremental tables need to be written slightly differently to avoid full table scans. See our [BigQuery specific guide](/warehouses/bigquery#optimizing-partitioned-incremental-tables-for-bigquery) for more info.
</div>

This dataset may not exist in the warehouse yet. That's OK, because the `WHERE` clause will only be added to the final query if the dataset already exists and new data is being inserted into it.

<div className="bp3-callout bp3-icon-info-sign" markdown="1">
  Note that when data is inserted into an incremental dataset, only fields that already exist in the
  dataset will be written. To make sure that new fields are written after changing the query, the
  dataset must be rebuilt from scratch with the <code>--full-refresh</code> option (if using the
  Dataform tool on the command line) or with the <code>Run with full refresh</code> option in
  Dataform Web.
</div>

### Generated SQL

The SQL generated by the above example will depend on the warehouse type, but generally follow the same format.

If the dataset doesn't exist yet:

```js
CREATE OR REPLACE TABLE default_schema.example_incremental AS
  SELECT timestamp, action
  FROM weblogs.user_actions;
```

When incrementally inserting new rows:

```js
INSERT INTO default_schema.example_incremental (timestamp, action)
  SELECT timestamp, user_action
  FROM weblogs.user_actions
  WHERE timestamp > (SELECT MAX(timestamp) FROM default_schema.example_incremental)
```

If no unique key is specified, then the merge condition (`T.user_id = S.user_id` in this example) is set as `false`, causing rows to always be inserted rather than merged.

## A merge example

<div className="bp3-callout bp3-icon-info-sign" markdown="1">
  Incremental merging requires <code>@dataform/core</code> version <code>1.5.0</code>.<br>
  Incremental merging is not current supported for Azure SQLDataWarehouse.
</div>

If you want to ensure that the output table only ever contains one row per some combination of key columns, you should specify a `uniqueKey`. When `uniqueKey` is specified, if a row arrives whose key matches an existing row's key, then the existing row is overwritten with the new data.

```sql
config {
  type: "incremental",
  uniqueKey: ["transaction_id"]
}

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

If using `uniqueKey` with **BigQuery**, we recommend that you set an `updatePartitionFilter` to only consider a subset of records. This helps to optimize costs, since without an `updatePartitionFilter`, BigQuery will scan the whole table to find matching rows.

```sql
config {
  type: "incremental",
  uniqueKey: ["transaction_id"],
  bigquery: {
    partitionBy: "DATE(timestamp)",
    updatePartitionFilter:
        "timestamp >= timestamp_sub(current_timestamp(), interval 24 hour)"
  }
}

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

### Generated SQL

As with above, the SQL generated by the above example will depend on the warehouse type, but generally follow the same format.

If the dataset doesn't exist yet:

```js
CREATE OR REPLACE TABLE default_schema.example_incremental PARTITION BY Date(timestamp) AS
  SELECT timestamp, action
  FROM weblogs.user_actions;
```

When incrementally merging new rows:

```js
MERGE default_schema.example_incremental T
USING (
  SELECT timestamp, action
  FROM weblogs.user_actions
  WHERE timestamp > (SELECT MAX(timestamp) FROM default_schema.example_incremental) S
ON T.user_id = S.user_id AND T.action = S.action AND timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR)
WHEN MATCHED THEN
  UPDATE SET timestamp = S.timestamp, user_id = S.user_id, action = S.action
WHEN NOT MATCHED THEN
  INSERT (timestamp, user_id, action) VALUES (timestamp, user_id, action)
```

## Daily snapshots with incremental datasets

Incremental datasets can be used to create a daily snapshot of mutable external datasets.

Assuming an external dataset called `productiondb.customers`, you could write the following incremental to create a daily snapshot of that data:

```js
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()})`) }
```

- By selecting the current date as `snapshot_date`, this effectively appends a dated snapshot of the `productiondb.customers` dataset to the output dataset each day.
- The `WHERE` clause here prevents more than one insertion per day (which could result in duplicate rows in the output dataset), since any newly inserted rows must have a later `snapshot_date`
  than any previously existing row in the output.
- This Dataform project should be scheduled to run at least once a day.

## Protecting incremental datasets from data loss

It's possible to force an incremental dataset to be rebuilt from scratch using either the command line interface with the <code>--full-refresh</code> option or with the <code>Run with full refresh</code> option in Dataform Web.

If you need to protect a dataset from ever being rebuilt from scratch, for example if the source data is only temporary, you can mark an incremental dataset as `protected`.
This means that Dataform will never delete this dataset, even if a user requests a full refresh.

`definitions/incremental_example_protected.sqlx`:

```js
config {
  type: "incremental",
  protected: true
}
SELECT ...
```

## Table partitioning / dist keys

Since incremental datasets are usually timestamp based, it's a best practice to set up dataset partioning on the timestamp field to speed up downstream queries.

For more information, check out the [BigQuery](/warehouses/bigquery) and [Redshift](/warehouses/redshift) guides.


incremental

Learn how to add data documentation for your tables and views.

Document datasets

## Introduction

Dataform allows you to add documentation to the datasets defined in your project.

## Adding table and field descriptions

Table and field descriptions are added using the config block.

```js
config {
  type: "table",
  description: "This table defines something",
  columns: {
    column1: "A test column",
    column2: "A test column",
    record1: {
      description: "A struct",
      columns: {
        column3: "A nested column"
      }
    }
  }
}

```

Alternatively you can use the JavaScript API:

```js
publish("table1", {
  type: "table",
  description: "This table defines something",
  columns: {
    column1: "A test column",
    column2: "A test column",
    record1: {
      description: "A struct",
      columns: {
        column3: "A nested column",
      },
    },
  },
});
```


documentation

datasets

Learn about the structure and features of SQLX files.

SQLX

## Overview

SQLX is a powerful extension of SQL. As it is an extension, **every SQL file is also a valid SQLX file**!

<div className="bp3-callout bp3-icon-info-sign bp3-intent-primary" >
<h4 class="bp3-heading">New to SQLX and Dataform?</h4>
<p>
If you are new to SQLX and Dataform, reading the introduction could be helpful to understand SQLX, its features, and how it works.
</p>
<a href="/introduction/dataform-in-5-minutes"><button intent="primary">How SQLX works in 5 min</button></a></div>

## Structure

SQLX contains the following components:

- [Config](#config): contains information on the purpose of the script, such as `view` or `table`.

- [SQL](#sql): The SQL operation(s) to be performed. [In-line JavaScript](#in-line-javascript) or [built-in functions](#built-in-functions) can be injected here.

- [JavaScript](#javascript) (and in-line JavaScript): These provide all the incredible functionality of JavaScript written alongside SQL!

- [Built-in functions](#built-in-functions): There are various useful built-in functions that can be used, such as `ref()` or `self()`.

### Config

All config properties, and the config itself, are optional. See [`ITableConfig` in the API reference](/reference#ITableConfig) for exact options.

### SQL

Anything written outside of control blocks (`{}`) is interpreted as SQL. Therefore to start an SQL block, just close off any prior blocks.

### JavaScript

JavaScript can be used within SQLX via a JavaScript block, which can then be injected into the SQL using in-line JavaScript in order to dynamically modify the query.

For example:

```SQL
js {
  const example = "foo";
}

SELECT * FROM ${example}
```

#### JavaScript Blocks

JavaScript blocks are defined in SQLX by writing `js { }`.

JavaScript blocks in SQLX can be used for defining reusable functions that can be used to generate repetitive parts of SQL code.

#### In-line JavaScript

In-line JavaScript can be used anywhere SQL is written in order to dynamically modify the query. It is injected by using `${}`, for example `${console.log("foo")}`.

### Built-in functions

Built in functions have special functionality and can be executed either within [in-line JavaScript](#in-line-javascript) or [JavaScript blocks](#javascript-blocks).

For all built in functions, see [`ITableContext` in the API reference](/reference#ITableContext). Some useful examples can be found here:

#### ref()

`ref()` enables you to easily reference another dataset in your project without having to provide the full SQL dataset name. `ref()` also adds the referenced dataset to the set of dependencies for the query.

Some examples can be found [here](datasets#referencing-other-datasets).

#### resolve()

`resolve()` works similarly to `ref()`, but doesn't add the dataset to the dependency list for the query.

#### self()

`self()` returns the fully qualified name (database, schema and name) of the current dataset. If the database, schema, or dataset name is overridden in the `config{}` block, `self()` will return the full and correct dataset name.

#### name()

`name()` returns the name of the current dataset.

[Here](incremental-datasets/#a-simple-example) is an example of an incremental table using the `self()` function.

- [Retrieve the name of the current dataset with `self()`](incremental-datasets#a-simple-example).

- [Execute code only if the script is for an incremental dataset using `incremental()`](incremental-datasets#conditional-code-if-incremental).

## Additional Features

- **Pre-operations**: defined in SQLX by writing `pre_operations { }`, SQL written inside will be executed before the main SQL. This can be useful for granting permissions, as can be seen in the [publishing datasets guide](datasets#example-granting-dataset-access-with-post_operations). **Actions may only include pre_operations if they create a dataset**, for example with `type: "table"` or `type: "view"` or `type: "incremental"` in their config.

- **Post-operations**: the same as pre-operations, but defined with `post_operations { }`, and runs after the main SQL.

BigQuery and SQL Data Warehouse run all operations for a file (such as pre and post operations) in the same context; the executed SQL is created by joining all operations with a semi-colon `;`. For other warehouse types, operations are run as separate queries.

This is useful for scripting, for example defining variables or UDFs in BigQuery before the create table statement.

## Sandboxing for fast, secure, reproducible SQLX compilation

Dataform executes all JavaScript and SQLX code in a Dataform project inside a sandboxed environment that has no access to the database, data, or network.

While this can be limiting in some cases, there are many benefits to this approach.

#### Speed

Dataform compilation is extremely fast, and can compile projects with hundreds of datasets and tens of thousands of lines of code in under a second. This is useful for development where you can immediately see what your code will do without waiting.

#### Reproducibility

Changing your generated SQL queries based on data in the warehouse can lead to pipelines breaking even when you haven't changed code. This can be frustrating to debug, and means table structures can change for downstream consumers without notice. With Dataform and SQLX, your build steps and generated table schematas are generally fixed, making debugging and reproducibility much easier.

#### Security

Third party code (such as dataform packages) shouldn't be able to read any of your data. As all project code runs in the sandbox, there is no way to read and leak your data, instead the worst a malicious package could do is cause SQL queries to execute in your warehouse.


sqlx

Learn how to test data quality with assertions.

Test data quality with assertions

## Introduction

Assertions enable you to check the state of data produced by other actions.

An assertion query is written to find rows that violate one or more rules. If the query returns any rows, then the assertion will fail.

![Assertions](/static/images/assertions.png)

## Auto-generated assertions

<div className="bp3-callout bp3-icon-info-sign" markdown="1">
  Auto generated assertions are only supported from <code>@dataform/core</code> version <code>1.4.13</code>.
</div>

Dataform provides a convenient way to define assertions as part of a dataset's `config` settings.

Here's a complete example:

```js
// definitions/my_table.sqlx
config {
  type: "table",
  assertions: {
    uniqueKey: ["user_id"],
    nonNull: ["user_id", "customer_id"],
    rowConditions: [
      'signup_date is null or signup_date > "2019-01-01"',
      'email like "%@%.%"'
    ]
  }
}
select ...
```

For all configuration options, view the [reference documentation for dataset assertions](/reference#ITableAssertions).

## Unique keys

If the `uniqueKey` property is set, the resulting assertion will fail if there is more than one row in the dataset with the same values for all of the column(s).

```js
config {
  type: "table",
  assertions: {
    uniqueKey: ["user_id"]
  }
}
select ...
```

Multiple key columns can be provided.

The generated assertion will be called `<original_dataset_name>_assertions_uniqueKey`.

## Non-nullness

To assert that a set of columns are never null, provide an array of the column names with the `nonNull` property:

```js
config {
  type: "table",
  assertions: {
    nonNull: ["user_id", "customer_id", "email"]
  }
}
select ...
```

The generated assertion will be called `<original_dataset_name>_assertions_rowConditions`, because `nonNull` is just shorthand for custom row conditions (see below).

## Custom row conditions

For assertions that require custom logic to be evaluated against rows, use the `rowConditions` property. Each row condition should be a SQL expression that is expected to evaluate to `true` if the assertion should pass.

```js
config {
  type: "table",
  assertions: {
    rowConditions: [
      'signup_date is null or signup_date > "2019-01-01"',
      'email like "%@%.%"'
    ]
  }
}
select ...
```

Each row will be evaluated against each condition, and all rows must pass all conditions for the assertion to pass.

The generated assertion will be called: `<original_dataset_name>_assertions_rowConditions`.

## Manual assertions

Assertions can also be defined manually for more advanced use cases, or for testing datasets that aren't created by Dataform.

To write a manual assertion, create a new SQLX file and set the type to `assertion` in the `config` block:

```js
// definitions/custom_assertion.sqlx
config {
  type: "assertion"
}
```

To write the assertion, write a new SQL query below the config block that should return zero rows. The best way to think about writing this queries is that they are **queries that look for errors**.

For example, to assert that fields `a`, `b`, and `c` are never `NULL` in a dataset named `sometable`, create a file `definitions/assert_sometable_not_null.sqlx`:

```js
// definitions/assert_sometable_not_null.sqlx
config { type: "assertion" }

SELECT
  *
FROM
  ${ref("sometable")}
WHERE
  a IS NULL
  OR b IS NULL
  OR c IS NULL
```

Another common requirement is to check that all values for a particular field or combination of fields are unique in a dataset.
For example, in a `daily_customer_stats` dataset, there should only ever be a single row for each combination of the `date` and `customer_id` fields.

You can assert these requirements as follows:

```js
config { type: "assertion" }

WITH base AS (
SELECT
  date,
  customer_id,
  SUM(1) as rows
FROM ${ref("daily_customer_stats")}
)
SELECT * FROM base WHERE rows > 1
```

This query will find any keys where there exists more than 1 row for that key (and thus are not unique).

## Inspecting failed assertion rows

Dataform automatically creates a view in your warehouse containing the results of the compiled assertion query.
This makes it easy to inspect the rows that caused the assertion to fail without increasing storage requirements or pulling any data out of your warehouse.

Assertions create views in a seperate schema to your default schema. This is configured in your [`dataform.json`](configuration) file.

Given a default assertion schema `dataform_assertions` and an assertion file called `definitions/some_assertion.sqlx`:

```js
config { type: "assertion" }
SELECT * FROM ${ref("example")} WHERE test > 1
```

Dataform will create a view called `dataform_assertions.some_assertion` in your warehouse using the following query:

```js
CREATE OR REPLACE VIEW dataform_assertions.some_assertion AS SELECT * from dataform.example WHERE test > 1
```

You can manually inspect this view to debug failing assertions.

## Assertions as dependencies

Assertions create named actions in your project that can be depended upon by using the `dependencies` configuration parameter.

If you would like another dataset, assertion, or operation to only run if a specific assertion passes, you can add the assertion to that action's dependencies.

For example, given two datasets called `table1` and `table2`, and an assertion called `table1_not_null`, if you want to ensure that `table2` only
runs if `table1_not_null` passes, you could add it as a dependency in `definitions/table2.sqlx`:

```js
// definitions/table2.sqlx
config {
  type: "view",
  dependencies: [ "table1_not_null" ]
}

SELECT * FROM ${ref("table1")} LEFT JOIN ...
```


assertions

Learn how to declare external datasets with declarations.

Declare external datasets with declarations

## Introduction

Your Dataform code will likely depend on at least one external (i.e. not produced by Dataform) dataset.
Declarations enable you to treat these external datasets as first-class Dataform objects.

## Example

Suppose we have an external dataset `input.data`.

To declare this dataset you can use SQLX:

```js
config {
  type: "declaration",
  schema: "input",
  name: "data"
}
```

Alternatively you can use the JavaScript API and add multiple declarations in a single file:

```js
declare({
  schema: "input",
  name: "foo",
});

declare({
  schema: "input",
  name: "bar",
});
```

## Dependencies on declared datasets

Once declared, a dataset can be referenced or resolved in the same way as any other dataset in Dataform.

Assuming the following declaration of the `input.data` dataset:

```js
config {
  type: "declaration",
  schema: "input",
  name: "data"
}
```

Other actions may now use the `ref()` or `resolve()` functions as usual, e.g. `ref("data")` or `resolve({schema: "input", name: "data"})`.


declarations

Learn how to define custom SQL operations in Dataform.

Write custom SQL operations

## Introduction

An `operations` file defines a set of SQL commands that will be executed in order against your warehouse. Operations can be used to run custom SQL that doesn't necessarily fit into the model of publishing a dataset or writing an assertion.

## Simple example

To define a new `operations` action, create a file such as `definitions/example_operation.sqlx`:

```js
// definitions/example_operation.sqlx
CREATE OR REPLACE VIEW someschema.someview AS (SELECT 1 AS test)
```

Multiple statements can be separated with a single line containing only 3 dashes `---`:

```js
CREATE OR REPLACE VIEW someschema.someview AS (SELECT 1 AS test)
---
DROP VIEW IF EXISTS someschema.someview
```

These statements will be run without modification against the warehouse. You can use warehouse specific commands in these files, such as BigQuery's DML or DDL statements, or Redshift/Postgres specific commands.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-none" markdown="1">
  BigQuery and SQL Data Warehouse run all operations for a file in the same context; the executed SQL is created by joining all operations with a semi-colon <code>;</code>.
  For other warehouse types, operations are run as separate queries.
</div>

## Custom dataset builds

In some cases, you may want to create a dataset manually rather than relying on Dataform's built-in logic. This can be achieved by writing an operations file and specifying the full `CREATE TABLE/VIEW` commands manually:

```js
CREATE OR REPLACE VIEW sometable.someschema AS (SELECT 1 AS TEST)
```

## Declaring and referencing outputs

If an operation creates a dataset or view that you would like to make available to other scripts, you can reference this operation as you would a normal dataset by using the `ref()` function. Note that operations may use `self()` to create a dataset or view that matches the current file name.

For example, in `definitions/custom_view.sqlx`:

```js
// definitions/custom_view.sqlx
config { hasOutput: true }
CREATE OR REPLACE VIEW ${self()} AS (SELECT 1 AS TEST)
```

References to `"customview"` will now resolve to `"defaultschema.customview"` and can be used in other SQL files, for example:

```js
SELECT * FROM ${ref("customview")}
```

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  The output dataset created by the operation must match the name of the file in order for
  references to work properly. We recommend using <code>self()</code> to enforce this requirement.
  If you would like to create a dataset in a custom schema, or override the dataset's name, use the{" "}
  <code>schema</code> or <code>name</code> configuration settings.
</div>

## Example: Running `VACUUM` commands (in Postgres or Redshift)

Postgres and Redshift have a `VACUUM` command that can be used to improve the performance of some datasets. This is a common use case for operations:

```js
VACUUM DELETE ONLY ${ref("sometable")} TO 75 PERCENT
---
VACUUM REINDEX ${ref("sometable")}
```


operations

Learn how to configure your Dataform project.

Configure your project

## Introduction

A Dataform project is primarily configured through the `dataform.json` file that is created at the top level of your project directory.

In addition, `package.json` is used to control NPM dependency versions, including the current Dataform version.

## dataform.json

This file contains information about the project. These settings, such as the warehouse type, default schema names, and so on, are used to compile final SQL.

The following is an example of the `dataform.json` file for a BigQuery project:

```json
// dataform.json
{
  "warehouse": "bigquery",
  "defaultDatabase": "my-gcp-project-id",
  "defaultSchema": "dataform",
  "assertionsSchema": "dataform_assertions"
}
```

All of these configuration settings are accessible in your project code as properties of the `dataform.projectConfig` object. For example:

```js
// definitions/my_view.sqlx
config { type: "view" }
select ${when(
  dataform.projectConfig.warehouse === "bigquery",
  "warehouse is set to bigquery!",
  "warehouse is not set to bigquery!"
)}
```

### Configure default schema names

Dataform aims to create all objects under a single schema (or dataset in BigQuery) in your warehouse. This is usually called `dataform` but can be changed
by changing the `defaultSchema` property to some other value. For example, to change it to `mytables`, update the configuration file as following:

```json
// dataform.json
{
  ...
  "defaultSchema": "mytables",
  ...
}
```

### Configure custom compilation variables

You may inject custom variables into project compilation:

```json
// dataform.json
{
  ...
  "vars": {
    "myVariableName": "myVariableValue"
  },
  ...
}
```

As with project configuration settings, you can access these in your project code. For example:

```js
// definitions/my_view.sqlx
config { type: "view" }
select ${when(
  dataform.projectConfig.vars.myVariableName === "myVariableValue",
  "myVariableName is set to myVariableValue!",
  "myVariableName is not set to myVariableValue!"
)}
```

### Control query concurrency

Dataform executes as many queries as possible in parallel, using per-warehouse default query concurrency limits. If you would like to limit the number of queries that may run concurrently during the course of a Dataform run, you can set the `concurrentQueryLimit` property:

```json
// dataform.json
{
  ...
  "concurrentQueryLimit": 10,
  ...
}
```

## package.json

This is a standard NPM package file which may be used to include JavaScript packages within your project.

Most importantly, your Dataform version is specified here, and can be updated by changing this file or running the `npm install` or `npm update` commands inside your project directory.

If you develop projects on <a target="_blank" rel="noopener" href="https://dataform.co">Dataform Web</a>, this is managed for you and can be largely ignored.

### Updating Dataform to the latest version

All Dataform projects depend on the `@dataform/core` NPM package. If you are developing your project locally and would like to upgrade your Dataform version, run the following command:

```bash
npm update @dataform/core
```

If you use the `dataform` command line tool, you may also wish to upgrade your globally installed Dataform version:

```bash
npm update -g @dataform/cli
```


configuration

Learn how you can use JavaScript to re-use code across your scripts and define several actions.

Power your code with JavaScript

Learn how to re-use code across your project with includes.

Re-use code with includes

## Introduction

JavaScript files can be added to the `includes/` folder to define simple scripts, constants or macros that can be reused across your project.
Each file in the includes folder will be made available for use within your other SQL or JavaScript files.

It's also possible to add JavaScript to a `.sqlx` file by wrapping it in a `js {...}` block.
Note: Functions, constants or macros defined in this way will only be available within the .sqlx file they are defined in, not across the whole project.

If you are new to JavaScript, the examples below should cover some common use cases.
<a target="_blank" rel="noopener" href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/A_re-introduction_to_JavaScript">MDN</a> is a useful learning resource if you'd like to learn more.

## Example: Defining and using constants

Create a new file in your project under the `includes/` folder, such as:

```js
// includes/constants.js
const PROJECT_ID = "my_project_name";
module.exports = { PROJECT_ID };
```
<br />
<callout intent="warning">
  Note that in order to use functions or constants elsewhere in the project, they must be exported
  using the <code>module.exports = {"{}"}</code> syntax.
</callout>

## Example: Using an include

You can reference any `include` function, constant, or macro by using its file name without the `.js` extension, followed by the name of the exported function or constant.

For example, to reference the constant `PROJECT_ID` in the file `includes/constants.js`:

```js
// definitions/query.sqlx
SELECT * FROM ${constants.PROJECT_ID}.my_schema_name.my_table_name
```

The query will be compiled into the following SQL before it is run:

```js
SELECT * FROM my_project_name.my_schema_name.my_table_name
```

## Example: Writing functions

Functions enable you to reuse the same block of SQL logic across many different scripts. Functions take 0 or more named parameters and must return a string.

In the example below, the function `countryGroup()` takes as input the name of the country code field and returns a CASE statement that maps country codes to country groups.

```js
// includes/country_mapping.js
function countryGroup(countryCodeField) {
  return `CASE
          WHEN ${countryCodeField} IN ("US", "CA") THEN "NA"
          WHEN ${countryCodeField} IN ("GB", "FR", "DE", "IT", "PL") THEN "EU"
          WHEN ${countryCodeField} IN ("AU") THEN ${countryCodeField}
          ELSE "Other countries"
          END`;
}

module.exports = { countryGroup };
```

This function can be used in a SQLX file:

```js
// definitions/revenue_by_country_group.sqlx
SELECT
  ${country_mapping.countryGroup("country_code")} AS country_group,
  SUM(revenue) AS revenue
FROM my_schema.revenue_by_country
GROUP BY 1

```

The query will be compiled into the following SQL before it is run:

```js
SELECT
  CASE
    WHEN country_code IN ("US", "CA") THEN "NA"
    WHEN country_code IN ("GB", "FR", "DE", "IT", "PL") THEN "EU"
    WHEN country_code IN ("AU") THEN country_code
    ELSE "Other countries"
  END AS country_group,
  SUM(revenue) AS revenue
FROM my_schema.revenue_by_country
GROUP BY 1
```

## Example: An include with parameters: groupBy

The example below defines a `groupBy()` function that takes as input a number of fields to group by and generates a corresponding `GROUP BY` statement:

```js
// includes/utils.js
function groupBy(n) {
  var indices = [];
  for (var i = 1; i <= n; i++) {
    indices.push(i);
  }
  return `GROUP BY ${indices.join(", ")}`;
}

module.exports = { groupBy };
```

This function can be used in a SQL query `definitions/example.sqlx`:

```js
SELECT field1,
       field2,
       field3,
       field4,
       field5,
       SUM(revenue) AS revenue
FROM my_schema.my_table
${utils.groupBy(5)}
```

The query will be compiled into the following SQL before it is run:

```js
SELECT field1,
       field2,
       field3,
       field4,
       field5,
       SUM(revenue) AS revenue
FROM my_schema.my_table
GROUP BY 1, 2, 3, 4, 5
```

## Example: Generating queries

Functions can be used to generate entire queries. This is a powerful feature that can be useful if you need to create several datasets which share a similar structure.

The example below includes a function that aggregates all metrics (using `SUM`) and groups by every dimension.

```js
// includes/script_builder.js
function renderScript(table, dimensions, metrics) {
  return `
      SELECT
      ${dimensions.map((field) => `${field} AS ${field}`).join(",\\n")},
      ${metrics.map((field) => `SUM(${field}) AS ${field}`).join(",\\n")}
      FROM ${table}
      GROUP BY ${dimensions.map((field, i) => `${i + 1}`).join(", ")}
    `;
}
module.exports = { renderScript };
```

This function can be used in a SQL query `definitions/stats_per_country_and_device.sqlx`:

```js
${script_builder.renderScript(
  ref("source_table"),
  ["country", "device_type"],
  ["revenue", "pageviews", "sessions"])}

```

Note that calls to functions such as `ref()` should be made in the SQL file itself and passed to the include function so that dependencies are configured correctly.

The query will be compiled into the following SQL before it is run:

```js
SELECT country AS country,
       device_type AS device_type,
       SUM(revenue) AS revenue,
       SUM(pageviews) AS pageviews,
       SUM(sessions) AS sessions
FROM my_schema.source_table
GROUP BY 1, 2
```


includes

Learn how to define several actions in a single Javascript file.

Use JavaScript files

## Introduction

Dataform provides a JavaScript API for defining all actions via code instead of creating individual SQL files for each action. This can be useful in advanced use cases.

To use the JavaScript API, create a `.js` file anywhere in the `definitions/` folder of your project. This code will be run during project compilation.

The JavaScript API exposes functions that create any of the actions that you would otherwise define in a SQL file:

- Datasets
- Declarations
- Assertions
- Operations
- Tests

These are regular JavaScript (ES5) files that can contain arbitrary code: loops, functions, constants, etc.

## Defining actions in JavaScript

The following file is a simple example of creating actions in JavaScript:

`definitions/example.js`:

```js
publish("table1").query("SELECT 1 AS test");

declare({
  schema: "rawdata",
  name: "input1"
});

assert("assertion1").query("SELECT * FROM source_table WHERE value IS NULL");

operate("operation1").queries("INSERT INTO some_table (test) VALUES (2)");

test("test1")
  .dataset("some_table")
  .input("input_data", "SELECT 1 AS test")
  .expect("SELECT 1 AS test");
```

This example creates a dataset, a declaration, an assertion, an operation, and a test in a single file, which if written in SQL would have required 5 seperate files.

## Setting properties on actions

Each of the global methods - `publish()`, `declare()`, `operate()`, `assert()`, and `test()` - return an object that can be used to configure that action. The API follows a builder syntax which can be seen in the following example:

```js
publish("table1")
  .query("SELECT 1 AS test") // Defines the query
  .type("table") // Sets the query's type
  .dependencies(["other_table"]) // Specifies dataset dependencies
  .descriptor({
    test: "Value is 1" // Describes fields in the dataset
  });
```

Multiple configuration properties can also be set using the `config()` method, or alternatively simply passed as a second argument to the method:

```js
publish("table1", {
  type: "table",
  dependencies: ["other_table"],
  descriptor: {
    test: "Value is 1"
  }
});
```

Learn more about configuration options for each type of action:

- [`publish()` (Datasets)](../datasets/publish)
- [`declare()` (Declarations)](../declarations)
- [`assert()` (Assertions)](../assertions)
- [`operate()` (Operations)](../operations)
- [`test()` (Tests)](../tests)

## Using built-in functions

When writing `.sqlx` files, Dataform makes a number of built-in functions such as `ref()` and `self()` available to use within the main query. For example:

```js
// definitions/example.sqlx
config { type: "view" }
SELECT * FROM ${ref("other_table")}
```

The `ref()` function is made available for the script to use automatically. Note that this is not the case when using the JavaScript API.

To use these functions in JavaScript, API methods - such as `query()` - take a `Contextable` argument. Instead of providing a string as the argument to the API method,
you can pass a function whose only parameter is a `context` object. This object exposes the built-in functions for JavaScript code to use.

For example, the above example written in JavaScript (making handy use of JavaScript template strings), in `definitions/example.js`:

```js
publish("example").query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`);
```

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  Note that when using JavaScript template strings - which allow use of the <code>${"{}"}</code>{" "}
  syntax - the string in question must be wrapped in backticks <code>`</code> instead of single{" "}
  <code>'</code> or double <code>"</code> quotes.
</div>

<br />

The following methods and configuration options accept a function taking a `Contextable` argument as in the above example:

- `query()`
- `preOps()`
- `postOps()`

## Referencing includes files

Just like in `.sqlx` files, you can reference any `includes` function, macro or constant inside a `.js` file. However, the syntax differs slightly and varies depending on the `includes` file location.

### Top-Level includes

When importing from a top-level `includes` file, you can simply reference the file name when declaring your variables.

For example, if you would like to reference `SERVICE_NAME` and `SERVICE_ID` from the `includes/service.js` file, you can reference the file name directly. This is because Dataform automatically imports
all top-level `includes` files.

```js
const {SERVICE_NAME, SERVICE_ID} = service;
...
```

### Nested includes

When importing from a nested `includes` file, you must declare these constants using the JavaScript builtin `require` function

For example, if you would like to reference `SERVICE_NAME` and `SERVICE_ID` from the `includes/all_services/service.js` file, you must use `require`.

```js
const {SERVICE_NAME, SERVICE_ID} = require("includes/all_services/service.js");
...
```

Regardless of whether the source file is top-level or nested, once declared, these variables are available without any additional notation (i.e.. you don't need to wrap the constants in `${}`).

## Example: Dynamic dataset generation

One of the most common use cases for using the JavaScript API is to perform a similar action repeatedly.

For example, imagine you have several datasets, all of which have a `user_id` field. Perhaps you would like to create a view of each dataset with certain blacklisted user IDs removed.
You could perform this action across all datasets using a JavaScript `forEach()` statement:

```js
// definitions/blacklist_views.js
const datasetNames = ["user_events", "user_settings", "user_logs"];

datasetNames.forEach(datasetNames => {
  publish(datasetNames + "_blacklist_removed").query(
    ctx => `
      SELECT * FROM ${ctx.ref(tableName)}
      WHERE user_id NOT IN (
        SELECT user_id
        FROM ${ctx.ref("blacklisted_user_ids")}
      )`
  );
});
```

This script would create 3 new datasets as views named `user_events_blacklist_removed`, `user_settings_blacklist_removed`, and `user_logs_blacklist_removed` that don't contain any of the blacklisted user IDs.


js-api

javascript

Learn how to organise your project with tags.

Organise your project with tags

## Introduction

Tags allow you to easily refer to a collection of actions. This can be useful in creating schedules and triggering runs.

## Example

Suppose we would like to add a "daily" tag to a dataset.

To add a tag to a dataset:

```js
config {
  type: "table",
  name: "users",
  tags: ["daily"]
}
```

Alternatively you can use the JavaScript API:

```js
publish("users").query("SELECT ...").tags(["daily"]);
```

Tags can be added to datasets, assertions, operations and declarations.

You can add more than one tag to each action like so:

```js
config {
  type: "view",
  name: "user_counts",
  tags: ["daily", "view"]
}
```

## Using tags

Once you have assigned tags to actions, you can use the tags to define a run or [schedule](../dataform-web/guides/scheduling) . For example, in the schedule creation
screen, simply choose the tags you'd like to include in that schedule.

Tags can also be used with the CLI using the `--tags` argument.


Learn how to run unit tests on your queries.

Run unit tests on your queries

## Introduction

Unit tests give you confidence that your code produces the output data you expect. Unit tests differ from assertions in that assertions are used to check the contents of datasets in your data warehouse,
while unit tests are used to validate your SQL code. Assertions verify _data_, and unit tests verify _logic_.

A SQLX unit test passes fake input to a `table` or `view` query, checking that the output rows match some expected output data.

## Example

Suppose we have the following `view` SQLX query:

```js
config {
  type: "view",
  name: "age_groups"
}
SELECT
  FLOOR(age / 5) * 5 AS age_group,
  COUNT(1) AS user_count
FROM ${ref("ages")}
GROUP BY age_group
```

We might want to write a unit test to check that the `age_groups` query works as we expect it to. Create a file `definitions/test_age_groups.sqlx`:

```js
config {
  type: "test",
  dataset: "age_groups"
}

input "ages" {
  SELECT 15 AS age UNION ALL
  SELECT 21 AS age UNION ALL
  SELECT 24 AS age UNION ALL
  SELECT 34 AS age
}

SELECT 15 AS age_group, 1 AS user_count UNION ALL
SELECT 20 AS age_group, 2 AS user_count UNION ALL
SELECT 30 AS age_group, 1 AS user_count
```

This unit test replaces the `ages` input to the `age_groups` query, and checks that the resulting output rows match the three expected `age_group` rows.

<callout intent="info">
  If you want to replace an input which has been fully-qualified with a schema name (and optionally a database name), you can fully-qualify inputs with the following syntax: <code>input "schemaName", "inputName" { ... }</code>.
</callout>

## Expected vs. actual output equality rules

A unit test fails if the actual output from the dataset is not equal to the expected output. This means that:

- the number of output rows must match
- the number of output columns and their names must match
- the contents of each row must match

Note that unit tests do not fail if columns are not output in the same order.

## Test hermiticity

As with unit testing in other languages and frameworks, it's considered bad practice for a unit test to be non-hermetic. This means that running your test
should have no dependencies on any state in your data warehouse. Thus, for queries you'd like to test, all input datasets should be referenced using `ref()` or `resolve()`. The test then injects the fake inputs
provided for each input dataset.

## Running unit tests

If you use the [Dataform CLI](../dataform-cli), you can run all tests in your project directory with the `dataform test` command.


tests

Configure continuous integration/deployment workflows for your Dataform project.

Configure CI/CD

## Introduction

Continous Integration / Continuous Deployment (CI/CD) workflows help prevent code changes from unintentionally breaking your Dataform project.

Typically, these workflows are configured to run on every commit to your Git repository, automatically checking that the commit doesn't break your code.

## Dataform CLI Docker image

Dataform distributes [a Docker image](https://hub.docker.com/r/dataformco/dataform) which can be used to run the equivalent of [Dataform CLI](/dataform-cli) commands.

For most CI/CD tools, this Docker image is what you'll use to run your automated checks.

## Using GitHub Actions

If you host your Dataform Git repository on GitHub, you can use GitHub Actions to run CI/CD workflows. Read more about configuring workflows with GitHub Actions [here](https://help.github.com/en/actions/configuring-and-managing-workflows/configuring-a-workflow).

GitHub workflows are defined in YAML and must exist in a `.github/workflows` directory in your Git repository. Once the workflow file is added to this directory, GitHub will automatically run it on the events you specify in the workflow configuration.

For example, in a `.github/workflows/dataform.yaml` file:

```yaml
name: CI

on:
  push:
    branches:
      - master
  pull_request:
    branches:
      - master

jobs:
  compile:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code into workspace directory
        uses: actions/checkout@v2
      - name: Install project dependencies
        uses: docker://dataformco/dataform:latest
        with:
          args: install
      - name: Run dataform compile
        uses: docker://dataformco/dataform:latest
        with:
          args: compile
```

This workflow is configured to run on every commit to the `master` branch and for all pull requests to the `master` branch.

It contains three steps:

1. Check out the project code into the working directory
2. Use the Dataform Docker image to run `dataform install`, installing project dependencies into the working directory
3. Use the Dataform Docker image to run `dataform compile`, checking that the project still compiles correctly

If any of these steps fails, the workflow fails. The end result will be made visible in the GitHub UI (including logs to help you understand what, if anything, broke).

### Running commands which require warehouse credentials

You may want to run Dataform CLI commands that require credentials to access your data warehouse (for example, `dataform test` or `dataform run`). The Dataform CLI [expects](/dataform-cli#create-a-credentials-file) credentials to exist in a `.df-credentials.json` file. However, it would be insecure to commit that file to Git.

Fortunately, GitHub Actions have support for "secrets". A GitHub secret is configured in your GitHub project settings. Once configured, you can use this secret to decrypt your warehouse credentials as part of a GitHub Actions workflow.

1. Create your `.df-credentials.json` file by following [these steps](/dataform-cli#create-a-credentials-file)
2. Encrypt your credentials file: `gpg --symmetric --cipher-algo AES256 .df-credentials.json`, using a secret passphrase
3. Commit the encrypted `.df-credentials.json.gpg` file
4. Add the secret passphrase to your GitHub repository as a secret named `CREDENTIALS_GPG_PASSPHRASE`
5. Edit your GitHub Actions workflow file to decrypt the credentials file:

```yaml
name: CI

on:
  push:
    branches:
      - master
  pull_request:
    branches:
      - master

jobs:
  compile:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code into workspace directory
        uses: actions/checkout@v2
      - name: Install NPM dependencies
        uses: docker://dataformco/dataform:1.6.11
        with:
          args: install
      - name: Decrypt dataform credentials
        run: gpg --quiet --batch --yes --decrypt --passphrase="$CREDENTIALS_GPG_PASSPHRASE" --output .df-credentials.json .df-credentials.json.gpg
        env:
          CREDENTIALS_GPG_PASSPHRASE: ${{ secrets.CREDENTIALS_GPG_PASSPHRASE }}
      - name: Execute dataform run
        uses: docker://dataformco/dataform:1.6.11
        with:
          args: run
```

This workflow's final step runs `dataform run`, which uses the decrypted warehouse credentials file.

## Branch protection

CI/CD checks are intended to prevent project breakages before they happen. Therefore, we strongly recommend users to turn on [branch protection](https://help.github.com/en/github/administering-a-repository/about-protected-branches) for the `master` branch. Using branch protection, you can require that all changes to `master` are made through a pull request, and that those pull requests must pass the checks that you specify before they are mergeable.


ci-cd

incremental-datasets

redshift

bigquery

warehouses

guides

Home

Learn the basics of Dataform, how it works, and where it fits in your data stack.

Introduction

An introduction to ELT and where Dataform fits in.

ELT and the modern data stack

modern-data-stack

Learn how Dataform and SQLX can help your team manage data in your warehouse.

SQLX and Dataform in 5 minutes

dataform-in-5-minutes

Learn how Dataform compiles your project and runs it in your warehouse.

How Dataform works

how-dataform-works

introduction

Learn about which data warehouses Dataform can work with and how to configure them.

Supported warehouses

Authentication, configuration options, and content for Snowflake.

Snowflake

snowflake

Authentication, configuration options, and content for BigQuery.

Google BigQuery

Authentication, configuration options, and content for Redshift.

Amazon Redshift

Authentication and configuration options for Azure SQL Data Warehouse.

Azure SQL Data Warehouse

sqldatawarehouse

Authentication and configuration options for Postgres.

Postgres

postgres

Learn how Dataform works with examples projects and scripts.

Example projects and scripts

A list of example projects for Snowflake, BigQuery, and Redshift.

Example projects

A simple project turning Stackoverflow public raw data into reporting tables in a BigQuery data warehouse.

Stackoverflow data on BigQuery

stackoverflow-bigquery

A fictional ecommerce store project on a Snowflake data warehouse.

Ecommerce store on Snowflake

ecommerce-snowflake

A fictional ecommerce store project on a Redshift data warehouse.

Ecommerce store on Redshift

ecommerce-redshift

projects

A list of examples of scripts to use in your Dataform projects.

Example scripts

example-scripts

examples

This tutorial is for people who are new to Dataform and want to be taught how to set up a new project. We will show you how to create your own data model, how to test and document it and how to run schedules on it.

Getting started tutorial

Learn how to create a new BigQuery project and generate warehouse credentials.

Getting set up

set-up

Learn how to connect to a warehouse and create and publish your first dataset.

Building your data model

building-your-data-model

Learn how to use the ref function in Dataform and how to view your project in the Dependency tree.

Managing dependencies

managing-dependencies

Learn how to set up a schedule and alerts in Dataform

Setting up a schedule

scheduling

Learn how to set up data quality tests using assertions and how to document your datasets

Data quality tests and documenting datasets

testing-and-documentation

Learn how to commit changes you've made in your Dataform project

Committing your changes

committing-changes

getting-started-tutorial

Learn how to set up and run your projects in Dataform Web's cloud environment.

Dataform web guides

Tutorials for getting start with Dataform web.

Tutorials

Learn how to create a new dataform web project and connect to your warehouse.

Creating a new project

Start building out your dataform pipelines using SQLX files and managing dependencies.

tutorials

Learn how to use version control in Dataform Web.

Version control

version-control

Learn how to use Git access tokens for connecting to git providers.

Git access tokens

git-access-tokens

Learn how to schedule runs and view logs

Schedule runs

Learn about environments in Dataform web and how to configure them

Environments

environments

Learn about configuring slack notifications with Dataform web

Slack Notifications

slack-notifications

Learn how to use the REST API to control Dataform Web

Use the REST API

Web API Reference

reference

using-the-api

api-reference

set-up-warehouse

dataform-web

Best practices to scale your Dataform project and your analytics

Best practices using Dataform

Principles to start your project on the right foot

First steps with your Dataform project

start-your-dataform-project

Follow best practices to build a project that will scale and maximise productivity