JH Wires

Pipelined Data Sets > JH Wires

Introduction

The goal of this pipeline is to make the JH Wires dataset available in Google Big Query where it can be queried using the SQL interface for business purposes and to drive downstream business processes.

Pipelines

The JH Wires ISO pipeline utilizes BigQuery External Connections in the Jack Henry Data Hub project, to the Publisher Spanner database in their project. Federated Queries are used with these External Connections to pull data from the Spanner Database into the BigQuery dataset / tables.

Querying the data

Partition Information for Tables in Dataset `jh_wires_us`

Table Name	Partition Column	Partition Type
base_wires_fct_cdd	_PARTITIONTIME	MONTH
message_sanction_status_fct_cdd	_PARTITIONTIME	MONTH
messages_fct_cdd	_PARTITIONTIME	MONTH
pacs_004_messages_fct_cdd	_PARTITIONTIME	MONTH
pacs_008_messages_fct_cdd	_PARTITIONTIME	MONTH
pacs_009_messages_fct_cdd	_PARTITIONTIME	MONTH
wire_step_status_fct_cdd	_PARTITIONTIME	MONTH

Example Query #1: PACS008- Customer Credit Transfer

SELECT
  pm.MessageId,
  pm.InstructionId,
  pm.InstitutionId,
  pm.CreditorAgentMemberId,
  pm.ChargeBearer,
  pm.Amount,
  pm.CurrencyType,
  pm.PaymentProprietaryCode,
  pm.SettlementCode,
  pm.InstructionIdentification,
  pm.EndToEndIdentification,
  pm.InstructedAgentMemberId,
  pm.MarketPracticeIdentification,
  pm.SettlementMethod,
  pm.InstructedAgentClearingCode,
  pm.InstructingAgentClearingCode,
  pm.UnstructuredRemittanceInformation,
  pm.IntermediaryAgentMemberId,
  pm.InstructingAgentMemberId,
  pm.DebtorAgentMemberId,
  pm.CreditorAgentClearingCode,
  pm.WireDefinition,
  pm.CreatedOn,
  pm.CreditorFI,
  pm.IntermediaryFI,
  pm.DebtorFI,
  pm.DebtorAgentClearingCode,
  pm.IntermediaryAgentClearingCode,
  pm.MessageIdentifier,
  pm.BusinessMessageIdentifier
FROM `pacs_008_messages` pm
JOIN (
  SELECT MessageId, MAX(CreatedOn) AS LatestCreatedOn
  FROM `pacs_008_messages`
  WHERE CreatedOn >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  GROUP BY MessageId
) latest ON pm.MessageId = latest.MessageId AND pm.CreatedOn = latest.LatestCreatedOn;

Example Query #2: PACS004- Return Wire

SELECT
  pm.MessageId,
  pm.InstructionId,
  pm.InstitutionId,
  pm.CreditorAgentMemberId,
  pm.Amount,
  pm.CurrencyType,
  pm.PaymentProprietaryCode,
  pm.SettlementCode,
  pm.InstructionIdentification,
  pm.EndToEndIdentification,
  pm.InstructedAgentMemberId,
  pm.MarketPracticeIdentification,
  pm.SettlementMethod,
  pm.InstructedAgentClearingCode,
  pm.InstructingAgentClearingCode,
  pm.UnstructuredRemittanceInformation,
  pm.IntermediaryAgentMemberId,
  pm.InstructingAgentMemberId,
  pm.DebtorAgentMemberId,
  pm.DebtorAgentClearingCode,
  pm.CreditorAgentClearingCode,
  pm.IntermediaryAgentClearingCode,
  pm.WireDefinition,
  pm.CreatedOn,
  pm.CreditorFI,
  pm.IntermediaryFI,
  pm.DebtorFI,
  pm.OriginalMessageIdentifier,
  pm.OriginalWireDefinition,
  pm.OriginalSentOn,
  pm.ReturnReasonCode,
  pm.ReturnReasonAdditionalInfo,
  pm.MessageIdentifier,
  pm.BusinessMessageIdentifier
FROM `pacs_004_messages` pm
JOIN (
  SELECT MessageId, MAX(CreatedOn) AS LatestCreatedOn
  FROM `pacs_004_messages`
  WHERE CreatedOn >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  GROUP BY MessageId
) latest ON pm.MessageId = latest.MessageId AND pm.CreatedOn = latest.LatestCreatedOn;

Example Query #3: PACS009- FI Credit transfer

SELECT
  pm.MessageId,
  pm.InstitutionId,
  pm.InstructionId,
  pm.DebtorAgentMemberId,
  pm.DebtorAgentClearingCode,
  pm.MarketPracticeIdentification,
  pm.Amount,
  pm.CurrencyType,
  pm.PaymentProprietaryCode,
  pm.SettlementMethod,
  pm.SettlementCode,
  pm.InstructionIdentification,
  pm.EndToEndIdentification,
  pm.InstructedAgentMemberId,
  pm.InstructedAgentClearingCode,
  pm.InstructingAgentMemberId,
  pm.InstructingAgentClearingCode,
  pm.CreditorAgentMemberId,
  pm.CreditorAgentClearingCode,
  pm.CreatedOn,
  pm.WireDefinition,
  pm.DebtorFI,
  pm.MessageIdentifier,
  pm.BusinessMessageIdentifier
FROM `pacs_009_messages` pm
JOIN (
  SELECT MessageId, MAX(CreatedOn) AS LatestCreatedOn
  FROM `pacs_009_messages`
  WHERE CreatedOn >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  GROUP BY MessageId
) latest ON pm.MessageId = latest.MessageId AND pm.CreatedOn = latest.LatestCreatedOn;

Topics in this section

Have a Question?

Have a how-to question? Seeing a weird error? Get help on StackOverflow.

Register for the Developer Office Hours where we answer technical Q&A from the audience.

Did this page help you?

Last updated Tue Oct 7 2025