I’m having trouble querying my duckdb database. I’m quite new to dbt so it could be a dumb mistake but all help is greately appreciated.
this is my models/example/schema.yml file
version: 2
models:
- name: energy_sellers
description: this dataset holds the data of the captured solar and wind energy also the price and the datetime this data was captured, this dataset has data for every 15 minuts
columns:
- name: id
description: "The primary key for this table"
tests:
- unique
- not_null
- name: date_time
description: The datetime the data was captured
tests:
- unique
- not_null
- name: solar_measured
description: The amount of solar energy produced in the whole of belgium at the given timestamp
tests:
- not_null
- name: wind_measured
description: The amount of wind energy produced in Belgium at the given timestamp
tests:
- not_null
- name: solar_price
description: The calculated price for solar energy at that given timestamp
tests:
- not_null
- name: wind_price
description: The calculated price for wind energy at that given timestamp
tests:
- not_null
this is my sources/sources.yml file (I was not sure if this file was mandatory to make)
version: 2
sources:
name: energy_sellers
database: energy_sellers.duckdb
schema: energy_sellers
tables:
- name: energy
columns:
- name: id
description: "The primary key for this table"
tests:
- unique
- not_null
- name: date_time
description: The datetime the data was captured
tests:
- unique
- not_null
- name: solar_measured
description: The amount of solar energy produced in the whole of belgium at the given timestamp
tests:
- not_null
- name: wind_measured
description: The amount of wind energy produced in Belgium at the given timestamp
tests:
- not_null
- name: solar_price
description: The calculated price for solar energy at that given timestamp
tests:
- not_null
- name: wind_price
description: The calculated price for wind energy at that given timestamp
tests:
- not_null
This is my dbt_project.yml file
# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: transform_dbt
version: '1.0.0'
config-version: 2
vars:
db_name: energy_sellers.duckdb
# This setting configures which "profile" dbt uses for this project.
profile: 'transform_dbt'
# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
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
# In this example config, we tell dbt to build all models in the example/
# directory as views. These settings can be overridden in the individual model
# files using the `{{ config(...) }}` macro.
models:
transform_dbt:
example:
materialized: table
and this is my profiles.yml file
transform_dbt:
target: dev
outputs:
dev:
type: duckdb
database: ./sources/energy_sellers.duckdb
schema: energy_sellers
path: './out/energy_sellers.duckdb'
extensions:
- httpfs
- parquet
settings:
s3_region: eu-central-1
s3_access_key_id: ${S3_ACCESS_KEY_ID}
s3_secret_access_key: ${S3_SECRET_ACCESS_KEY}
My duckdb file is located in sources/energ_sellers.duckdb
the sql file I’m trying to run looks like this.
select id, date_time, solar_measured, wind_measured, solar_price, wind_price
from {{ source('energy_sellers', 'energy') }}
The database structure:
Schema: energy_sellers
table: energy
but when I try to query it through a IDE I have to do "Select * from “energy_sellers.energy_sellers.energy”
I think it has something to do with Catalog.
When i run dbt run I receive the following error:
Compilation Error
Model 'model.transform_dbt.energy_sellers' (models\example\energy_sellers.sql) depends on a source named 'energy_sellers.energy' which was not found
As I said all help is greatly appreciated and if you need any more information you can ask. Thanks in advance.
