===== lab05x - Wed Nov  4 12:53:56 PM UTC 2026 =====
$ cd /home/onyxia/work/ece-2026-bigdata/lab_medallion
$ cat
$ python3 -
$ cat models/silver/silver.yml
models:
  - name: stg_users
    description: One row per user, typed, with the address split into its components.
    columns:
      - name: user_id
        description: Identifier of the user.
        data_tests:
          - unique
          - not_null
      - name: username_normalized
        description: Username without leading/trailing whitespace, in lowercase.
        data_tests:
          - not_null
          - unique
      - name: birthdate_is_valid
        description: False when the original birthdate is later than the first order of the user (the birthdate is then NULL).
        data_tests:
          - not_null
      - name: email
        data_tests:
          - unique
          - not_null
      - name: sex
        data_tests:
          - accepted_values:
              arguments:
                values: ["F", "M"]
      - name: state
        description: Two letters code of the state, territory or military region.
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('states')
                field: code

  - name: stg_orders
    description: One row per order, typed and deduplicated.
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: user_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_users')
                field: user_id
      - name: quantity
        data_tests:
          - accepted_values:
              arguments:
                values: [1, 2, 3, 4, 5]
                quote: false
      - name: product
        data_tests:
          - accepted_values:
              arguments:
                values: ["bread", "brioche", "cookie", "croissant", "donut", "drink"]
$ sed -i '/config(severity/d' tests/assert_orders_after_user_birth.sql
$ cat tests/assert_orders_after_user_birth.sql

-- A user cannot order before being born: the test fails if this query returns rows
select
    o.order_id,
    o.ordered_at,
    u.user_id,
    u.birthdate
from {{ ref('stg_orders') }} o
join {{ ref('stg_users') }} u on o.user_id = u.user_id
where o.ordered_at < u.birthdate
$ cat
$ cat
$ cat
$ uv run dbt build
12:53:59  Running with dbt=1.12.6
12:53:59  Registered adapter: duckdb=1.11.1
12:54:02  Found 2 seeds, 20 data tests, 2 models, 2 sources, 511 macros
12:54:02  
12:54:02  Concurrency: 4 threads (target='dev')
12:54:02  
12:54:03  3 of 24 START seed file main.states ............................................ [RUN]
12:54:03  2 of 24 START sql view model main_silver.stg_users ............................. [RUN]
12:54:03  1 of 24 START sql view model main_silver.stg_orders ............................ [RUN]
12:54:03  4 of 24 START seed file main.zip_ranges ........................................ [RUN]
12:54:04  4 of 24 OK loaded seed file main.zip_ranges .................................... [INSERT 64 in 1.28s]
12:54:04  3 of 24 OK loaded seed file main.states ........................................ [INSERT 62 in 1.31s]
12:54:04  1 of 24 OK created sql view model main_silver.stg_orders ....................... [OK in 1.51s]
12:54:04  2 of 24 OK created sql view model main_silver.stg_users ........................ [OK in 1.52s]
12:54:04  8 of 24 START test not_null_stg_orders_order_id ................................ [RUN]
12:54:04  5 of 24 START test accepted_values_stg_orders_product__bread__brioche__cookie__croissant__donut__drink  [RUN]
12:54:04  6 of 24 START test accepted_values_stg_orders_quantity__False__1__2__3__4__5 ... [RUN]
12:54:04  7 of 24 START test assert_orders_quantity_positive ............................. [RUN]
12:54:05  7 of 24 PASS assert_orders_quantity_positive ................................... [PASS in 0.63s]
12:54:05  6 of 24 PASS accepted_values_stg_orders_quantity__False__1__2__3__4__5 ......... [PASS in 0.67s]
12:54:05  9 of 24 START test not_null_stg_orders_user_id ................................. [RUN]
12:54:05  5 of 24 PASS accepted_values_stg_orders_product__bread__brioche__cookie__croissant__donut__drink  [PASS in 0.70s]
12:54:05  10 of 24 START test unique_stg_orders_order_id ................................. [RUN]
12:54:05  8 of 24 PASS not_null_stg_orders_order_id ...................................... [PASS in 0.73s]
12:54:05  11 of 24 START test accepted_values_stg_users_sex__F__M ........................ [RUN]
12:54:05  12 of 24 START test assert_orders_after_user_birth ............................. [RUN]
12:54:06  9 of 24 PASS not_null_stg_orders_user_id ....................................... [PASS in 0.49s]
12:54:06  13 of 24 START test assert_orders_have_users ................................... [RUN]
12:54:06  10 of 24 PASS unique_stg_orders_order_id ....................................... [PASS in 0.60s]
12:54:06  14 of 24 START test assert_users_zip_matches_state ............................. [RUN]
12:54:06  11 of 24 PASS accepted_values_stg_users_sex__F__M .............................. [PASS in 0.56s]
12:54:06  15 of 24 START test not_null_stg_users_birthdate_is_valid ...................... [RUN]
12:54:06  12 of 24 PASS assert_orders_after_user_birth ................................... [PASS in 0.59s]
12:54:06  16 of 24 START test not_null_stg_users_email ................................... [RUN]
12:54:06  13 of 24 PASS assert_orders_have_users ......................................... [PASS in 0.42s]
12:54:06  17 of 24 START test not_null_stg_users_state ................................... [RUN]
12:54:06  15 of 24 PASS not_null_stg_users_birthdate_is_valid ............................ [PASS in 0.49s]
12:54:06  16 of 24 PASS not_null_stg_users_email ......................................... [PASS in 0.47s]
12:54:06  14 of 24 WARN 49 assert_users_zip_matches_state ................................ [WARN 49 in 0.56s]
12:54:06  18 of 24 START test not_null_stg_users_user_id ................................. [RUN]
12:54:06  19 of 24 START test not_null_stg_users_username_normalized ..................... [RUN]
12:54:06  20 of 24 START test relationships_stg_orders_user_id__user_id__ref_stg_users_ .. [RUN]
12:54:07  17 of 24 PASS not_null_stg_users_state ......................................... [PASS in 0.52s]
12:54:07  21 of 24 START test relationships_stg_users_state__code__ref_states_ ........... [RUN]
12:54:07  19 of 24 PASS not_null_stg_users_username_normalized ........................... [PASS in 0.40s]
12:54:07  22 of 24 START test unique_stg_users_email ..................................... [RUN]
12:54:07  18 of 24 PASS not_null_stg_users_user_id ....................................... [PASS in 0.43s]
12:54:07  23 of 24 START test unique_stg_users_user_id ................................... [RUN]
12:54:07  20 of 24 PASS relationships_stg_orders_user_id__user_id__ref_stg_users_ ........ [PASS in 0.53s]
12:54:07  24 of 24 START test unique_stg_users_username_normalized ....................... [RUN]
12:54:07  21 of 24 PASS relationships_stg_users_state__code__ref_states_ ................. [PASS in 0.41s]
12:54:07  22 of 24 PASS unique_stg_users_email ........................................... [PASS in 0.36s]
12:54:07  24 of 24 PASS unique_stg_users_username_normalized ............................. [PASS in 0.21s]
12:54:07  23 of 24 PASS unique_stg_users_user_id ......................................... [PASS in 0.36s]
12:54:07  
12:54:07  Finished running 2 seeds, 20 data tests, 2 view models in 0 hours 0 minutes and 4.98 seconds (4.98s).
12:54:07  
12:54:07  Completed with 1 warning:
12:54:07  
12:54:07  [WARNING]: in test assert_users_zip_matches_state (tests/assert_users_zip_matches_state.sql)
12:54:07  [WARNING]: Got 49 results, configured to warn if != 0
12:54:07  
12:54:07    compiled code at target/compiled/lab_medallion/tests/assert_users_zip_matches_state.sql
12:54:07  
12:54:07  Done. PASS=23 WARN=1 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=24
$ uv run python -c '
import duckdb
con = duckdb.connect('\''lab_medallion.duckdb'\'', read_only=True)
con.sql('\''SELECT birthdate_is_valid, count(*) FROM main_silver.stg_users GROUP BY 1'\'').show()
con.sql('\''SELECT username, username_normalized, birthdate, birthdate_is_valid FROM main_silver.stg_users LIMIT 5'\'').show()
'
┌────────────────────┬──────────────┐
│ birthdate_is_valid │ count_star() │
│      boolean       │    int64     │
├────────────────────┼──────────────┤
│ false              │            5 │
│ true               │           45 │
└────────────────────┴──────────────┘

┌──────────────┬─────────────────────┬────────────┬────────────────────┐
│   username   │ username_normalized │ birthdate  │ birthdate_is_valid │
│   varchar    │       varchar       │    date    │      boolean       │
├──────────────┼─────────────────────┼────────────┼────────────────────┤
│ ithomas      │ ithomas             │ 1972-03-21 │ true               │
│ kayla51      │ kayla51             │ 2018-08-13 │ true               │
│ millertodd   │ millertodd          │ 1939-08-07 │ true               │
│ perezrebecca │ perezrebecca        │ 1959-12-23 │ true               │
│ wrightjames  │ wrightjames         │ 1941-08-09 │ true               │
└──────────────┴─────────────────────┴────────────┴────────────────────┘

$ sed -i 's/+materialized: view/+materialized: table/' dbt_project.yml
$ uv run dbt build --select silver
12:54:12  Running with dbt=1.12.6
12:54:13  Registered adapter: duckdb=1.11.1
12:54:14  Unable to do partial parsing because a project config has changed
12:54:19  Found 2 models, 20 data tests, 2 seeds, 2 sources, 511 macros
12:54:19  
12:54:19  Concurrency: 4 threads (target='dev')
12:54:19  
12:54:19  1 of 22 START sql table model main_silver.stg_orders ........................... [RUN]
12:54:19  2 of 22 START sql table model main_silver.stg_users ............................ [RUN]
12:54:21  1 of 22 OK created sql table model main_silver.stg_orders ...................... [OK in 1.40s]
12:54:21  2 of 22 OK created sql table model main_silver.stg_users ....................... [OK in 1.40s]
12:54:21  5 of 22 START test assert_orders_quantity_positive ............................. [RUN]
12:54:21  4 of 22 START test accepted_values_stg_orders_quantity__False__1__2__3__4__5 ... [RUN]
12:54:21  3 of 22 START test accepted_values_stg_orders_product__bread__brioche__cookie__croissant__donut__drink  [RUN]
12:54:21  6 of 22 START test not_null_stg_orders_order_id ................................ [RUN]
12:54:21  3 of 22 PASS accepted_values_stg_orders_product__bread__brioche__cookie__croissant__donut__drink  [PASS in 0.41s]
12:54:21  5 of 22 PASS assert_orders_quantity_positive ................................... [PASS in 0.43s]
12:54:21  4 of 22 PASS accepted_values_stg_orders_quantity__False__1__2__3__4__5 ......... [PASS in 0.47s]
12:54:21  7 of 22 START test not_null_stg_orders_user_id ................................. [RUN]
12:54:21  8 of 22 START test unique_stg_orders_order_id .................................. [RUN]
12:54:21  6 of 22 PASS not_null_stg_orders_order_id ...................................... [PASS in 0.49s]
12:54:21  9 of 22 START test accepted_values_stg_users_sex__F__M ......................... [RUN]
12:54:21  10 of 22 START test assert_orders_after_user_birth ............................. [RUN]
12:54:21  7 of 22 PASS not_null_stg_orders_user_id ....................................... [PASS in 0.36s]
12:54:22  9 of 22 PASS accepted_values_stg_users_sex__F__M ............................... [PASS in 0.32s]
12:54:22  11 of 22 START test assert_orders_have_users ................................... [RUN]
12:54:22  8 of 22 PASS unique_stg_orders_order_id ........................................ [PASS in 0.40s]
12:54:22  10 of 22 PASS assert_orders_after_user_birth ................................... [PASS in 0.28s]
12:54:22  12 of 22 START test assert_users_zip_matches_state ............................. [RUN]
12:54:22  13 of 22 START test not_null_stg_users_birthdate_is_valid ...................... [RUN]
12:54:22  14 of 22 START test not_null_stg_users_email ................................... [RUN]
12:54:22  13 of 22 PASS not_null_stg_users_birthdate_is_valid ............................ [PASS in 0.31s]
12:54:22  11 of 22 PASS assert_orders_have_users ......................................... [PASS in 0.38s]
12:54:22  12 of 22 WARN 49 assert_users_zip_matches_state ................................ [WARN 49 in 0.36s]
12:54:22  14 of 22 PASS not_null_stg_users_email ......................................... [PASS in 0.32s]
12:54:22  15 of 22 START test not_null_stg_users_state ................................... [RUN]
12:54:22  16 of 22 START test not_null_stg_users_user_id ................................. [RUN]
12:54:22  17 of 22 START test not_null_stg_users_username_normalized ..................... [RUN]
12:54:22  18 of 22 START test relationships_stg_orders_user_id__user_id__ref_stg_users_ .. [RUN]
12:54:22  15 of 22 PASS not_null_stg_users_state ......................................... [PASS in 0.38s]
12:54:22  19 of 22 START test relationships_stg_users_state__code__ref_states_ ........... [RUN]
12:54:22  16 of 22 PASS not_null_stg_users_user_id ....................................... [PASS in 0.41s]
12:54:22  18 of 22 PASS relationships_stg_orders_user_id__user_id__ref_stg_users_ ........ [PASS in 0.44s]
12:54:22  20 of 22 START test unique_stg_users_email ..................................... [RUN]
12:54:22  17 of 22 PASS not_null_stg_users_username_normalized ........................... [PASS in 0.48s]
12:54:23  21 of 22 START test unique_stg_users_user_id ................................... [RUN]
12:54:23  22 of 22 START test unique_stg_users_username_normalized ....................... [RUN]
12:54:23  19 of 22 PASS relationships_stg_users_state__code__ref_states_ ................. [PASS in 0.41s]
12:54:23  20 of 22 PASS unique_stg_users_email ........................................... [PASS in 0.34s]
12:54:23  21 of 22 PASS unique_stg_users_user_id ......................................... [PASS in 0.29s]
12:54:23  22 of 22 PASS unique_stg_users_username_normalized ............................. [PASS in 0.27s]
12:54:23  
12:54:23  Finished running 2 table models, 20 data tests in 0 hours 0 minutes and 4.10 seconds (4.10s).
12:54:23  
12:54:23  Completed with 1 warning:
12:54:23  
12:54:23  [WARNING]: in test assert_users_zip_matches_state (tests/assert_users_zip_matches_state.sql)
12:54:23  [WARNING]: Got 49 results, configured to warn if != 0
12:54:23  
12:54:23    compiled code at target/compiled/lab_medallion/tests/assert_users_zip_matches_state.sql
12:54:23  
12:54:23  Done. PASS=21 WARN=1 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=22
$ uv run python -c '
import duckdb
con = duckdb.connect('\''lab_medallion.duckdb'\'', read_only=True)
con.sql('\''SELECT table_schema, table_name, table_type FROM information_schema.tables ORDER BY ALL'\'').show()
con.sql("SELECT database_size, total_blocks, used_blocks FROM pragma_database_size()").show()
'
┌──────────────┬────────────┬────────────┐
│ table_schema │ table_name │ table_type │
│   varchar    │  varchar   │  varchar   │
├──────────────┼────────────┼────────────┤
│ main         │ states     │ BASE TABLE │
│ main         │ zip_ranges │ BASE TABLE │
│ main_silver  │ stg_orders │ BASE TABLE │
│ main_silver  │ stg_users  │ BASE TABLE │
└──────────────┴────────────┴────────────┘

┌───────────────┬──────────────┬─────────────┐
│ database_size │ total_blocks │ used_blocks │
│    varchar    │    int64     │    int64    │
├───────────────┼──────────────┼─────────────┤
│ 1.2 MiB       │            5 │           5 │
└───────────────┴──────────────┴─────────────┘

$ ls -la lab_medallion.duckdb
-rw-r--r-- 1 onyxia users 1323008 Nov  4 12:54 lab_medallion.duckdb
$ sed -i 's/+materialized: table/+materialized: view/' dbt_project.yml
$ uv run dbt build --select silver
12:54:28  Running with dbt=1.12.6
12:54:28  Registered adapter: duckdb=1.11.1
12:54:29  Unable to do partial parsing because a project config has changed
12:54:33  Found 2 models, 20 data tests, 2 seeds, 2 sources, 511 macros
12:54:33  
12:54:33  Concurrency: 4 threads (target='dev')
12:54:33  
12:54:34  2 of 22 START sql view model main_silver.stg_users ............................. [RUN]
12:54:34  1 of 22 START sql view model main_silver.stg_orders ............................ [RUN]
12:54:35  1 of 22 OK created sql view model main_silver.stg_orders ....................... [OK in 1.25s]
12:54:35  2 of 22 OK created sql view model main_silver.stg_users ........................ [OK in 1.26s]
12:54:35  3 of 22 START test accepted_values_stg_orders_product__bread__brioche__cookie__croissant__donut__drink  [RUN]
12:54:35  4 of 22 START test accepted_values_stg_orders_quantity__False__1__2__3__4__5 ... [RUN]
12:54:35  6 of 22 START test assert_orders_after_user_birth .............................. [RUN]
12:54:35  5 of 22 START test accepted_values_stg_users_sex__F__M ......................... [RUN]
12:54:36  3 of 22 PASS accepted_values_stg_orders_product__bread__brioche__cookie__croissant__donut__drink  [PASS in 0.69s]
12:54:36  7 of 22 START test assert_orders_have_users .................................... [RUN]
12:54:36  5 of 22 PASS accepted_values_stg_users_sex__F__M ............................... [PASS in 0.78s]
12:54:36  4 of 22 PASS accepted_values_stg_orders_quantity__False__1__2__3__4__5 ......... [PASS in 0.79s]
12:54:36  8 of 22 START test assert_orders_quantity_positive ............................. [RUN]
12:54:36  9 of 22 START test assert_users_zip_matches_state .............................. [RUN]
12:54:36  6 of 22 PASS assert_orders_after_user_birth .................................... [PASS in 0.94s]
12:54:36  10 of 22 START test not_null_stg_orders_order_id ............................... [RUN]
12:54:36  7 of 22 PASS assert_orders_have_users .......................................... [PASS in 0.41s]
12:54:36  11 of 22 START test not_null_stg_orders_user_id ................................ [RUN]
12:54:36  8 of 22 PASS assert_orders_quantity_positive ................................... [PASS in 0.45s]
12:54:36  12 of 22 START test not_null_stg_users_birthdate_is_valid ...................... [RUN]
12:54:36  9 of 22 WARN 49 assert_users_zip_matches_state ................................. [WARN 49 in 0.57s]
12:54:36  13 of 22 START test not_null_stg_users_email ................................... [RUN]
12:54:36  10 of 22 PASS not_null_stg_orders_order_id ..................................... [PASS in 0.46s]
12:54:37  14 of 22 START test not_null_stg_users_state ................................... [RUN]
12:54:37  11 of 22 PASS not_null_stg_orders_user_id ...................................... [PASS in 0.48s]
12:54:37  15 of 22 START test not_null_stg_users_user_id ................................. [RUN]
12:54:37  12 of 22 PASS not_null_stg_users_birthdate_is_valid ............................ [PASS in 0.52s]
12:54:37  16 of 22 START test not_null_stg_users_username_normalized ..................... [RUN]
12:54:37  14 of 22 PASS not_null_stg_users_state ......................................... [PASS in 0.37s]
12:54:37  13 of 22 PASS not_null_stg_users_email ......................................... [PASS in 0.46s]
12:54:37  17 of 22 START test relationships_stg_orders_user_id__user_id__ref_stg_users_ .. [RUN]
12:54:37  18 of 22 START test relationships_stg_users_state__code__ref_states_ ........... [RUN]
12:54:37  15 of 22 PASS not_null_stg_users_user_id ....................................... [PASS in 0.42s]
12:54:37  19 of 22 START test unique_stg_orders_order_id ................................. [RUN]
12:54:37  16 of 22 PASS not_null_stg_users_username_normalized ........................... [PASS in 0.39s]
12:54:37  20 of 22 START test unique_stg_users_email ..................................... [RUN]
12:54:37  18 of 22 PASS relationships_stg_users_state__code__ref_states_ ................. [PASS in 0.33s]
12:54:37  21 of 22 START test unique_stg_users_user_id ................................... [RUN]
12:54:37  19 of 22 PASS unique_stg_orders_order_id ....................................... [PASS in 0.29s]
12:54:37  22 of 22 START test unique_stg_users_username_normalized ....................... [RUN]
12:54:38  20 of 22 PASS unique_stg_users_email ........................................... [PASS in 0.36s]
12:54:38  17 of 22 PASS relationships_stg_orders_user_id__user_id__ref_stg_users_ ........ [PASS in 0.71s]
12:54:38  21 of 22 PASS unique_stg_users_user_id ......................................... [PASS in 0.36s]
12:54:38  22 of 22 PASS unique_stg_users_username_normalized ............................. [PASS in 0.28s]
12:54:38  
12:54:38  Finished running 20 data tests, 2 view models in 0 hours 0 minutes and 4.41 seconds (4.41s).
12:54:38  
12:54:38  Completed with 1 warning:
12:54:38  
12:54:38  [WARNING]: in test assert_users_zip_matches_state (tests/assert_users_zip_matches_state.sql)
12:54:38  [WARNING]: Got 49 results, configured to warn if != 0
12:54:38  
12:54:38    compiled code at target/compiled/lab_medallion/tests/assert_users_zip_matches_state.sql
12:54:38  
12:54:38  Done. PASS=21 WARN=1 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=22
$ grep materialized dbt_project.yml
      +materialized: view
$ cd /home/onyxia/work/ece-2026-bigdata
$ git add lab_medallion
$ git status --short
M  lab_medallion/models/silver/silver.yml
M  lab_medallion/models/silver/stg_users.sql
M  lab_medallion/tests/assert_orders_after_user_birth.sql
A  lab_medallion/tests/assert_orders_quantity_positive.sql
A  lab_medallion/tests/assert_users_zip_matches_state.sql
$ git commit -m 'feat(dbt): exercises - normalized username, birthdate validation, zip ranges'
[main f8bfa12] feat(dbt): exercises - normalized username, birthdate validation, zip ranges
 5 files changed, 49 insertions(+), 12 deletions(-)
 create mode 100644 lab_medallion/tests/assert_orders_quantity_positive.sql
 create mode 100644 lab_medallion/tests/assert_users_zip_matches_state.sql
$ gpush
$ echo '[git push differe : il sera fait a la fin, avec l'\''authentification GitHub de l'\''utilisateur]'
[git push differe : il sera fait a la fin, avec l'authentification GitHub de l'utilisateur]
$ true
EXIT_CODE=0