Add CI

[dataders]

To Do:

• [ ] Add Git to actual docker image
• [x] figure out why submodules aren't auto-installing? @jtcohen6 halp plz
• [ ] add a local version of SQL server for testing as well

[jtcohen6]

Looks like the CI failures have to do with the dbt seed step. These are tricky:

• Cannot find data type 'timestamp'.
• Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
• A table can only have one timestamp column. Because table 'data_dateadd' already has one, the column 'result' cannot be added.

It's possible that we'll need to:

• Disable the seed coming from dbt_utils_integration_tests
• Recreate the seed (different name) + staging view (same name) to replicate the test setup

[dataders]

• Cannot find data type 'timestamp'.

• Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

• A table can only have one timestamp column. Because table 'data_dateadd' already has one, the column 'result' cannot be added.

yeah these definitely seem T-SQL specific. I'll ask @NandanHegde15 to take a look at those errors. I agree we might have to make different versions of the csvs if we can't fix it adapter-side.

meanwhile, is there a way to disable that set of seeds so we can get some feedback on which macros work and which don't?

[jtcohen6]

meanwhile, is there a way to disable that set of seeds so we can get some feedback on which macros work and which don't?

based on the seeds that are currently failing, I think you can update what's here to be:

seeds:
  dbt_utils_integration_tests:
    cross_db:
      data_date_trunc:
        +enabled: false
      data_dateadd:
        +enabled: false
      data_datediff:
        +enabled: false
      data_last_day:
        +enabled: false
    etc:
      data_people:
        +enabled: false
    sql:
      data_get_query_results_as_dict:
        +enabled: false
      data_safe_add:
        +enabled: false
    schema_tests:
      data_test_mutually_exclusive_ranges_with_gaps:
        +enabled: false
      data_test_not_null_where:
        +enabled: false

models:
  dbt_utils_integration_tests:
    cross_db_utils:
      +enabled: true
      test_date_trunc:
        +enabled: false
      test_dateadd:
        +enabled: false
      test_datediff:
        +enabled: false
      test_last_day:
        +enabled: false
    datetime:
      +enabled: true
    materializations:
      +enabled: false
    schema_tests:
      +enabled: false
    sql:
      +enabled: true
      test_safe_add:
        +enabled: false
    web:
      +enabled: false

[dataders]

The issue I'm seeing now is that in T-SQL, the type timestamp is really datetime (timestamp is a special thing in T-SQL that's mostly deprecated now). So if I want to change the column types in dbt-utils's 'integration_tests/dbt_project.yml` how might one do that?

seeds:

  +quote_columns: false
  dbt_utils_integration_tests:

    cross_db:
      data_date_trunc:
        +column_types:
          updated_at: timestamp
          day: date
          month: date

      data_dateadd:
        +column_types:
          from_time: timestamp
          result: timestamp

      data_datediff:
        +column_types:
          first_date: timestamp
          second_date: timestamp

[jtcohen6]

the way you just did it is what I was going to recommend :)

[dataders]

ok now we're getting juicy, actionable information! thanks @jtcohen6! @alittlesliceoftom here's the short list of the 16 dbt-utils macros that need TLC (T-SQL loving care) and re-implemented here in this shim package.

Incorrect syntax near...

• ::
  • cross_db_utils/current_timestamp.sql
  • cross_db_utils/current_timestamp_in_utc.sql
• the keyword with
  • sql/generate_series.sql
  • datetime/date_spine.sql
• -
  • sql/get_relations_by_pattern.sql
  • sql/get_relations_by_prefix_and_union.sql
• string_text
  • cross_db_utils/position.sql

    function is not a recognized built-in function name

• date_trunc
  • cross_db_utils/date_trunc.sql
  • cross_db_utils/last_day.sql
• split_part
  • cross_db_utils/split_part.sql
• mod
  • cross_db_utils/width_bucket.sql
• length
  • cross_db_utils/length.sql

    MISC

  • Each GROUP BY expression must contain at least one column that is not an outer reference
• sql/get_column_values.sql
• sql/groupby.sql
  • Invalid column name 'field'
• cross_db_utils/hash.sql
• sql/surrogate_key.sql