Trinium
Start free trial
Home Product Security Nodes & connectors Docs Use cases Pricing FAQ About us Start free trial
Browse the docs
Output node

BigQuery output

Load rows into a BigQuery table with a load job, appending or replacing. Loading is free, and the table is created if needed.

Category
Output
Connection
BigQuery

What it does

Loads the incoming rows into a BigQuery table through a saved Connection. Rows are written in a columnar format and handed over as a single load job, and the table is created automatically if it doesn't exist.

Inputs and outputs

InputsOne.
OutputsNone. This node ends a pipeline.

When to use it

  • Land prepared data in the warehouse for reporting
  • Refresh a reporting table on a schedule
  • Move data from a database, a file or an API into BigQuery

Examples

Load staging, then merge

Pre-SQL: TRUNCATE TABLE proj.ds.stg_orders · Post-SQL: MERGE proj.ds.orders t USING proj.ds.stg_orders s ON t.id = s.id WHEN MATCHED THEN UPDATE SET total = s.total WHEN NOT MATCHED THEN INSERT ROW

Each statement is a real BigQuery job, billed by bytes scanned exactly as if you ran it in the console.

Append

analytics.events

Adds rows to whatever is already there.

Full refresh

Write mode: truncate

Replaces the table's contents in one step.

Fully qualified

my-project.analytics.events

Write to a different project than the Connection's.

Tips

  • Each pre- and post-SQL statement has its own ⤢, the SQL Studio, with the connection's tables and columns beside the statement. It has no Run button: a Test run executes none of these statements.
  • Pre-SQL and post-SQL run on this same connection, around the load. Pre-SQL runs and commits BEFORE the load starts; post-SQL runs only AFTER it has finished successfully, so a failed load never fires the merge.
  • One statement per row, run in order. If one fails the run stops there, and the message names which statement it was and what the database said.
  • A Test run (Run to here) writes nothing and executes none of them, so testing stays safe on a pipeline whose pre-SQL truncates a table.
  • Loading this way is free: BigQuery does not charge for load jobs, where streaming inserts are billed per row.
  • The load either commits completely or leaves the table untouched: a failure part way through cannot leave you with half the rows.
  • A truncate is a genuine replace, so a source that legitimately went empty will empty the table. That is the point of a full refresh, but it surprises people once.
  • An auto-created table takes its column types from the data. If the table already exists, its own types win and incoming values are converted to them.
  • There is no upsert mode: merging into a warehouse table is a MERGE against a staging table, which is better built explicitly as its own step than hidden in a checkbox.
BigQuery input