> ## Documentation Index
> Fetch the complete documentation index at: https://zenskar.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Amazon Redshift

> Connect an Amazon Redshift cluster or Serverless workgroup as a remote data source, then query it directly from a billable metric's SQL builder, without syncing the data into Zenskar first.

## Concepts

### Query directly from source

Zenskar can connect to a data source by syncing it into Zenskar, or by querying it live. With **Directly from source**, no data is copied into Zenskar. Each time a billable metric runs, Zenskar sends the SQL straight to your Redshift cluster or workgroup and returns the live result. This means no sync lag and no duplicate copy of your data, but it also means Redshift must be reachable from Zenskar's servers for every query, not just during initial setup.

### Why network access matters

Since queries run live, Zenskar's servers need an open path to your cluster or workgroup on every request. That is why the endpoint must be reachable (publicly, or via private networking), and why the VPC security group must allow inbound traffic from Zenskar's IP address on port 5439. If this path is missing, a connection typically hangs and times out rather than failing with a clear error, so it is worth confirming before setup.

### How SSL fits in

Redshift presents a trusted certificate by default, so encryption works out of the box with SSL mode and certificate fields left blank. Full certificate verification (`verify-ca` or `verify-full`) is an extra layer for organizations whose security policy requires it, not a replacement for the default encryption, and most setups do not need it.

### How billable metrics use a remote data source

A metric's SQL builder can pull from synced data or remote data. Choosing **Remote data** and a Redshift source created with **Directly from source** is what lets the metric query Redshift each time it runs. Zenskar variables, such as customer external ID and billing start and end dates, are placeholders substituted at run time, so one query can serve every customer and billing period instead of needing a separate query for each.

## How-to guides

### How to add Amazon Redshift as a data source

Before you begin, gather your connection details and confirm your Redshift endpoint is reachable from Zenskar's servers. You can find the endpoint in the AWS console, under **Amazon Redshift > your cluster or Redshift Serverless > Workgroup configuration > General information**. Copy only the endpoint host, the part before `:5439/dev`.

<Steps>
  <Step title="Open the data sources page">
    From the left navigation, go to **Data sources**, then click **Add new data source** in the top right.
  </Step>

  <Step title="Select Amazon Redshift">
    In the source type list, choose **Amazon Redshift**.
  </Step>

  <Step title="Choose direct access">
    Under **Source access**, select **Directly from source**. This is the option that lets a billable metric query Redshift live, without syncing data into Zenskar.
  </Step>

  <Step title="Enter the connection details">
    Fill in the connection fields using the values you gathered: host, port, database name, username, schema, and password.

    <Warning>
      Type the password raw, without URL-encoding it. Zenskar handles any encoding internally.
    </Warning>
  </Step>

  <Step title="Configure SSL">
    Leave **SSL connection** enabled. For a standard setup, leave **SSL mode** and the certificate fields blank. Only set **SSL mode** to `verify-ca` or `verify-full`, and paste your Amazon Redshift CA certificate, if your security policy requires full certificate verification. Client certificate and client key fields are not used by Redshift and should stay blank.
  </Step>

  <Step title="Save">
    Click **Save**. The new data source appears in your list with status **Active** and is immediately ready to query.
  </Step>
</Steps>

<Card title="Connection and SSL reference" icon="table" href="#connection-detail-fields">
  Look up every connection field, network access requirement, and SSL option.
</Card>

### How to query Redshift from a billable metric

<Steps>
  <Step title="Open the SQL builder">
    Go to **Usage > Billable metrics**, create a new metric or open an existing one, then switch to the **SQL builder** tab.
  </Step>

  <Step title="Set the source to remote data">
    Under **Source of data**, select **Remote data** instead of **Synced data**. From the **Remote datasource** dropdown, choose the Amazon Redshift source you created earlier.

    <Note>
      If the dropdown is empty, the source was not created with **Directly from source** access. Go back and check the previous how-to guide.
    </Note>
  </Step>

  <Step title="Browse your tables">
    Use the **Data Navigator** panel to browse the tables Zenskar can see in your Redshift database. Click a table to insert its name into the query editor.
  </Step>

  <Step title="Write your query">
    Write standard SQL against your Redshift tables. Use the **Zenskar Variables** tab to insert billing context values, such as **Customer External ID**, **Billing Start Date**, and **Billing End Date**, that Zenskar fills in at run time.

    ```sql theme={null}
    SELECT NULLIF(COUNT("transaction_id"), 0) AS "quantity"
    FROM transactions
    WHERE DATE("timestamp") >= DATE( {Billing Start Date} )
      AND DATE("timestamp") <= DATE( {Billing End Date} )
      AND "customer_id" = CAST( {Customer External ID} AS VARCHAR )
      AND "transaction_status" = true
    ```
  </Step>

  <Step title="Preview the result">
    In the **Query preview** bar, pick a customer and a billing period, then click **Run query** to see the rows Zenskar returns from Redshift.

    <Note>
      Make sure your test data falls inside the chosen billing period, or the result will be empty.
    </Note>
  </Step>

  <Step title="Save the metric">
    When the preview looks correct, click **Save changes**. The metric now queries Redshift live whenever it runs.
  </Step>
</Steps>

<CardGroup cols={2}>
  <Card title="Zenskar variables reference" icon="brackets-curly" href="#zenskar-variables-available-in-the-sql-builder">
    See every variable available in the SQL builder and what each one does.
  </Card>

  <Card title="Redshift SQL notes" icon="database" href="#redshift-sql-notes-and-limitations">
    Check syntax rules and unsupported functions before you write your query.
  </Card>
</CardGroup>

## Reference

### Connection detail fields

| Field         | Description                                                 | Example                                                              |
| ------------- | ----------------------------------------------------------- | -------------------------------------------------------------------- |
| Host          | Redshift endpoint host, without the port or database suffix | `my-workgroup.123456789.us-east-1.redshift-serverless.amazonaws.com` |
| Port          | Redshift connection port                                    | `5439` (Redshift default)                                            |
| Database name | Name of the database to connect to                          | `dev` (the default database)                                         |
| Username      | Username for the database                                   | `prod_db`                                                            |
| Schema        | Schema(s) that hold your tables                             | `public`                                                             |
| Password      | Database password, entered raw and not URL-encoded          | n/a                                                                  |

### Network access requirements

| Requirement                      | Detail                                                                                                                |
| -------------------------------- | --------------------------------------------------------------------------------------------------------------------- |
| Reachability                     | Workgroup or cluster must be reachable from Zenskar, typically via **Publicly accessible**, or via private networking |
| Security group                   | VPC security group must allow inbound traffic on port 5439 from Zenskar's IP address                                  |
| Unknown IP or private networking | Contact your Zenskar administrator before continuing                                                                  |
| Symptom of missing access        | A connection that hangs and times out                                                                                 |

### SSL options

| Setting                         | Standard setup | When to change                                                                        |
| ------------------------------- | -------------- | ------------------------------------------------------------------------------------- |
| SSL connection                  | Enabled        | Leave enabled                                                                         |
| SSL mode                        | Blank          | Set to `verify-ca` or `verify-full` only if full certificate verification is required |
| Certificate                     | Blank          | Paste your Amazon Redshift CA certificate if using `verify-ca` or `verify-full`       |
| Client certificate / client key | Blank          | Not used by Redshift, always leave blank                                              |

### Zenskar variables available in the SQL builder

| Variable             | Purpose                                                   |
| -------------------- | --------------------------------------------------------- |
| Customer External ID | Identifies the customer the metric is being evaluated for |
| Billing Start Date   | Start of the billing period being queried                 |
| Billing End Date     | End of the billing period being queried                   |

### Redshift SQL notes and limitations

* Quote reserved words, such as `"timestamp"`, in double quotes.
* Redshift does not support every PostgreSQL function. Catalog helpers such as `pg_table_size` and `obj_description` are not available.
* Ordinary `SELECT` queries against your tables work normally.

### Troubleshooting

| Symptom                             | Likely cause                                                                                     |
| ----------------------------------- | ------------------------------------------------------------------------------------------------ |
| Connection hangs and times out      | Network access to Redshift is not fully configured (reachability or security group rule missing) |
| Remote datasource dropdown is empty | The data source was not created with **Directly from source** access                             |
| Query preview returns no rows       | Test data does not fall inside the chosen billing period                                         |