Create a simple data catalog with Sort, Postgres, and Markdown
Use markdown to describe your database schema, columns, and how to query your data.
Organizations need a well-organized data catalog to efficiently manage and discover their data assets. Traditional data catalogs can be complex and expensive to set up, but with the right tools, you can create a streamlined, effective catalog using just Sort, Postgres, and Markdown. This guide will walk you through setting up a data catalog using these tools, making it easy to organize, document, and query your data—all with a focus on simplicity.
What You'll Need
Sort - A tool that allows you to use Markdown to describe database schemas and queries in a human-readable format.
Postgres - A robust, open-source relational database to store and manage your data.
Markdown - A lightweight markup language for adding descriptive, readable documentation.
Step 1: Setting Up Postgres
We recommend creating a free account on Neon. It’s a simple way to create a hosted Postgres database, without the hassle of managing the infrastructure.
Define Tables: Now, define the tables you want to catalog. For example, let’s say you’re cataloging a customer data schema:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
signup_date DATE,
active BOOLEAN
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE,
total_amount DECIMAL
);
With these tables created, you now have a basic database structure for customer and order data.
Step 2: Introducing Sort for Documentation
Sort allows you to build a database catalog using Markdown descriptions for each table, column, and query in your database. This catalog can then be used to query and explore data easily.
Add your Postgres database connection to Sort: Visit Sort → Settings → Connections → Add
Visit the Database Overview: Sort will automatically import your schema when the database connection is added. After your schema has imported, visit your organization overview to see your databases. Click on the Overview button of your Neon database.
Create Markdown Documentation: Click the Edit button to define your database description using Markdown. We’ve added markdown for the customers and orders tables below.
# Database Overview: Customer and Order Data Catalog
## Customers
### Table Description
This table contains information about customers, including their unique ID, name, email, signup date, and active status.
### Columns
- **customer_id**: Unique identifier for each customer.
- **name**: Full name of the customer.
- **email**: Contact email address for the customer.
- **signup_date**: Date when the customer signed up.
- **active**: Boolean indicating if the customer is currently active.
### Sample Query
```sql
SELECT * FROM customers WHERE active = TRUE;
```
## Orders
### Table Description
This table tracks customer orders, including the order date and total amount.
### Columns
- **order_id**: Unique identifier for each order.
- **customer_id**: References the customer who made the order.
- **order_date**: The date the order was placed.
- **total_amount**: Total amount of the order.
### Sample Query
```sql
SELECT * FROM orders WHERE total_amount > 100;
```
Voila!
Step 3: Querying the Database with Sort
Once your schema is documented, you can query the data directly in Sort, using the Markdown file as a reference for how to write and interpret queries. With Sort’s Markdown interface, you’ll get a seamless experience without needing to switch back and forth between documentation and your query environment.
Run Queries from the Markdown Documentation: Since the documentation includes example queries, you can copy and paste these into Sort’s Data Explorer.
Step 4: Making It All Readable and Accessible
Using Sort’s Markdown-first approach gives you a catalog that is both human-readable and connected to your database. Team members, regardless of SQL expertise, can now:
Navigate the catalog: Understand tables, columns, and data relationships through Markdown descriptions.
Execute Queries: Run the sample queries directly from Sort to get data insights quickly.
Collaborate: Keep everyone on the same page with clear documentation and shared queries.
Conclusion
Creating a data catalog doesn’t have to be complicated or costly. By using Sort with Postgres and Markdown, you can build a flexible, user-friendly data catalog that supports documentation, query execution, and easy collaboration. With this setup, your team can find, understand, and use your data effectively without extensive setup or technical overhead.