Building a Data Profiler

This command-line data profiler is a small side project I've been working on to help get a quick understanding of an unfamiliar dataset. It currently supports JSON, but I'm building the application around a pipeline that will eventually accommodate other file formats.

The current workflow is roughly: 1. Parse file into an abstract syntax tree (AST); 2. Explode the AST into a tabular format; 3. Analyze the output; 4. Output a report in html format.

For JSON, the file is parsed into an AST that preserves the structure of the objects, arrays, values, paths and their relationships in the original file.

The AST is exploded into tabular format and loaded into a DuckDB in-memory database where SQL is used to perform analytical work. The profiling logic is separate from the JSON parsing mechanics.

The analytical layer uses SQL queries with DuckDB. Once the data is transformed into a representation suitable for analysis, SQL can be used to answer questions about the file structure like field occurrence, object depth, array cardinality and structural variation.

The results of these analytical queries are passed to a Report class which actually runs these queries against DuckDB and assembles the information needed for the report. The report is then rendered as HTML using Jinja templates, which are intentionally kept as a dumb presentation layer. All business logic is encapsulated in a Report class.

A cli ties the application together and provides an interface for profiling a file without having to write a python script.

One of the more interesting problems so far has been understanding JSON arrays. A path such as $.results[*] describes the structure of an array, while an instance path such as $.results[12] identifies a specific element. Keeping these concepts separate makes it possible to analyze both the overall shape of an array and the individual objects in it. This helps the profiler answer question like:

How many objects occur at a particular path?
What keys are present in those objects?
What percentage of objects contain each key?
How many elements does an array contain?
How homogeneous are objects within an array?

The project is still evolving, but the architecture is starting to settle on clear responsibilities: Parsers for each file format. Analysis is performed with SQL against DuckDB. A Report class assembles results. Jinja presents results. 5. A cli ties everything together.

Exploring Data Structure Design Tradeoffs with Various Solutions for Two Sum III

Leetcode #170. We are tasked with designing a data structure that accepts a stream of integers and checks if it has a pair of integers that sum up to a particular value. To solve this problem, we need to handle tradeoffs between read and write operations: 1. find(value) and

Deploying Server with GitHub Actions and Fly.io

I am setting up a CICD pipeline in continuation of my Dallas Police Active Calls project. Terraform infrastructure, the data pipeline and the application server all reside in the same project, but I want to use GitHub actions to keep the infrastructure and application deployments loosely coupled. CICD Workflow 1.

DSA: Find First and Last Position of Element in Sorted Array

A solution to Leetcode #34, Find First and Last Position of Element in Sorted Array Problem Statement: Given an array of integers nums sorted in non-decreasing order, find the starting and ending position of a given target value. If target is not found in the array, return [-1, -1]

SQL: User Purchase Platform

This SQL challenge comes from LeetCode. We need to find the total user counts and amount spent for each date in three mutually exclusive categories: 1. mobile; 2. desktop; 3. mobile and desktop. Approach We create a truth table of user and platform usage for each date to help segment

Read more

Exploring Data Structure Design Tradeoffs with Various Solutions for Two Sum III

Deploying Server with GitHub Actions and Fly.io

DSA: Find First and Last Position of Element in Sorted Array

SQL: User Purchase Platform