Tables to Tables script file

This is the description of the JSON configuration file of a Tables to Tables data operation.

The configuration file is in JSON format. It contains the following sections:

  • Global parameters: General information about the data operation.

  • Workflow task parameters: Information about the different tasks of the workflow.

📹 Tables to Tables script video

Here is an example of a TTT configuration file:

{
	"configuration_type": "table-to-table",
	"configuration_id": "000099_iowa_liquor_agg_store",
	"short_description": "Create_aggregation",
	"doc_md": "000099_iowa_liquor_agg_store.md",
	"account": "000099",
	"environment": "DEV",
	"activated": true,
	"archived": false,
	"direct_execution": true,
	"start_date": "2019, 1, 1",
	"catchup": false,
	"schedule_interval": "0 8 * * *",
	"max_active_runs": 1,
	"task_concurrency": 3,
	"default_gcp_project_id": "fd-io-jarvis-demo-dlk",
	"default_bq_dataset": "dlk_demo_iowa_liquor_bda_cluster",
	"default_write_disposition": "WRITE_TRUNCATE",
	"task_dependencies": [
		"create_table >> run_query"
	],
	"workflow": [
        {
            "id": "create_table",
            "short_description": "Create an empty table with the proper name and descriptions.",
            "doc_md": "iowa_liquor_agg_store.md",
            "write_disposition": "WRITE_TRUNCATE",
            "task_type": "create_gbq_table",
            "force_delete": true,
            "bq_table": "iowa_liquor_agg_store_2019",
            "bq_dataset": "dlk_demo_iowa_liquor_bda_cluster",
            "ddl_file": "iowa_liquor_agg_store_DDL.json"
        },
        {
            "id": "run_query",
            "short_description": "Run the query and store the results in a table.",
			"doc_md": "iowa_liquor_agg_store.md",
            "write_disposition": "WRITE_APPEND",
            "task_type": "sql",
            "sql_file": "iowa_liquor_agg_store.sql",
            "table_name": "iowa_liquor_agg_store_2019",
            "bq_dataset": "dlk_demo_iowa_liquor_bda_cluster"
        }
	]
}

🌐 Global parameters

General information about the data operation.

ParameterDescription

configuration_type

type: string

mandatory

Type of data operation.

For a TTT data operation, the value is always "table-to-table".

configuration_id

type: string

mandatory

ID of the data operation.

You can pick any name you want, but is has to be unique for this data operation type.

Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:

  • your account ID,

  • the word "load",

  • and the target dataset or table.

short_description

type: string

optional

Short description of the context of the data operation.

doc_md

type: string

optional

Path to a file containing a detailed description of the data operation. The file must be in Markdown format.

environment

type: string

mandatory

Deployment context.

Values: PROD, PREPROD, STAGING, DEV.

account

type: string

mandatory

Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator.

activated

type: boolean

optional

Flag used to enable/disable the execution of the data operation.

Default value: true

archived

type: boolean

optional

Flag used to enable/disable the visibility of the data operation's configuration and runs in Jarvis Studio.

Default value: false

start_date

type: string

mandatory

Start date of the data operation.

The format must be:

"YYYY, MM, DD"

Where:

  • YYYY >= 1970

  • MM = [1, 12]

  • DD = [1, 31]

schedule_interval

type: string

mandatory

A Tables to Tables data operation can be launched in two different ways:

  • If schedule_interval is set to "None", the data operation will need to be started with a Workflow, when a given condition is met. (This solution is recommended.)

  • If you want the data operation to start at regular intervals, you can define this in the schedule_interval parameter with a Cron expression.

Example

For the data operation to start everyday at 7:00, you need to set it as follows:

"schedule_interval": "0 7 * * *",

You can find online tools to help you edit your Cron expression (for example, crontab.guru).

max_active_runs

type: integer

optional

This parameter limits the number of concurrent runs for this data operation. As most data operations are run once daily, there is usually no need to set a value higher than 1 here.

Default value: 1

task_concurrency

type: integer

optional

This parameter limits the number of tasks that might run concurrently.

As a great volume of data might be handled by each task, it is important to make sure to avoid consuming too many resources for one data operation.

Make sure also that the value you set here is high enough for concurrent tasks set in the task_dependencies parameter to run properly.

Default value: 5

catchup

type: boolean

optional

This parameter allows you to specify if you want to execute the data operation runs that were supposed to happen between start_date and the actual deployment date.

Example

If you start receiving data from September 1st, but you only finish writing your code on September 7th, you might want to run your data operation from a date in the past: September 1st.

The catchup parameter can have two values:

  • If it is set to "true" AND a scheduling_interval is set AND start_date is set in the past, Composer/Airflow will execute every run of the data operation scheduled from the start date until the current date.

  • If it is set to "false", the data operation will only be executed starting from the current date.

Default value: false

task_dependencies

type: array of strings

mandatory

The task_dependencies parameter allows you to create dependencies between the different tasks specified in the workflow parameter (see below). It will define in which order the workflow tasks will run, some of them running concurrently, others sequentially.

Usually workflow tasks will be run in the following order:

  1. create_gbq_table

  2. sql

  3. copy_gbq_table

Syntax

  • The double chevron >> means that the first task needs to be completed before the next one can start.

  • The comma , means that the tasks will run concurrently.

  • The square brackets [ and ] allow you to define a set of tasks that will run together.

For detailed information about the syntax, refer to the Airflow documentation.

Example 1

We have the following tasks that we want to run sequentially: taskA (create_gbq_table), taskB (sql) and taskC (copy_gbq_table). The task_dependencies parameter will be as follows: "task_dependencies": [" taskA >> taskB >> taskC "],

Example 2

We have the following tasks that we want to run concurrently: taskA, taskB and taskC.

The task_dependencies parameter will be as follows: "task_dependencies": [" taskA, taskB, taskC "],

Example 3

We have the following 9 tasks we want to order: taskA, taskD, taskG (create_gbq_table), taskB, taskE, taskH (sql), taskC, taskF, taskI (copy_gbq_table). The task_dependencies parameter will be as follows:"task_dependencies": [" [taskA, taskD, taskG] >> [taskB, taskE, taskH] >> [taskC, taskF, taskI] "],

Example 4

In the example above, we want taskH to run before taskE so we can use its result for taskE.

The task_dependencies parameter will be as follows:

"task_dependencies": [" [taskA, taskD, taskG] >> taskH >> [taskB, taskE] >> [taskC, taskF, taskI] "],

workflow

type: array of maps

mandatory

List of tasks the data operations will execute.

Check the section below for detailed information on their parameters.

default_gcp_project_id

type: string

mandatory

Default GCP Project ID.

This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.

default_bq_dataset

type: string

mandatory

Default BigQuery dataset ID.

This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.

default_write_disposition

type: string

mandatory

Action that occurs if the destination table already exists (see Google BigQuery documentation).

Possible values:

  • "WRITE_TRUNCATE" (default): The run will write table data from the beginning. If the table already contained lines, they will all be deleted and replaced by the new lines. This option is used most of the time for daily runs to avoid duplicates.

  • "WRITE_APPEND": The run will append new lines to the table. When using this option, make sure not to run the data operation several times.

  • "WRITE_EMPTY": This option only allows adding data to an empty table. If the table already contains data, it returns an error. It is hardly ever used as data operations are usually run periodically, so they will always contain data after the first run.

This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.

direct_execution

type: boolean

optional

Tailer's execution engine has been rewritten to switch from Airflow/Composer to a Kubernetes severless architecture, improving its speed, stability, security and scalability.

To use the new execution mode, the direct_execution parameter must be set to "true".

Default value: true (from January 5, 2021)

âžŋ Workflow task parameters

A Tables to Tables workflow can include for types of tasks:

  • create_gbq_table: This type of task allows you to create the skeleton of a table based on a DDL file. You could compare it to the baking pan of your table.

  • sql: This type of task allows you to fill that baking pan using a SQL query or a SQL script from a file.

  • copy_gbq_table: This type of task allows you to duplicate a table named X into another table named Y.

  • expectation: This type of task allows you to perform data quality tests. See expectations for more details.

For each workflow sub-object, parameters will differ depending on the task type.

Refer to this page to know how to create the DDL and SQL files corresponding to these tasks.

SQL task parameters

ParameterDescription

task_type

type: string

mandatory

The value has to be set to "sql" for this task type.

As "sql" is the default type, this parameter can be omitted for this task type.

id

type: string

mandatory

ID of the task. It must be unique within the data operation.

short_description

type: string

optional

Short description of what the task does.

doc_md

type: string

optional

Path to a file containing a detailed description of the task. The file must be in Markdown format.

sql_file

type: string

mandatory

Path to the file containing the actual SQL query or the SQL script (can be assertions or expectations). This file is going to be read and its content uploaded to Firestore upon deployment of the data operation.

gcp_project_id

type: string

optional

GCP Project ID.

Overrides default_gcp_project_id.

bq_dataset

type: string

optional

Name of the BigQuery destination dataset.

Overrides default_bq_dataset.

write_disposition

type: string

optional

Action that occurs if the destination table already exists (see Google BigQuery documentation). Possible values:

  • "WRITE_TRUNCATE" (default): The run will write table data from the beginning. If the table already contained lines, they will all be deleted and replaced by the new lines. This option is used most of the time for daily runs to avoid duplicates.

  • "WRITE_APPEND": The run will append new lines to the table. When using this option, make sure not to run the data operation several times.

  • "WRITE_EMPTY": This option only allows adding data to an empty table. If the table already contains data, it returns an error. It is hardly ever used as data operations are usually run periodically, so they will always contain data after the first run.

Overrides default_write_disposition.

table_name

type: string

mandatory if the SQL file contains a query, optional for a SQL script

Target table used upon SQL query execution. This parameter is optional if the SQL file contains a script, i.e. one or more statements separated by a ; (for ex. assertions or expectations). Otherwise, this parameter is mandatory.

criticality

type: string

optional

Task criticality which defines the impact of an error on this task on the data operation global status and behavior. It allows to create tasks dedicated to warning tests (see expectations) that won't break the execution if it fails or conditional tasks that can lead to different tasks depending on predefined conditions (see this article).

Criticality can be:

  • critical: if a critical task fails, the run stops and have the global status "failed".

  • warning: if a warning task fails, the run proceeds to the next task, executing the following dependent task if any, and the global status of the run will be "warning" (if no critical task fails afterward). The workflow manager regards warning as success, so any job chained by a workflow configuration will be triggered.

  • transparent: if a transparent task fails, the run proceeds to the next task, executing the following dependent task if any, and the global status of the run will be "success" (if no critical or warning task fails). Any job chained by a workflow configuration will be triggered.

  • break: if a break task fails, the run skips all the dependent tasks and proceeds to the independent tasks if any. The global status of the run will be "success" (if no critical or warning task fails). Any job chained by a workflow configuration will be triggered. You can use this criticality to create conditional tasks that lead to different tasks depending on predefined conditions. See this article for more information.

Default value: "critical"

temporary_table

type: boolean

optional

If set to "true", this parameter will force the deletion of the table at the end of the data operation. If the TTT data operation failed before deleting the temporary table, the table will be deleted automatically after 24 hours.

Default value: false

sql_query_template

type: string

optional

If you want to use variables in your SQL query or script, you need to set this parameter to "TEMPLATE_CURRENT_DATE" (only supported value). This variable will be set to the execution date of the data operation (and not today's date).

For example, if you want to retrieve data corresponding to the execution date, you can use the following instruction:

WHERE sale_date = DATE('{{TEMPLATE_CURRENT_DATE}}')

Table Creation task parameters

Parameter

Description

task_type

type: string

mandatory

In this case, the value has to be set to "create_gbq_table".

id

type: string

mandatory

ID of the task. It must be unique within the data operation.

short_description

type: string

optional

Short description of what the task does.

doc_md

type: string

optional

Path to a file containing a detailed description of the task. The file must be in Markdown format.

gcp_project_id

type: string

optional

GCP Project ID.

Overrides default_gcp_project_id.

bq_dataset

type: string

optional

Name of the BigQuery destination dataset.

Overrides default_bq_dataset.

bq_table

type: string

mandatory

Name of the BigQuery table name.

ddl_file

type: string

mandatory

Path to the JSON file containing DDL information to create the table.

force_delete

type: boolean

optional

If set to "true", this parameter will force the deletion of the table prior to its creation.

criticality

type: string

optional

Task criticality which defines the impact of an error on this task on the data operation global status and behavior.

Criticality can be: critical, warning, transparent or break.

See "criticality" description in the SQL task above for more details

Default value: "critical"

Table copy task parameters

Parameter

Description

task_type

type: string

mandatory

In this case, the value has to be set to "copy_gbq_table".

id

type: string

mandatory

ID of the task. It must be unique within the data operation.

short_description

type: string

optional

Short description of what the task does.

doc_md

type: string

optional

Path to a file containing a detailed description. The file must be in Markdown format.

source_gcp_project_id

type: string

mandatory

GCP project ID for the source BigQuery table.

source_bq_dataset

type: string

mandatory

BigQuery dataset for the source table.

source_bq_table

type: string

mandatory

Name of the source table.

gcp_project_id

type: string

optional

GCP project ID for the destination BigQuery table.

Overrides default_gcp_project_id.

bq_dataset

type: string

optional

Name of the BigQuery destination dataset.

Overrides default_bq_dataset.

destination_bq_table

type: string

mandatory

Name of the BigQuery destination table.

destination_bq_table_date_suffix

type: boolean

mandatory

Enables/Disables the date suffix to allow date partitioning in BigQuery.

destination_bq_table_date_suffix_format

type: string

mandatory if destination_bq_table_date_suffix is set to true

Date format for the table suffix.

As of now, the only possible value is "%Y%m%d".

criticality

type: string

optional

Task criticality which defines the impact of an error on this task on the data operation global status and behavior.

Criticality can be: critical, warning, transparent or break.

See "criticality" description in the SQL task above for more details

Default value: "critical"

Last updated