How do I add a column to my table with its own logic?

The problem I’m having

I’m trying to add a column to my table, that has a random value between x and y for each row.

The context of why I’m trying to do this

I have 2 datasets, I want to join each row in dataset A with a random row in dataset B. Dataset B has a unique and incrementing ID. I want to add a column to dataset B which has a random value of [1, length(dataset B)].

What I’ve already tried

I think making a macro is the way to go, and I tried experimenting with some, but I don’t see a lot of information about this.

Some example code or error messages

I want to keep it really simple

with some_result as (
    select * from {{ source('some_schema', 'some_table') }}

select * from some_result