1.8.0 RC Releases

[cody-scott]

Conversation around 1.8.0 release.

Please share any comments on the release here and link any issues if you've come across them.

[axellpadilla]

@cody-scott I completed the merge of the new 1.8.0rc1 and I'm using it already, working ok, will keep you updated if anything is broken.

[gbarrington]

@cody-scott A few things I've noticed in testing 1.8.0rc1

Seeds

I have a few seed files in my project. I think the BatchSize in the Fabric adapter isn't working as expected
If the number of element in the CSV < 2000 it works fine (I have a 2x5 CSV - gets loaded fine)
Where elements > 2000 it fails with error below (I have an 13x506 CSV - fails)
Error message:
[Microsoft][ODBC Driver 18 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)

Looking at the generated SQL its creates a parameterised insert with 6,578 params ( = 13*506). So its not splitting on batch size 2000

Incremental Models

In my setup I have 4 databases (Raw, Staging, Intermediate, Mart), all using "dbo" schema
Incremental models are now creating in the correct DB/Schema (weren't in 1.7.x)
However on an incremental run, dbt isn't cleaning up after itself.
For each incremental model, there is also a corresponding "__dbt_tmp" table, even though dbt finishes successfully.

[cody-scott]

Yes I think the chunk size macro for sql server got missed. Will add that back in later this week. There is a few other items too I was going to put in an rc2 anyways. 

[cody-scott]

RC2 is released. @gbarrington and @axellpadilla can you take a look and see if the issues are resolved?

[gbarrington]

@cody-scott the 2 things I noticed seem to be fixed, but a new issue has shown up.
I've run dbt seed on a few large seed files and it worked fine.
I've also run an full-refresh and incremental and dbt is cleaning up after itself now.. no more "__dbt_temp" tables.

Snapshots are now failing (they may have been earlier, except I was running with "fail-fast", so didn't notice). This is working in 1.4.9
I'll try to explain my config, but if its too difficult to understand, let me know and I can create a dummy project.

Setup

DWH_Raw

• claims (SQL table, marked as a source in dbt): This is raw data loaded from another system

DWH_Staging

• claims_snapshot (table, created from a snapshot in dbt)

My snapshot sql looks like

{% snapshot claims_snapshot %}

{{
   config(
       target_database='DWH_Staging',
       target_schema='dbo',
       unique_key='id',

       strategy='timestamp',
       updated_at='updated_at',
   )
}}

select * from {{source('mysource', 'claims')}}

{% endsnapshot %}

So, effectively I want the raw data kept in DWH_Raw, but the snapshot to save in DWH_Staging.

Error

Unhandled error while executing 
('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name 'DWH_Staging.dbo.claims_snapshot_snapshot_staging_temp_view'. (208) 22:49:29  Registered adapter: sqlserver=1.8.0-rc2

Running SQL Profiler, it looks like the following is happening.

• dbt runs DROP VIEW IF EXISTS dbo.claims_snapshot_snapshot_staging_temp_view in DWH_Raw
• dbt runs CREATE VIEW dbo.claims_snapshot_snapshot_staging_temp_view as select * from xxx in DWH_Raw
• creates some views with suffix "__dbt_tmp__dbt_tmp_vw"
• dbt then runs

create view "dbo"."claims_snapshot__dbt_tmp__dbt_tmp_vw" as with snapshot_query as (
        select * from "DWH_Staging"."dbo"."claims_snapshot_snapshot_staging_temp_view"
    ),
    snapshotted_data as (
        select *,
            claim_id as dbt_unique_key
        from "DWH_Staging"."dbo"."claims_snapshot"
        where dbt_valid_to is null
    ), 

... snipped snapshot sql logic ...

NOTE: The top select * from "DWH_Staging"."dbo"."claims_snapshot_snapshot_staging_temp_view"
This includes the DB (DWH_Staging), but we created the view in DWH_Raw earlier... I think this is the cause of the SQL Error in dbt.

[cody-scott]

@gbarrington If you have a chance can you test this branch and see if its resolved https://github.com/dbt-msft/dbt-sqlserver/tree/snapshot-issue

[gbarrington]

@cody-scott I've run my project against that branch and all looks good.
The snapshots are creating in the correct DB without error.

I've also run a full dbt build on my project with full-refresh and incremental, and everything is working as expected for me.
My 3 issues fixed.

[axellpadilla]

Hi @cody-scott , no issues so far, amazing work, thanks!

[Mira-Roland]

Hi,
first of all, thanks a lot for picking up on dbt-sqlserver @cody-scott and everyone else contributing!!

We tested our project with rc2 and everything works fine for now.

One thing I noticed now, when playing around with the unit test feature, which we have not used before, I get an error.

I oversimplify here, but the result is the same for more complex tests.

Model to be tested, called mawi_unit_test

select 
    ik
    ,standort_id
from
    {{ ref('mawi_psta_beschaffung') }}

Unit test file

unit_tests:
  - name: ut1 # this is the unique name of the test
    model: mawi_unit_test # name of the model I'm unit testing
    given: # the mock data for your inputs
      - input: ref('mawi_psta_beschaffung')
        rows:
          - {ik: 10}
    expect: # the expected output given the inputs above
      rows:
          - {ik: 10}

Result when running the test:

[0m16:50:59.525562 [debug] [Thread-1 (]: fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")

SQL generated for the unit test that causes the error:

-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
  select
    ik, 'actual' as "actual_or_expected"
  from (
    with __dbt__cte__mawi_psta_beschaffung as (
-- Fixture for mawi_psta_beschaffung
select 
    try_cast(10 as int)
 as ik, try_cast(null as int) as standort_id, try_cast(null as nvarchar) as fall, try_cast(null as int) as jahr, try_cast(null as int) as monat, try_cast(null as int) as jahr_monat, try_cast(null as uniqueidentifier) as project_id, try_cast(null as nvarchar) as kst_nummer, try_cast(null as nvarchar) as kst_bezeichnung, try_cast(null as nvarchar) as ka_nummer, try_cast(null as nvarchar) as ka_bezeichnung, try_cast(null as nvarchar) as artikel_nummer, try_cast(null as nvarchar) as artikel_bezeichnung, try_cast(null as nvarchar) as bezug_bezeichnung, try_cast(null as nvarchar) as mengeneinheit, try_cast(null as nvarchar) as warengruppe_bezeichnung, try_cast(null as nvarchar) as lieferantenartikel_nummer, try_cast(null as nvarchar) as lieferant_bezeichnung, try_cast(null as nvarchar) as eclasscodedname, try_cast(null as nvarchar) as pzn, try_cast(null as numeric(20,6)) as menge, try_cast(null as numeric(20,6)) as wert, try_cast(null as nvarchar) as waehrung, try_cast(null as int) as dq_qualifier, try_cast(null as uniqueidentifier) as row_id, try_cast(null as uniqueidentifier) as batch_id, try_cast(null as datetime2) as batch_created_on, try_cast(null as datetime2) as created_on
) select 
    ik
    ,standort_id
from
    __dbt__cte__mawi_psta_beschaffung
  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    ik, 'expected' as "actual_or_expected"
  from (
    select 
    try_cast(10 as int)
 as ik
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected

Looking at the SQL, it is easy to fix, the internal WITH clause is not allowed, so one could rewrite it as a derived table for example like this:

-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
  select
    ik, 'actual' as "actual_or_expected"
  from (
     select 
    ik
    ,standort_id
from
  (
-- Fixture for mawi_psta_beschaffung
select 
    try_cast(10 as int)
 as ik, try_cast(null as int) as standort_id, try_cast(null as nvarchar) as fall, try_cast(null as int) as jahr, try_cast(null as int) as monat, try_cast(null as int) as jahr_monat, try_cast(null as uniqueidentifier) as project_id, try_cast(null as nvarchar) as kst_nummer, try_cast(null as nvarchar) as kst_bezeichnung, try_cast(null as nvarchar) as ka_nummer, try_cast(null as nvarchar) as ka_bezeichnung, try_cast(null as nvarchar) as artikel_nummer, try_cast(null as nvarchar) as artikel_bezeichnung, try_cast(null as nvarchar) as bezug_bezeichnung, try_cast(null as nvarchar) as mengeneinheit, try_cast(null as nvarchar) as warengruppe_bezeichnung, try_cast(null as nvarchar) as lieferantenartikel_nummer, try_cast(null as nvarchar) as lieferant_bezeichnung, try_cast(null as nvarchar) as eclasscodedname, try_cast(null as nvarchar) as pzn, try_cast(null as numeric(20,6)) as menge, try_cast(null as numeric(20,6)) as wert, try_cast(null as nvarchar) as waehrung, try_cast(null as int) as dq_qualifier, try_cast(null as uniqueidentifier) as row_id, try_cast(null as uniqueidentifier) as batch_id, try_cast(null as datetime2) as batch_created_on, try_cast(null as datetime2) as created_on
)  __dbt__cte__mawi_psta_beschaffung
  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    ik, 'expected' as "actual_or_expected"
  from (
    select 
    try_cast(10 as int)
 as ik
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected

Unfortunately, I don't know enough about the inner workings of dbt, so I can't help in changing the code for dbt-sqlserver.

[cody-scott]

Looks like it's generating a with clause inside the test.

This is similar issue to how custom tests using with is applied. In that case a view is created to use in the statement instead of a nest CTE.

I will add it to an issue and see if I can get it resolved before 1.8. Good chance 1.8 will go out and I'll add this to 1.8.1.

[cody-scott]

If you'd like to test out this branch, let me know if that logic works now.
https://github.com/dbt-msft/dbt-sqlserver/tree/540-units-tests-not-working-as-expected

[cody-scott]

I merged the branch as it was passing all the adapter tests, if you find a problem we can open a new issue to resolve. Thanks!

[Mira-Roland]

Thank you!
Wasn't able to test it anymore yesterday, but just reran with the new version1.8.0 and it worked.

Will play around with the unit tests some more and open a new issues, should I encounter any problems.

[cody-scott]

Closing this as 1.8.0 is release. Thanks for the help on the pre-release!
#544