Overview of Dataform core
Dataform core is an open source meta-language to create SQL tables and workflows. Dataform core extends SQL by providing a dependency management system, automated data quality testing, and data documentation.
You can use Dataform core for the following purposes:
- Defining tables, views, materialized views, or incremental tables.
- Defining data transformation logic.
- Declaring source data and managing table dependencies.
- Documenting table and column descriptions inside code.
- Reusing functions and variables across different queries.
- Writing data assertions to ensure data consistency.
In Dataform, you use Dataform core to develop SQL workflows and deploy assets to BigQuery.
Dataform core is part of the open-source Dataform data modeling framework that also includes Dataform CLI. You can compile and run Dataform core locally through the Dataform CLI outside of Google Cloud.
To use Dataform core, you write SQLX files. Each SQLX file contains a query that defines a database relation that Dataform creates and updates inside BigQuery.
Dataform compiles your Dataform core code in real time to create a SQL compilation result that you can execute in BigQuery.
Dataform compilation is hermetic to ensure compilation consistency, meaning that the same code compiles to the same SQL compilation result every time. Dataform compiles your code in a sandbox environment with no internet access. No additional actions, such as calling external APIs, are available during compilation.
SQLX file config block
A SQLX file consists of a config block and a body. All config properties, and the config block itself, are optional. Given this, any plain SQL file is a valid SQLX file that Dataform executes as-is.
In the config block, you can perform the following actions:
Specify query metadata
You can configure how Dataform materializes queries into BigQuery, for example the output table type, the target database, or labels using the config metadata.
Document data
You can document your tables and their fields directly in the config block. Documentation of your tables is pushed directly to BigQuery. You can parse this documentation and push it out to other tools.
Define data quality tests
You can define data quality tests, called assertions, to check for uniqueness, null values, or a custom condition. Dataform adds assertions defined in the config block to your workflow dependency tree after table creation. You can also define assertions outside the config block, in a separate SQLX file.
The following code sample shows you how to define the output table type, document the table, and define a quality test in a config block of a SQLX file.
config {
type: "table",
description: "This table joins orders information from OnlineStore & payment information from PaymentApp",
columns: {
order_date: "The date when a customer placed their order",
id: "Order ID as defined by OnlineStore",
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"]
}
}
SQLX file body
In the body of a SQLX file you can perform the following actions:
- Define a table and its dependencies.
- Define additional SQL operations to run in BigQuery.
- Generate SQL code with JavaScript.
Define a table
To define a new table you can use SQL SELECT
statements and the ref
function.
The ref
function is a SQLX built-in function that is critical to dependency
management in Dataform. The ref
function lets you reference tables
defined in your Dataform project instead of hard coding the schema and
table names of your data table.
Dataform uses the ref
function to build a dependency tree of all the
tables to be created or updated. After compiling, Dataform adds
boilerplate statements like CREATE
, REPLACE
, or INSERT
.
The following code sample shows you how to reference a table in a SQLX file
with the ref
function.
config { type: "table" }
SELECT
order_date AS date,
order_id AS order_id,
order_status AS order_status,
SUM(item_count) AS item_count,
SUM(amount) AS revenue
FROM ${ref("store_clean")}
GROUP BY 1, 2, 3
The output is similar to the following:
CREATE OR REPLACE TABLE Dataform.orders AS
SELECT
order_date AS date,
order_id AS order_id,
order_status AS order_status,
SUM(item_count) AS item_count,
SUM(amount) AS revenue
FROM Dataform_stg.store_clean
GROUP BY 1, 2, 3
For more information on additional dependency management, for example, executing code conditionally, using other Dataform core built-in functions, see the Dataform core reference.
Define additional SQL operations
To configure Dataform to execute one or more SQL statements before or after creating a table or view, you can specify pre-query and post-query operations.
The following code sample shows you how to configure table or view access permissions in a post-query operation.
SELECT * FROM ...
post_operations {
GRANT `roles/bigquery.dataViewer` ON TABLE ${self()} TO "group:someusers@dataform.co"
}
Encapsulate SQL code
To define reusable functions to generate repetitive parts of SQL code, you can use JavaScript blocks. You can reuse code defined in a JavaScript block only inside the SLQX file where the block is defined. To reuse code across your entire repository, you can create includes.
To dynamically modify a query, you can use inline JavaScript anywhere in the body.
The following code sample shows how to define a JavaScript block in a SQLX file and use it inline inside a query:
js {
const columnName = "foo";
}
SELECT 1 AS ${columnName} FROM "..."
What's next
- To learn more about Dataform core built-in functions, see the Dataform core reference.
- To learn more about Dataform CLI, see Use the Dataform CLI.
- To learn how to define additional SQL operations, see Add custom SQL operations.
- To learn how to create JavaScript includes, see Reuse variables and functions with includes.
- To learn how to define a table using Dataform, see Create a table.