Skip to main content

Information Schema

Overview​

This document describes the Information Schema file information_schema.csv, a crucial component for defining the metadata of your source systems. This metadata is essential for ensuring that all source columns referenced within your Data Vault model are correctly identified and available in the corresponding source database.

Ensure that following mandatory columns are included in the file:

  • TABLE_SCHEMA: The schema where the table resides.
  • TABLE_NAME: The name of the table.
  • COLUMN_NAME: The name of the column.
  • ORDINAL_POSITION: The position of the column within its table.
  • DATA_TYPE: The data type of the column (e.g., NUMBER, TEXT, TIMESTAMP).

Snowflake​

For Snowflake users, you can easily extract the necessary information schema details using the following SQL query.

USE SCHEMA <DATABASE_NAME>.<TABLE_SCHEMA>;

SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_SCHEMA = '<TABLE_SCHEMA>' 
 ORDER BY TABLE_NAME, ORDINAL_POSITION ;
tip

Automate the schema extraction via Makefile