https://www.gooddata.ai/ logo
Join Slack
Powered by
URGENT PRIORITY AFFECTING ALL OUR CUSTOMERS We’re...
# gooddata-cloud
s
URGENT PRIORITY AFFECTING ALL OUR CUSTOMERS We’re seeing a join-planning issue where adding attributes from our custom "profile fields" dataset causes GoodData to generate SQL that joins through MOVINGGROUPRESIDENCY -> RESIDENCY to reach PROFILEID, instead of using the direct APPLICATION -> PROFILE relationship that exists in our model. This breaks reporting for a specific cycle where residency/moving-group-residency rows are missing (data issue on our side). The main goal here is to make profile fields always usable with application/cycle reporting via APPLICATION.PROFILEID, not via residency/moving group tables. Workspace + report - Workspace: washburn - Report: washburn/4d41ab0b-a1c8-4939-93b5-824ddbbae66d Snowflake details - Schema/table backing profile fields: PC_FIVETRAN_DB.PUBLIC_PUBLIC.VW_PROFILES_WITH_CUSTOM_FIELDS - Tenant scoping is by TENANTID - Example tenant used below: TENANTID = 167 What we expect When a report includes application/cycle fields and a profile field attribute (Student Type, Student Classification, etc.), we expect the join path to be: APPLICATION.PROFILEID -> PROFILE.ID -> VW_PROFILES_WITH_CUSTOM_FIELDS.PROFILEID In other words, profile fields should connect to applications through APPLICATION.PROFILEID, not through residencies. What GoodData is doing instead When profile field attributes are used along with application/cycle fields, GoodData generates SQL that requires residency and moving group residency rows in order to bring in profile fields. The generated SQL starts from MOVINGGROUPRESIDENCY, joins RESIDENCY, then joins VW_PROFILES_WITH_CUSTOM_FIELDS on RESIDENCY.PROFILEID, and groups by RESIDENCY.APPLICATIONID. This makes the report go empty for Cycle = "Fall 2026" because those applications do not have the expected residency / moving group residency rows. Example: SQL generated by GoodData (key excerpt) This is the SQL pattern we see when profile fields are included (trimmed for length, but the join path is intact):
Copy code
sql
-- dsId: 382fcbd4-f676-4ce3-8843-01facab6a7cc
SELECT
  "t13"."ID" AS "l_profile_id",
  "t6"."a_application_id" AS "l_application_id",
  "t6"."a_profile_fields_student_type_331" AS "l_profile_fields_student_type_331",
  "t6"."a_profile_fields_student_classification_334" AS "l_profile_fields_student_classification_334",
  ...
FROM (
  SELECT
    "t2"."APPLICATIONID" AS "a_application_id",
    "t3"."Student Classification_[167]" AS "a_profile_fields_student_classification_334",
    "t3"."Student Type_[167]" AS "a_profile_fields_student_type_331",
    SUM(1) AS "m_...",
    TRUE AS "def_m_..."
  FROM (
    SELECT *
    FROM "MOVINGGROUPRESIDENCY"
    WHERE "_FIVETRAN_DELETED" = false
      AND "TENANTID" = 167
  ) AS "t0"
  INNER JOIN (
    SELECT *
    FROM "RESIDENCY"
    WHERE "_FIVETRAN_DELETED" = false
      AND "TENANTID" = 167
  ) AS "t2"
    ON "t0"."RESIDENCYID" = "t2"."ID"
  INNER JOIN (
    SELECT *
    FROM "PUBLIC_PUBLIC"."VW_PROFILES_WITH_CUSTOM_FIELDS"
    WHERE "TENANTID" = 167
  ) AS "t3"
    ON "t2"."PROFILEID" = "t3"."PROFILEID"
  GROUP BY
    "t2"."APPLICATIONID",
    "t3"."Student Classification_[167]",
    "t3"."Student Type_[167]"
) AS "t6"
...
WHERE
  "t11"."NAME" = 'Fall 2026'
  AND ...
  AND "t6"."def_m_..." ;
Control queries in Snowflake showing why this breaks If we query applications/tags/cycle without requiring residencies, we see applications exist (non-zero count, currently 358 in our case):
Copy code
sql
select count(distinct a."ID") as apps
from PC_FIVETRAN_DB.PUBLIC_PUBLIC."APPLICATION" a
join PC_FIVETRAN_DB.PUBLIC_PUBLIC."CYCLE" c
  on c."ID" = a."CYCLEID"
 and c."TENANTID" = a."TENANTID"
join PC_FIVETRAN_DB.PUBLIC_PUBLIC."APPLICATIONS_TAG_ASSIGNMENT" ata
  on ata."APPLICATIONID" = a."ID"
 and ata."TENANTID" = a."TENANTID"
where a."TENANTID" = 167
  and a."_FIVETRAN_DELETED" = false
  and c."_FIVETRAN_DELETED" = false
  and c."NAME" = 'Fall 2026'
  and ata."TAGNAME" in (
    'first-gen community interest',
    'honors community interest',
    'leadership community interest'
  );
If we force a residency join, the count drops because residencies are missing for those applications:
Copy code
sql
select count(distinct r."APPLICATIONID") as apps_with_residency
from PC_FIVETRAN_DB.PUBLIC_PUBLIC."APPLICATION" a
join PC_FIVETRAN_DB.PUBLIC_PUBLIC."CYCLE" c
  on c."ID" = a."CYCLEID"
 and c."TENANTID" = a."TENANTID"
join PC_FIVETRAN_DB.PUBLIC_PUBLIC."APPLICATIONS_TAG_ASSIGNMENT" ata
  on ata."APPLICATIONID" = a."ID"
 and ata."TENANTID" = a."TENANTID"
join PC_FIVETRAN_DB.PUBLIC_PUBLIC."RESIDENCY" r
  on r."APPLICATIONID" = a."ID"
 and r."TENANTID" = a."TENANTID"
where a."TENANTID" = 167
  and a."_FIVETRAN_DELETED" = false
  and c."_FIVETRAN_DELETED" = false
  and r."_FIVETRAN_DELETED" = false
  and c."NAME" = 'Fall 2026'
  and ata."TAGNAME" in (
    'first-gen community interest',
    'honors community interest',
    'leadership community interest'
  );
How we model profile fields in GoodData We add an extra dataset "profile_fields" backed by VW_PROFILES_WITH_CUSTOM_FIELDS and reference it to the core PROFILE dataset via PROFILEID. This is pushed per tenant workspace via the GoodData API. Relevant code snippet:
Copy code
ts
const CORE_PROFILE_DATASET_ID = 'PROFILE';
const DATASOURCE_TABLE = 'VW_PROFILES_WITH_CUSTOM_FIELDS';
const DATASOURCE_SCHEMA = 'PUBLIC_PUBLIC';

function constructProfileFieldsDataset(customFields, dataSourceId, coreProfilePkId) {
  return {
    id: 'profile_fields',
    title: 'Profile Fields',
    description: 'Custom fields for the Profile dataset.',
    dataSourceTableId: {
      dataSourceId,
      id: DATASOURCE_TABLE,
      path: [DATASOURCE_SCHEMA, DATASOURCE_TABLE],
      type: 'dataSource',
    },

    grain: [],

    attributes: customFields.map((field) => ({
      id: `profile_fields.${field.safeId}_${field.id}`,
      title: `[Profile Field] ${field.name}`,
      sourceColumn: `${field.name}_[${field.tenantId}]`,
      sourceColumnDataType: 'STRING',
      tags: ['Profiles'],
      labels: [],
    })),

    facts: [],

    references: [
      {
        identifier: { id: CORE_PROFILE_DATASET_ID, type: 'dataset' },
        multivalue: true,
        sources: [
          {
            column: 'PROFILEID',
            dataType: 'INT',
            target: { id: coreProfilePkId, type: 'attribute' }, // PROFILE.ID
          },
        ],
      },
    ],

    workspaceDataFilterReferences: [
      {
        filterColumn: 'TENANTID',
        filterColumnDataType: 'INT',
        filterId: { id: 'Tenantid', type: 'workspaceDataFilter' },
      },
    ],
  };
}
Questions / what we need from you 1) Why does the planner choose the MOVINGGROUPRESIDENCY -> RESIDENCY path to connect profile_fields to APPLICATION (via RESIDENCY.APPLICATIONID) rather than using APPLICATION.PROFILEID -> PROFILE.ID to reach profile_fields? 2) Is there a supported way in LDM to prefer or force the APPLICATION.PROFILEID join path so profile fields always work even when residencies are missing? 3) If the recommended approach is to add a second dataset that is explicitly application-grained (for example, a view keyed by APPLICATIONID that includes the profile field columns), can you confirm that’s the intended pattern in GoodData Cloud for "profile attributes usable in application reports"? The key requirement for us: profile fields must be usable consistently in reports filtered by cycle/application data, without depending on residency/moving group tables being populated. Thanks.
k
Sorry @Sasha Bratyshkin, I'm currently experiencing some technical difficulties. Please try again in a few minutes. If this issue persists, please reach out to the administrators of this Slack workspace.
y
Hi Sasha, Apologies for the delay. Im checking this, I will get back to you as soon as possible
Hi Sasha, To answer your questions: 1) Why is the planner choosing the RESIDENCY path? GoodData’s engine starts from the fact / lowest‑grain dataset used by the metric and then follows valid paths in the LDM to reach the attributes in the insight. When there are multiple valid paths between datasets (alternate paths), the engine may choose one that is not what you expect, and this can lead to inconsistent or “wrong” results. The recommended fix is to remove alternate paths from the LDM. Many issues where facts/attributes “disappear” or the SQL unexpectedly goes through a bridge table are traced back to how datasets are connected and arrow directions in the LDM, not to a bug in the planner. In other words, if both of these paths exist and are valid in your LDM: • 
APPLICATION.PROFILEID → PROFILE.ID → profile_fields.PROFILEID
• 
APPLICATION.ID → RESIDENCY.APPLICATIONID → RESIDENCY.PROFILEID → profile_fields.PROFILEID (via MOVINGGROUPRESIDENCY/RESIDENCY)
then the planner is free to pick either, and the docs do not describe a way to “rank” or “prefer” one path over the other. The behavior you see (planner going through RESIDENCY) matches the general description of alternate‑path issues. 2) Is there a supported way to force the 
APPLICATION.PROFILEID
path? What we do recommend in similar situations is: Eliminate alternate paths in the LDM so that only the desired path remains. • If you absolutely cannot change the model, you may sometimes use 
MAQL with BY / explicit lifting
 to connect metrics to specific datasets, but this is about metric–attribute compatibility, not about changing the physical join path the planner uses between datasets. You can essentially connect datasets via MAQL using a BY clause. We used to refer to this as explicit lifting in our older product, but the theory is essentially the same. 3) Is an application‑grained “profile fields” dataset the intended pattern? Yes, this is consistent: when you need attributes to be reliably usable with a particular fact grain, you can create a separate dataset (often via a view) at that grain and connect it directly, avoiding dependence on intermediate datasets that may be sparsely populated. When a dataset is logically at a different grain (e.g., user‑level vs. activity‑level), and you want its attributes to be always usable with a particular fact, a common pattern is to create a separate dataset at the fact’s grain (often via a view) and connect it directly to that fact. • You can try creating a bridge/SQL dataset at the desired grain to avoid going through an unwanted dataset when combining two datasets of your preference. Or alternatively, add a second dataset that is explicitly application‑grained Creating a separate dataset at APPLICATION grain (e.g., a view keyed by 
APPLICATIONID
that already joins in the profile fields) and Connecting that dataset directly to 
APPLICATION
is a recommended way to ensure those attributes are always available in application/cycle reporting, without depending on other bridge tables being populated. Let me know if this helps.
s
so you're telling me that here
Copy code
Creating a separate dataset at APPLICATION grain (e.g., a view keyed by APPLICATIONID that already joins in the profile fields) and Connecting that dataset directly to APPLICATION is a recommended way to ensure those attributes are always available in application/cycle reporting, without depending on other bridge tables being populated.
wouldn't this create two sets of profile fields now? like [Profile Field} X [Applications] & [Profile Field] Y [Profiles]?
y
Hi Sasha, You’re right that this approach would mean you have two different datasets that expose “profile‑field‑like” attributes, but they would serve different grains/use cases, not be a single attribute duplicated in a confusing way. The intent was to: Create a separate dataset at APPLICATION grain (e.g., a view keyed by APPLICATIONID that already joins in the profile fields) and connect it directly to APPLICATION… That implies: Profile‑grained dataset (what you already have): Keyed by PROFILEID ,used when you analyze profiles and their fields (one row per profile). Application‑grained dataset (the new one): Keyed by APPLICATIONID. Contains application‑level copies of the same logical fields (e.g., “Student Type at time of application”). Used when you analyze applications/cycles and want those fields to always be available, even if RESIDENCY/MOVINGGROUPRESIDENCY is empty. So yes, you would effectively have: 
[Profile Field] … (profile grain)
[Application Profile Field] … (application grain)
They are not the same attribute in the LDM; they are two attributes with the same business meaning but different technical grain, each connected to the dataset where it’s safe and unambiguous to use it. This pattern is consistent to help avoid alternate paths between the same entities; if you need the same logical information at multiple grains, model it as separate datasets/attributes at each grain, rather than relying on a single attribute that can be reached via multiple join paths. Limitations to M:N If you want to reduce confusion for users, you can: • Name/label the application‑grained fields clearly (e.g., “Application Student Type”), and • Keep the profile‑grained ones under a “Profile” or “Person” section. Let me know if this helps.
s
sorry but could someone from the actual engineering team chime in and suggest as solution? this is spitting the same answers chat gpt was giving me before I even opened the ticket
y
Hi Sasha, Can you share with us a direct link to the report, so that we can have a look?
s
it's the third line of this ticket 😕
image.png
👍 1
y
Hi Sasha, Thank you for your patience while we investigated this in detail. After checking your LDM and testing, below is a full summary of the troubleshooting steps we performed, how we isolated the behavior, and what we found. To understand exactly where the dependency on 
Residency/MovingGroupResidency
was introduced, we created a series of minimal test reports and examined the generated SQL at each step. Test 1: 
Profile ID + Profile Field Value
only We created a report containing only Profile ID and Profile Field Value in the result , the generated SQL queried only the 
PROFILEFIELD
table.No joins to 
Application
, 
Residency
, or 
MovingGroupResidency
occurred.This confirms that the ProfileField dataset works correctly on its own and does not inherently depend on Residency. Test 2: 
Application ID + Profile Field Value
 Next, we created a report containing only containing Application Id and Profile Field Value. The generated SQL immediately anchored on: 
MOVINGGROUPRESIDENCY → RESIDENCY → APPLICATION → PROFILEFIELD
Even though the join to 
PROFILEFIELD
is performed using 
APPLICATION.ProfileID,
the query begins from the 
MovingGroupResidency/Residency branch
. This means that simply combining 
Application ID
with a multi-valued Profile Field causes the engine to choose a join path that depends on 
Residency/MovingGroupResidency
rows existing. No Cycle fields, Tags, or additional attributes were included at this stage. *This Happens b*ased on the LDM structure.Profile Fields are modeled as multi-valued (M:N) per Profile. 
Application
references Profile via Profile`ID`. Application has a 1:N relationship to Residency. Residency connects to MovingGroupResidency. Because multiple valid join paths exist between Application and Profile Fields, and because Profile Fields are multi-valued, the analytical engine must resolve the join graph and determine a driving dataset. In this model, the engine selects the 
MovingGroupResidency
→ Residency branch as the anchor, and then joins forward to Application and ProfileField. From a relational perspective this is valid, but it introduces an important dependency. If residency or moving-group-residency rows are missing, applications will not appear in the result set even if the applications and profile field values exist. This is why the report returns empty, where residency rows are missing. Kindly note that there is currently no supported setting in the LDM to explicitly force the engine to prefer the 
APPLICATION.PROFILEID → PROFILEFIELD.PROFILEID
path. Join resolution is determined by the full relationship graph, relationship cardinality (including multivalue relationships)and required aggregation grain. Because an alternative valid path exists through Residency, the planner is allowed to select it. As explained earlier, to guarantee that 
Profile Fields
always work with 
Application/Cycle
reporting, regardless of residency completeness, the supported approach is to model an explicit application-grain bridge/view, for example: 
APPLICATION_PROFILEFIELD {
(StudentID ,ApplicationID ,ProfileID.,Profile Field identifierValue)}
This dataset would ideally be related directly to 
Application
via 
ApplicationID
, Remove dependency on `Residency/MovingGroupResidency`tables and ensure Profile Fields remain usable even when residency rows are absent. Please let us know if this helps.
Hi Sasha, To follow up with a quick summary of my previous response, When we tested Profile ID and Profile Field Value alone, everything worked correctly and did not involve Residency. However, once we added Application ID, the engine chose a join path through MovingGroupResidency → Residency → Application before joining to ProfileField. Because of this, if Residency records are missing, the Application won’t appear in the results , even if Profile Field data exists. To remove this dependency completely, you would need to introduce an application-level bridge dataset linked directly by ApplicationID. Kindly refer to our documentation: many-to-many , BY, and lifting as it could helpful for your use-case. Let me know if this helps.
6 Views
Open in Slack
PreviousNext
Powered by