TS Recipe Formula Business Days Snowflake

ThoughtSpot + Snowflake: Business Day UDFs

ThoughtSpot's built-in diff_days / diff_hours / diff_minutes count calendar days. When users need to exclude weekends — SLA tracking, order fulfillment time, invoice payment terms, support ticket aging — there's no native option. This skill closes that gap by deploying three Snowflake scalar UDFs and showing how to call them from ThoughtSpot formulas using sql_int_op / sql_string_op.

All three clamp weekend boundaries: if start or end falls on a Saturday or Sunday, the function shifts to the nearest weekday rather than erroring. get_business_duration_str internally calls get_business_minutes_clamped by fully qualified name, so creation order matters — create minutes first.

The UDF DDL lives in references/business-day-udfs.sql and is deployed with ts snowflake exec (ts-cli ≥ 0.48.0) — the SQL is never retyped, and {target_db} / {target_schema} are filled deterministically via --var.

In the commands below, {skill_dir} is the absolute path of the directory containing this SKILL.md (e.g. ~/.claude/skills/ts-recipe-formula-business-days-snowflake in Claude Code, ~/.snowflake/cortex/skills/... in Cortex Code CLI). Substitute the real path when running.

Ask one question at a time for dependent decisions. Batch independent questions into a single prompt to cut round-trips.

Prerequisites

• Snowflake profile configured — run /ts-profile-snowflake if not
• Snowflake role with CREATE FUNCTION privilege on the target schema

Step 1 — Connect to Snowflake

Read ~/.claude/snowflake-profiles.json. If the file is missing or the array is empty, ask the user to run /ts-profile-snowflake first.

If multiple profiles exist, show a numbered list and ask which to use. If exactly one exists, confirm it. Save the chosen profile name as {sf_profile_name}.

Test the connection — ts snowflake exec handles both method: python and method: cli profiles, reusing the same connector as ts load (no credential handling in this skill):

ts snowflake exec -q "SELECT CURRENT_USER()" --sf-profile "{sf_profile_name}"

Expect a JSON object on stdout with a rows array containing the current user. If the command fails, refer the user to /ts-profile-snowflake for credential troubleshooting.

Step 2 — Collect Target Database and Schema

Ask:

Which Snowflake database should these UDFs be created in?
(e.g. ANALYTICS, PROD_DB)

Save as {target_db} (uppercase).

Which schema within {target_db}?
(e.g. PUBLIC, SHARED, UTILS)

Save as {target_schema} (uppercase).

Confirm before creating anything:

Ready to create 3 UDFs in {target_db}.{target_schema}:

  get_business_minutes_clamped(start_ts, end_ts)            → INT
  get_business_days_clamped(start_ts, end_ts, inclusive)    → INT
  get_business_duration_str(start_ts, end_ts)               → STRING

Weekend boundaries are clamped to the nearest weekday.
Existing functions with these names will be replaced (CREATE OR REPLACE).

Proceed? (Y / N)

Step 3 — Create the UDFs

Deploy all three UDFs in one call. The DDL comes from references/business-day-udfs.sql; ts snowflake exec runs its statements in file order (minutes first, so get_business_duration_str's fully-qualified call resolves) and stops at the first error, so a dependent function is never created after the function it references failed.

ts snowflake exec -f "{skill_dir}/references/business-day-udfs.sql" \
  --sf-profile "{sf_profile_name}" \
  --var target_db={target_db} \
  --var target_schema={target_schema}

--var fills the {target_db} / {target_schema} placeholders in the SQL. If a placeholder is left without a --var, the command aborts before touching Snowflake rather than shipping a literal {target_schema}.

If the command exits non-zero, show the error and stop — do not proceed to verification. On success, confirm:

✓ get_business_minutes_clamped created
✓ get_business_days_clamped created
✓ get_business_duration_str created

Step 4 — Verify

Run three checks to confirm the UDFs return expected values. Each reads the scalar from the rows array of the JSON on stdout.

Test 1: Mon 2026-01-05 → Fri 2026-01-09, exclusive → 4 business days

ts snowflake exec --sf-profile "{sf_profile_name}" \
  -q "SELECT {target_db}.{target_schema}.get_business_days_clamped('2026-01-05'::TIMESTAMP, '2026-01-09'::TIMESTAMP, FALSE)"
# Expected: 4

Test 2: One full business day (Mon 09:00 → Tue 09:00) → 1440 minutes

ts snowflake exec --sf-profile "{sf_profile_name}" \
  -q "SELECT {target_db}.{target_schema}.get_business_minutes_clamped('2026-01-05 09:00:00'::TIMESTAMP, '2026-01-06 09:00:00'::TIMESTAMP)"
# Expected: 1440

Test 3: Duration string for same interval → 24:00

ts snowflake exec --sf-profile "{sf_profile_name}" \
  -q "SELECT {target_db}.{target_schema}.get_business_duration_str('2026-01-05 09:00:00'::TIMESTAMP, '2026-01-06 09:00:00'::TIMESTAMP)"
# Expected: 24:00

If any test returns an unexpected value, show the actual result and re-run Step 3 to refresh the functions.

Step 5 — ThoughtSpot Formula Examples

Present the formula syntax for calling these UDFs inside ThoughtSpot:

The UDFs are ready. Here's how to use them in ThoughtSpot formulas:

Business days between a date column and today (exclusive):
  sql_int_op ("{target_db}.{target_schema}.get_business_days_clamped({0},{1}, FALSE)", [your date column], today())

Business days between two date columns (inclusive):
  sql_int_op ("{target_db}.{target_schema}.get_business_days_clamped({0},{1}, TRUE)", [start date], [end date])

Weekday elapsed time as HH:MM string:
  sql_string_op ("{target_db}.{target_schema}.get_business_duration_str({0},{1})", [your date column], today())

Weekday-only minutes elapsed:
  sql_int_op ("{target_db}.{target_schema}.get_business_minutes_clamped({0},{1})", [start date], [end date])

To add a formula to a ThoughtSpot Model:
  1. Open the Model → Edit
  2. Click + Add formula
  3. Name it (e.g. "Business Days Open", "SLA Minutes Elapsed")
  4. Paste the expression above, replacing [your date column] with the column name

Formula type note:
  sql_int_op   → numeric column (use for counts and minutes)
  sql_string_op → text column (use for HH:MM display)

Then ask:

Would you like help adding one of these formulas to a specific ThoughtSpot Model? (Y / N)

If Y:

1. Check ~/.claude/thoughtspot-profiles.json — if missing, ask user to run /ts-profile-thoughtspot first

2. Verify: ts auth whoami --profile "{ts_profile_name}"

3. Ask: which model, what to name the formula (e.g. Business Days Open), which UDF to use, and which date column(s) to pass as arguments

4. Export TML: ts tml export {model_guid} --profile {ts_profile_name} --fqn --parse

5. Add both of the following to the TML — a formula alone is not enough; without the columns[] entry the formula is hidden from users:

   In formulas[] — the expression:

   - id: "formula_{formula_name}"          # e.g. formula_Business Days Open
     name: "{formula_name}"
     expr: >-
       sql_int_op ("{target_db}.{target_schema}.get_business_days_clamped({0},{1}, FALSE)", [{date_column}], today())
     properties:
       column_type: MEASURE
   

   For get_business_duration_str (returns a string), use column_type: ATTRIBUTE and wrap with sql_string_op.

   In columns[] — the visible column entry that references the formula:

   - name: "{formula_name}"
     formula_id: "formula_{formula_name}"  # must match the id above exactly
     properties:
       column_type: MEASURE                # ATTRIBUTE for string UDF
       aggregation: SUM                    # omit for ATTRIBUTE
       index_type: DONT_INDEX
   

6. Import: ts tml import --profile {ts_profile_name} --policy ALL_OR_NONE

7. Verify the formula appears in the Model — search for the formula name or run ts metadata search --profile {ts_profile_name} --name "{formula_name}" to confirm it is indexed.

If N → done.

Error Handling

Changelog