Vertica is a SQL analytics database built to manage rapidly growing volumes of data in Data Warehouse. Many times CSV files, received from internal upstream or external vendor system, required to be cleansed, consumed, integrated, and reported in Data Warehouse. Data integration complexity increase to a greater extent if an incoming CSV file consists of dissimilar patterns of record and header layout.

This white paper will show SQL only practical solution approach for consumption and integration of CSV file with similar data layout in the Vertica analytics database without using user-defined function/shell scrips/programming script etc.

What will you learn in this course?

• Business scenario
• Header row specification
  • Header row in CSV format
  • Header row in tabular format
• Data row specification
  • Standard data row in CSV format
  • Detail data row in CSV format
  • Data row in tabular format
• Sample data files
• Business requirement
  • Load above data files merging header row information in data rows and store in a raw table in integration schema
  • Load final integration table with clean customer data - update existing and insert new records
• Prerequisites
  • Copy files in the database server folder
  • Create schema in the database
  • Create staging and integration tables
• Load and transform staging raw table
  • Load file data
  • Remove null, double quotes, non-breaking space, and folder name from file path with an empty string
  • Split fields based on comma delimiter and trim
  • Copy standard header row to standard data row based on file name join
  • Copy detail header row to detail data row based on file name join
  • Delete header rows
  • Preview of loaded table data
• Load integration raw table
  • Preview of loaded table data
• Load integration master table
  • Update existing records based on the key column
  • Insert new records based on the new key column
  • Preview of loaded table data
• Scope of enhancement adding reject logic and reject reprocessing