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.
SELECT CAST(retention_array AS INT) as retention_array FROM session, UNNEST(string_to_array(retention, ',')) AS retention_array WHERE retention NOT LIKE '%NaN%'
SELECT 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:
schemaPath: process.env.CUBEJS_DB_TYPE == "postgres" ? "/schema/postgres" // local : "/schema/bigquery", // production