From OLTP to Analytics: ETL, Lake, Real-Time OLAP, Reverse ETL

From OLTP to Analytics: ETL, Lake, Real-Time OLAP, Reverse ETL A data-flow diagram generated by Archify. 01 / OLTP Sources 02 / Ingest 03 / Analytics Store 04 / Consumers 05 / Back to Ops Orders DB · PostgreSQL OLTP · 01 / OLTP Sources · system of record Orders DB PostgreSQL OLTP system of record External SaaS · CRM, payments · 01 / OLTP Sources · vendor API External SaaS CRM, payments vendor API Backend Svcs · user activity · 01 / OLTP Sources · event producers Backend Svcs user activity event producers ETL Jobs · extract, transform · 02 / Ingest · batch or CDC ETL Jobs extract, transform batch or CDC Connectors · Fivetran, Airbyte · 02 / Ingest · SaaS pull Connectors Fivetran, Airbyte SaaS pull Event Stream · Kafka log · 02 / Ingest · seconds latency Event Stream Kafka log seconds latency Warehouse · curated SQL schema · 03 / Analytics Store · read-only copy Warehouse curated SQL schema read-only copy Data Lake · raw Avro, Parquet · 03 / Analytics Store · no schema Data Lake raw Avro, Parquet no schema Real-Time OLAP · ClickHouse, Druid · 03 / Analytics Store · low latency Real-Time OLAP ClickHouse, Druid low latency BI Dashboards · Tableau, Looker · 04 / Consumers · ad-hoc SQL BI Dashboards Tableau, Looker ad-hoc SQL Data Science · Pandas, Spark · 04 / Consumers · ML features Data Science Pandas, Spark ML features Product UI · ranking, recs · 04 / Consumers · in-product Product UI ranking, recs in-product Prod Services · model serving · 05 / Back to Ops · reverse ETL Prod Services model serving reverse ETL dump/CDC extract transform then load ad-hoc SQL read-only API pull extract raw files load raw train data derived sets deployed reverse ETL app events append-only live ingest stream processing queries read-only Legend primary data async batch data store data flow