Search code examples
oracle-databasedbt

Approximate Relation Match with dbt on Oracle

I am new to dbt and I'm not sure if my problem is a problem with the code or a problem with my understanding of the tool. So I'll lay this out as best I can and hope someone can point me in the right direction.

Relevant Info

  • Server: Oracle 12.2
  • Desktop OS: Windows 10
  • Constraints: Many - this is a highly regulated environment so I can't go to IT and ask for any changes to the server. I either need to figure this out on my end or do something different.

I'm trying to transition myself, and eventually my team to using dbt for building repeatable database objects in our personal schemas. I know - my schema is not a data mart. But that's what I can do.

Setup

I created a simple example to demonstrate my problem called simple_test which has a single model.

This is what I have in dbt_project.yml

name: 'simple_test'
version: '1.0.0'
config-version: 2

profile: 'issuers_datamart'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models
models:
  simple_test:
    +materialized: table

And yes, I want my models materialized as tables. These queries can take hours to run so a view is simply not effective for my use-case.

In models, I have a file properties.yml:

name: "{{ env_var('DBT_ORACLE_SCHEMA') }}"
version: 2

models:
  - name: testy_mctest_face

And finally in models, I have a file testy_mctest_face.sql:

select
   1 an_int
  ,'two' a_string
  ,3.3 a_float
from dual

The query does work.

In fact, when I run dbt debug, everything looks great.

18:40:50  Running with dbt=1.4.1
dbt version: 1.4.1
python version: 3.10.2
python path: C:\Users\ayc58\AppData\Local\Programs\Python\Python310\python.exe
os info: Windows-10-10.0.19045-SP0
Using profiles.yml file at C:\Users\ayc58\.dbt\profiles.yml
Using dbt_project.yml file at C:\Users\ayc58\Dev\simple_test\dbt_project.yml

18:40:51  oracle adapter: Running in thick mode
Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  user: ***
  database: ***
  schema: ***
  protocol: ***
  host: ***
  port: ***
  tns_name: ***
  service: ***
  connection_string: None
  shardingkey: []
  supershardingkey: []
  cclass: None
  purity: None
  retry_count: 1
  retry_delay: 3
  Connection test: [OK connection ok]

All checks passed!

And I can then run my "model" testy_mctest_face.

19:08:57  oracle adapter: Running in thick mode
19:08:57  Running with dbt=1.4.1
19:08:57  Unable to do partial parsing because a project dependency has been added
19:08:58  Found 1 model, 0 tests, 0 snapshots, 1 analysis, 325 macros, 0 operations, 1 seed file, 0 sources, 0 exposures, 0 metrics
19:08:58
19:09:06  Concurrency: 4 threads (target='dev')
19:09:06
19:09:06  1 of 1 START sql table model AYC58.testy_mctest_face ........................... [RUN]
19:09:10  1 of 1 OK created sql table model AYC58.testy_mctest_face ...................... [OK in 4.04s]
19:09:12
19:09:12  Finished running 1 table model in 0 hours 0 minutes and 13.71 seconds (13.71s).
19:09:12
19:09:12  Completed successfully
19:09:12
19:09:12  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

And the contents of my table are as I would expect.

When I then try to rebuild my model using dbt run -f, I run into a very frustrating problem:

19:09:31  oracle adapter: Running in thick mode
19:09:31  Running with dbt=1.4.1
19:09:31  Found 1 model, 0 tests, 0 snapshots, 1 analysis, 325 macros, 0 operations, 1 seed file, 0 sources, 0 exposures, 0 metrics
19:09:31  
19:09:38  Concurrency: 4 threads (target='dev')
19:09:38  
19:09:38  1 of 1 START sql table model AYC58.testy_mctest_face ........................... [RUN]
19:09:38  1 of 1 ERROR creating sql table model AYC58.testy_mctest_face .................. [ERROR in 0.06s]
19:09:40  
19:09:40  Finished running 1 table model in 0 hours 0 minutes and 8.95 seconds (8.95s).
19:09:40  
19:09:40  Completed with 1 error and 0 warnings:
19:09:40
19:09:40  Compilation Error in model testy_mctest_face (models\testy_mctest_face.sql)
19:09:40    When searching for a relation, dbt found an approximate match. Instead of guessing
19:09:40    which relation to use, dbt will move on. Please delete AYC58.TESTY_MCTEST_FACE, or rename it to be less ambiguous.
19:09:40    Searched for: AYC58.TESTY_MCTEST_FACE
19:09:40    Found: AYC58.TESTY_MCTEST_FACE
19:09:40
19:09:40    > in macro materialization_table_oracle (macros\materializations\table\table.sql)
19:09:40    > called by model testy_mctest_face (models\testy_mctest_face.sql)
19:09:40
19:09:40  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

And yes, the table exists. But my understanding of dbt is that it would create a temporary table while building my new table, drop my old table, and rename the temporary table to the final name. Instead it just gets hung up.

  • Changing my schema name to lower case and hard-coding it into the model. No difference.
  • I've tried all the various permutations of quoting and none seem to make any difference.
  • Existing SO questions do not fit.
    • Example: I'm already using dbt run -f
    • Example: I don't think I have any spelling errors. My real project has less silly spelling.
  • And I've ready over the DBT documentation. I've even tried to fix with a pre-hook. My understanding of DBT is that this should be unnecessary, but I tried it anyway.

My understanding/expectation is that DBT will create my table with a temporary name, and then replace my old table with my new table because I am using dbt run -f. I even looked in the Oracle specific macros and while I don't understand everything there, that's what it looks like it does.

Solution

  • I have exactly the same configuration at my organization and I was struggle with this same problem. The solution that worked in my case was to add this in the dbt_project.yml file:

    quoting:
  database: true