dbt run create bigquery view and not table

Help
,
1

Hello,

my need is the following:
I have bigquery datasets containing tables in a project.
I want to retrieve one of these tables, create a model for it and create a new table in a new dataset, resulting from this model.

I currently work locally. Here is the tree structure of my dbt project :

.
├── logs
│ └── dbt.log
└── my_project_dbt
├── README.md
├── analyses
├── dbt_packages
├── dbt_project.yml
├── logs
│ └── dbt.log
├── macros
├── models
│ ├── dbt_packages
│ └── my_sql_file.sql
├── seeds
├── snapshots
├── target
│ ├── compiled
│ │ └── mon_projet_dbt
│ │ └── models
│ │ └── my_model.sql
│ ├── graph.gpickle
│ ├── graph_summary.json
│ ├── manifest.json
│ ├── partial_parse.msgpack
│ ├── run
│ │ └── mon_projet_dbt
│ │ └── models
│ │ └── my_model.sql
│ ├── run_results.json
│ └── semantic_manifest.json
└── tests

And below are the files I used.

my_sql_file.sql :

WITH my_model AS (
SELECT
REGEXP_EXTRACT(STUDYID, r’CL(.*)') AS extracted_content
FROM
my_project.sas_test.ta
)
SELECT
extracted_content AS extracted_content
FROM
my_model

dbt_project.yml :

name: ‘my_project_dbt’
version: ‘1.0.0’
config-version: 2

profile: ‘my_project_dbt’

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

clean-targets: # directories to be removed by dbt clean

  • “target”
  • “dbt_packages”

Configuring models

models:
my_model:
description: “Transformation of STUDYID data”
+materialized: table
database: my_project
schema: sas_test
alias: tabletest
sql: models/my_model.sql

profiles.yml :

my_project_dbt:
target: dev
outputs:
dev:
type: bigquery
method: oauth
project: my_project
dataset: sas_test

I have two problem when I run dbt run :

  • the view that is created has the same name as the sql file but I want another name, for exemple “tabletest”
  • I want a table, not a view.

When I run “dbt run” I got this :

12:03:03 Running with dbt=1.6.3
12:03:03 Registered adapter: bigquery=1.6.5
12:03:03 [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:

  • models.my_model
    12:03:03 Found 1 model, 0 sources, 0 exposures, 0 metrics, 390 macros, 0 groups, 0 semantic models
    12:03:03
    12:03:05 Concurrency: 1 threads (target=‘dev’)
    12:03:05
    12:03:05 1 of 1 START sql view model sas_test.my_sql_file … [RUN]
    12:03:06 1 of 1 OK created sql view model sas_test.my_sql_file … [CREATE VIEW (0 processed) in 0.93s]
    12:03:06
    12:03:06 Finished running 1 view model in 0 hours 0 minutes and 2.75 seconds (2.75s).
    12:03:06
    12:03:06 Completed successfully
    12:03:06
    12:03:06 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

We can see that my model is not used :

There are 1 unused configuration paths:

  • models.my_model

here my dbt --version

Core:

  • installed: 1.6.3
  • latest: 1.6.3 - Up to date!

Plugins:

  • bigquery: 1.6.5 - Up to date!

Thank you for your help !

2

It seems your model is called my_sql_file, but in the dbt_project.yml you are calling it my_model.

You should use the file name in the dbt_project.yml, so my_sql_file instead of my_model