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
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 API
Reports for multiple accounts
Revenue recognition
Data
OverviewSchema
Custom reports
    Overview
    Sigma API
    Write queries using Sigma
      Schedule queries
      Migrate queries
    Query data across an organization
Data Pipeline
Data management
HomeRevenueCustom reportsWrite 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.

-- FAILED: Presto error: NOT_SUPPORTED: Time zone not supported: AMERICA/NEW_YORK select date_format( c.created AT TIME ZONE 'AMERICA/NEW_YORK', '%Y-%m-%d' ) from charges c

Instead, use America/New_York.

-- VALID select date_format( c.created AT TIME ZONE 'America/New_York', '%Y-%m-%d' ) from charges c

Invalid column references

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

-- FAILED: Presto error: COLUMN_NOT_FOUND: Column 'c.created' can't be resolved select c.created from (select created from charges c)

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.

-- VALID select created from (select created from charges c)
-- VALID select c.created from (select created from charges) c

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.

-- RESULT: 1.0E2 select cast(100.0 as varchar)

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

-- RESULT: 100.0 select cast(cast(100.0 as decimal(18,1)) as varchar)

Timestamp functions

FROM_UNIXTIME

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

-- Trino v414 RESULT: 1970-01-01 00:00:00.000 UTC -- Presto v334 RESULT: 1970-01-01 00:00:00 +0000 select from_unixtime(0)

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

-- RESULT: 1970-01-01 00:00:00 +0000 select cast(from_unixtime(0) 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.

-- Presto v334 RESULT: 2024-04-01T00:00:00.000Z -- Trino v414 RESULT: 2024-04-01T00:00:00 select to_iso8601(timestamp '2024-04-01')

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

-- RESULT: 2024-04-01T00:00:00Z select to_iso8601(timestamp '2024-04-01' at time zone 'UTC')

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

-- POTENTIALLY NON-DETERMINISTIC select * from charges order by created DESC limit 10

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.

-- DETERMINISTIC select * from charges order by created DESC, id limit 10

Window aggregation

-- POTENTIALLY NON-DETERMINISTIC select * from ( select c.*, row_number() over ( partition by c.customer_id order by c.amount DESC ) as row_rank from charges c ) where row_rank = 1

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.

-- DETERMINISTIC select * from ( select c.*, row_number() over ( partition by c.customer_id order by c.amount DESC, c.created DESC, c.id ) as row_rank from charges c ) where row_rank = 1
Was this page helpful?
YesNo
Need help? Contact Support.
Join our early access program.
Check out our changelog.
Questions? Contact Sales.
LLM? Read llms.txt.
Powered by Markdoc