Handling data schemas for different databases

We are using Postgres primarily while developing and writing cubes locally and BigQuery on production. The main challenge we faced was while BQ is built on the same engine as Postgres, the raw queries differ at some places. Hence, our local queries weren’t working on production and vice-versa.

For example:
Postgres syntax

CAST(retention_array AS INT) as retention_array
FROM session, UNNEST(string_to_array(retention, ',')) AS retention_array
WHERE retention NOT LIKE '%NaN%'

BigQuery syntax

CAST(retention_array AS INT64) as retention_array
FROM session, UNNEST(SPLIT(retention, ',')) AS retention_array
WHERE retention NOT LIKE '%NaN%'

We came up with managing two different schemas to support both the database types. We manage different schemas in different directories and set the path based on environment variable configured:

    process.env.CUBEJS_DB_TYPE == "postgres"
      ? "/schema/postgres" // local
      : "/schema/bigquery", // production