SmartConnector SQL Design Best Practices

Audience: Administrators, Developers, Integrators, Solution Architects

Purpose: Provides SQL-writing conventions that help developers structure output tables correctly, manage credentials securely, and keep transformation logic in the right layer, so SmartConnectors are easier to validate, maintain, and operate reliably in production.

Overview

Getting the shape, credentials, and structure of your SQL output correct at the start makes everything downstream, including variable mapping, load step configuration, and production runs, significantly easier to validate and maintain.

Never Use SELECT *

Do not use SELECT * in SmartConnector SQL output tables. Column order in ClickHouse output is not guaranteed, and relying on positional order has caused real production data integrity issues where values are mapped to the wrong execution variables silently and without error.

Always specify column names explicitly in your SELECT statement:

-- Do this
CREATE OR REPLACE TABLE output.table_for_output
ENGINE = Log() AS
SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM source_data;

-- Not this
CREATE OR REPLACE TABLE output.table_for_output
ENGINE = Log() AS
SELECT * FROM source_data

If the source table changes and a column is added, dropped, or reordered, an explicit column list surfaces the change as an error rather than silently mapping wrong values.

Keep SQL Output Close to Final Form

Perform data transformations in your SQL script rather than through load step mapping rules. Mapping rules assign a source value directly to a destination field. They do not transform values along the way. When transformation logic lives in SQL, your output table arrives in its final shape, your mapping rules stay simple, and your connector is easier to validate and maintain.

For detailed guidance on what is possible in the SQL processing layer, see SQL Processing.

Use SQL Parameters for Configurable Values

If your SQL script includes threshold values, date offsets, record limits, or other configuration that may need to change over time, define them as SQL parameters rather than hard-coding them in the script body. Parameters carry a static default value and can be overridden at runtime via API call without editing the script.

Reference a parameter in SQL using the meta.parameters table:

SELECT value FROM meta.parameters WHERE name = 'my_parameter'

This keeps your SQL script stable while allowing configuration to vary between runs or environments.

Store Credentials in Integration Secrets

Never hard-code credentials, API keys, tokens, or passwords directly in a SQL script. Store credential values as integration secrets and reference them in SQL using the named collection pattern in ClickHouse table functions. For example, when connecting to a MySQL database:

SELECT *
FROM mysql(my_secret, 'database_name', 'table_name')

Where my_secret is the name of the integration secret configured on the connector. Secrets are masked in execution logs and never exposed in run reports or output files.

For guidance on configuring integration secrets, see see SmartConnector Settings.

Test SQL Before Configuring Load Steps

Before configuring load steps, publish your SQL script and initialize execution variables. Initializing execution variables locks in your output schema, so review the SQL output carefully at this stage.

• Download the SQL output zip after a test execution to confirm column names, types, and data look as expected before mapping to execution variables
• Use the dev package when your SmartConnector queries reference data from Kizen. Local execution against the packaged reference data takes seconds compared to the minutes a full test execution may take when reference data is large

For more information on the dev package and local development, see see Build with SmartConnectors (Topic Coming Soon).

---

What's Next

Continue to SmartConnector Execution Variable Design Best Practices to learn how execution variables act as the typed interface between SQL output and Kizen field types, and how to declare, type, and validate them before configuring load steps.

Related Topics

• SmartConnector Design Best Practices
• SmartConnector Load Step Design Best Practices
• SmartConnector Running and Testing Design Best Practices