[Hypercare] restrict_direct_pg_catalog_access implementation

[mikealfare]

Description

We recently began migrating dbt-redshift to use the redshift-connector SDK for metadata collection in an effort to retire our custom metadata queries that use pg_catalog. While we intended no end user impact with this change, we felt the blast radius was large enough that we should gate this behind a behavior flag (restrict_direct_pg_catalog_access). This would allow users to turn this functionality off in the event that their experience degrades or breaks. In an effort to expedite remediation of these issues, we are asking users to participate in this discussion and to share any negative experiences they encounter.

Bug reports

Bug reports will be collected from comments below and organized in this list. Feel free to submit your bug report as a comment below, or by using our normal bug report template and linking the issue in a comment below.

• [Bug] dbt build fails when flag restrict_direct_pg_catalog_access set True #914
• https://dbt-labs.slack.com/archives/C067EUS2D7S/p1727453184616519 (multiple in this thread)

[migueldichoso]

ESTO Holdings OU (id: 193974 in US MT) is seeing snapshot build failures after enabling flag restrict_direct_pg_catalog_access.

Bad job Run #328791671

• Commit SHA: #1da1eb0
• Triggered Sep 28, 2024, 6:07 AM UTC
• Finished Sep 28, 2024, 6:09 AM UTC
• Portion of dbt snapshot debug logs:

2024-09-28 06:08:24.904248 (MainThread): 06:08:24  Running dbt...
2024-09-28 06:08:24.947711 (MainThread): 06:08:24  Redshift adapter: Setting redshift_connector to ERROR
2024-09-28 06:08:24.948426 (MainThread): 06:08:24  Redshift adapter: Setting redshift_connector.core to ERROR
2024-09-28 06:08:25.519228 (MainThread): 06:08:25  Registered adapter: redshift=1.9.0-post6+fb1b5ae4ef2d1626037331f6e879f1cf0237a158
2024-09-28 06:08:25.710320 (MainThread): 06:08:25  checksum: 52ed996f17fbc77451e72ba921bd86a220610e93df73a174e46c296e13e4bd79, vars: {}, profile: user, target: default, version: 2024.9.246
2024-09-28 06:08:26.025074 (MainThread): 06:08:26  Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
2024-09-28 06:08:26.025669 (MainThread): 06:08:26  Partial parsing enabled, no changes found, skipping parsing
...
2024-09-28 06:08:37.012039 (Thread-1 (worker)): 06:08:37  Began running node snapshot.esto_project.merchants_snapshot
2024-09-28 06:08:37.013494 (Thread-1 (worker)): 06:08:37  1 of 2 START snapshot core_snapshot.merchants_snapshot ......................... [RUN]
2024-09-28 06:08:37.016201 (Thread-1 (worker)): 06:08:37  Began compiling node snapshot.esto_project.merchants_snapshot
2024-09-28 06:08:37.019275 (Thread-1 (worker)): 06:08:37  Began executing node snapshot.esto_project.merchants_snapshot
2024-09-28 06:08:37.081139 (Thread-1 (worker)): 06:08:37  Using redshift connection "snapshot.esto_project.merchants_snapshot"
2024-09-28 06:08:37.083370 (Thread-1 (worker)): 06:08:37  On snapshot.esto_project.merchants_snapshot: /* 
{
    "app": "dbt",
    "dbt_version": "2024.9.246",
    "profile_name": "user",
    "target_name": "default",
    "node_id": "snapshot.esto_project.merchants_snapshot"
} */
select * from (
        select * from (
select * from "datalake-prod"."core"."int_merchants_snapshot_base" where created_at<current_date
            ) subq
    ) as __dbt_sbq
    where false
    limit 0
2024-09-28 06:08:42.475711 (Thread-1 (worker)): 06:08:42  On snapshot.esto_project.merchants_snapshot: call redshift_connector.Connection.get_columns(datalake-prod, core_snapshot, merchants_snapshot)
2024-09-28 06:08:43.162692 (Thread-1 (worker)): 06:08:43  On snapshot.esto_project.merchants_snapshot: call redshift_connector.Connection.get_columns(datalake-prod, core_snapshot, merchants_snapshot)
2024-09-28 06:08:43.408787 (Thread-1 (worker)): 06:08:43  On snapshot.esto_project.merchants_snapshot: Close
2024-09-28 06:08:43.411596 (Thread-1 (worker)): 06:08:43  Compilation Error in snapshot merchants_snapshot (snapshots/merchants_snapshot.sql)
  Snapshot target is missing configured columns (missing "dbt_scd_id", "dbt_valid_from", "dbt_valid_to"). See https://docs.getdbt.com/docs/build/snapshots#snapshot-meta-fields for more information.
  > in macro materialization_snapshot_default (macros/materializations/snapshots/snapshot.sql)
  > called by snapshot merchants_snapshot (snapshots/merchants_snapshot.sql)
2024-09-28 06:08:43.413136 (Thread-1 (worker)): 06:08:43  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '18a3d77c-37d2-4d24-9ac0-61e59aacfc29', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1cd79f4650>]}
2024-09-28 06:08:43.414033 (Thread-1 (worker)): 06:08:43  1 of 2 ERROR snapshotting core_snapshot.merchants_snapshot ..................... [�[31mERROR�[0m in 6.40s]
2024-09-28 06:08:43.414824 (Thread-1 (worker)): 06:08:43  Finished running node snapshot.esto_project.merchants_snapshot
2024-09-28 06:08:43.415751 (Thread-7 (_handle_results)): 06:08:43  Marking all children of 'snapshot.esto_project.merchants_snapshot' to be skipped because of status 'error'.  Reason: Compilation Error in snapshot merchants_snapshot (snapshots/merchants_snapshot.sql)
  Snapshot target is missing configured columns (missing "dbt_scd_id", "dbt_valid_from", "dbt_valid_to"). See https://docs.getdbt.com/docs/build/snapshots#snapshot-meta-fields for more information.
  > in macro materialization_snapshot_default (macros/materializations/snapshots/snapshot.sql)
  > called by snapshot merchants_snapshot (snapshots/merchants_snapshot.sql).    
2024-09-28 06:08:53.427963 (MainThread): 06:08:53  Connection 'snapshot.esto_project.merchants_snapshot' was properly closed.
2024-09-28 06:08:53.595791 (MainThread): 06:08:53    Compilation Error in snapshot merchants_snapshot (snapshots/merchants_snapshot.sql)
  Snapshot target is missing configured columns (missing "dbt_scd_id", "dbt_valid_from", "dbt_valid_to"). See https://docs.getdbt.com/docs/build/snapshots#snapshot-meta-fields for more information.
  
  > in macro materialization_snapshot_default (macros/materializations/snapshots/snapshot.sql)
  > called by snapshot merchants_snapshot (snapshots/merchants_snapshot.sql)

They then immediately changed the flag to false due to two consecutive failed job runs.

Good job Run #328794583

• Commit SHA: #6caa747
• Triggered Sep 28, 2024, 6:29 AM UTC
• Finished Sep 28, 2024, 6:35 AM UTC
  

2024-09-28 06:30:15.220572 (MainThread): 06:30:15  Running dbt...
2024-09-28 06:30:15.281688 (MainThread): 06:30:15  Redshift adapter: Setting redshift_connector to ERROR
2024-09-28 06:30:15.282758 (MainThread): 06:30:15  Redshift adapter: Setting redshift_connector.core to ERROR
2024-09-28 06:30:15.926785 (MainThread): 06:30:15  Registered adapter: redshift=1.9.0-post6+fb1b5ae4ef2d1626037331f6e879f1cf0237a158
2024-09-28 06:30:16.117129 (MainThread): 06:30:16  checksum: 52ed996f17fbc77451e72ba921bd86a220610e93df73a174e46c296e13e4bd79, vars: {}, profile: user, target: default, version: 2024.9.246

Debug logs for both job runs (dbt command: dbt snapshot)
bad_snapshot_restrict_pg.log
good_snapshot_restrict_pg.log