Multi Database Colaboration and Extract - Transform - Load with HTSQL
summary
HTSQL now carries ETL features that let data analysts compose navigational queries, test them incrementally, and then run an insert‑update merge that does update‑or‑insert by identity; a recent Autism Speaks migration produces roughly 30,000 lines of generated queries, reading from CSVs (temporarily in sqlite) and tunneling back to merge into Postgres; it succeeds in letting non‑developers perform repeatable, auditable conversions but remains unoptimized, limited in cross‑database date/scalar functions, and is mid‑refactor with copy‑into style loading and Postgres‑only extensions planned.
AI-written summary · about
chapters (24)
- 0:00Intro
- 1:26Verifiable Transformations
- 2:14Simple Request
- 3:40A "Dumb" Approach
- 9:26What is HTSQL?
- 11:47HTSQL Background
- 12:54Case Study: Autism Speaks
- 13:41Autism Speaks: Topology
- 15:40Simple Query
- 16:24Typical Navigation
- 17:08Occassional Re-Normalization
- 19:40Autism Speaks, Retrospective
- 21:39HTSQL Core Features
- 27:17Smart Aggregates
- 28:15Nested Aggregates
- 28:45Records & Lists
- 29:41Projections
- 30:45Sorting & Choosing
- 31:02Cross Products
- 32:03Macro Definitions
- 33:01Attachments or Ad-Hoc Joins
- 35:30Identity and Locator
- 37:38Insert (One Record)
- 39:32Update (One Record)
related talks
36mHow Can HTAP Databases Simplify Your Data Infrastructure?
29mHTAP vs OLTP/OLAP
01h 04mExtract Transform Load using mETL
36mBeyond dbt: Modern SQL Transformation and Lineage with SQLGlot and SQLMesh
32mPromoting a data driven culture in a world of microservices
47mSQL Utility Belt
55mThe ELT pattern in action with DBT and SQLMesh
15mNext Gen ETL Generators