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

## Organise your project with folders

Managing your project in folders makes it easier to maintain your project. One simple option is to follow the following folder structure:

<img src="https://assets.dataform.co/docs/best_practices/folder_example.png"  alt="" />

<figcaption>Example of a Dataform project</figcaption>

**Sources**

Where you define transformations on your data sources. This is where you can create datasets that transform data from your different sources into a consistent format, using the same structure and naming conventions. Some examples of transformations you may want to include at this stage are:

- Normalising sources (ex: ensure email have the same field name in all tables)
- Aligning data types (ex: ensure timestamps are in a unique time zone and money fields in dollars)

All subsequent datasets should be built on top of these models, reducing the amount of duplicated code in your project.

**Staging**

Where you can define intermediary datasets that are only used within your Dataform project. The objectives of these tables are to make your project easier to maintain, easier to reason about, and make your pipelines more efficient.

**Reporting**

Where you define datasets that represent entities and processes relevant to your business and will be consumed by downstream users (BI tools, reports…).

As your project gets bigger and more complex, you can leverage subdirectories within those folders to separate groups of sources or analytics tables.

## Manage dependencies with `ref()`

The `ref()` function ensures data transformation pipelines run in the correct sequence. We recommend you always use the `ref()` function when selecting or joining from another dataset rather than using the direct relation reference.

```sql
-- definitions/table_without_ref.sqlx
config { type: "table" }

select * from "schema"."my_table"
```

<br />

```sql
-- definitions/table_with_ref.sqlx
config { type: "table" }

select * from ${ref("my_table")}
```

The `ref` function is how you tell Dataform about the dependencies in your project. With this information, Dataform builds a dependency tree of your project in real time, and achieves the following things:

- Alerts you in in real time about dependency errors
- Ensures you are in the correct environment
- Run your transformation pipelines in the correct sequence

## Define your source data with declarations

Your Dataform project will depend on raw data stored in your warehouse, created by processes external to Dataform. These external processes can change the structure of your tables over time (column names, column types…).

We recommend defining raw data as declarations to build your projects without any direct relation reference to tables in your warehouse.

<img src="https://assets.dataform.co/docs/best_practices/declarations_dag.png"  alt="" />

Using declarations enables you to reference your raw data in a unique place that can be updated in your data sources changes.

There are two ways to define declarations: in SQLX files and in JS files.

In SQLX files, with one declaration per file.

```sql
-- definitions/sources/customer.sqlx
config {
  type: "declaration",
  database: "SNOWFLAKE_SAMPLE_DATA",
  schema: "TPCH_SF1",
  name: "CUSTOMER",
}
```

In JS files, where you can define multiple declarations in one file. You can use this to define all raw data from the same source for example.

```js
// definitions/sources/stripe_dependencies.js
declare({
  schema: "stripe",
  name: "charges"
});

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

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

Once a declaration has been defined, it can be referenced by the ref() function like any other Dataform dataset.

```sql
-- definitions/staging/charges.sqlx
config { type: "table" }

select * from ${ref("charges")}
```

## Use tags to manage your schedules

Tags can be added to SQLX config blocks to create collection of actions and datasets. When starting your project, it can useful to start using the following two tags:

- `daily` to tag actions that need to be updated once a day
- `hourly` to tag actions that need to be updated once per hour

You can create two schedules, one that will run all actions tagged `daily` every day and one that will run all actions tagged `hourly` every hour 

With this setup, when creating a dataset, simply add the right tag to ensure it gets updated at the correct frequency.

```sql
-- definitions/analytics/customers.sqlx
config { type: "table", tags: ["daily"] }

select * from ${ref("crm_data")}
```


start-your-dataform-project

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

Best practices for managing dataform projects

## Choose the right action type

SQLX supports three types of materializations depending on your needs: tables, views and incremental tables. As general rules:

- Use `views` by default (they’re cheap and quick)
- Use `tables` for datasets you expect to have downstream users for performance reasons. For example datasets queries by BI tools or datasets that have multiple descendents.
- If the input data is very large, use `incremental` tables. They transform only new data (but are more complex to manage)

## Organise data for your users using custom schemas

Dataform has a default schema defined in your dataform.json file. You can override it in the config block of your SQLX files.

```sql
-- definitions/staging/stg_customer.sqlx
config {
  type: "view",
  schema: "staging",
  tags: ["staging", "daily"],
  description: "Cleaned version of the raw customer table."
}

select
  c_custkey as customer_key,
  c_name as full_name,
  ...
```

## Test the quality of your data with assertions

Assertions are data quality checks, used to ensure data meets expectations.

Assertions are run as part of your schedule: if an assertion fails, you will be notified (if notifications are configured)

<img src="https://assets.dataform.co/docs/best_practices/assertions_dag.png"  alt="" />

Assertions can be added to the config block in your .sqlx files. There are three types:

- `uniqueKey`: ensure there is only one row per value of the supplied column(s)
- `nonNull`: ensure the field(s) are not null
- `rowConditions`: takes a custom SQL expression. If this expression is FALSE for any rows, the assertion fails.

```sql
-- definitions/analytics/order_stats.sqlx

config {
  type: "table",
  assertions: {
    nonNull: ["order_date", "order_key", "customer_key"],
    uniqueKey: ["order_key"],
    rowConditions: [
      "total_parts >= 0"
    ]
  }
}

select ...
```

Note: you can also create custom assertions using SQLX. Read the [page on assertions](guides/assertions) for more.

## Document your data

Help keep collaboration on your project frictionless by adding documentation to your Dataform code.

You can add table and columns descriptions in the config block of SQLX files.

```sql
-- definitions/analytics/order_stats.sqlx

config {
  type: "table",
  description: "This table contains summary stats by date aggregated by country",
  columns: {
    order_date: "Date of the order",
    order_id: "ID of the order",
    customer_id: "ID of the customer in the CRM",
    order_status: "Status of the order, from Shopify",
    payment_status: "Status of payment, from Stripe",
    payment_method: "Credit card of ACH",
    item_count: "Number of items in that order",
    amount: "Amount charged for that order, in US dollars using a floating FX rate"
  }
}

select ...
```

Documentation is automatically added to the Data Catalog within Dataform and can later be exported to other tools.

<img src="https://assets.dataform.co/docs/best_practices/catalog_example.png"  alt="" />

## Keep your code DRY using reusable `includes` macros

DRY stands for Don’t Repeat Yourself. JavaScript files can be added to the includes/ folder to define simple scripts, constants or macros that can be reused across your project.

Defining macros within includes allows you to keep your transformation logic DRY: write a function or variable once, and use it across the rest of your project

**Example: country group mapping**
The function country_group defines a mapping from country to region. It can be defined once, and then reused throughout your project. If the grouping changes, you only need to change code in one place

```sql
-- includes/country_group.js
module.exports = (country) => {
  return `
  case
    when ${country} in ('US', 'CA') then 'NA'
    when ${country} in ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') then 'EU'
    when ${country} in ('AU') then ${country}
    else 'Other'
  end`;
}
```

<br />

```sql
-- definitions/new_table.sqlx
config { type: "table"}

select
  country as country,
  ${country_group("country")} as country_group,
...
```

<br />

```sql
-- compiled.sql
select
  country as country,
  case
    when country in ('US', 'CA') then 'NA'
    when country in ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') then 'EU'
    when country in ('AU') then country
    else 'Other'
  end as country_group,
...
```


best-practices

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

bigquery

Authentication, configuration options, and content for Redshift.

Amazon Redshift

redshift

Authentication and configuration options for Azure SQL Data Warehouse.

Azure SQL Data Warehouse

sqldatawarehouse

Authentication and configuration options for Postgres.

Postgres

postgres

warehouses

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

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

publish

Learn how to configure tables that update incrementally.

Incremental datasets

incremental

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

Document datasets

documentation

datasets

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.

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.

Best practices using Dataform

What's next

First steps with your Dataform project

Best practices for managing dataform projects

Sitemap