Dataform Web

How to guides

Environments

## Introduction

By default, Dataform runs all of your project code directly off your project's `master` Git branch. Configuring environments allows you to control this behaviour, enabling you to run multiple different versions of your project code.

**An environment is effectively a wrapper around a version of your project code (including the `schedules.json` file)**. Once you have defined an environment, Dataform runs all of the schedules defined inside that environment 'wrapper', using that version of the project code.

A common use-case for environments is to run a staged release process. Code is first run in a `staging` environment, and after being sufficiently tested, is then later pushed to a `production` environment.

<div className="bp3-callout bp3-icon-info-sign" markdown="1">
  If you would like to use environments, please verify that the version of <code>@dataform/core</code> that your project uses is at least <code>1.4.9</code>.
</div>

<div className="bp3-callout bp3-icon-info-sign" markdown="1">
  Environments are an advanced feature. To get the most out of them, the <b>use of an external Git provider is recommended</b>. This makes it easier to view specific commit SHAs, manage Git branches, and control your release process.
</div>

## Configuring environments

Environments are configured in your Dataform project's `environments.json` file.

An environment consists of:

- a name
- a Git reference at which the code should be run (either a branch name or a commit SHA)
- (optionally) overridden values for the project's configuration (the settings in `dataform.json`)

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
If an environment is configured using a branch, that branch must exist on the project's remote Git repository (e.g. GitHub, if the project is stored there). Note that <b>Dataform branches are not remote Git branches.</b>
</div>

A simple example of an `environments.json` file is:

```json
{
  "environments": [
    {
      "name": "staging",
      "gitReference": {
        "commitSha": "67bed6bd4205ce97fa0284086ed70e5bc7f6dd75"
      },
      "configOverride": {
        "defaultDatabase": "dataform_staging"
      }
    }
  ]
}
```

This `staging` environment runs the code (and schedules) at the `67bed6bd4205ce97fa0284086ed70e5bc7f6dd75` Git commit SHA. It also overrides the values of
`defaultDatabase` to isolate `staging` schedule runs from those in other environments.

Note that Dataform uses the `environments.json` file on your `master` branch to determine your project's environments. **Any changes to your environments must be pushed to `master` before Dataform will take note of those changes**.

<div className="bp3-callout bp3-icon-info-sign" markdown="1">
  If your project has a missing or empty <code>environments.json</code> file, Dataform uses a default environment which runs the code on your <code>master</code> branch with no configuration overrides.
</div>

## Example: manage a production release process using environments

More advanced Dataform projects typically have a tightly-controlled `production` environment. All changes to project code go into a `staging` environment which is intentionally kept separate from `production`. Once the code in `staging` has been verified to be sufficiently stable, that version of the code is then pushed into the `production` environment.

Note that, unless you want Dataform to run schedules in `staging`, it may not be a requirement to define an explicit `staging` environment. Instead, you could maintain a single `production` environment and simply run all code under development using the project's default settings (as defined in `dataform.json`).

A clean and simple way to separate staging and production data is to use a different database for each environment. However, this is only supported for BigQuery and Snowflake, so we recommend using per-environment schema suffixes for other warehouse types. The examples below show how to do both by overriding project configuration settings.

### Configuration via commit SHA

In the below example:

- the `production` environment is locked to a specific Git commit

- the `staging` environment runs the project's schedules at the latest version of the project's code (as exists on the `master` branch)

A nice property of this configuration is that any change to the `production` environment leaves an audit trail (by being recorded in your project's Git history).

```json
{
  "environments": [
    {
      "name": "staging",
      "gitReference": {
        "branch": "master"
      },
      "configOverride": {
        "defaultDatabase": "dataform_staging"
      }
    },
    {
      "name": "production",
      "gitReference": {
        "commitSha": "PRODUCTION_GIT_COMMIT_SHA_GOES_HERE"
      },
      "configOverride": {
        "defaultDatabase": "dataform_production"
      }
    }
  ]
}
```

To update the version of the project running in `production`, change the value of `commitSha`, and then push that change to your `master` branch. On GitHub, Git commit SHAs can be found by opening the project page and clicking 'commits'.

<div className="bp3-callout bp3-icon-info-sign" markdown="1">
  <p>A Git branch can be specified instead of a commit SHA: <code>"gitReference": { "branch": "GIT_BRANCH_NAME_GOES_HERE" }</code></p>

<p>
However, we do not recommend using Git branches to manage a <code>production</code> environment:

  <ul>
    <li>Managing merges across Git branches can be tricky and occasionally produces confusing results</li>
    <li>Consequently, the audit trail of changes made to the environment can be harder to follow</li>
  </ul>
</p>
</div>

### Per-environment schema suffixes

An alternative approach to separating production and staging data is to append a suffix to schemas in each environment. For example:

```json
{
  "environments": [
    {
      "name": "staging",
      "gitReference": {
        "branch": "master"
      },
      "configOverride": {
        "schemaSuffix": "_staging"
      }
    },
    {
      "name": "production",
      "gitReference": {
        "commitSha": "PRODUCTION_GIT_COMMIT_SHA_GOES_HERE"
      },
      "configOverride": {
        "schemaSuffix": "_production"
      }
    }
  ]
}
```

## Example: use separate databases for development and production data

Some teams may not be at the stage where they require a `staging` environment, but still would like to keep development and production data separated. This can be done using a `configOverride` in the `production` environment.

In the example below:

- any code deployed during development will use the `defaultDatabase` from the `dataform.json`
- schedules only run in the single `production` environment, and so use the `defaultDatabase` from that environment's `configOverride`
- the `production` environment specifies the `master` Git branch, so all of its schedules will run using that version of the code

`dataform.json`:
```json
{
    "warehouse": "bigquery",
    "defaultSchema": "dataform_data",
    "defaultDatabase": "analytics-development",
}
```


`environments.json`:
```json
 {
    "environments": [
        {
            "name": "production",
            "gitReference": {
                "branch": "master"
            },
            "configOverride": { "defaultDatabase": "analytics-production" }
        }
    ]
}
```

Note, this is only supported for BigQuery and Snowflake. For other warehouses, we recommend overriding schema suffixes instead of `defaultDatabase`.


environments

Git access tokens

Git access tokens are important for Dataform projects linked to remote git providers, such as GitHub. They provide authentication for accessing and controlling the git project.

## User authentication

User access tokens are used to authenticate git access of Dataform projects during project manipulation.

Without a valid access token, users will be unable to do things such as create branches.

They must be configured to have both **read and write access** to your remote repository.

They can be added or removed on the **user settings page**.

## Scheduler authentication

Scheduler access tokens are used to authenticate git access of Dataform projects when running automated schedules.

Without a valid access token, the scheduler will not be able to read the contents of the project and perform the run.

They must have **read** access to your remote repository.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  GitHub doesn't support read-only access to the repo. You should use the <code>repo</code> <a target="_blank" rel="noopener" href="https://developer.github.com/apps/building-oauth-apps/understanding-scopes-for-oauth-apps">scope</a>
</div>

They can be updated on the **project settings page**, within the app.

For projects with only a few collaborators (e.g. 3), it's ok to use a user's personal access token to authenticate the scheduler, but for larger teams a **machine user** is recommended. If you have a DevOps team, ask them about whether a machine user already exists.

### Machine user

Machine users are generally regular user accounts, but used purely to run automated (or triggered) tasks.

If a user's personal access token is used and they leave the organization or their permission is revoked, then the scheduler will fail.

Because of this, **we recommend to have a global machine user on organization level, to make sure there is no disruption during scheduling**.

Check [this](https://developer.github.com/v3/guides/managing-deploy-keys/#machine-users) for further info on creating machine users.

Before creating a machine user on GitHub, please read their [TOCs](https://help.github.com/en/github/site-policy/github-terms-of-service#3-account-requirements).

## Creating git access tokens

How to create a git access token differs between remote git providers.

[Creating a **GitHub** access token](https://help.github.com/en/github/authenticating-to-github/creating-a-personal-access-token-for-the-command-line#creating-a-token).

For providers other than GitHub (e.g. GitLab), the access token name must be `dataform`, and it must have the **api** permission.

[Creating a **GitLab** access token](https://docs.gitlab.com/ee/user/profile/personal_access_tokens.html#creating-a-personal-access-token).


git-access-tokens

Scheduling runs

## Introduction

Schedules can be used to run any selection of your datasets at a user-specified frequency. Running your scripts on a repeating schedule ensures output data is always kept up to date.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  Schedules
  <b>
    only run from the <code>production</code> branch
  </b> (if there is no environment configured)
  . Make sure you commit and push your changes to the production branch after configuring schedules.
</div>

## Create a schedule

1. Navigate to the Scheduling page using the left menu
2. Click `Add schedule`
3. Enter your schedule's settings and click `Create schedule`

![Create schedule](/static/images/how_to_guides/scheduling/create_schedule.png)

## View past runs

You can find all current and historical scheduled runs in the _Run logs_ page, accessible using the left menu bar.

![Run logs](/static/images/how_to_guides/scheduling/run_logs.png)

## Scheduler access tokens

For projects using a remote git project (e.g. hosted on GitHub), a scheduler access token is required. [More info on configuring git access tokens](./git-access-tokens).


scheduling

Set up a warehouse

## Introduction

In order to connect to your data warehouse to run your project's compiled SQL on your behalf, Dataform requires some warehouse access details.

Configuring your data warehouse settings should take less than 5 minutes. If you encounter any difficulty, please contact our team using Intercom or via email at [team@dataform.co](mailto:team@dataform.co).

## BigQuery

### Create a service account

You’ll need to create a service account from your Google Cloud Console and assign it permissions to access BigQuery.

1. Follow <a target="_blank" rel="noopener" href="https://cloud.google.com/iam/docs/creating-managing-service-accounts#creating_a_service_account">these instructions</a> to create a new service account in Google Cloud Console.
2. Grant the new account the `BigQuery Admin` role. (Admin access is required by Dataform so that it can create queries and list tables.) Read
   <a target="_blank" rel="noopener" href="https://cloud.google.com/iam/docs/granting-roles-to-service-accounts#granting_access_to_a_service_account_for_a_resource">this</a> if you need help.
3. Create a key for your new service account (in JSON format). You will upload this file to Dataform. Read
   <a target="_blank" rel="noopener" href="https://cloud.google.com/iam/docs/creating-managing-service-account-keys#creating_service_account_keys">this</a> if you need help.

## Postgres (running in AWS) or Redshift

Postgres and Redshift projects require the following configuration settings:

- Hostname in the form `[name].[id].[region].redshift.amazonaws.com`
- Port (usually `5432` for Postgres or `5439` for Redshift)
- Username and password
- Database name

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  Dataform's IP addresses must be whitelisted in order to access your Redshift cluster. Please
  follow
  <a
    target="_blank"
    rel="noopener"
    href="https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-authorize-cluster-access.html"
  >these instructions</a>. Dataform's IP addresses are <code>35.233.106.210</code> and <code>104.196.10.242</code>.
</div>

### How to find Redshift credentials

1. Go to `Redshift` in your AWS console.
2. Select your cluster under `Clusters`.
3. The hostname is the endpoint listed at the top of the page. Username and database name are listed under cluster database properties.
   You may prefer to create a separate username and password for Dataform to use - please contact our team if you need help.

## Snowflake

Snowflake connections require the following elements:

- Account name: the first part of your Snowflake url, including region: `account-name.region`.snowflakecomputing.com
- Username and password used for your Snowflake console. You may prefer to create a separate username and password for Dataform to use - please contact our team if you need help.
- Warehouse name: Click "Warehouses" from within your Snowflake console to view a list of warehouses. Any warehouse in your account will work with any database.
- Database: Click "Databases" from within your Snowflake console to view a list of databases.

You may also need to whitelist Dataform's IP addresses: `35.233.106.210` and `104.196.10.242`


set-up-warehouse

Slack Notifications

## Introduction

Success and failure alerts for schedule runs can be configured as part of schedule set up. This guide explains how to set
up notifications to a [Slack](https://www.slack.com) channel.

## Create an Incoming WebHook url for your slack workspace

1. Open the [Incoming WebHook](https://dataformco.slack.com/apps/A0F7XDUAZ-incoming-webhooks?next_id=0) app.
2. Click "Add to Slack".
3. Choose the slack channel you would like alerts to be sent to
4. Click "Add Incoming WebHooks Integration".
5. On the next page you will see a "Webhook URL". You'll need this for the next step.

## Create a notification channel and link the webhook url

1. Open a development branch.
2. Navigate to the scheduling page using the left menu.
3. Open the "Notification Channels" tab.
4. Click "Add Channel". Give the channel a name, choose the "Slack" notification type, and copy in the "Webhook URL" from
the previous step.
5. Click "Create Channel".

## Add the notification channel to a schedule

1. Open the "Schedules" tab on the schedules page.
2. Create a new schedule, or edit an existing schedule.
3. In the "Notifications" section of the schedule dialogue, click "Add new notification settings".
4. Select the channel you just created. Choose `success`, `failure` or both as "Events".


slack-notifications

Using the API

Dataform Web can be controlled through a REST API. This is currently a paid feature, if you'd like to get set up please reach out to our team on Intercom on the bottom right of the page.

<div class="bp3-callout bp3-icon-info-sign bp3-intent-warning">
  The Dataform Web API is currently in Alpha, and breaking changes are likely to happen.
</div>

## Authorization

Calls to the Dataform Web API are authenticated using API tokens. These can be created from the Dataform project's settings page.

When calling the API, you can pass your API token in an `Authorization` header, using a bearer token format.

For example, with `curl` these headers can be provided as follows:

```bash
curl -H "Authorization: Bearer 5235783003799552|s+N8gAs72qbi90pFEv7yW/KBImTshRdBoVKjjFA7lD0=|1" https://api.dataform.co/v1/project/1234/run/5678
```

## Creating runs

Runs can be created by making a `POST` call to the `RunCreate` method.

For detailed documentation on supported parameters, see the [`RunCreate`](../api-reference#RunCreate) reference documentation.

For example, to create a run for the project ID `1234` and to trigger a specific schedule name:

```bash
curl -H "Authorization: Bearer 5235783003799552|s+N8gAs72qbi90pFEv7yW/KBImTshRdBoVKjjFA7lD0=|1" -X POST -d '{ "scheduleName": "some_schedule" }' https://api.dataform.co/v1/project/1234/run
```

This will return a [RunGetResponse](../api-reference#/definitions/v1RunCreateResponse), that includes the created run's ID:

```json
{
  "id": "1029591293203"
}
```

## Getting run information

After creating a run, the status of the run can be checked with the [`RunGet`](../api-reference#RunGet) method.

This should be a `GET` request to the appropriate path, for example for project ID `1234` and a run ID `5678`:

```bash
curl -H "Authorization: Bearer 5235783003799552|s+N8gAs72qbi90pFEv7yW/KBImTshRdBoVKjjFA7lD0=|1" https://api.dataform.co/v1/project/1234/run/5678
```

This will return a [`RunGetResponse`](../api-reference#/definitions/v1RunGetResponse) such as:

```json
{
  "id": "5678",
  "status": "RUNNING",
  "runLogUrl": "https://app.dataform.co/#/1234/run/5678"
}
```


using-the-api

Version control

## Introduction

Dataform uses the Git version control system to maintain a record of each change made to project files and to manage file versions. Each Dataform project has an associated Git repository.

By default, Dataform will manage your project's Git repository for you. However, projects can be configured to use GitHub instead by clicking the `Migrate project to GitHub` button in Settings.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-primary" markdown="1">
  Version control is tightly integrated into Dataform Web and has consequences on how your data
  pipelines run. We strongly recommend understanding the following concepts before developing your
  datasets in Dataform.
</div>

## Branches

One of the main benefits of Git is that a developer can work in an isolated "branch" of the file repository without affecting the base files. Dataform has full support for Git branches.

From any non-`production` branch you can edit files (and run those edited scripts on your warehouse, if desired).

Once you are happy with your changes, you can commit them and push them to the `master` branch. If at any point you make changes that you don't want to commit, you can revert your branch to its last commit.

Note that the `production` branch is not directly editable and **all schedules run from the production branch**. This enables users to develop and test changes without affecting the project's data pipelines or affecting other users.

### Personal and shared branches

When you first use Dataform we will automatically create a personal development branch for you, `name_dev`. Only you can see this branch and commit to it.

It's also possible to create a shared branch, which everyone in the project can see and commit to. These can be useful if you're collaborating with someone on a project.

### Creating and deleting branches

To create a new branch, open the branch selection menu and click "New Branch". You will have the option to make it a shared branch.

To delete a branch, first open the branch. Then, from the version control settings, choose the `Delete branch` option.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
If you delete a branch, any changes which have not yet been pushed to `production` will be lost.
</div>

## How to use version control

Select the branch you would like to use to make your changes.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1" style="margin-bottom: 10px">
  We recommend that each team member uses their own separate development branch.
</div>

After making changes to files, you can commit them. You will be shown a list of the modified files, and asked to provide a commit message.

Once you are happy with your changes, you can `Push to master`. The changes will now be on `production` and used for future schedule runs.

## Reverting uncommitted changes

If you'd like to undo any changes you've made since your last commit, you can `Revert uncommitted changes` from the version control menu.

## Resolving merge conflicts

If other changes have been pushed to the `master` branch while you were making edits in your branch, you will need to pull those changes into your branch first by choosing `Pull from master`.

There may be conflicting edits that you will have to manually resolve. Files with conflicts are highlighted in the files list. Once you are comfortable with your edits, you can commit and push your changes to `master`.


version-control

guides

Tutorials

Creating a new project

This tutorial demonstrates the first few steps in configuring a new project.

Google BigQuery is used as the data warehouse in this example. We also support [these other warehouses](../guides/set-up-warehouse).

## Log in or sign up

After opening [the app](https://app.dataform.co/).

![Sign up](/static/images/how_to_guides/dataform_101/1_sign_up.png)

If not signing in with GitHub, Google or Microsoft then you will need to verify your email address.

## Create new project

This will create a new, empty project.

![New project](/static/images/how_to_guides/dataform_101/2_new_project.png)

## Give the project an inspirational name

![Project name](/static/images/how_to_guides/dataform_101/3_project_name.png)

## Choose to "create a table" from the file templates

Ignore the instruction to the configure the data warehouse; we'll do that next.

![File templates](/static/images/how_to_guides/dataform_101/4_file_templates.png)

## Connect to your warehouse

Big green button towards towards the central right.

![Create table](/static/images/how_to_guides/dataform_101/5_create_table.png)

## Select Google BigQuery

Input the project ID. For alternatives warehouses, see [here](../guides/set-up-warehouse).

![Add warehouse](/static/images/how_to_guides/dataform_101/6_add_warehouse.png)

## Select and test the server

Input, credentials, and test that they are valid. See [here](../guides/set-up-warehouse) for how to create a JSON credentials key.

![Test server](/static/images/how_to_guides/dataform_101/7_test_server.png)

## Preview the results

This shows the exact affect it will have on the warehouse.

![Preview results](/static/images/how_to_guides/dataform_101/8_preview_results.png)

## Publish the table

![Publish table](/static/images/how_to_guides/dataform_101/9_publish_table.png)

## Next Steps

You've taken the first steps towards improving your data pipeline!

Some of the next features you could use next include:

- Automating repeated runs using [scheduling](../guides/scheduling).

- Checking validity of data using [assertions](../../guides/assertions) or [unit tests](../../guides/tests).

- [Documenting datasets](../../guides/documentation).


Managing dependencies

This tutorial demonstrates how dependency trees are made within a project.

## Create a new file

Create a new file by clicking on the + button next to the `definitions` folder and create a new SQLX file named `onboarding_table`.
For now, ignore the other options; these are more advanced features which will be covered in later documentation.

![New file](/static/images/how_to_guides/publish_tables/new_file.png)

## Write a query

In the text editor, write a simple SQLX statement:

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

select 
  1 as one,
  2 as two,
  3 as three
```

You will see the right sidebar update with `onboarding_table` which is the name of the dataset, a tag for the type - a `view` - as well as a validation message and two action buttons.

The `Preview results` button executes the query in your warehouse and returns the output at the bottom of the page. This can be useful during query development to check that the query returns expected output.

<img
src="/static/images/how_to_guides/publish_tables/compilation.png"
style="width: 100%"
/>

## Run the query

Clicking the `Run this action` button will open a dialog box with a few options for running your actions. Click `Run now` to continue.

Upon successful completion of the run, a new view named `dataform.onboarding_table` will be created in your warehouse.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-primary" markdown="1">
  By default, Dataform creates datasets and views under a schema named <code>dataform</code>. You
  can change this in the <code>dataform.json</code> in your
  <a href="../../guides/configuration">project configuration</a>.
</div>

![Run node](/static/images/how_to_guides/publish_tables/run_node.png)

## Create a table instead of a view

To create a table instead of a view, simply change your script's `config` block:

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

You will see the tag on the right sidebar update from `view` to `table`.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-none" markdown="1">
  Dataform provides many other options for dataset creation. Learn about
  <a href="../../guides/datasets">publishing datasets</a> and
  <a href="../../guides/incremental-datasets">incremental datasets</a>.
</div>

<img
src="/static/images/how_to_guides/publish_tables/table_vs_view.png"
style="width: 100%"
/>

## Referencing other datasets

Dataform provides methods that enable you to easily reference another dataset in your project using the `ref()` function:

```js
select 
  * 
from 
  ${ref('my_table')}
```

This provides two advantages:

- You don’t have provide the full SQL dataset name.
- Any dataset that is referenced by a query will be automatically added to that query's dependencies. Dependency queries are always executed before dependent queries to ensure correctness.

## Create a new file

Create a new SQLX file named `onboarding_table2` and add the following content:

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

select
  * 
from
  ${ref('onboarding_table')}
```

<img
src="/static/images/how_to_guides/publish_tables/ref_query.png"
style="width: 100%"
/>

You will see the right sidebar update with the name of the view, `onboarding_table2`. Clicking on `Compiled query` will display the fully qualified query to be run in your data warehouse.
The `${ref()}` function will be replaced with the actual name of the dataset in your data warehouse. Note that the dependency is also listed just above the compiled query.

_*Your compiled script will differ depending on your cloud data warehouse. This example uses Redshift.*_

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  Referencing a dataset that hasn't yet been created in your data warehouse will cause validation
  errors. These validation errors are warning you that the dataset you are referencing doesn't exist
  (yet). However, since Dataform will run your project's scripts in dependency order, you can safely
  ignore these errors.
</div>

## View dependency tree

The overview page (linked at the top left of Dataform) gives you an overview of your project. The overview includes a visualization of the dependency tree of your project, containing the two datasets created by your scripts.

<img
src="/static/images/how_to_guides/publish_tables/dependencies.png"
style="width: 100%"
/>

In bigger projects containing dozens or hundreds of datasets with complicated interdependencies, the overview page can help you keep track of your project's overall structure.


tutorials

Web API Reference

api-reference

Dataform Web provides a fully managed environment for Dataform projects, where your data team can work together to develop your core analytics infrastructure.

![hero](https://static.dataform.co/images/hero_screenshot.png)

It provides a number of features on top of the open-source framework that makes it a great choice for larger teams.

To get started with Dataform Web, check out our [getting started tutorial](dataform-web/tutorials/101) or [sign up](https://dataform.co) and jump straight into a trial.

## Scheduling

Dataform Web makes it easy to schedule many complex pipelines without managing infrastructure.

## SQL IDE

Develop and run Dataform SQL directly against your warehouse. Get development hints and validation of queries as your write them.

## GitHub & GitLab integration

Dataform Web projects can be connected directly to your own Git provider, meaning you stay in control of your code and can continue to use the CLI.

## Data catalog

Dataform Web has a built in data catalog that is automatically generated from your Dataform project and can be shared with the rest of your team.
