Incremental Table

Class: IncrementalTable

When you define an incremental table, sqlanvil builds the incremental table from scratch only for the first time. During subsequent executions, sqlanvil only inserts or merges new rows into the incremental table according to the conditions that you configure.

You can create incremental tables in the following ways. Available config options are defined in IncrementalTableConfig, and are shared across all the following ways of creating tables.

Using a SQLX file:

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

Using the Javascript API:

definitions/file.js

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

Note: When using the Javascript API, methods in this class can be accessed by the returned value. This is where query comes from.

Naming the incremental table:

By default an incremental table is named after its file: definitions/name.sqlx creates a table called name. Set name in the config to override that. The table is created under the new name, and other actions ref() it by that name, not by the filename:

-- definitions/events/inc.sqlx
config {
  type: "incremental",
  name: "events_incremental"
}
SELECT ${when(incremental(), 1, 2) }

In the Javascript API the name is the first argument.

Hierarchy

• ActionBuilder‹Table›

  ↳ IncrementalTable

Index

Methods

• assertions
• bigquery
• columns
• database
• dependencies
• description
• disabled
• hermetic
• postOps
• preOps
• protected
• query
• schema
• setDependOnDependencyAssertions
• tags
• type
• uniqueKey

Methods

assertions

▸ assertions(tableAssertionsConfig: TableAssertionsConfig): IncrementalTable

deprecated Deprecated in favor of IncrementalTableConfig.assertions.

Sets in-line assertions for this incremental table.

Parameters:

Name	Type
tableAssertionsConfig	TableAssertionsConfig

Returns: IncrementalTable

---

bigquery

▸ bigquery(bigquery: IBigQueryOptions): this

deprecated Deprecated in favor of options available directly on IncrementalTableConfig. For example: publish("name", { type: "table", partitionBy: "column" }) for BigQuery, or the postgres: config block for Postgres.

Sets bigquery options for the action.

Parameters:

Name	Type
bigquery	IBigQueryOptions

Returns: this

---

columns

▸ columns(columns: ColumnDescriptor[]): this

deprecated Deprecated in favor of IncrementalTableConfig.columns.

Sets the column descriptors of columns in this incremental table.

Parameters:

Name	Type
columns	ColumnDescriptor[]

Returns: this

---

database

▸ database(database: string): this

deprecated Deprecated in favor of IncrementalTableConfig.project.

Sets the Sets the database in which to create the output of this action. For BigQuery targets this is the Google Cloud project ID; for Postgres/Supabase targets this is the database name in workflow_settings.yaml.

Parameters:

Name	Type
database	string

Returns: this

---

dependencies

▸ dependencies(value: Resolvable | Resolvable[]): this

deprecated Deprecated in favor of IncrementalTableConfig.dependencies.

Sets dependencies of the incremental table.

Parameters:

Name	Type
value	Resolvable | Resolvable[]

Returns: this

---

description

▸ description(description: string): this

deprecated Deprecated in favor of IncrementalTableConfig.description.

Sets the description of this incremental table.

Parameters:

Name	Type
description	string

Returns: this

---

disabled

▸ disabled(disabled: boolean): this

deprecated Deprecated in favor of IncrementalTableConfig.disabled.

If called with true, this action is not executed. The action can still be depended upon. Useful for temporarily turning off broken actions.

Parameters:

Name	Type	Default
disabled	boolean	true

Returns: this

---

hermetic

▸ hermetic(hermetic: boolean): void

deprecated Deprecated in favor of IncrementalTableConfig.hermetic.

If true, this indicates that the action only depends on data from explicitly-declared dependencies. Otherwise if false, it indicates that the action depends on data from a source which has not been declared as a dependency.

Parameters:

Name	Type
hermetic	boolean

Returns: void

---

postOps

▸ postOps(posts: Contextable‹ITableContext, string | string[]›): this

Sets a post-operation to run after the query is run. This is often used for revoking temporary permissions granted to access source tables.

Example:

definitions/file.js

publish("example")
  .preOps(ctx => `GRANT \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)
  .query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
  .postOps(ctx => `REVOKE \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)

Parameters:

Name	Type
posts	Contextable‹ITableContext, string | string[]›

Returns: this

---

preOps

▸ preOps(pres: Contextable‹ITableContext, string | string[]›): this

Sets a pre-operation to run before the query is run. This is often used for temporarily granting permission to access source tables.

Example:

definitions/file.js

publish("example")
  .preOps(ctx => `GRANT \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)
  .query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
  .postOps(ctx => `REVOKE \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)

Parameters:

Name	Type
pres	Contextable‹ITableContext, string | string[]›

Returns: this

---

protected

▸ protected(isProtected: boolean): this

deprecated Deprecated in favor of IncrementalTableConfig.protected.

If called with true, prevents the dataset from being rebuilt from scratch.

Parameters:

Name	Type
isProtected	boolean

Returns: this

---

query

▸ query(query: Contextable‹ITableContext, string›): this

Sets the query to generate the table from.

Parameters:

Name	Type
query	Contextable‹ITableContext, string›

Returns: this

---

schema

▸ schema(schema: string): this

deprecated Deprecated in favor of IncrementalTableConfig.dataset.

Sets the schema (BigQuery dataset / Postgres schema) in which to create the output of this action.

Parameters:

Name	Type
schema	string

Returns: this

---

setDependOnDependencyAssertions

▸ setDependOnDependencyAssertions(dependOnDependencyAssertions: boolean): this

deprecated Deprecated in favor of IncrementalTableConfig.dependOnDependencyAssertions.

When called with true, assertions dependent upon any dependency will be add as dedpendency to this action.

Parameters:

Name	Type
dependOnDependencyAssertions	boolean

Returns: this

---

tags

▸ tags(value: string | string[]): this

deprecated Deprecated in favor of IncrementalTableConfig.tags.

Sets a list of user-defined tags applied to this action.

Parameters:

Name	Type
value	string | string[]

Returns: this

---

type

▸ type(type: TableType): this

deprecated Deprecated in favor of action type can being set in the configs passed to action constructor functions, for example publish("name", { type: "incremental" }).

Parameters:

Name	Type
type	TableType

Returns: this

---

uniqueKey

▸ uniqueKey(uniqueKey: string[]): void

deprecated Deprecated in favor of IncrementalTableConfig.uniqueKey.

If set, unique key represents a set of names of columns that will act as a the unique key. To enforce this, when updating the incremental table, sqlanvil merges rows with uniqueKey instead of appending them.

Parameters:

Name	Type
uniqueKey	string[]

Returns: void