Enforcing Snowflake Unique Key Constraint in Retool (No More Duplicate Data!)

Introduction

Retool is an amazing visual wrapper for Snowflake. It offers query abstractions, such as GUI mode and dependency for success/failure.

But as we dive into complex applications, we encounter the limit of Snowflake: Snowflake doesn’t enforce the UNIQUE constraint on standard tables. How would we build a solution in Retool that can enforce such constraint on insert/update operations, and furthermore perform the constraint check on multiple records in a cost efficient manner?

Solution

Let’s suppose we have a table with 4 columns (team, points, rebounds; auto-generated PK) and want to ensure uniqueness on (team, points).

To implement this in the Retool app, we would need 3 things:

• Javascript query to trigger the Snowflake query

• Snowflake query to check uniqueness

• Event handlers (success, failure)

Here’s the order of sequence:

1. Javascript query triggers the Snowflake query along with the parameter

2. Retool constructs the Snowflake query dynamically when triggered; in this case, combinations will be evaluated

3. Depending on the result, success/failure event handlers will run

Here are the Javascript and Snowflake queries:

Snowflake (check_uniqueness). Keep in mind, combinations has to be defined in an additional scope.

Time complexity of this query is O(m + N) where m = number of elements in the JSON array (and hence rows produced by FLATTEN) and N = number of rows in BASKET_BALL_TABLE.

WITH combinations AS (
    SELECT
        PARSE_JSON({{ combinations }}) AS combination_array
),
flattened_combinations AS (
    SELECT
        combination.value:team::STRING AS team,
        combination.value:points::INTEGER AS points
    FROM combinations,
    LATERAL FLATTEN(input => combination_array) AS combination
),
combination_checks AS (
    SELECT
        fc.team,
        fc.points,
        COUNT(BASKET_BALL_TABLE.id) AS combination_count
    FROM flattened_combinations fc
    LEFT JOIN BASKET_BALL_TABLE
    ON fc.team = BASKET_BALL_TABLE.team
    AND fc.points = BASKET_BALL_TABLE.points
    GROUP BY fc.team, fc.points
)
SELECT
   combination_count = 0 AS combination_status
FROM combination_checks;

JavaScript. Construct JSON with JSON.stringify from the table component, namely table_basketball. Then, trigger the Snowflake query check_uniqueness with the combinations variable passed as an additional scope.

// Convert an array with an object to a JSON string with 2-space indentation
const combinations = JSON.stringify(
  table_basketball.data.map((row) => ({
    team: row.TEAM,
    points: row.POINTS,
  })),
  null,
  2
);

const result = await check_uniqueness.trigger({
  additionalScope: {
    combinations: combinations,
  },
});

Conclusion

In this article, we explored how to enforce UNIQUE constraints in Snowflake using Retool, despite Snowflake's limitations with standard tables. The solution involves triggering a Snowflake query via JavaScript, dynamically constructing the query, and using event handlers based on query results. It’s notable that the uniqueness check can cause delays, which downgrades UX, but this is a negligible trade-off.