> ## Documentation Index > Fetch the complete documentation index at: https://docs.switch.vaultera.co/llms.txt > Use this file to discover all available pages before exploring further. # 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](https://aws.amazon.com/redshift/). ### 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://////.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 ```sql theme={"system"} 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 ```sql theme={"system"} CREATE TEMP TABLE payments_stage (LIKE payments); COPY payments_stage FROM 's3://///payments' CREDENTIALS 'aws_iam_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`.