Remap a Logical Data Model to a New Data Source

Follow these steps to point an existing logical data model at a new data source. The new data source needs to contain the same tables and columns. Your datasets keep their identifiers, so your metrics, visualizations, and dashboards continue to use them.

In a workspace hierarchy, run these steps in the parent workspace. Child workspaces inherit the model.

Remap the model

The example below moves a model from a PostgreSQL data source to a BigQuery data source. Both databases store unquoted table and column names in lower case. If your new database stores those names in a different case, also follow Change the case of table and column names.

Steps:

  1. Copy the tables and columns into the new database. Use the same names the model already stores.

  2. Register the new data source.

  3. Save a copy of the current model, replace the data source IDs, and write the model back to the workspace:

    from gooddata_sdk import GoodDataSdk
    
    # GoodData host in the form of uri eg. "https://*.gooddata.com" (GoodData Cloud),
    # or "http://localhost:3000" (GoodData Cloud Native)
    host = "<HOST_URL>"
    token = "<API_TOKEN>"
    workspace_id = "<WORKSPACE_ID>"
    sdk = GoodDataSdk.create(host, token)
    
    # Save the current model to disk before you change it.
    sdk.catalog_workspace_content.store_declarative_ldm(workspace_id)
    
    data_source_mapping = {
        "<POSTGRESQL_DATA_SOURCE_ID>": "<BIGQUERY_DATA_SOURCE_ID>",
    }
    
    declarative_model = sdk.catalog_workspace_content.get_declarative_ldm(workspace_id)
    declarative_model.ldm.modify_mapped_data_source(data_source_mapping)
    sdk.catalog_workspace_content.put_declarative_ldm(workspace_id, declarative_model)

    In data_source_mapping, the key is the data source ID stored in the model today. The value is the ID of the data source you registered in the previous step. Add one pair for each data source you want to replace. Omit an ID when you want that dataset to stay on its current data source.

  4. Update each SQL dataset query so that it uses the SQL dialect of the new database and the table and column names as they exist there. The script in the previous step updates the data source ID on a SQL dataset and keeps the query text you wrote.

  5. Open a visualization that uses this model and check that it shows data from the new database.

Change the case of table and column names

Follow these steps when the new database stores unquoted table and column names in a different case from the names in your model. Add the call before put_declarative_ldm in the script above.

Steps:

  1. Add this call immediately after modify_mapped_data_source:

    declarative_model.ldm.change_tables_columns_case(upper_case=False)
  2. Set upper_case to True when you want every mapped name rewritten in upper case. Set it to False when you want every mapped name rewritten in lower case.

The call updates the table name on each dataset, and the column names mapped to attributes, labels, facts, aggregated facts, and references.

Skip this section when you move between PostgreSQL and BigQuery. Both store unquoted names in lower case. Snowflake stores unquoted names in upper case, so set upper_case to True when you move a lower-case model there. When you created the tables with quoted identifiers, those names keep the case you wrote. Rename those columns one by one in the model when they do not all use the same case.