search

Datavault-UK / automate-dv

A free to use dbt package for creating and loading Data Vault 2.0 compliant Data Warehouses (powered by dbt, an open source data engineering tool, registered trademark of dbt Labs)
https://www.automate-dv.com
Apache License 2.0
511 stars 131 forks source link

Creation of PIT fails with case sensitive SQL Server setting due to as_of_date_window.sql #211

Closed MartinHofpower closed 8 months ago

MartinHofpower commented 1 year ago

Describe the bug When creating an incremental pit table, dbt run fails with errorMsg "Invalid column name as_of_date'." This seems to be the case because we are executing the macro as_of_date_window.sql (macros/supporting/as_of_date_window.sql) on a case sensitive SQL Server. Within as_of_date_window.sql, a CTE as_of_grain_new_entries is defined with a column _AS_OFDATE but later it is used with the column _as_ofdate.

Environment

dbt version: 1.4.7 automate_dv version: 0.10.1 Database/Platform: MS SQL SERVER 2022

To Reproduce Steps to reproduce the behavior:

  1. Create a target with case sensitivity set to on
  2. Create a PIT table and corresponding as-of-dates table
  3. dbt run should result in the described error

Expected behavior For me the local workaround of replacing _as_ofdate with _AS_OFDATE in 

SELECT as_of_date 
FROM as_of_grain_new_entries

worked just fine. See line 82 und 83 currently in macros/supporting/as_of_date_window.sql

Screenshots image

DVAlexHiggs commented 8 months ago

Fixed in v0.10.2 😄 Thanks for your patience for release of this! Please let us know if you experience any issues by responding here or opening a new issue.