Skip to content
Create account
or
Sign in
The Stripe Docs logo
/
Ask AI
Create account
Sign in
Get started
Payments
Revenue
Platforms and marketplaces
Money management
Developer resources
APIs & SDKsHelp
Overview
Billing
OverviewAbout the Billing APIs
Subscriptions
Invoicing
Usage-based billing
Quotes
Customer management
Billing with other products
Revenue recovery
Automations
Test your integration
Tax
Overview
Use Stripe tax
Manage compliance
Reporting
Overview
Select a report
Configure reports
Reports for multiple accounts
Reports API
Revenue recognition
Data
Overview
Query business data
Sigma
    Overview
    How Sigma works
    Use the Sigma API
    Write queries using Sigma
      Schedule queries
      Migrate queries
    Query data across an organization
Data Pipeline
Import external data
United States
English (United States)
HomeRevenueSigmaWrite queries using Sigma

Migrate queries

Migrate your Sigma queries from Presto to Trino.

We upgraded Sigma’s query infrastructure from Presto v334 to Trino v414. Most queries run faster as a result of the upgrade, but a few queries might error unexpectedly or produce results in different formats.

Use the following suggestions to make your Sigma queries compatible with Trino v414.

Invalid time zones

The AMERICA/NEW_YORK time zone is no longer valid in Trino v414.

Instead, use America/New_York.

Invalid column references

Trino v414 doesn’t allow referencing column names with their originating sub-query or conditional table expression (CTE) out of scope.

The previous query is invalid because the sub-query c isn’t defined at the top level but is referenced at the top level, out of its defined scope.

Either reference the column without the sub-query or define the sub-query at the same level as its reference.

Scientific notation

Casting a double to a varchar in Trino v414 produces results in scientific notation instead of decimal notation as in Presto v334.

To maintain this decimal notation, cast the double as a decimal and then as a varchar.

Timestamp functions

FROM_UNIXTIME

Trino v414 assumes that the result timestamp is in UTC and adds a trailing “UTC” when using from_unixtime.

To remove the trailing “UTC”, cast the result of from_unixtime as timestamp.

TO_ISO8601

In Presto v334, to_iso8601 adds a trailing Zulu time zone suffix (“Z”) to a timestamp without a time zone while Trino v414 doesn’t.

To make sure the trailing Zulu time zone suffix is added, interpret the timestamp in UTC before calling to_iso8601.

Query non-determinism

If you query is non-deterministic, regardless of the Sigma version, different executions can yield different results. Here are common query patterns that can lead to non-deterministic results.

Top K queries

If the 10th and 11th latest created charges were created at the same time, there’s no guarantee which charge is returned. Make sure to also sort on a unique identifier for deterministic results.

Window aggregation

The above query returns the biggest charge for each customer using row_number. See window functions. If a customer has multiple charges with the same maximum amount, there’s no guarantee which charge is returned.

Sort on a unique identifier in the window ordering for deterministic results.

Was this page helpful?
YesNo
  • Need help? Contact Support.
  • Check out our changelog.
  • Questions? Contact Sales.
  • LLM? Read llms.txt.
  • Powered by Markdoc