ADAP-473: Table materialization not properly dropping existing relation on refresh

[mikealfare]

resolves #725

Description

We were not properly dropping an existing table relation when that model was refreshed using a non-admin account in Databricks. That prevented us from refreshing those models, as the non-admin account could not see the already existing tables, hence could not overwrite them. The original workaround was to elevate the service account to be an admin, but this was deemed too steep of a requirement. The solution that worked in the end was to update the table materialization itself, and drop the target_relation instead of the old_relation.

Checklist

• [x] I have read the contributing guide and understand what's expected of me
• [x] I have signed the CLA
• [x] I have run this code in development and it appears to resolve the stated issue
• [x] This PR includes tests, or tests are not required/relevant for this PR
• [x] I have opened an issue to add/update docs, or docs changes are not required/relevant for this PR
• [ ] I have run changie new to create a changelog entry

[github-actions[bot]]

Thank you for your pull request! We could not find a changelog entry for this change. For details on how to document a change, see the dbt-spark contributing guide.

[github-actions[bot]]

The backport to 1.4.latest failed:

The process '/usr/bin/git' failed with exit code 1

To backport manually, run these commands in your terminal:

# Fetch latest updates from GitHub
git fetch
# Create a new working tree
git worktree add .worktrees/backport-1.4.latest 1.4.latest
# Navigate to the new working tree
cd .worktrees/backport-1.4.latest
# Create a new branch
git switch --create backport-724-to-1.4.latest
# Cherry-pick the merged commit of this pull request and resolve the conflicts
git cherry-pick -x --mainline 1 44b10f92a8ea9956c455dd96cfd6388325c4da91
# Push it to GitHub
git push --set-upstream origin backport-724-to-1.4.latest
# Go back to the original working tree
cd ../..
# Delete the working tree
git worktree remove .worktrees/backport-1.4.latest

Then, create a pull request where the base branch is 1.4.latest and the compare/head branch is backport-724-to-1.4.latest.

[github-actions[bot]]

The backport to 1.3.latest failed:

The process '/usr/bin/git' failed with exit code 1

To backport manually, run these commands in your terminal:

# Fetch latest updates from GitHub
git fetch
# Create a new working tree
git worktree add .worktrees/backport-1.3.latest 1.3.latest
# Navigate to the new working tree
cd .worktrees/backport-1.3.latest
# Create a new branch
git switch --create backport-724-to-1.3.latest
# Cherry-pick the merged commit of this pull request and resolve the conflicts
git cherry-pick -x --mainline 1 44b10f92a8ea9956c455dd96cfd6388325c4da91
# Push it to GitHub
git push --set-upstream origin backport-724-to-1.3.latest
# Go back to the original working tree
cd ../..
# Delete the working tree
git worktree remove .worktrees/backport-1.3.latest

Then, create a pull request where the base branch is 1.3.latest and the compare/head branch is backport-724-to-1.3.latest.