As of now, I have an INSERT INTO clause, that works like this: The final_table has 4 columns: ID (incremental/serial), name, age and created_at. The person_table has only name, age, and created_at. When I translate it to the dbt logic, it looks like this: However, dbt keeps compiling as an INSERT CLAUSE containing the ID (that it is auto-generated). Does
Tag: dbt
How to create temporal table from current and history?
Let’s say I have a current table that looks like this: and I have a history table that looks as such: How can I build a temporal table such as the following in pure SQL? I am using a PostgresDB is dialect helps! I know I can do this in Python, I’m stuck on calling dynamically named columns using the
Why is this query not able to execute?
The query goes on without executing, any ideas on how I can help it resolve? Using the Economy Data Atlas dataset from Knoema within Snowflake. Answer This might be a case where temporary tables will help your what I assume is a massive table be pruned (aka one of the reasons to avoid complex CTE’s) I would re-write your SQL
How to structure DBT tables with cyclical dependencies
I have one table containing my members. customer_id name age 1 John 74 2 Sarah 87 Everyday, I get a new table containing the current members. If a new member has joined, I want to add them. If a member has left, I want to nullify their name/id If a current member is still a member then I want to
What is the value of schema tests in dbt?
When would you want to use a dbt schema tests (unique, not_null, accepted_values, & relationships) when you could instead use SQL schema constraints? For example, here are some SQL schema constraints that could replace each of the dbt schema tests: unique: UNIQUE constraint not_null: NOT NULL constraint accepted_values: FOREIGN KEY constraint to a lookup table relationships: FOREIGN KEY constraint to
Assign incremental id based on number series in ordered sql table
My table of interview candidates has three columns and looks like this (attempt is what I want to calculate): candidate_id interview_stage stage_reached_at attempt <- want to calculate 1 1 2019-01-01 1 1 2 2019-01-02 1 1 3 2019-01-03 1 1 1 2019-11-01 2 1 2 2019-11-02 2 1 1 2021-01-01 3 1 2 2021-01-02 3 1 3 2021-01-03 3 1
Checking for different columns OR empty result PostgreSQL
I’m writing some query tests for dbt and I’d like to check that two queries return the same thing and that neither is empty. Since it’s a dbt test, I need to return a row if either of these conditions is false. So far I’ve come up with the following: Theoretically, this should return a row with 1 if either
How to add a Jinja function to .sqlfluff config
I’m using the jinja functions run_query and execute. https://docs.getdbt.com/reference/dbt-jinja-functions/run_query But when sqlfluff lint I get the following error: Undefined jinja template variable: ‘run_query’ I’m trying to add it to the .sqlfluff config but there doesn’t seem to be any guidance anywhere on how to add this to the config file. Any help would be greatly appreciated! Thanks Answer Add templater=dbt
calculation with undefined number of rows
I would like to add a calculation taking into account we could have one, two, three or a lot of more rows. We can get all the data using the relations but I am a bit stuck because the number of relation are undefined. For example, as a source: Source Table id amount debt relation rn 123 250 225 NULL
dbt macro – using *args/**kwargs
Occasionally, our tables need to append multiple columns together to make a unique key. This can be done by doing something like: select *, col1 || “_” || col2 as unique_key from …