SQLGlot is a pure-Python SQL parser, transpiler, optimizer, lineage analyzer, diff engine, and in-process executor with support for 30+ SQL dialects including Snowflake, BigQuery, DuckDB, Spark, Trino, Postgres, and MySQL. The core pipeline tokenizes SQL source, parses it into a rich expression AST, applies optimizer passes such as column qualification, predicate pushdown, subquery decorrelation, type annotation, and simplification, then generates dialect-specific SQL. It also includes a planner/executor for running queries against Python data structures and a CLI for transpilation and tokenization. Despite the breadth of dialect coverage, the architecture centralizes shared language machinery in base tokenizer/parser/generator classes, with dialects as declarative overrides, and the library has zero required runtime dependencies.
sqlit is a Terminal User Interface (TUI) for SQL databases, inspired by lazygit. It provides a keyboard-driven interface for connecting to, querying, and managing multiple database types (including SQL Server, PostgreSQL, MySQL, SQLite, Oracle, and 20+ others). The application features a modular architecture with domains for connections, query execution, exploration, results, and shell, using a plugin-like provider system for database adapters. Built on the Textual framework, it offers Vim-style editing, autocomplete, query history, result filtering, SSH tunneling, and cloud provider integration. The codebase demonstrates strong engineering practices with reusable patterns such as a provider plugin architecture, mixin-based UI composition, protocol-driven interfaces, and secure credential management.
This repository implements a Model Context Protocol (MCP) server that exposes DuckDB and MotherDuck databases to AI assistants and IDEs. The server provides five MCP tools: execute_query for running SQL queries, list_databases/list_tables/list_columns for catalog exploration, and switch_database_connection for changing the active database at runtime. It supports multiple database sources including in-memory DuckDB, local .duckdb files, S3-hosted databases, and MotherDuck cloud databases. The server can run in read-only or read-write mode, with additional security features like SaaS mode for MotherDuck that restricts local filesystem access. The codebase is well-architected with clear separation of concerns: a DatabaseClient class handles all connection management and query execution, tool implementations are pure functions living in a dedicated subpackage, and server configuration is centralized in configs.py. The use of FastMCP for the MCP protocol provides a solid foundation. The repository includes a comprehensive test suite of over 30 E2E tests covering local files, in-memory databases, MotherDuck, S3, read-only mode, concurrent access, timeouts, result limits, type serialization, catalog tools, and HTTP transport modes. CI/CD pipelines automatically run tests, publish to PyPI and the MCP registry, and create GitHub releases with MCPB bundles. The server is production-ready with features like lazy connection initialization, query timeout via threading, automatic result truncation based on row and character limits, and support for ephemeral connections that avoid file locks. The code handles edge cases such as MotherDuck read-scaling token validation, S3 credential chain support, and proper redaction of sensitive tokens in switch database responses. While the architecture is solid, most components are tightly coupled to the DatabaseClient interface, limiting direct reusability outside this project. The instructions.py module is the most easily extractable component, providing a template for generating dynamic MCP server instructions based on configuration.
Homer is a production-grade, all-in-one HEP (Homer Encapsulation Protocol) capture and API server for SIP, RTP, RTCP, OTLP, and log data. It powers Homer 11.x data lake with DuckDB, Parquet, and Apache Arrow. The system ingests packets over UDP, TCP, TLS, HTTP, and WebSocket, decodes them using a custom zero-copy SIP parser, and stores them in a columnar DuckLake (DuckDB-backed) storage with SQLite catalog, supporting S3 and Azure backends. It provides a REST API (Echo) for search, dashboards, alerts, and configuration, an Arrow FlightSQL server for Grafana integration, and a React/TypeScript dashboard with drag-and-drop widgets, call flow visualization, and even browser-based games (chess, Doom). Architecture is modular with four main components: Ingest (transport servers), Storage (DuckLake manager with sharded writers, compactor, tiered mover), Node (FlightSQL server, catalog metadata), and Coordinator (REST API, auth, Lua correlation engine, LLM integration). Data flows from HEP/OTLP/Line Protocol receivers through decoders into DuckLake, then served via FlightSQL or REST. External dependencies include DuckDB, S3/Azure SDKs, Lua (golua), OTLP protobuf, and InfluxDB Line Protocol parser. The codebase demonstrates strong engineering practices: object pools for hot-path allocations, idempotent stop patterns, retry with backoff for catalog operations, and a Lua sandbox for scripting. However, it also carries some feature creep (games, LLM) and technical debt (dual SIP parsers, SQL injection risks via string interpolation in some services, and a few commented-out tests). Overall, it is a well-maintained, high-quality observability platform for VoIP and telemetry data.
This repository provides a SQLAlchemy dialect for DuckDB, enabling DuckDB to be used as a database backend within SQLAlchemy's ORM and Core APIs. It maps DuckDB's type system to SQLAlchemy types, handles connection management (including in-memory and file-based databases), supports reflection (schema introspection), and integrates with DuckDB extensions. The dialect is built on top of the PostgreSQL psycopg2 dialect, customizing it for DuckDB's capabilities and limitations. The source code is well-structured, extensively tested, and actively maintained, with support for DuckDB versions 0.5.0 through 1.2.2 and SQLAlchemy 1.3 through 2.0.35.
Lumen is an open-source agent framework for chatting with data and building interactive dashboards, built on the HoloViz ecosystem (Panel, hvPlot, Bokeh). It provides a dual interface: a YAML-based declarative dashboard specification system for non-AI use cases, and an AI-powered natural language interface that generates SQL queries and Vega-Lite visualizations from user prompts. The architecture is modular, with clear separation between data sources (files, SQL databases, REST APIs, xarray), data pipelines (filters, transforms, SQL rewrites), views (hvPlot, Vega-Lite, DeckGL, tables), and an AI agent layer (Planner, Coordinator, specialized agents for SQL, visualization, validation, and source ingestion). The AI system supports multiple LLM providers (OpenAI, Anthropic, Google, Ollama, etc.), vector stores for RAG (Numpy, DuckDB, ChromaDB), and a tool system for external API integration. The codebase demonstrates high craftsmanship with reusable patterns like spec-driven component creation (MultiTypeComponent), composable SQL transforms, and a consistent agent/tool abstraction. While primarily an application framework, it includes data pipeline components that could be extracted for data engineering use. The project is actively maintained with extensive test coverage, though its large optional dependency surface and rapid feature velocity introduce some maintenance risk.
@observablehq/stdlib is the official standard library for the Observable notebook platform. It provides a comprehensive set of utilities for building interactive data visualizations and applications directly in the browser. The library covers everything from DOM element creation (HTML/SVG/Markdown/LaTeX rendering), file access (FileAttachments with CSV, JSON, SQLite, Excel, Arrow, Parquet support), interactive inputs and reactive programming via generators (observe, queue, range), promise utilities (delay, tick, when), and database clients (SQLite via sql.js, DuckDB via WebAssembly). It is architecturally a monolithic client-side library that exports a main `Library` class, composed from small, single-purpose modules under `src/`. The primary entry point is `src/index.js` (package module) which re-exports all public symbols. The library is consumed primarily by the Observable runtime, but it can also be used standalone via the `Library` constructor. External dependencies are minimal (d3-array, d3-dsv, d3-require); heavy parts like DuckDB and Plot load dynamically from a CDN at runtime. The codebase demonstrates high craftsmanship in its generator implementation (push-to-pull adaptation) and schema inference system for tabular data. However, some modules are tightly coupled to the Observable operation model (e.g., `table.js`). Overall, it is a well-structured, extensively tested library that serves a specific ecosystem but contains several reusable patterns.
fakesnow is a Python library that provides a local, in-memory fake implementation of the Snowflake database connector. It uses DuckDB as the underlying SQL engine and sqlglot to transpile Snowflake SQL dialect into DuckDB-compatible queries. The library monkey-patches `snowflake.connector.connect` and `snowflake.connector.pandas_tools.write_pandas`, enabling developers to run Snowflake-dependent code entirely locally without a real Snowflake account. It also includes an optional HTTP server that mimics Snowflake's REST API, allowing non-Python clients (JDBC, Go, .NET) to connect. The project is primarily a test double and development tool, with a strong emphasis on behavior equivalence to real Snowflake. It supports a wide range of Snowflake SQL features, including DDL, DML, COPY INTO, MERGE, semi-structured data (JSON), sequences, stages, file formats, and session variables. The codebase is well-structured with a modular transform pipeline, extensive tests, and CI/CD pipelines for release. However, some features are partially implemented or marked as not yet supported, and the project is tightly coupled to evolving dependencies (DuckDB, sqlglot) which may cause drift.
Frappe Insights is a full-stack business intelligence application enabling users to connect to data sources, build queries visually or via SQL, create visualizations, and assemble dashboards and workbooks. The decoupled architecture features a Vue.js 3 frontend communicating with a Python/Frappe backend through REST APIs. Key capabilities include a multi-database query builder using Ibis and SQLGlot, ECharts-powered visualizations, role-based access control, and DuckDB-based data warehousing for caching imported tables. The frontend leverages Vue composition API for reusable logic, while the backend uses Frappe's DocType system for extensibility. The project demonstrates solid engineering practices with modular API design, reusable components, and abstraction layers, though complexity arises from supporting numerous database types and deep Frappe integration.
pg_mooncake is a Postgres extension that adds a custom table access method ('mooncake') backed by Apache Iceberg, enabling real-time analytics on columnar data stored in Iceberg tables. It integrates DuckDB (via pg_duckdb) as the query engine for fast analytics and provides a background worker (moonlink) that streams changes from regular Postgres heap tables to Iceberg tables via logical replication, ensuring sub-second data freshness. The extension exposes SQL procedures to create columnstore mirrors of Postgres tables, manage snapshots, load files, and optimize storage. It is built using the pgrx framework for Rust-based Postgres extensions and supports Postgres versions 14-18. The architecture is a hybrid of a shared library loaded into the Postgres backend and a separate background worker process. The codebase is well-structured and actively developed, though many table access method callbacks are still stubs, indicating it is in an early but functional stage.
EasyXT is a comprehensive quantitative trading toolkit for Chinese A-share markets, built on top of the Xuntou QMT broker platform. It provides a modular ecosystem that includes data management (multi-source with automatic fallback via DuckDB, Tushare, BaoStock, QMT DAT files, etc.), factor research (Alpha101/Alpha191 engines, IC/IR analysis, layered backtesting, Fama-French factors), backtesting engines (vectorized and event-driven with T+1 enforcement), live trading capabilities (auto-login, signal bridges, real-time quote providers, WebSocket push, monitoring), and multiple strategy implementations (grid trading, small-cap, convertible bonds, Xueqiu portfolio following). The system also features a PyQt5 desktop GUI and a Streamlit web app for factor analysis and strategy configuration. While primarily an application framework for executing trading strategies, it contains significant data research components for alpha factor development and performance analysis, making it a hybrid tool with a focus on production deployment. The codebase is large and actively maintained, with well-designed abstractions like data source adapters, cache backends, and a config manager, but also exhibits some duplication, hardcoded paths, and mixed Chinese/English naming that may hinder portability.