composite UK is failing

[jthandy]

When defining a composite unique key, my archive is failing. It seems like this should work, and the expression is valid (verified in a separate SELECT).

I've defined the following unique key in one of my archives:

unique_key: _sdc_source_key__id || '|' || _sdc_level_0_id

I'm getting this error:

(venv)Tristans-MacBook-Pro:analytics tristan$ dbt archive
Traceback (most recent call last):
  File "/Users/tristan/dev/venv/bin/dbt", line 6, in <module>
    exec(compile(open(__file__).read(), __file__, 'exec'))
  File "/Users/tristan/dev/dbt/scripts/dbt", line 8, in <module>
    dbt.main.main(sys.argv[1:])
  File "/Users/tristan/dev/dbt/dbt/main.py", line 38, in main
    return handle(args)
  File "/Users/tristan/dev/dbt/dbt/main.py", line 52, in handle
    res = run_from_args(parsed)
  File "/Users/tristan/dev/dbt/dbt/main.py", line 85, in run_from_args
    return task.run()
  File "/Users/tristan/dev/dbt/dbt/task/archive.py", line 19, in run
    self.compile()
  File "/Users/tristan/dev/dbt/dbt/task/archive.py", line 15, in compile
    compiled = compiler.compile_archives()
  File "/Users/tristan/dev/dbt/dbt/compilation.py", line 412, in compile_archives
    sql = archive.compile()
  File "/Users/tristan/dev/dbt/dbt/model.py", line 705, in compile
    query = archival.compile()
  File "/Users/tristan/dev/dbt/dbt/archival.py", line 41, in compile
    self.schema.create_table(target_schema, target_table, dest_columns, sort=updated_at, dist=unique_key)
  File "/Users/tristan/dev/dbt/dbt/schema.py", line 257, in create_table
    self.execute_and_handle_permissions(sql, table)
  File "/Users/tristan/dev/dbt/dbt/schema.py", line 159, in execute_and_handle_permissions
    return self.execute(query)
  File "/Users/tristan/dev/dbt/dbt/schema.py", line 137, in execute
    raise e
  File "/Users/tristan/dev/dbt/dbt/schema.py", line 129, in execute
    cursor.execute(sql)
psycopg2.NotSupportedError: column "_sdc_source_key__id || '|' || _sdc_level_0_id" specified as distkey/sortkey is not in the table "mongo.accounts__duplicate_authors"

[drewbanin]

dbt tries to use the unique_key as the distkey. It also tries to use the updated_at field as the sortkey. This means that using an expression as the unique_key or updated_at definition will break archival. The solution here is to use the dbt_updated_at and scd_id fields directly instead of the expressions which are responsible for generating them

[jthandy]

Oh. Yeah. Completely agree.

[drewbanin]

#324