What is the importance of system catalogue tables/views in Redshift? What are some important system catalogue tables/views used in Redshift?

There are two types of system tables, which are as follows: STL and STV tables. STL tables are generated from logs that have been persisted to disk to provide a history of the system. STV tables are virtual tables that contain snapshots of the current system data. They are based on transient in-memory data and are not persisted to disk-based logs or regular tables.
System views that contain any reference to a transient STV table are called SVV views. Views containing only references to STL tables are called SVL views.

Some important system tables and views are as follows:

  • STV_INFLIGHT: View currently running queries
  • STL_QUERY: View historical queries that ran on the database
  • STL_LOAD_ERRORS: View COPY command errors