BQ Process - An Overview

The BQ Process in Syntasa is designed to seamlessly integrate the power of Google BigQuery (BQ) with Syntasa’s data orchestration capabilities. BQ process is currently compatible only for the GCP environments. 

It enables users to run SQL queries directly on BigQuery data—either to extract, transform, or load data—without needing separate infrastructure or code deployments.

Through this process, users can connect to BigQuery datasets, perform complex transformations, and create partitioned or non-partitioned tables efficiently within the Syntasa environment.

Key Features of the BQ Process

1. Multiple Output Tables

   The BQ Process allows defining multiple output tables within a single workflow.

   This enables you to split and transform data into different segments or business categories in one execution, writing each segment into a separate output table.

   Example use case:

   • Output 1 → Cleaned transaction data
   • Output 2 → Aggregated daily metrics
   • Output 3 → Error or anomaly records

   Each output can be configured separately in the process setup.

2. Support for Partitioned Tables

   The BQ Process supports creating and writing into partitioned tables, enabling efficient querying and optimized cost management.

   To generate a partitioned output table, you can define partition logic within your SQL, such as:

   CREATE TABLE IF NOT EXISTS @OutputTable1
   PARTITION BY created_at AS
   SELECT * FROM @InputTable1;
   --END--

   Partitioned outputs allow incremental loading, faster lookups, and lower query costs by restricting scans to specific partitions (e.g., daily or monthly).

3. Parameterization and Dynamic Querying

   Syntasa provides built-in runtime parameters for flexible and dynamic query design.

   You can easily reference:

   • @InputTable1, @InputTable2 – input table references
   • @OutputTable1, @OutputTable2 – output table references
   • @fromDate, @toDate, @partitionDate – date-based runtime values

   This makes your SQL reusable and environment-independent, ideal for scheduled and incremental loads.

4. End-to-End SQL Control

   You can include multiple SQL statements in one BQ Process using the --END-- separator.

   This allows combining table creation, transformation, and insertion logic in one process.

   Example:
   
   CREATE TABLE IF NOT EXISTS @OutputTable1 (
    session_date DATE,
    customer_code STRING,
    country STRING
   )
   PARTITION BY session_date;
   --END--
   
   INSERT INTO @OutputTable1
   SELECT
    DATE(session_timestamp) AS session_date,
    customer_code,
    country
   FROM @InputTable1
   WHERE DATE(session_timestamp) = DATE('@partitionDate');
   --END--
   
   

5. Integration with Syntasa Scheduling and Monitoring

   Each BQ Process can be scheduled, monitored, and retried within Syntasa just like other data processes. You can track query duration, data volume processed, and runtime logs directly in the Syntasa UI.