Alteryx to Vertica Database

[RuochanGao]

Hello,

I need to build a scheduled Alteryx workflow to pull data from the Vertica database and automatically insert the output into a new table in the database on a daily basis.

Currently, I have successfully connected Alteryx to Vertica, with no problem to pull any data using query, however, there are some issues with output:

1. The output connection seems ok because it successfully wrote data to the vertical table, but only when I sampled the output to 20 records or fewer.

2. The total output is not large, actually, less than 0.5G, while it still confused me because 20 records is such a small amount.

3. I have tried to adjust the ODBC setting to check the 'Direct Batch Insert' and set the 'Result buffer size' to 1G, but it seems to have not helped. 

I have attached the message box from <20 and >20. Please advise if you have any ideas; any insight would be appreciated.

[Vinod28]

Hi @RuochanGao 

Thanks for reaching out and for providing detailed context along with the screenshots; that was extremely helpful in identifying the root cause.

Based on your description and our analysis of the attached logs, the issue is not with connectivity or query logic but with how the Vertica ODBC driver handles larger insert operations from Alteryx. Here's a breakdown of what’s happening and how we recommend resolving it.

Recommended Solution:

1. Output Tool Configuration

• Uncheck "Direct Batch Input" in the Output Data tool.

• Avoid using COMMIT; in the PostSQL section.

• Use PreSQL only  DELETE if truncation is required before the insert.

2. Data Preparation

• Ensure all string fields are trimmed and do not exceed the defined VARCHAR lengths.

• Explicitly cast column types using the Select Tool to avoid type mismatches.

• Use the Data Cleansing Tool to handle nulls and remove problematic characters.

3. Batch Insert (Optional for Larger Datasets)

• For data volumes over 10,000 rows:

  • Use the Tile Tool to split the dataset into manageable chunks (e.g., 500 records).

  • Use a Batch Macro to insert each chunk sequentially, reducing the risk of failure.

4. Logging and Alerting

• Add a Summarise Tool to log the total number of records written.

• Output this log to a CSV file or an audit table in Vertica.

• Optionally configure the Email Tool to send alerts when record counts are too low or insert operations fail.

---

Scheduling the Workflow

Once the above changes are implemented:

• Schedule the workflow using Alteryx Designer Scheduler or Alteryx Server.

• A recommended time would be daily at 7:00 AM, with logging and alert hooks enabled to track performance and data integrity.