Make dbt's Jaffle Shop project fully compatible with ClickHouse

[koletzilla]

I tried last week to use the dbt's Jaffle Shop with ClickHouse and I found a few differences that made me need to fork the project to make it ClickHouse compatible.

Making this project directly compatible with ClickHouse is interesting mainly because of two reasons:

• Increase awareness: It will be a nice way to make people aware that ClickHouse is also compatible with dbt.
• Help with initial setup for new users: The Jaffle Shop is a well know project in the dbt community. It will be really helpful for new users that want to try ClickHouse and dbt for the first time.

Ideally we should make the needed changes so we can directly use the Jaffle Shop project without modifications. This is the list of things I needed to change to make it work:

• dbt project:

  • [ ] Move project to ClickHouse organization. I have it currently under my own account
  • [ ] New clickhouse__cents_to_dollars macro as default is not compatible
  • [ ] New clickhouse__convert_timezone macro as dbt-date package (godatadriven/dbt-date) is not compatible with ClickHouse.
  • [ ] Fix unit-test as we needed to add default values for all column. This should not be needed once https://github.com/ClickHouse/dbt-clickhouse/pull/552 is merged.
  • [ ] The test test_does_location_opened_at_trunc_to_date fails because the raw.raw_stores's opened_at column is inferred as DateTime and the mock data tries to insert a date with microseconds which is not compatbiel with it. To fix it I needed to type this column as DateTime64(3)
  • [ ] ClickHouse seems incompatible with using order_items.* in order_items.sql and I needed to instead define all column names. Pending to see if they are some workarounds

• Docs side:

  • [ ] Remove dbt Cloud references: full compatibility will came soon, but we are still not compatible with dbt Cloud.
  • [ ] Instructions about how to kickstart a local ClickHouse cluster or use a ClickHouse Cloud one.

[koletzilla]

While working on https://github.com/ClickHouse/dbt-clickhouse/issues/492 I have found that the original repo raises deprecation warnings. These were raised by the commands:

• dbt-autofix deprecations
• dbt parse --no-partial-parse --show-all-deprecations

The warnings are now fixed in our Jaffle repo, but it would be nice the have these fixes also in the original one.