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

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
Dataform signups are closed. Please join
  <a
    target="_blank"
    rel="noopener"
    href="https://forms.gle/YBfHb6CWLD4Kmbd46"
  >the waitlist</a> to register your interest in using Dataform.
</div>

Welcome to the Dataform Getting Started Tutorial. This tutorial is for people who are new to Dataform and would like to learn 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 configure schedules. A working knowledge of SQL will be helpful for this tutorial.

For this tutorial we’re going to pretend we are a fictional e-commerce shop. We already have 3 main data sources in our data warehouse:

- Information about our customers coming from Salesforce
- Orders information from Shopify
- Payment information from Stripe

The aim of this tutorial is to create two new tables in our warehouse, one called `order_stats` and one called `customers`, which are:

- Updated every hour
- Tested for data quality
- Well documented

<div style="position: relative; padding-bottom: 55.78124999999999%; height: 0;"><iframe src="https://www.loom.com/embed/2368b67928ec43b2a7eaf8fabda636f9" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>

## Create a new BigQuery project

For this this tutorial we’ll use BigQuery. Anyone with a Google Account can use it and it has a free tier. We’ve created a public dataset in BigQuery that anyone can access for the purpose of this tutorial.

1. First you will need to create a new Bigquery project:

   - Go to the [BigQuery console](https://console.cloud.google.com/bigquery) (If you don’t already have a GCP account you’ll need to create one here).
   - If you’ve just created a new account you’ll be asked to create a new project straight away. If you already have an existing account you can select the project drop down in the header bar, and create a new project from there.

<img src="https://assets.dataform.co/getting%20started%20tutorial/set%20up/Screenshot%202020-08-13%20at%2015.40%201%20(1).png" max-width="753"  alt="" />

## Generate warehouse credentials

In order for Dataform to connect to your BigQuery warehouse you’ll need to use Application Default Credentials or a service account and JSON key.

### Using Application Default Credentials

If running on GCE or GKE this will be automatically available. If not, then use `[gcloud auth application-default](https://cloud.google.com/sdk/gcloud/reference/auth/application-default)` to authenticate.

### Create a service account with JSON key

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

1. To create a new service account in Google Cloud Console you need to:

   - Go to the [Services Account page](https://console.cloud.google.com/iam-admin/serviceaccounts)
   - Make sure the new project you created is selected and click `Open`.
   - Click on `Create Service Account` and give it a name.
   - Grant the new account the BigQuery Admin role.

2. Once you’ve done this you need to create a key for your new service account (in JSON format):

   - On the [Service Accounts page](https://console.cloud.google.com/iam-admin/serviceaccounts), find the row of the service account that you want to create a key for and click the `More` button.
   - Then click `Create key`.
   - Select JSON key type and click `Create`.

Now you've created a new BigQuery project and generated your warehouse credentials, you're ready to create your Dataform project!

For more detailed info on generating credentials for BigQuery, see our [docs](https://docs.dataform.co/warehouses/bigquery).


set-up

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

Building your data model

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
Dataform signups are closed. Please join
  <a
    target="_blank"
    rel="noopener"
    href="https://forms.gle/YBfHb6CWLD4Kmbd46"
  >the waitlist</a> to register your interest in using Dataform.
</div>

Now you have your BigQuery project and warehouse credentials, you’re going to set up your Dataform project using Dataform Web. In this step you'll create a project, connect your warehouse and build out your first dataset.

<div style="position: relative; padding-bottom: 55.93750000000001%; height: 0;"><iframe src="https://www.loom.com/embed/56df10cba03045ddbf27e3a577907876" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>

## Create a Dataform project

To create a new project in Dataform:

1. Create a Dataform account [here](https://app.dataform.co/).
2. Follow the sign up flow:

   - We will ask you for a bit of information about yourself and what you’re hoping to achieve with Dataform.
   - As part of the sign up flow we will create a new project for you. Give it the name `Dataform Tutorial`.
   - If you already have an account, you can create a new project by going to the homepage and clicking `New Project`.

<img src="https://assets.dataform.co/getting%20started%20tutorial/creating%20a%20dataset/Group%209%20(1).png" max-width="753"  alt="Creating a new project" />

## Connecting a warehouse

As part of the project creation flow you'll be asked to connect to a warehouse. You’re going to use the credentials you generated in the earlier part of this tutorial to connect:

1. On the Configure Warehouse modal click `Connect`.
2. Select `Google BigQuery` from the drop down menu.
3. Enter your Project ID.

   - This can be found by going to the [BigQuery console](https://console.cloud.google.com/) and looking at the Project info box.

4. Browse for the service account key JSON file you created in the `Setting Up` part of this tutorial and upload it.
5. Check your connection is working:

   - Press `Test Connection` to check that the connection is working.
   - Once this is successful, you can press `Save Connection`.

<img src="https://assets.dataform.co/getting%20started%20tutorial/creating%20a%20dataset/Screenshot%202020-08-13%20at%2015.46%201%20(1).png" max-width="753"  alt="Connecting a warehouse" />

## Creating a dataset

Now that you've created your project and connected a warehouse, you're ready to start defining your data model.

1. Make sure you are in a development branch:

   - By default you should already be in a development branch called `yourname_dev`.
   - However, if you would like to create a new branch, click `Develop` and select `New Branch`.
   - Give your branch a name.

2. Create a new dataset:

   - Click on the `New Dataset` button in the left hand side bar.
   - Choose whether you want your dataset to be a table, view or incremental table. In this case we want to create a table.
   - Name the table `order_stats` and click `Create Table`.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-primary" markdown="1">
<h4 class="bp3-heading">In Dataform transformations are defined using SQLX</h4>
 SQLX is an extension of SQL. A typical SQLX file contains a SELECT statement defining a new table or a view and a config block at the top. The config block is used to specify additional options for the table or view. To see a full list of options for the config block, open the Documentation tab.
</a></div>

3. Define the dataset:

   - To create your table use the below query which joins orders information from Shopify, payment details from Stripe and also applies some filters to the data:

```sql
SELECT
  orders.date AS order_date,
  orders.id AS id,
  orders.customer_id AS customer_id,
  orders.status AS order_status,
  charges.status AS payment_status,
  charges.payment_method AS payment_method,
  SUM(orders.item_count) AS item_count,
  SUM(charges.amount) AS amount

FROM
  dataform-demos.dataform_tutorial.shopify_orders AS orders
  LEFT JOIN dataform-demos.dataform_tutorial.stripe_payments AS charges
    ON orders.payment_id = charges.id

WHERE
  orders.id <= 999
  AND orders.item_count > 0
  AND orders.status <> 'internal'
  AND charges.payment_method IN ('debit_card', 'subscription', 'coupon')

GROUP BY 1, 2, 3, 4, 5, 6
```

- Paste the query into `order_stats.sqlx` below the config block.
- Dataform will automatically validate your query and check for any errors
- Once you see that the query is valid you can click `Preview Results` to check that the data looks correct

3. Create the table in your warehouse:

   - Click `Publish Table` to create the table in your warehouse
   - This will take the SQLX that we’ve written, compile it into the SQL syntax of your warehouse (in this case, BigQuery), and then execute that SQL in your warehouse with the correct boilerplate code to create a table

4. Check Run Logs:

   - You can see the progress of the run in the header menu.
   - Once you see that it has been successful, you can view `Run Logs` by clicking on the hamburger menu in the top left hand corner.
   - In `Run Logs` you can see all the past runs in the project. You can see their status, as well as how and when they were triggered.
   - For each run you can see the exact SQL run against the warehouse to create your datasets by clicking the `Details` button.

<img src="https://assets.dataform.co/getting%20started%20tutorial/creating%20a%20dataset/Screenshot%202020-08-13%20at%2015.51%201%20(1).png" max-width="753"  alt="Run Logs" />

You now have a new table called `order_stats` which has been created in your warehouse and you're ready to add to your data model!

For more detailed info on publishing datasets in Dataform, see our [docs].(https://docs.dataform.co/guides/datasets/publish)


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

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
Dataform signups are closed. Please join
  <a
    target="_blank"
    rel="noopener"
    href="https://forms.gle/YBfHb6CWLD4Kmbd46"
  >the waitlist</a> to register your interest in using Dataform.
</div>

## Managing Dependencies

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

This provides two advantages:

- You don’t have to 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.

In this step you'll learn how to manage dependencies in Dataform.

<div style="position: relative; padding-bottom: 55.93750000000001%; height: 0;"><iframe src="https://www.loom.com/embed/201481ab82914d55b7c7787e6c903f26" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>

1. You'll now create a second table called `customers`, following the same process as before

   - Click `New Dataset` and select the table template.
   - Give your table the name `customers`
   - Click `Create`.

2. Define your dataset:

- To create your table, use the following query:

```sql
SELECT
  customers.id AS id,
  customers.first_name AS first_name,
  customers.last_name AS last_name,
  customers.email AS email,
  customers.country AS country,
  COUNT(orders.id) AS order_count,
  SUM(orders.amount) AS total_spent

FROM
  dataform-demos.dataform_tutorial.crm_customers AS customers
  LEFT JOIN ${ref('order_stats')} orders
    ON customers.id = orders.customer_id

WHERE
  customers.id IS NOT NULL
  AND customers.first_name <> 'Internal account'
  AND country IN ('UK', 'US', 'FR', 'ES', 'NG', 'JP')

GROUP BY 1, 2, 3, 4, 5
```

- Paste the query into `customers.sqlx`, below the config block.
- This query uses the `ref` function. The `ref` function enables you to reference any other table defined in a Dataform project.
- You can see the dependencies of a dataset in the right hand side bar.
- If you open the compiled query, you can see that the `ref` function has been replaced with the fully qualified table name.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-primary" markdown="1">
<h4 class="bp3-heading">The <code>ref()</code> function</h4>
To 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>

3. Once you can see that your query is valid you can publish the table to your warehouse by clicking on `Publish Table`.

4. View the Dependency tree:

   - Navigate to the menu in the top left hand corner of your project and click on the `Dependency Tree` tab.

<img src="https://assets.dataform.co/getting%20started%20tutorial/manage%20dependencies/Screenshot%202020-08-13%20at%2015.52%201%20(1).png" max-width="753"  alt="The dependency tree" />

<div className="bp3-callout bp3-icon-info-sign bp3-intent-primary" markdown="1">
<h4 class="bp3-heading">The dependency tree</h4>
Here you can see a visualisation of your entire Dataform project. Every time Dataform creates a run and executes SQL in your warehouse, it will update the actions in the corect dependency order. 
</a></div>

You now have two tables created in your warehouse, one called `order_stats` and one called `customers`. `customers` depends on `order_stats` and will start running when `order_stats` is completed.

For more detailed info on managing dependencies in Dataform, see our [docs](https://docs.dataform.co/dataform-web/tutorials/102).


managing-dependencies

Learn how to set up a schedule and alerts in Dataform

Setting up a schedule

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
Dataform signups are closed. Please join
  <a
    target="_blank"
    rel="noopener"
    href="https://forms.gle/YBfHb6CWLD4Kmbd46"
  >the waitlist</a> to register your interest in using Dataform.
</div>

In the first few videos we introduced the basic concepts of Dataform. We’ll now introduce some of the more advanced features: [testing](https://docs.dataform.co/guides/assertions) and [documentation](https://docs.dataform.co/guides/datasets/documentation), [scheduling](https://docs.dataform.co/dataform-web/scheduling) and [version control](https://docs.dataform.co/dataform-web/version-control).

## Setting up a schedule

Schedules allow you to make sure your data is updated regularly. You're going to create a schedule that updates your datasets every hour and sends you a notification by email if the pipeline fails.

Schedules are defined in the environments.json file. It’s easiest to set them up in the interactive UI, but it's also possible to edit them directly in the `environments.json` file.

<div style="position: relative; padding-bottom: 55.93750000000001%; height: 0;"><iframe src="https://www.loom.com/embed/28219ff65f9c4faca1604289c07cae3c" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>

1. Navigate to the `Scheduling` page by clicking on the hamburger menu in the top left hand corner.

2. Press the `Create New Schedule` button.

   - Give your schedule the name `hourly_update`.
   - Press the 'Choose Frequency' button and select `hourly`.
   - Press Save.

3. Set up a new notification channel

   - Navigate back to the environments.json file.
   - Click on the `Create New Notification channel` button.
   - Give your notification channel the name `email`.
   - Enter your email address into the box below and press enter.
   - It’s also possible to set up notifications to Slack or a custom webhook.

<img src="https://assets.dataform.co/getting%20started%20tutorial/schedululing/Screenshot%202020-08-13%20at%2015.54%201%20(1).png" max-width="753"  alt="Setting up a schedule" />

You have now set up a schedule which updates your project every hour annd an alert which notifies you by email if your pipeline fails.

For more detailed info on setting up schedules, see our [docs](https://docs.dataform.co/dataform-web/scheduling).


scheduling

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

Data quality tests and documenting datasets

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
Dataform signups are closed. Please join
  <a
    target="_blank"
    rel="noopener"
    href="https://forms.gle/YBfHb6CWLD4Kmbd46"
  >the waitlist</a> to register your interest in using Dataform.
</div>

## Data quality tests

Adding tests to a project helps validate that your models are working correctly. These tests are run every time your project updates, sending alerts if the tests fail. Data quality tests in Dataform are called assertions.

<div className="bp3-callout bp3-icon-info-sign bp3-intent-primary" markdown="1">
<h4 class="bp3-heading">Assertions</h4> 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. There are various different types of assertions you can use including uniqueness checks and null checks. The simplest way to define assertions is as part of a dataset's `config` settings.
</a></div>

We want to create an assertion for our `order_stats` table that will fail if there is more than one row in the dataset with the same value for `id`.

<div style="position: relative; padding-bottom: 55.78124999999999%; height: 0;"><iframe src="https://www.loom.com/embed/f4735816f4614ea6bb2b99de2ec98d46" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>

1. Navigate back to your `order_stats` table:

   - Click on the hamburger menu in the top left hand corner of your project and click on `Develop Project`.
   - Navigate to the `order_stats` file.

2. In the config block add your assertion:

   - Copy and paste this example into your file, replacing the config block that is already there.

```js
config {
  type: "table",
  assertions: {
    uniqueKey: ["id"]
  }
}
```

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.

You can also choose to use assertions as dependencies. Assertions create named actions in your project that can be depended upon by using the dependencies `config` 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.

## Documenting your dataset

Dataform allows you to add documentation to the datasets defined in your project. Table and field descriptions are added using the same config block where you wrote your assertion.

1. In the config block write your documentation:

   - Copy and paste the below example into your file, replacing the config block that is already there.
   - This config block is now adding table and field descriptions to your table.

```js
config {
  type: "table",
    description: "This table joins orders information from Shopify & payment information from Stripe",
  columns: {
    order_date: "The date when a customer placed their order",
    id: "Order ID as defined by Shopify",
    order_status: "The status of an order e.g. sent, delivered",
    customer_id: "Unique customer ID",
    payment_status: "The status of a payment e.g. pending, paid",
    payment_method: "How the customer chose to pay",
    item_count: "The number of items the customer ordered",
    amount: "The amount the customer paid"
  },
    assertions: {
    uniqueKey: ["id"]
  }
}
```

- Table and field descriptions are now added to your table.

2. View the `Dependency tree`:

   - Navigate back to the `Dependency tree` tab in the hamburger menu in the top left hand corner:
   - Click on the `order_stats` table on the left.
   - You can view your table and field descriptions in the data catalog.

<img src="https://assets.dataform.co/getting%20started%20tutorial/tests%20%26%20documentation/Screenshot%202020-08-13%20at%2015.56%201%20(1).png" max-width="753"  alt="The Data Catalog" />

You have now created an assertion which will fail if any value in your `id` field is duplicated and you have given your `order_stats` table and its fields descriptions.

You can find more information on [assertions](https://docs.dataform.co/guides/assertions) and [documentation](https://docs.dataform.co/guides/datasets/documentation) in our docs.


testing-and-documentation

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

Committing your changes

## Committing changes

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
Dataform signups are closed. Please join
  <a
    target="_blank"
    rel="noopener"
    href="https://forms.gle/YBfHb6CWLD4Kmbd46"
  >the waitlist</a> to register your interest in using Dataform.
</div>

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.

When you first use Dataform we will automatically create a personal development branch for you called `name_dev`. Only you can see this branch and commit to it. All the changes you've made so far should be on this branch.

<div style="position: relative; padding-bottom: 55.93750000000001%; height: 0;"><iframe src="https://www.loom.com/embed/7e0f28b3ff8d473f9594019a7b0bff70" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>

1. Press the `Commit Changes` button:

   - In the modal you should see a list of all the changes you made to your project.

2. Give your commits a description and press `Commit`.

3. Click the `Push to Master` button to push your changes to the master branch:

   - The changes will now be on `production` and used for future schedule runs.

For more information on how version control works in Dataform, see our [docs](https://docs.dataform.co/dataform-web/version-control).

## Summary

You’ve now created your two tables; `order stats` and `customers`. `customers` depends on `order_stats` and will not be run until `order_stats` is updated. You have added data quality tests so that you know the data is reliable and you have documented your datasets so other team members know what the tables contain. A schedule will run every hour to keep the tables up to date, and you will be automatically alerted if the pipeline fails or there are data quality issues.

## Next Steps

### Start working on your own project

Follow the steps in this tutorial to get started with your own project. Connect to your own warehouse and create your first dataset using SQL.

### Learn more about Dataform

Learn more about [Dataform & SQLX in 5 minutes](https://docs.dataform.co/introduction/dataform-in-5-minutes) and our [best practices](https://docs.dataform.co/best-practices).

### Join the Dataform community

Join our [Slack channel](https://join.slack.com/t/dataform-users/shared_invite/zt-dark6b7k-r5~12LjYL1a17Vgma2ru2A) and read our [blog](https://dataform.co/blog) to learn more about Dataform use cases, best practices and to meet other people solving similar problems!


committing-changes