> For the complete documentation index, see [llms.txt](https://developer.kizen.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://developer.kizen.com/docs/concepts/smartconnectors/smartconnector-design-best-practices/smartconnector-sql-design-best-practices.md).

# SmartConnector SQL Design Best Practices

{% hint style="success" %}
**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 <code class="expression">space.vars.smartconnectors</code> are easier to validate, maintain, and operate reliably in production.
{% endhint %}

## 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 <code class="expression">space.vars.smartconnector</code> 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:

```sql
-- 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](/docs/concepts/smartconnectors/smartconnector-sql-processing.md).

### 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:

```sql
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:

```sql
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](/docs/concepts/smartconnectors/smartconnector-settings.md).

### 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 <code class="expression">space.vars.smartconnector</code> 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](/docs/concepts/smartconnectors/smartconnector-design-best-practices/smartconnector-execution-variable-design-best-practices.md) 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.

<details>

<summary>Related Topics</summary>

* [SmartConnector Design Best Practices](/docs/concepts/smartconnectors/smartconnector-design-best-practices.md)
* [SmartConnector Load Step Design Best Practices](/docs/concepts/smartconnectors/smartconnector-design-best-practices/smartconnector-load-step-design-best-practices.md)
* [SmartConnector Running and Testing Design Best Practices](/docs/concepts/smartconnectors/smartconnector-design-best-practices/smartconnector-running-and-testing-design-best-practices.md)

</details>


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://developer.kizen.com/docs/concepts/smartconnectors/smartconnector-design-best-practices/smartconnector-sql-design-best-practices.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.