Skip to content
Create account
or
Sign in
The Stripe Docs logo
/
Ask AI
Create account
Sign in
Get started
Payments
Finance automation
Platforms and marketplaces
Money management
Developer tools
Get started
Payments
Finance automation
Get started
Payments
Finance automation
Platforms and marketplaces
Money management
Overview
Billing
Tax
Reporting
Data
    Overview
    Schema
    Custom reports
    Sigma API
    Create custom reports
    Write queries using Sigma
      Schedule queries
      Migrate queries
    Query data across an organization
    Sync Stripe data
    Data Pipeline
    Export data to a data warehouse
    Export data to cloud storage
    Data management
    Data freshness
    Business and product data use cases
    Import external data
Startup incorporation
HomeFinance automationDataWrite queries using Sigma

Migrate queries

Migrate your Sigma queries from Presto to Trino.

Copy page

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