Recuperando o valor de status mais recente antes de uma determinada data de transação

Como você não especificou o banco de dados que usa, aqui está um exemplo para SQL Server

SELECT T.ACCT,T.TRAN_DATE,X.STATUS
FROM TRANSACTIONS_TABLE AS T
CROSS APPLY
(
  SELECT TOP 1 S.STATUS
  FROM STATUS_TABLE AS S
  WHERE T.ACCT=S.ACCT
  AND S.INSERT_DATE<=T.TRAN_DATE
  ORDER BY S.INSERT_DATE DESC
)AS X

Conceitualmente o mesmo da resposta acima, aqui está a versão em floco de neve, já que você mencionou que é para o Snowflake.

1. Unimos a tabela Tran com a tabela Status para obter todas as correspondências possíveis onde os números de conta são os mesmos e o status INSERT_DATEé igual ou anterior a TRAN_DATE.

2. Em seguida, uma classificação é atribuída para a mesma conta e TRAN_DATE, com base no status INSERT_DATE, elas são ordenadas da mais recente para a mais antiga, de modo que a mais recente INSERT_DATErecebe a classificação 1.

3. Isso é então filtrado usando QUALIFYpara buscar o status mais recente para cada TRAN_DATE.

SELECT T.ACCT, T.TRAN_DATE, S.STATUS
FROM
TRAN T
LEFT JOIN 
STATUS S
ON T.ACCT = S.ACCT
AND S.INSERT_DATE <= T.TRAN_DATE
QUALIFY ROW_NUMBER() OVER (PARTITION BY T.ACCT, T.TRAN_DATE ORDER BY S.INSERT_DATE DESC) = 1
ORDER BY T.TRAN_DATE DESC;

Gera