DOCSEARCHbyAlgolia
Submit Docs

Dataform Core Documentation

repository·main·Indexed 21 days ago

https://github.com/dataform-co/dataform

An open-source meta-language for building scalable SQL transformation pipelines in BigQuery. Dataform Core provides dependency management, automated data quality testing via assertions, and data documentation. It includes a CLI for local development, project initialization, compilation of SQLX projects into execution graphs, and integration options for Google Cloud Platform.

Tokens
46.4K
Snippets
112
Records
159
Agent score
77%

What's inside Dataform Core

  2. What is Dataform Core

    main

    Dataform Core is an open-source meta-language designed to create SQL tables and workflows in BigQuery. It extends standard SQL by adding:

    • Dependency Management: A system to manage relationships between different data assets.
    • Automated Data Quality Testing: Built-in mechanisms for assertions and checks.
    • Data Documentation: Tools to document your data assets.

    It allows data teams to build scalable SQL transformation pipelines using software engineering best practices like version control and testing.

  4. Write unit tests for SQL using the Test class

    main

    Dataform test actions allow you to write unit tests for your generated SQL. You can implement tests using either SQLX files or the Javascript API.

    Using SQLX

    In a .sqlx file, set the type to "test" in the config block. You can define inputs using the input block to provide mock data for the test.

    Using the Javascript API

    Use the test("name") function. The methods input() and expect() are available on the object returned by test().

    • input(refName, contextableQuery): Sets the input query (mock data) to test against.
    • expect(contextableQuery): Sets the expected output of the query being tested.
    // Using SQLX
-- definitions/name.sqlx
config {
  type: "test"
}

input "foo" {
  SELECT 1 AS bar
}

SELECT 1 AS bar

// Using Javascript API
// definitions/file.js
test("name")
  .input("sample_data", `SELECT 1 AS bar`)
  .expect(`SELECT 1 AS bar`);

publish("sample_data", { type: "table" }).query("SELECT 1 AS bar")

  6. Create tables in Dataform

    main

    Tables are the fundamental building blocks in Dataform. Dataform compiles your code into SQL, executes it, and creates the defined tables in BigQuery. You can define tables using three different methods:

    1. SQLX files: Use a .sqlx file with a config block.
    2. Action config files: Use a .yaml file to map actions to existing .sql files.
    3. Javascript API: Use the table() function within a .js file.

    Note: When using the Javascript API, configuration methods are accessed via the object returned by the table() function.

    -- Using a SQLX file
-- definitions/name.sqlx
config {
  type: "table"
}
SELECT 1
    # Using action configs files
# definitions/actions.yaml
actions:
- table:
  filename: name.sql
    // Using the Javascript API
// definitions/file.js
table("name", { type: "table" }).query("SELECT 1 AS TEST")

  7. Create an incremental table using SQLX

    main

    To define an incremental table in a .sqlx file, set the type to incremental within the config block. Use the incremental() function within your SQL to differentiate between the initial full build and subsequent incremental runs (e.g., to filter for only new rows).

    -- definitions/name.sqlx
config {
  type: "incremental"
}

-- This inserts `1` the first time running, and `2` on subsequent runs.
SELECT ${when(incremental(), 1, 2) }
    -- definitions/name.sqlx
config {
  type: "incremental"
}
-- This inserts `1` the first time running, and `2` on subsequent runs.
SELECT ${when(incremental(), 1, 2) }

  8. Set up the Dataform VS Code extension

    main

    To use this extension, you must have the Dataform CLI installed globally on your system. You can install it using npm:

    npm i -g @dataform/cli

    Once installed, the extension provides:

    • Syntax highlighting for .sqlx files.
    • Realtime compilation of your project.
    • Navigation: Use cmd + click (or ctrl + click on Windows/Linux) on a ref() function to jump to the referenced file.

  9. Create an assertion in Dataform

    main

    An assertion is a data quality test query. If the query returns any rows, the assertion fails. You can create assertions using four different methods:

    1. SQLX file: Define the assertion type in a config block.
    2. Table configuration: Add assertions directly to the assertions property within a table's config block.
    3. Action config files (YAML): Define the assertion in a .yaml file and point to a corresponding .sql file.
    4. Javascript API: Use the assert() function and chain the .query() method.

    Note: When using the Javascript API, configuration methods are accessed via the object returned by assert().

    -- Using a SQLX file
-- definitions/name.sqlx
config {
  type: "assertion"
}
SELECT * FROM table WHERE a IS NULL
    # Using action configs files
# definitions/actions.yaml
actions:
- assertion:
  filename: name.sql
    // Using the Javascript API
// definitions/file.js
assert("name").query("SELECT * FROM table WHERE a IS NULL")

  10. Create a Notebook action

    main

    Notebooks allow you to run Jupyter Notebook (.ipynb) files within Dataform. The output is sent to the storage buckets defined in your workflow_settings.yaml file.

    You can define a notebook action using either YAML configuration files or the JavaScript API.

    // Using the Javascript API
notebook("name", { filename: "name.ipynb" })

// Using action configs files (YAML)
# definitions/actions.yaml
actions:
- notebook:
    filename: name.ipynb

  11. Create a custom Dataform package

    main

    To create a new Dataform package, follow these steps:

    1. Clone the base package repository: Use the dataform-package-base repository as your starting point. This repo provides the necessary structure, including index.js, example.js, and README.md.

    2. Implement package functionality: Modify the base files to implement your logic. The base repo contains a simple dependency graph (one declaration and two chained tables). At a minimum, you should update the following files to reflect your package's purpose:

      • README.md
      • index.js
      • example.js
      • includes/dataset_one.js
      • includes/dataset_two.js

    3. Test against a data warehouse: Connect your package to a live data warehouse to verify that the dependency graph and transformations behave as expected.

    4. Release: Once verified, you can share your package with the community. If you want your package listed in the official documentation, submit a pull request to the Dataform repository.

  12. Create an incremental table using the Javascript API

    main

    You can create incremental tables using the publish function in a .js file. Pass { type: "incremental" } as the configuration object. The query method is called on the returned object to define the SQL logic. Use ctx.incremental() within the query context to handle incremental logic.

    // definitions/file.js
publish("name", { type: "incremental" }).query(
  ctx => `SELECT ${ctx.when(ctx.incremental(), 1, 2) }`
)