The target database for pre_operations and post_operations is controlled by the projectId in .df-credentials.json

[Masayuki0927]

Overview

• When executing SQL statements in pre_operations or post_operations, it is expected that they will run against the defaultProject specified in workflow_settings.yaml or the database overwritten in the config block of .sqlx file if defined.
• However, they are executed against the projectId defined in .df-credentials.json, regardless of these settings.
• The following are specific examples.

Example

Pattern1: When the database is overwritten in the config block

.df-credentials.json

{
    "projectId": "test_project_a",
    "location": "asia-northeast1"
}

workflow_settings.yaml

defaultProject: test_project_a
defaultLocation: asia-northeast1
defaultDataset: dataform
defaultAssertionDataset: dataform_assertions

test_pre_operations.sqlx

config {
  type: "table",
  database: "test_project_b",
  name: "test_pre_operations",
  schema: "intermediate",
}

pre_operations {
  DROP TABLE intermediate.test_pre_operations;
}

SELECT id FROM ${ref("source", "test")}

• The DROP TABLE execution is expected to test_project_b, the database defined in the config block, but it is actually executed on test_project_a.
• In this pattern, it is possible that the defaultProject from workflow_settings.yaml was used, so let's look at the next pattern.

Pattern2: When the database in the config block and the defaultProject in workflow_settings.yaml are same, and projectId in .df-credentials.json is only different.

.df-credentials.json

{
    "projectId": "test_project_a",
    "location": "asia-northeast1"
}

workflow_settings.yaml

defaultProject: test_project_b
defaultLocation: asia-northeast1
defaultDataset: dataform
defaultAssertionDataset: dataform_assertions

test_pre_operations.sqlx

config {
  type: "table",
  database: "test_project_b",
  name: "test_pre_operations",
  schema: "intermediate",
}

pre_operations {
  DROP TABLE intermediate.test_pre_operations;
}

SELECT id FROM ${ref("source", "test")}

• The DROP TABLE execution is expected to test_project_b, defined as the defaultProject in workflow_settings.yaml, but it is actually executed on test_project_a.
• The above behaviors were the same for post_operations as well.

Expected Conclusion

• Based on these results, it is possible that the target project for pre_operations and post_operations is being controlled by the projectId in .df-credentials.json , not workflow_settings.yaml nor the config block in the SQLX file.
• If there is anything I may have overlooked or any missing information, please let me know.

[Ekrekr]

Thanks for the bug report!

We definitely have some issues with how the default from how the project configs and billing credentials are loaded. Similar issue: #1696.