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.sql` 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).

For a detailed description of how to use the `ref` function, check out the [`ITableContext`](/reference#ITableContext) reference documentation.

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 `---`:

```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>

Assuming a similar situation to the example above, but where it's only desirable for the table to contain only the most recent user actions, `uniqueKey` could be specified.

If `uniqueKey` is specified, then if an incremental update appears, and if each unique key specified within a row matches that of existing data, then the row will be updated with the newly arriving values. Any rows that don't match will be inserted on top.

In order to optimise this merge on **BigQuery**, an update partition filter is set, that merges into records only from the last 24 hours.

```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

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.

Dataform and SQLX 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

Authentification, configuration options, and content for Snowflake.

Snowflake

snowflake

Authentification, configuration options, and content for BigQuery.

Google BigQuery

bigquery

Authentification, configuration options, and content for Redshift.

Amazon Redshift

redshift

Authentification and configuration options for Azure SQL Data Warehouse.

Azure SQL Data Warehouse

sqldatawarehouse

Authentification and configuration options for Postgres.

Postgres

postgres

warehouses

Guides to build your Dataform project.

Build your Dataform project

Learn about the structure and features of SQLX files.

SQLX

sqlx

Learn how to test data quality with assertions.

Test data quality with assertions

assertions

Learn how to declare external datasets with declarations.

Declare external datasets with declarations

declarations

Learn how to define custom SQL operations in Dataform.

Write custom SQL operations

operations

Learn how to configure your Dataform project.

Configure your project

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

includes

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

Use JavaScript files

js-api

javascript

Learn how to organise your project with tags.

Organise your project with tags

Learn how to run unit tests on your queries.

Run unit tests on your queries

tests

Configure continuous integration/deployment workflows for your Dataform project.

Configure CI/CD

ci-cd

incremental-datasets

guides

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

Guides to use Dataform web

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.

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

scheduling

Web API Reference

api-reference

Learn how to use the Dataform web REST API.

Use the REST API

using-the-api

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

Best practices for managing dataform projects

best-practices

Guide to learn how to use the Dataform command line interface tool.

Use the Dataform CLI

dataform-cli

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.

Publish data tables and views

What's next

Publish datasets

Incremental datasets

Document datasets

Sitemap