dbt-labs / dbt-labs/dbt-utils

generate_surrogate_key treats Redshift super column types as NULL

Open
#1,081 0 comments 1 reaction 0 assignees View on GitHub
bug triage
Dominant language
Makefile
Stars
1.8k
Forks
632
PR merge metrics
No merged PRs in 30d

Description

### Describe the bug

The `generate_surrogate_key` macro silently treats Redshift super column types as NULL.

### Steps to reproduce

1. Use `generate_surrogate_key` on a SUPER column
2. Use `generate_surrogate_key` on a column containing all NULL values

### Expected results

The generated keys should be different.

### Actual results

The generated keys are the same.

### System information
**The contents of your `packages.yml` file:**

```yaml
packages:
- package: dbt-labs/dbt_utils
version: 1.3.3
- package: metaplane/dbt_expectations
version: 0.10.10
```

**Which database are you using dbt with?**
- [ ] postgres
- [x] redshift
- [ ] bigquery
- [ ] snowflake
- [ ] other (specify: ____________)

**The output of `dbt --version`:**
```
1.11.7
```

### Additional context

The macro generates code like:

```sql
md5(cast(coalesce(cast(col_name as TEXT), '_dbt_utils_surrogate_key_null_') || '-' || ... AS TEXT))
```

But the inner `cast(col_name as TEXT)` step returns NULL if `col_name` is a SUPER type.

Consider:

```sql
select
JSON_PARSE('{"count": 1}') as super_col,
cast(super_col as TEXT) as super_col_text,
md5(cast(coalesce(cast(super_col as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) as super_col_hash,
md5(cast('_dbt_utils_surrogate_key_null_' as TEXT)) as surrogate_key_hash
```

Image

Similar to #1016, the workaround is to cast SUPER columns prior to calling `generate_surrogate_key`:

```sql
select
JSON_PARSE('{"count": 1}') as super_col,
cast(super_col as TEXT) as super_col_text,
md5(cast(coalesce(cast(super_col as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) as super_col_hash,
md5(cast('_dbt_utils_surrogate_key_null_' as TEXT)) as surrogate_key_hash,
JSON_SERIALIZE(super_col) as super_col_serialized,
md5(cast(coalesce(cast(super_col_serialized as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) as super_col_hash_fixed
```

Image

### Are you interested in contributing the fix?

Fix seems non-trivial and I'm too dumb.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.