Coldstart BQ tables for BQ Re-Warehouse(using DBT)

with inputs_unnested AS (
SELECT
  ins.index AS index,
  ins.spent_transaction_id AS create_transaction_id,
  transaction_id AS spending_transaction_id,
  spent_output_index AS create_output_index,
  0 AS sequence,
  '' AS script_asm,
  '' AS script_hex,
  0 AS required_signatures,
  SPLIT(address, ",") AS addresses,
  ins.value AS value,
  ins.type AS type
FROM
  `staging-btc-etl.ch_tg_prod_litecoin.inputs_v2`,
  UNNEST(inputs) AS ins
WHERE
  DATE(block_timestamp) < '2022-10-01'
  ),
  outputs_unnested AS(
  SELECT
  outs.index AS index,
  transaction_id AS create_transaction_id,
  '' AS spending_transaction_id,
  '' AS script_asm,
  '' AS script_hex,
  type,
  SPLIT(address, ",") AS addresses,
  outs.value AS value,
  0 AS required_signatures
FROM
  `staging-btc-etl.ch_tg_prod_litecoin.outputs_v2`,
  UNNEST(outputs) AS outs
WHERE
  DATE(block_timestamp) < '2022-10-01'
  )
SELECT
  txns.hash,
  txns.size,
  txns.virtual_size,
  txns.version,
  txns.lock_time,
  txns.block_hash,
  txns.block_number,
  txns.block_timestamp,
  txns.block_timestamp_month,
  txns.input_count,
  txns.output_count,
  txns.input_value,
  txns.output_value,
  txns.is_coinbase,
  txns.fee,
  ARRAY_AGG(STRUCT(ins.index,
      ins.create_transaction_id,
      ins.spending_transaction_id,
      ins.create_output_index,
      ins.sequence,
      ins.script_asm,
      ins.script_hex,
      ins.required_signatures,
      ins.addresses,
      ins.value,
      ins.type)) AS inputs,
  ARRAY_AGG(STRUCT(outs.index,
      outs.create_transaction_id,
      outs.spending_transaction_id,
      outs.script_asm,
      outs.script_hex,
      outs.type,
      outs.addresses,
      outs.value,
      outs.required_signatures)) AS outputs
FROM
  `bigquery-public-data.crypto_litecoin.transactions` AS txns
LEFT JOIN
  outputs_unnested AS outs
ON
  txns.hash=outs.create_transaction_id
LEFT JOIN
  inputs_unnested AS ins
ON
  ins.spending_transaction_id=outs.create_transaction_id
WHERE
  DATE(txns.block_timestamp) < '2022-10-01'
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  12,
  13,
  14,
  15

SQL

BCH

SELECT
  *
FROM
  `bigquery-public-data.crypto_bitcoin_cash.transactions`
WHERE
  DATE(block_timestamp) < '2022-10-01'

CODE

BTC

SELECT
  *
FROM
  `bigquery-public-data.crypto_bitcoin.transactions`
WHERE
  DATE(block_timestamp) < '2022-10-01'

CODE

dogecoin

SELECT
  *
FROM
  `bigquery-public-data.crypto_dogecoin.transactions`
WHERE
  DATE(block_timestamp) < '2022-10-01'

CODE