Accessing an Unfamiliar Database | Dataford Interview Questions

You were given a task to collect data from an unfamiliar database. How would you approach identifying and accessing it?

Write a PostgreSQL query against the provided catalog snapshot to identify accessible base tables in the public schema and list their columns in ordinal order. Exclude inaccessible tables and views.

Output

One row per accessible base table
Columns: table_schema, table_name, and column_names
Sort by table_name ascending

Schema

catalog_tables

Column	Type	Description
table_schema	VARCHAR(63)	Schema containing the database object
table_name	VARCHAR(63)	Database object name
table_type	VARCHAR(20)	Object type such as BASE TABLE or VIEW
is_accessible	BOOLEAN	Whether the current analyst can access the object

catalog_columns

Column	Type	Description
table_schema	VARCHAR(63)	Schema containing the referenced table
table_name	VARCHAR(63)	Referenced table name
ordinal_position	INT	One-based position of the column
column_name	VARCHAR(63)	Column name
data_type	VARCHAR(40)	PostgreSQL data type

Your question is Accessing an Unfamiliar Database. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem