Name: writing-sql
Author: celigo

Write SQL queries for Celigo RDBMS exports and imports -- SELECT, INSERT, UPDATE, UPSERT, MERGE, delta, once, and bulk operations across Snowflake, Postgres, MySQL, SQL Server, Oracle, BigQuery, and Redshift. Use when editing rdbms.query or troubleshooting SQL errors.

<! TIER:1 Writing SQL for RDBMS Integrations RDBMS exports and imports use SQL queries to read from and write to relational databases. Queries are Handlebars templates the platform evaluates expressions like {{{record.fieldName}}} at runtime, then executes the resulting SQL against the connected database. This skill covers what SQL to write and how to write it . For which adaptor type to use, connection setup, and queryType selection, see the related skills below. When the task is to create an export or import, create it with the query in place rather than handing back the SQL [Delta export]( delta export incremental) shows a complete export. Quick Reference Context decision matrix Where Field Handlebars prefix Braces Example Export query rdbms.query record. (parameterized) or none (standalone) {{{ }}} SELECT FROM orders WHERE id = {{{record.id}}} Export delta query rdbms.query platform injected tokens {{ }} (adds the quotes) WHERE updated at {{lastExportDateTime}} Export once mark as processed rdbms.once.query record. {{{ }}} UPDATE orders SET exported = true WHERE id = {{{record.id}}} Import per record query rdbms.query[] record. {{ }} strings, {{{ }}} numbers INSERT INTO users (name, age) VALUES ({{record.name}}, {{{record.age}}}) Import per page query rdbms.query[] batch of records + record. {{ }} strings, {{{ }}} numbers {{ each batch of records}}...{{record.field}}...{{/each}} Import bulk load override rdbms.bulkLoad.overrideMergeQuery staging table ref {{ }} MERGE INTO target USING {{import.rdbms.bulkLoad.preMergeTemporaryTable}} Key syntax rules 1. Always use record. prefix (AFE 2.0). Never bare fieldName or data.fieldName . 2. Double braces quote strings for you. {{record.name}} renders a single quoted SQL string literal with embedded quotes doubled ( O'Neil Co renders 'O''Neil Co' ), and so does every other string an expression produces: helper output such as {{dateFormat ...}} or {{jsonSerialize ...}} , and {{this}} inside {{ each}} . Use it for string and date values, including {{lastExportDateTime}} , and never add quotes around it '{{record.name}}' renders doubled quotes. On Snowflake, a value holding a backslash needs another form ([gotcha 13]( gotchas)). 3. Triple braces are raw. Use {{{ }}} for numbers ( {{{record.quantity}}} a number that arrives as a string, "5" , renders '5' in double braces; JSON numbers and booleans render bare either way), identifiers such as table or column names, and SQL fragments you built yourself. Never wrap a triple brace string in single quotes of your own ( '{{{record.name}}}' ): the first apostrophe in the data breaks the statement and leaves it open to SQL injection. (Snowflake's dollar quoted constants are the one exception, in gotcha 13.) 4. Import query is an array of strings , not a single string: ["INSERT INTO ..."] . 5. Nested fields use dot notation: {{record.address.city}} . Related skills [configuring exports RDBMS](../configuring exports/SKILL.md) adaptorType, delta/once mode, export level config [configuring imports RDBMS](../configuring imports/SKILL.md) queryType decision tree, bulkInsert/bulkLoad config [writing handlebars](../writing handlebars/SKILL.md) full Handlebars syntax, helpers, block expressions [configuring connections RDBMS](../configuring connections/SKILL.md) database connection setup Schema reference [rdbms export.yml](references/schemas/rdbms export.yml) export query and once fields [rdbms import.yml](references/schemas/rdbms import.yml) import query, queryType, bulkInsert, bulkLoad fields <! TIER:2 How to Write a SQL Query 1. Discover the schema Query the live database to find tables and columns before writing SQL: For Snowflake, use fully qualified names: database.schema.table . 2. Determine the query pattern Exports what kind of data fetch? Pattern Export type Query template Full fetch type: null SELECT columns FROM table WHERE conditions Delta/incremental type: "delta" SELECT ... WHERE updated at {{lastExportDateTime}} Once (mark as processed) type: "once" rdbms.query = SELECT unprocessed; rdbms.once.query = UPDATE to mark processed Imports what kind of write operation? (The bulk load rows apply to Snowflake, Google BigQuery, Microsoft SQL Server on a cloud connection, Azure Synapse and Amazon Redshift, and to NSAW through the JDBC import.) Pick the fastest type the operation allows: bulk load where the database has it, bulk insert for pure inserts, then per page one statement per page, rendered against batch of records , so it must cover the whole batch and per record when the SQL can't be written for a page. Pattern queryType What to configure INSERT with duplicate check ["per page"] or ["per record"] rdbms.query with INSERT ... ON CONFLICT / ON DUPLICATE KEY per page as one multi row statement UPDATE ["per page"] or ["per record"] rdbms.query with UPDATE ... WHERE, or one statement that updates the whole page UPSERT / MERGE ["bulk load"] , else ["per page"] or ["per record"] bulk load: bulkLoad.primaryKeys ; otherwise rdbms.query with MERGE or ON CONFLICT Pure INSERT (no checking) ["bulk insert"] rdbms.bulkInsert.tableName and batchSize no SQL needed Bulk load with upsert ["bulk load"] rdbms.bulkLoad.tableName and primaryKeys auto generates MERGE Custom bulk merge ["bulk load"] bulkLoad.overrideMergeQuery: true + custom SQL referencing staging table 3. Write the SQL Follow the database specific [dialect patterns]( dialect patterns) below. Inject runtime values with {{record.fieldName}} for strings and dates and {{{record.fieldName}}} for numbers and identifiers (see [Key syntax rules]( key syntax rules)). 4. Test the query An export query can be tried before it's saved: a preview of an unsaved export document runs its query and saves nothing. For a delta query, give the document postData with both variables, or they render empty and the SQL doesn't compile ([Delta export]( delta export incremental)). Nothing shows an import's SQL without running it. A flow test run never executes imports, an import preview ( celigo imports invoke with a document) answers 422 invalid adaptor for an RDBMS import, and the Handlebars processor HTML escapes values instead of quoting them ( O'Neil & Co renders O& x27;Neil & Co ), so its output isn't what the database receives. Check the statement in the import editor's SQL preview, then run one record against a test table. Export Query Patterns Standard SELECT Delta export (incremental) Use {{lastExportDateTime}} (platform injected, not from a record). It is the start of the run's window: the end of the last run that exported records, or delta.startDate on a new export's first run. It renders as a quoted ISO 8601 UTC literal with milliseconds ( '2026 01 15T00:00:00.000Z' ), so don't add quotes of your own: '{{lastExportDateTime}}' doubles the quotes, and a bare {{{lastExportDateTime}}} is an unquoted timestamp both are SQL errors. {{currentExportDateTime}} is the window's end; never use it as the lower bound. Set delta.startDate on the export. Without it, the first run's {{lastExportDateTime}} is the run's own time, and the first run exports nothing. A complete delta export: Snowflake compares the literal with a timestamp column directly. Where the database needs a timestamp value or another format: A preview of an unsaved export document renders both variables empty and unquoted, so the query fails there unless the document supplies both in postData ([configuring exports Gotchas](../configuring exports/SKILL.md gotchas), 18). In a flow run, remember that a run given its own window ( flows run start time/ end time ) leaves the checkpoint at its end time. Once export (mark as processed) Two queries work together. The export rdbms.query fetches unprocessed records; rdbms.once.query marks each one after successful export. JOINs Aggregations Import Query Patterns All import queries inject values from incoming records with record. paths {{ }} for strings and dates (quoted and escaped for you), {{{ }}} for numbers. The query field is an array of strings . INSERT (per record) UPDATE (per record) UPSERT database specific MySQL (ON DUPLICATE KEY UPDATE): PostgreSQL (ON CONFLICT): Snowflake (MERGE): SQL Server (MERGE): Multiple statements (per record) When you need to run multiple SQL statements per record, add them as separate array elements: Bulk insert (no SQL needed) For pure INSERTs with no duplicate checking, use bulkInsert the platform generates the SQL: Column mapping comes from mappings[] on the import (Mapper 2.0). Each mapping's generate value must match a column name in the target table, and each needs a dataType string , number or boolean for a column value; a timestamp is a string , since there is no date type (see [writing mappings](../writing mappings/SKILL.md)). Bulk load with auto generated MERGE bulk load is available on Snowflake, Google BigQuery, Microsoft SQL Server (a cloud connection, not one through an on premise agent), Azure Synapse and Amazon Redshift, and on NSAW through the JDBC import. Redshift needs no AWS setup for it: Celigo stages the files and Redshift loads them with COPY. The import form shows the option on BigQuery, SQL Server, Synapse and Redshift only when it's enabled for the account, but the API accepts the import either way. On other databases (MySQL, MariaDB, PostgreSQL, Oracle), use bulk insert for pure inserts and per page or per record SQL for updates and upserts. On a supported database, a high volume upsert uses bulkLoad with primaryKeys , an array of column names: The platform stages data into a temporary table, then auto generates a MERGE using the primary keys. primaryKeys is an array even for one column, never a string; for composite keys, list each column: "primaryKeys": ["ORDER ID", "PRODUCT ID"] . Without primaryKeys the load is a plain insert, and without tableName the save is refused with 422 "rdbms imports require a tableName." Bulk load with custom merge When the auto generated MERGE isn't sufficient (conditional updates, ignore existing, multi table ops), override it: The custom SQL goes in the rdbms.query field (yes, even with bulkLoad ). Reference the staging table via {{import.rdbms.bulkLoad.preMergeTemporaryTable}} : per page batch operations per page gives you the entire page of records as batch of records . Use {{ each}} to iterate: Dialect Patterns Snowflake PostgreSQL MySQL / MariaDB SQL Server / Azure Synapse Oracle BigQuery Redshift <! TIER:3 Pre Submit Checklist Export queries [ ] SELECT query is syntactically valid for the target database dialect [ ] Delta uses {{lastExportDateTime}} in the query, NOT a delta object inside rdbms double braces with no quotes of your own, and the export's top level delta.startDate set so the first run isn't empty [ ] Once export has both queries rdbms.query for SELECT and rdbms.once.query for UPDATE [ ] Table and column names exist verify with celigo metadata types/fields <connectionId [ ] Snowflake uses fully qualified names database.schema.table unless the connection sets defaults Import queries [ ] query is an array of strings ["INSERT INTO ..."] , not "INSERT INTO ..." [ ] queryType is one value that fits the operation ["bulk load"] on Snowflake, BigQuery, SQL Server (cloud connect