Export your payments data to Redshift from vaultera switch Exporting your payments data to Amazon Redshift enhances analytics by leveraging Redshift’s high-performance query capabilities. This allows for efficient data analysis, reporting, and business intelligence, thereby deriving valuable insights.

​
Integration Steps

​
Prerequisites

You need to have an AWS account with Redshift enabled. More details can be found here.

​
Steps

  1. Create IAM Role: You are required to create a new IAM role for Redshift use and provide vaultera switch with the corresponding role ARN. This IAM role must be configured with S3 read permissions. Example image of an IAM role created
  2. Share ARN: After sharing the ARN with vaultera switch, we will share the S3 bucket & path that is to be synced for data along with providing access to the IAM role from which you will be able to get files from S3.
  3. Create Table Schema: Once the above step is done, you need to create the table schema on Redshift.
  4. Data Ingestion: Post which you can proceed with either:
    • Handle the ingestion & post-processing of data using scripts
    • OR
    • Auto-ingestion using Redshift

​
File Format and Path Specifications

  • The files will be plain CSV files with the 1st row being a header
  • The file path would be: s3://<bucket>/<merchant_id>/<version>/<payments>/<date>.csv
  • There will be one CSV file corresponding to each day up to 7 days
  • Updates to the payments data will be in-place
  • Changes to file formats, content, or similar changes would modify the version in the above path and would be communicated

​
Data Update Frequency & Retention

AspectDetails
Data update schedule6-hour frequency up to 7 days
Data retention on S3 folder7 days
Type of data exposedPayments as per schema
Data storage locationus-east-1

​
Auto Ingestion Using Redshift

Redshift supports ingesting CSV data directly from S3 files which we’ll rely on. The ingestion to Redshift would happen via a COPY job. This can be automated via the following options:
  • You can use the auto copy job if running a preview cluster
  • Or the more mainstream lambda loader

​
Table Creation/Schema

CREATE TABLE payments (
  payment_id VARCHAR(64),
  attempt_id VARCHAR(64),
  status TEXT,
  amount INTEGER,
  currency VARCHAR(10),
  amount_to_capture INTEGER,
  customer_id VARCHAR(64),
  created_at TIMESTAMP,
  order_details VARCHAR(255),
  connector VARCHAR(255),
  error_message VARCHAR(255),
  connector_transaction_id VARCHAR(255),
  capture_method VARCHAR(255),
  authentication_type VARCHAR(255),
  mandate_id VARCHAR(64),
  payment_method VARCHAR(255),
  payment_method_type TEXT,
  metadata TEXT,
  setup_future_usage TEXT,
  statement_descriptor_name TEXT,
  description TEXT,
  off_session TEXT,
  business_country TEXT,
  business_label TEXT,
  business_sub_label TEXT,
  allowed_payment_method_types TEXT
);

​
Ingesting Data from S3

CREATE TEMP TABLE payments_stage (LIKE payments);

COPY payments_stage FROM 's3://<BUCKET_NAME>/<MERCHANT_ID>/<VERSION>/payments' 
CREDENTIALS 'aws_iam_role=<ARN_ROLE>'
IGNOREHEADER 1
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
CSV;

MERGE INTO payments USING payments_stage ON payments.payment_id = payments_stage.payment_id
WHEN MATCHED THEN UPDATE SET
  payment_id = payments_stage.payment_id,
  attempt_id = payments_stage.attempt_id,
  status = payments_stage.status,
  amount = payments_stage.amount,
  currency = payments_stage.currency,
  amount_to_capture = payments_stage.amount_to_capture,
  customer_id = payments_stage.customer_id,
  created_at = payments_stage.created_at,
  order_details = payments_stage.order_details,
  connector = payments_stage.connector,
  error_message = payments_stage.error_message,
  connector_transaction_id = payments_stage.connector_transaction_id,
  capture_method = payments_stage.capture_method,
  authentication_type = payments_stage.authentication_type,
  mandate_id = payments_stage.mandate_id,
  payment_method = payments_stage.payment_method,
  payment_method_type = payments_stage.payment_method_type,
  metadata = payments_stage.metadata,
  setup_future_usage = payments_stage.setup_future_usage,
  statement_descriptor_name = payments_stage.statement_descriptor_name,
  description = payments_stage.description,
  off_session = payments_stage.off_session,
  business_country = payments_stage.business_country,
  business_label = payments_stage.business_label,
  business_sub_label = payments_stage.business_sub_label,
  allowed_payment_method_types = payments_stage.allowed_payment_method_types
WHEN NOT MATCHED THEN INSERT VALUES (
  payments_stage.payment_id,
  payments_stage.attempt_id,
  payments_stage.status,
  payments_stage.amount,
  payments_stage.currency,
  payments_stage.amount_to_capture,
  payments_stage.customer_id,
  payments_stage.created_at,
  payments_stage.order_details,
  payments_stage.connector,
  payments_stage.error_message,
  payments_stage.connector_transaction_id,
  payments_stage.capture_method,
  payments_stage.authentication_type,
  payments_stage.mandate_id,
  payments_stage.payment_method,
  payments_stage.payment_method_type,
  payments_stage.metadata,
  payments_stage.setup_future_usage,
  payments_stage.statement_descriptor_name,
  payments_stage.description,
  payments_stage.off_session,
  payments_stage.business_country,
  payments_stage.business_label,
  payments_stage.business_sub_label,
  payments_stage.allowed_payment_method_types
);

DROP TABLE payments_stage;
The above query creates a temporary table to load all the CSV data and then merges this with the main table while deduplicating based on payment_id.