Data Engineering in 2022: Wrangling the feedback data from Current 22 with dbt

with source as (
  select * from {{ ref('rating_detail')}}
)

seeds:
  +schema: seed_data

$ rm current_dbt.duckdb
$ dbt seed
13:26:03  Running with dbt=1.1.1
13:26:03  Unable to do partial parsing because a project config has changed
13:26:03  Found 2 models, 4 tests, 0 snapshots, 0 analyses, 167 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
13:26:03
13:26:04  Concurrency: 1 threads (target='dev')
13:26:04
13:26:04  1 of 2 START seed file main_seed_data.rating_detail ............................ [RUN]
13:26:04  1 of 2 OK loaded seed file main_seed_data.rating_detail ........................ [INSERT 2416 in 0.54s]
13:26:04  2 of 2 START seed file main_seed_data.session_scans ............................ [RUN]
13:26:04  2 of 2 OK loaded seed file main_seed_data.session_scans ........................ [INSERT 163 in 0.11s]
13:26:04
13:26:04  Finished running 2 seeds in 0.84s.
13:26:04
13:26:04  Completed successfully
13:26:04
13:26:04  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

$ duckdb current_dbt.duckdb -c "select table_schema, table_name, table_type from information_schema.tables;"

┌────────────────┬───────────────┬────────────┐
│  table_schema  │  table_name   │ table_type │
├────────────────┼───────────────┼────────────┤
│ main_seed_data │ rating_detail │ BASE TABLE │
│ main_seed_data │ session_scans │ BASE TABLE │
└────────────────┴───────────────┴────────────┘

WITH      source_data AS (
          -- Spec #4: Rename fields to remove spaces etc
          SELECT    title           AS session_name,
                    "Rating Type"   AS rating_type,
                    rating,
                    "comment"       AS rating_comment,
                    "Attendee Type" AS attendee_type
                    -- Spec #7 Create a new field showing if attendee was in-person or not
                    CASE WHEN "Attendee Type" = 'Virtual' THEN 1 ELSE 0 AS virtual_attendee
                    -- Spec #3: Remove PII data of those who left ratings
          FROM      {{ ref('rating_detail') }}
          )

SELECT    *
FROM      source_data
-- Spec #8: Exclude irrelevant sessions
WHERE     session_name NOT IN ('Breakfast', 'Lunch', 'Registration')

$ rm -rf models/example

$ dbt compile
14:24:11  Running with dbt=1.1.1
14:24:12  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:
- models.current_dbt.example

14:24:12  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 167 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
14:24:12
14:24:12  Concurrency: 1 threads (target='dev')
14:24:12
14:24:12  Done.

$ cat ./target/compiled/current_dbt/models/staging/stg_ratings.sql
WITH      source_data AS (
          -- Spec #4: Rename fields to remove spaces etc
          SELECT    title           AS session_name,
                    "Rating Type"   AS rating_type,
                    rating,
                    "comment"       AS rating_comment,
                    "Attendee Type" AS attendee_type
                    -- Spec #7 Create a new field showing if attendee was in-person or not
                    CASE WHEN "Attendee Type" = 'Virtual' THEN 1 ELSE 0 AS virtual_attendee
                    -- Spec #3: Remove PII data of those who left ratings
          FROM      "main"."main_seed_data"."rating_detail"
          )

SELECT    *
FROM      source_data
-- Spec #8: Exclude irrelevant sessions
WHERE     session_name NOT IN ('Breakfast', 'Lunch', 'Registration')

models:
  current_dbt:
    staging:
      +materialized: view

$ dbt run
14:27:41  Running with dbt=1.1.1
14:27:41  Unable to do partial parsing because a project config has changed
14:27:42  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 167 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
14:27:42
14:27:42  Concurrency: 1 threads (target='dev')
14:27:42
14:27:42  1 of 1 START view model main.stg_ratings ....................................... [RUN]
14:27:42  1 of 1 ERROR creating view model main.stg_ratings .............................. [ERROR in 0.05s]
14:27:42
14:27:42  Finished running 1 view model in 0.24s.
14:27:42
14:27:42  Completed with 1 error and 0 warnings:
14:27:42
14:27:42  Runtime Error in model stg_ratings (models/staging/stg_ratings.sql)
14:27:42    Parser Error: syntax error at or near "CASE"
14:27:42    LINE 12:                     CASE WHEN "Attendee Type" = 'Virtual' THEN 1 ELSE 0 AS virtual_attendee
14:27:42                        -- Spec #3: Remove PII data of those who left ratings
14:27:42              FROM      "main"."main_seed_data"."rating_detail"
14:27:42              )
14:27:42
14:27:42    SELECT    *
14:27:42    FROM      source_data
14:27:42    -- Spec #8: Exclude irrelevant sessions
14:27:42    WHERE     session_name NOT IN ('Breakfast', 'Lunch', 'Registration')
14:27:42      );
14:27:42    ...
14:27:42                                 ^
14:27:42
14:27:42  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Runtime Error in model stg_ratings (models/staging/stg_ratings.sql)
  Parser Error: syntax error at or near "CASE"

  "Attendee Type" AS attendee_type
  CASE WHEN "Attendee Type" = 'Virtual' THEN 1 ELSE 0 AS virtual_attendee

  --                             👇️👀
  "Attendee Type" AS attendee_type,
  CASE WHEN "Attendee Type" = 'Virtual' THEN 1 ELSE 0 AS virtual_attendee

  "Attendee Type" AS attendee_type,
  CASE WHEN "Attendee Type" = 'Virtual' THEN 1 ELSE 0 END AS virtual_attendee

$ dbt run
14:55:57  Running with dbt=1.1.1
14:55:57  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 167 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
14:55:57
14:55:57  Concurrency: 1 threads (target='dev')
14:55:57
14:55:57  1 of 1 START view model main.stg_ratings ....................................... [RUN]
14:55:57  1 of 1 OK created view model main.stg_ratings .................................. [OK in 0.08s]
14:55:57
14:55:57  Finished running 1 view model in 0.24s.
14:55:57
14:55:57  Completed successfully
14:55:57
14:55:57  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

$ duckdb current_dbt.duckdb -c "select table_schema, table_name, table_type from information_schema.tables;"

┌────────────────┬───────────────┬────────────┐
│  table_schema  │  table_name   │ table_type │
├────────────────┼───────────────┼────────────┤
│ main_seed_data │ session_scans │ BASE TABLE │
│ main_seed_data │ rating_detail │ BASE TABLE │
│ main           │ stg_ratings   │ VIEW       │
└────────────────┴───────────────┴────────────┘

$ duckdb current_dbt.duckdb -c "select * from stg_ratings using sample 5;"

┌────────────────┬────────────────────┬────────┬─────────────────────┬─────────────────┬──────────────────┐
│   session_name │    rating_type     │ rating │ rating_comment      │  attendee_type  │ virtual_attendee │
├────────────────┼────────────────────┼────────┼─────────────────────┼─────────────────┼──────────────────┤
│ Session x      │ Content            │ 4      │ Need more cheetohs  │ Sponsor         │ 0                │
│ Session y   .. │ Content            │ 3      │                     │ General         │ 0                │
│ Session z      │ Presenter          │ 4      │ Great hair, ...     │ Sponsor         │ 0                │
│ Session foo .. │ Overall Experience │ 5      │                     │ Virtual         │ 1                │
│ Session bar .. │ Presenter          │ 5      │                     │ General         │ 0                │
└────────────────┴────────────────────┴────────┴─────────────────────┴─────────────────┴──────────────────┘

+-----------+-----------+--------+
| SessionID |Rating Type| rating |
+-----------+-----------+--------+
| 42        | Overall   | 5      |
| 42        | Presenter | 5      |
| 42        | Content   | 4      |
| 42        | Overall   | 5      |
| 42        | Presenter | 5      |
| 42        | Content   | 5      |
+-----------+-----------+--------+

+------------+----------------+------------------+----------------+
| session_id | content_rating | presenter_rating | overall_rating |
+------------+----------------+------------------+----------------+
| 42         | <null>         | <null>           | 5              |
| 42         | <null>         | 5                | <null>         |
| 42         | 4              | <null>           | <null>         |
| 42         | <null>         | <null>           | 5              |
| 42         | <null>         | 5                | <null>         |
| 42         | 5              | <null>           | <null>         |
+------------+----------------+------------------+----------------+

current_dbt.duckdb> select distinct rating_type from stg_ratings;
+--------------------+
| rating_type        |
+--------------------+
| Overall Experience |
| Presenter          |
| Content            |
+--------------------+
3 rows in set
Time: 0.010s

{% set rating_types = ['Overall Experience','Presenter', 'Content'] %}

-- Spec #5: Pivot rating type into individual columns
{% for r in rating_types -%}
  CASE WHEN rating_type = '{{ r }}' THEN rating END AS {{ r.lower().replace(' ','_') }}_rating,
  CASE WHEN rating_type = '{{ r }}' THEN "comment" END AS {{ r.lower().replace(' ','_') }}_comment,
{% endfor -%}

{% set rating_types = ['Overall Experience','Presenter', 'Content'] %}

WITH      source_data AS (
          -- Spec #4: Rename fields to remove spaces etc
          SELECT    TRIM(title)      AS session_name,
                    -- Spec #5: Pivot rating type into individual columns
                    {% for r in rating_types -%}
                      CASE WHEN "Rating Type" = '{{ r }}' THEN rating END AS {{ r.lower().replace(' ','_') }}_rating,
                      CASE WHEN "Rating Type" = '{{ r }}' THEN "comment" END AS {{ r.lower().replace(' ','_') }}_comment,
                    {% endfor -%}
                    -- Spec #7 Create a new field showing if attendee was in-person or not
                    CASE WHEN "Attendee Type" = 'Virtual' THEN 1 ELSE 0 END AS virtual_attendee
                    -- Spec #3: Remove PII data of those who left ratings
          FROM      {{ ref('rating_detail') }}
          )

SELECT    *
FROM      source_data
-- Spec #8: Exclude irrelevant sessions
WHERE     session_name NOT IN ('Breakfast', 'Lunch', 'Registration')

+-----+----------------------------+---------+
| cid | name                       | type    |
+-----+----------------------------+---------+
| 0   | session_name               | VARCHAR |
| 1   | overall_experience_rating  | INTEGER |
| 2   | overall_experience_comment | VARCHAR |
| 3   | presenter_rating           | INTEGER |
| 4   | presenter_comment          | VARCHAR |
| 5   | content_rating             | INTEGER |
| 6   | content_comment            | VARCHAR |
| 7   | attendee_type              | VARCHAR |
| 8   | virtual_attendee           | INTEGER |
+-----+----------------------------+---------+

+--------+--------+
| A      | B      |
+--------+--------+
| <null> | X      |
| X      | <null> |
| X      | X      |
+--------+--------+

WITH X AS (SELECT A, B,
       CASE WHEN A='X' THEN ['A'] END AS F0,
       CASE WHEN B='X' THEN ['B'] END AS F1
FROM FOO)
SELECT LIST_CONCAT(F0, F1) AS COMBINED_FLAGS FROM X

+----------------+
| COMBINED_FLAGS |
+----------------+
| ['B']          |
| ['A']          |
| ['A', 'B']     |
+----------------+

{% set tracks = ['Architectures You've Always Wondered About','Case Studies','Data Development Life Cycle','Developing Real-Time Applications','Event Streaming in Academia and Beyond','Fun and Geeky','Kafka Summit','Modern Data Flow','Operations and Observability','Panel','People & Culture','Real Time Analytics','Sponsored Session','Streaming Technologies'] %}

WITH      source_data AS (
          -- Spec #4: Rename fields to remove spaces etc
          SELECT    NAME                   AS session_name,
                    Speakers               AS speakers,
                    scans                  AS scans,
                    "# Survey Responses"   AS rating_ct,
                    -- Spec #9 Combine all track fields into a single summary
                    {% for t in tracks -%}
                    CASE WHEN t IS NOT NULL THEN ['t'] END
                                           AS F{{ loop.index }},
                    {% endfor -%}
          FROM      {{ ref('session_scans') }}
          )
SELECT    session_name,
          speakers,
          scans,
          rating_ct,
          LIST_CONCAT(
            {% for t in tracks -%}
              F{{ loop.index }},
            {% endfor -%}
          ) AS track
FROM      source_data

$ dbt run --select stg_scans
16:17:19  Running with dbt=1.1.1
16:17:19  Found 2 models, 0 tests, 0 snapshots, 0 analyses, 167 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
16:17:19
16:17:19  Concurrency: 1 threads (target='dev')
16:17:19
16:17:19  1 of 1 START view model main.stg_scans ......................................... [RUN]
16:17:19  1 of 1 OK created view model main.stg_scans .................................... [OK in 0.08s]
16:17:19
16:17:19  Finished running 1 view model in 0.20s.
16:17:20
16:17:20  Completed successfully
16:17:20
16:17:20  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

current_dbt.duckdb> describe stg_scans;
+-----+------------------+---------+---------+------------+-------+
| cid | name             | type    | notnull | dflt_value | pk    |
+-----+------------------+---------+---------+------------+-------+
| 0   | session_name     | VARCHAR | False   | <null>     | False |
| 1   | rating_type      | VARCHAR | False   | <null>     | False |
| 2   | rating           | INTEGER | False   | <null>     | False |
| 3   | rating_comment   | VARCHAR | False   | <null>     | False |
| 4   | attendee_type    | VARCHAR | False   | <null>     | False |
| 5   | virtual_attendee | INTEGER | False   | <null>     | False |
+-----+------------------+---------+---------+------------+-------+
Time: 0.009s

$ dbt run --select stg_scans
16:22:33  Running with dbt=1.1.1
16:22:33  Encountered an error:
Compilation Error in model stg_scans (models/staging/stg_scans.sql)
  expected token ',', got 've'
    line 1
      {% set tracks = ['Architectures You've Always Wondered About',
      […]

16:23:35  Completed with 1 error and 0 warnings:
16:23:35
16:23:35  Runtime Error in model stg_scans (models/staging/stg_scans.sql)
16:23:35    Parser Error: syntax error at or near ")"
16:23:35    LINE 62:             ) AS track
16:23:35                         ^

$ cat ./target/compiled/current_dbt/models/staging/stg_scans.sql

WITH      source_data AS (
          -- Spec #4: Rename fields to remove spaces etc
          SELECT    NAME                   AS session_name,
                    Speakers               AS speakers,
                    scans                  AS scans,
                    "# Survey Responses"   AS rating_ct,
                    -- Spec #9 Combine all track fields into a single summary
                    CASE WHEN t IS NOT NULL THEN ['t'] END
                                           AS F1,
                    CASE WHEN t IS NOT NULL THEN ['t'] END
                                           AS F2,
[…]
                    FROM      "main"."main_seed_data"."session_scans"
          )
SELECT    session_name,
          speakers,
          scans,
          rating_ct,
          LIST_CONCAT(
            F1,
            F2,
[…]
            ) AS track
FROM      source_data

  CASE WHEN t IS NOT NULL THEN ['t'] END

  CASE WHEN {{ t }} IS NOT NULL THEN ['{{ t }}'] END

$ cat ./target/compiled/current_dbt/models/staging/stg_scans.sql


WITH      source_data AS (
          -- Spec #4: Rename fields to remove spaces etc
          SELECT    NAME                   AS session_name,
                    Speakers               AS speakers,
                    scans                  AS scans,
                    "# Survey Responses"   AS rating_ct,
                    -- Spec #9 Combine all track fields into a single summary
                    CASE WHEN Architectures You've Always Wondered About IS NOT NULL THEN ['Architectures You've Always Wondered About'] END
                                           AS F1,
                    CASE WHEN Case Studies IS NOT NULL THEN ['Case Studies'] END
                                           AS F2,
                    CASE WHEN Data Development Life Cycle IS NOT NULL THEN ['Data Development Life Cycle'] END
                                           AS F3,
[…]

Parser Error: syntax error at or near ")"
LINE 60:             ) AS track

[…]
SELECT    session_name,
          speakers,
          scans,
          rating_ct,
          LIST_CONCAT(
            F1,
            F2,
[…]
            F13,
            ) AS track
FROM      source_data

LIST_CONCAT(
  {% for t in tracks -%}
    F{{ loop.index }},
  {% endfor -%}
) AS track

LIST_CONCAT(
  {% for t in tracks -%}
--  Literal                 If the current loop index    Literal
--  |    Loop index         is not the last one THEN     |      end if
--  |       |                       |                    |      |
--  V\--------------/ \---------------------------------/V \---------/
    F{{ loop.index }} {% if loop.index < tracks|length %}, {% endif %}
  {% endfor -%}
) AS track

LIST_CONCAT(
  F1 ,
  F2 ,
[…]
  F12 ,
  F13
  ) AS track

$ dbt run --select stg_scans
16:54:13  Running with dbt=1.1.1
16:54:14  Found 2 models, 0 tests, 0 snapshots, 0 analyses, 167 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0
 metrics
16:54:14
16:54:14  Concurrency: 1 threads (target='dev')
16:54:14
16:54:14  1 of 1 START view model main.stg_scans ......................................... [RUN]
16:54:14  1 of 1 ERROR creating view model main.stg_scans ................................ [ERROR in 0.05s]
16:54:14
16:54:14  Finished running 1 view model in 0.21s.
16:54:14
16:54:14  Completed with 1 error and 0 warnings:
16:54:14
16:54:14  Runtime Error in model stg_scans (models/staging/stg_scans.sql)
16:54:14    Binder Error: No function matches the given name and argument types 'list_concat(VARCHAR[], VARCHAR[], VARCHAR[],
 VARCHAR[], VARCHAR[], VARCHAR[], VARCHAR[], VARCHAR[], VARCHAR[], VARCHAR[], VARCHAR[], VARCHAR[], VARCHAR[])'. You might ne
ed to add explicit type casts.
16:54:14        Candidate functions:
16:54:14        list_concat(ANY[], ANY[]) -> ANY[]
16:54:14
16:54:14
16:54:14  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

current_dbt.duckdb> WITH X AS (SELECT A, B,
                           CASE WHEN A='X' THEN ['A'] END AS F0,
                           CASE WHEN B='X' THEN ['B'] END AS F1, CASE WHEN B='X' THEN ['B'] END AS F2
                    FROM FOO)
                    SELECT LIST_CONCAT(F0, F1, F2) AS COMBINED_FLAGS FROM X

Binder Error: No function matches the given name and argument types 'list_concat(VARCHAR[], VARCHAR[], VARCHAR[])'. You might need to add explicit type casts.
        Candidate functions:
        list_concat(ANY[], ANY[]) -> ANY[]

LINE 5: SELECT LIST_CONCAT(F0, F1, F2) AS COMBINED_FLAGS FROM X...
               ^

current_dbt.duckdb> WITH X AS (SELECT A, B,
                           CASE WHEN A='X' THEN ['A'] END AS F0,
                           CASE WHEN B='X' THEN ['B'] END AS F1,
                           CASE WHEN B='X' THEN ['B'] END AS F2
                    FROM FOO)
                    SELECT LIST_CONCAT(LIST_CONCAT(F0, F1), F2) AS COMBINED_FLAGS FROM X

+-----------------+
| COMBINED_FLAGS  |
+-----------------+
| ['B', 'B']      |
| ['A']           |
| ['A', 'B', 'B'] |
+-----------------+
3 rows in set
Time: 0.009s

[…]
SELECT    […],
          -- LIST_CONCAT takes two parameters, so we're going to stack them.
          -- Write a nested LIST_CONCAT for all but one occurance of the tracks
          {% for x in range((tracks|length -1)) -%}
            LIST_CONCAT(
          {% endfor -%}
          -- For every track…
          {% for t in tracks -%}
            -- Write out the field number
            F{{ loop.index }}
            -- Unless it's the first one, add a close parenthesis
            {% if loop.index !=1  %}) {% endif %}
            -- Unless it's the last one, add a comma
            {% if loop.index < tracks|length %}, {% endif %}
          {% endfor -%}
          AS track
FROM      source_data

SELECT    […]
          LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT ( LIST_CONCAT (
                        F1 , F2 ) , F3 ) , F4 ) , F5 ) , F6 ) , F7 ) , F8 ) , F9 ) , F10 ) , F11 ) , F12 ) , F13 )
          AS track
FROM      source_data

+-------------------------------------------------------+
| track                                                 |
+-------------------------------------------------------+
| ['Kafka Summit', 'Modern Data Flow']                  |
| ['Panel']                                             |
| <null>                                                |
| ['Kafka Summit', 'Streaming Technologies']            |
| ['Event Streaming in Academia and Beyond']            |
[…]

current_dbt.duckdb> select count (distinct session_name) from stg_scans;
+------------------------------+
| count(DISTINCT session_name) |
+------------------------------+
| 157                          |
+------------------------------+
1 row in set
Time: 0.009s
current_dbt.duckdb> select count (distinct session_name) from stg_ratings
+------------------------------+
| count(DISTINCT session_name) |
+------------------------------+
| 123                          |
+------------------------------+
1 row in set
Time: 0.008s

current_dbt.duckdb> select session_name from stg_ratings
                      where session_name not in
                        (select session_name
                           from stg_scans);
0 rows in set
Time: 0.006s
current_dbt.duckdb> select session_name from stg_scans
                      where session_name not in
                        (select session_name
                           from stg_ratings);
117 rows in set
Time: 0.032s

current_dbt.duckdb> select session_name
                      from stg_scans
                    where session_name in
                      (select session_name
                         from stg_ratings)
                    fetch first 1 row only;
+---------------------------------+
| session_name                    |
+---------------------------------+
| "Why Wait?" Real-time Ingestion |
+---------------------------------+
1 row in set
Time: 0.009s

current_dbt.duckdb> SELECT    'stg_ratings'     AS source,
                              MD5(session_name) AS session_id,
                              session_name
                    FROM      stg_ratings
                    WHERE     session_name = '"Why Wait?" Real-time Ingestion'
                    UNION
                    SELECT    'stg_scans'       AS source,
                              MD5(session_name) AS session_id,
                              session_name
                    FROM      stg_scans
                    WHERE     session_name = '"Why Wait?" Real-time Ingestion';
+-------------+----------------------------------+---------------------------------+
| source      | session_id                       | session_name                    |
+-------------+----------------------------------+---------------------------------+
| stg_ratings | 43f10e52cd2f23100571189beee23450 | "Why Wait?" Real-time Ingestion |
| stg_scans   | 43f10e52cd2f23100571189beee23450 | "Why Wait?" Real-time Ingestion |
+-------------+----------------------------------+---------------------------------+
2 rows in set
Time: 0.011s

current_dbt.duckdb> SELECT    MD5(session_name) AS session_id,
                              session_name
                    FROM      stg_ratings
                    WHERE     session_name = '"Why Wait?" Real-time Ingestion'
                    UNION
                    SELECT    MD5(session_name) AS session_id,
                              session_name
                    FROM      stg_scans
                    WHERE     session_name = '"Why Wait?" Real-time Ingestion';
+----------------------------------+---------------------------------+
| session_id                       | session_name                    |
+----------------------------------+---------------------------------+
| 43f10e52cd2f23100571189beee23450 | "Why Wait?" Real-time Ingestion |
+----------------------------------+---------------------------------+
1 row in set
Time: 0.010s

current_dbt.duckdb> select session_name
                      from stg_scans
                     where session_name not in
                      (select session_name
                          from stg_ratings)
                    fetch first 1 row only;


+----------------------------------------------------------------------------------+
| session_name                                                                     |
+----------------------------------------------------------------------------------+
| A Systematic Literature Review and Meta-Analysis of Event Streaming in Academia  |
+----------------------------------------------------------------------------------+
1 row in set
Time: 0.009s

current_dbt.duckdb> SELECT    'stg_ratings'     AS source,
                              MD5(session_name) AS session_id,
                              session_name
                    FROM      stg_ratings
                    WHERE     session_name = 'A Systematic Literature Review and Meta-Analysis of Event Streaming in Academia'
                    UNION
                    SELECT    'stg_scans'       AS source,
                              MD5(session_name) AS session_id,
                              session_name
                    FROM      stg_scans
                    WHERE     session_name = 'A Systematic Literature Review and Meta-Analysis of Event Streaming in Academia';

+-------------+----------------------------------+---------------------------------------------------------------------------------+
| source      | session_id                       | session_name                                                                    |
+-------------+----------------------------------+---------------------------------------------------------------------------------+
| stg_ratings | a8b8ea81d950cee37061756ddebc67a0 | A Systematic Literature Review and Meta-Analysis of Event Streaming in Academia |
+-------------+----------------------------------+---------------------------------------------------------------------------------+
1 row in set
Time: 0.012s

current_dbt.duckdb> SELECT    MD5(session_name) AS session_id,
                              session_name
                    FROM      stg_ratings
                    WHERE     session_name IN ('"Why Wait?" Real-time Ingestion', 'A Systematic Literature Review and Meta-Analysis of Event Streaming in Academia')
                    UNION
                    SELECT    MD5(session_name) AS session_id,
                              session_name
                    FROM      stg_scans
                    WHERE     session_name IN ('"Why Wait?" Real-time Ingestion', 'A Systematic Literature Review and Meta-Analysis of Event Streaming in Academia')

+----------------------------------+---------------------------------------------------------------------------------+
| session_id                       | session_name                                                                    |
+----------------------------------+---------------------------------------------------------------------------------+
| 43f10e52cd2f23100571189beee23450 | "Why Wait?" Real-time Ingestion                                                 |
| a8b8ea81d950cee37061756ddebc67a0 | A Systematic Literature Review and Meta-Analysis of Event Streaming in Academia |
+----------------------------------+---------------------------------------------------------------------------------+
2 rows in set
Time: 0.012s

WITH      source_data AS (
          -- Spec #6: Create a unique ID for each session
          SELECT    md5(session_name)  AS session_id,
                    session_name
          FROM      {{ ref('stg_ratings') }}
          UNION
          SELECT    md5(session_name)  AS session_id,
                    session_name
          FROM      {{ ref('stg_scans') }}
          )

SELECT    *
FROM      source_data

$ dbt run
08:32:55  Running with dbt=1.1.1
08:32:55  Found 3 models, 0 tests, 0 snapshots, 0 analyses, 167 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
08:32:55
08:32:55  Concurrency: 1 threads (target='dev')
08:32:55
08:32:55  1 of 3 START view model main.stg_ratings ....................................... [RUN]
08:32:55  1 of 3 OK created view model main.stg_ratings .................................. [OK in 0.07s]
08:32:55  2 of 3 START view model main.stg_scans ......................................... [RUN]
08:32:55  2 of 3 OK created view model main.stg_scans .................................... [OK in 0.04s]
08:32:55  3 of 3 START view model main.stg_sessionid ..................................... [RUN]
08:32:55  3 of 3 OK created view model main.stg_sessionid ................................ [OK in 0.07s]
08:32:55
08:32:55  Finished running 3 view models in 0.30s.
08:32:55
08:32:55  Completed successfully
08:32:55
08:32:55  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

current_dbt.duckdb> select count(*) from stg_sessionid;
+--------------+
| count_star() |
+--------------+
| 241          |
+--------------+
1 row in set
Time: 0.009s

WITH      source_data AS (
          -- Spec #6: Create a unique ID for each session
          SELECT    md5(session_name)  AS session_id,
                    session_name
          FROM      {{ ref('stg_ratings') }}
          UNION
          SELECT    md5(session_name)  AS session_id,
                    session_name
          FROM      {{ ref('stg_scans') }}
          )

SELECT    src.session_id,
          src.session_name,
          sc.speakers,
          sc.track,
          SUM(sc.scans) AS scans,
          SUM(sc.rating_ct) AS rating_ct
FROM      src.source_data src
          LEFT OUTER JOIN
          {{ ref('stg_scans') }} sc
          ON src.session_name = sc.session_name
WHERE     src.session_name IS NOT NULL
GROUP BY  src.session_id,
          src.session_name,
          sc.speakers,
          sc.track

diff --git a/current_dbt/models/staging/stg_scans.sql b/current_dbt/models/staging/stg_scans.sql
[…]
-                    scans                  AS scans,
+                    TRY_CAST(scans AS INT) AS scans,