Exporting Payments Data

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

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
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.
Create Table Schema: Once the above step is done, you need to create the table schema on Redshift.
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

Aspect	Details
Data update schedule	6-hour frequency up to 7 days
Data retention on S3 folder	7 days
Type of data exposed	Payments as per schema
Data storage location	us-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.

About Vaultera

Use Cases

Explore Vaultera

Checklist for Production

Essentials

Integration Steps

Prerequisites

Steps

File Format and Path Specifications

Data Update Frequency & Retention

Auto Ingestion Using Redshift

Table Creation/Schema

Ingesting Data from S3

​Integration Steps

​Prerequisites

​Steps

​File Format and Path Specifications

​Data Update Frequency & Retention

​Auto Ingestion Using Redshift

​Table Creation/Schema

​Ingesting Data from S3

Integration Steps

Prerequisites

Steps

File Format and Path Specifications

Data Update Frequency & Retention

Auto Ingestion Using Redshift

Table Creation/Schema

Ingesting Data from S3