Dl/Overview - Revision history

Stonezhong: /* ETL */

2025-11-25T21:02:24Z

ETL

← Older revision		Revision as of 21:02, 25 November 2025
Line 63:		Line 63:
	graph TD		graph TD
	Scheduler[Apache Airflow/Scheduler]		Scheduler[Apache Airflow/Scheduler]
	ETLE[ETL Executor~~&lt~~;Airflow Task~~&gt~~;]		ETLE[ETL Executor#40;Airflow Task#41;]
	LC[Local ETL Code]		LC[Local ETL Code]
	ER[ETL Code Repo]		ER[ETL Code Repo]
	JDBC[JDBC~~&lt~~;Thrift Server~~&gt~~;]		JDBC[JDBC#40;Thrift Server#41;]
	User[User~~&lt~~;Data Engineer~~&gt~~;]		User[User#40;Data Engineer#41;]
	Spark[Apache Spark]		Spark[Apache Spark]
	Scheduler --2: trigger--> ETLE		Scheduler --2: trigger--> ETLE

Stonezhong: /* Data Tiers */

2025-11-25T19:02:56Z

Data Tiers

← Older revision		Revision as of 19:02, 25 November 2025
Line 41:		Line 41:

	* dimention tables and fact tables that forms star schema		* dimention tables and fact tables that forms star schema
			* a star schema is designed in such a way that it can answer any question about a business area.
	<hr /><br />		<hr /><br />

Line 47:		Line 48:

	* Various query result for specific business questions materized in tables		* Various query result for specific business questions materized in tables
			** Queries are generated from star schema from gold tier
	* Tables may be replicated to a RDBMS for BI tool to access (sometime you can expose them directly, e.g. Spark Thrift Server)		* Tables may be replicated to a RDBMS for BI tool to access (sometime you can expose them directly, e.g. Spark Thrift Server)

Stonezhong: /* Data Tiers */

2025-11-25T19:00:46Z

Data Tiers

← Older revision		Revision as of 19:00, 25 November 2025
Line 2:		Line 2:
	= Data Tiers =		= Data Tiers =
	<div class="toccolours mw-collapsible mw-collapsed expandable">		<div class="toccolours mw-collapsible mw-collapsed expandable">
	<div class="mw-collapsible-preview">~~Using RDBMS~~</div>		<div class="mw-collapsible-preview">Data Tiers</div>
	<div class="mw-collapsible-content">		<div class="mw-collapsible-content">
	{\| class="wikitable grid mono section"		{\| class="wikitable grid mono section"

Stonezhong at 19:00, 25 November 2025

2025-11-25T19:00:03Z

← Older revision		Revision as of 19:00, 25 November 2025
Line 1:		Line 1:
	<p>[[dl/home\|Data Lake Knowledge Center]]</p>		<p>[[dl/home\|Data Lake Knowledge Center]]</p>
			= Data Tiers =
			<div class="toccolours mw-collapsible mw-collapsed expandable">
			<div class="mw-collapsible-preview">Using RDBMS</div>
			<div class="mw-collapsible-content">
			{\| class="wikitable grid mono section"
			\|-
			! Diagram
			! Description
			\|-
			\|
			{{#mermaid:
			graph TD
			BornzeTier[Bronze Tier]
			SilverTier[Silver Tier]
			GoldTier[Gold Tier]
			PlatniumTier[Platnium Tier]
			ExternalData[External Data]

			ExternalData --ingestion--> BornzeTier --ingestion--> SilverTier --ETL--> GoldTier --ETL--> PlatniumTier
			}}
			\|
			<big><b>Bronze Tier</b></big>:<br /><br />
			<b>The purpose for bronze tier is to store data downloaded from external world into data lake so we can use all sort of tools inside data lake to further process it</b>
			* raw data
			* no uniformed format, could be csv, JSON, AVRO, parquet, binary, anything
			* could even be unstructured
			* no data quality assurance
			<hr /><br />

			<big><b>Silver Tier</b></big>:<br /><br />
			<b>The purpose for bronze tier is to allow data ingestion application to sanitize data, verify the quality of the data</b>

			* Data quality is assurred
			* Data may not be normalized. One table may use UTC for a timestamp column while aother table may use timestamp without timezone. No stadnardlization for column name.
			* Data format is uniformed, usually it is stored as a format that is best fits the further ETL process, for example parquet.
			<hr /><br />

			<big><b>Gold Tier</b></big>:<br /><br />
			<b>Tables for star schema</b>

			* dimention tables and fact tables that forms star schema
			<hr /><br />

			<big><b>Platinum Tier</b></big>:<br /><br />
			<b>Various query result for specific business questions materized in tables</b>

			* Various query result for specific business questions materized in tables
			* Tables may be replicated to a RDBMS for BI tool to access (sometime you can expose them directly, e.g. Spark Thrift Server)

			\|}
			</div>
			</div>
			<p></p>

	= ETL =		= ETL =

Stonezhong: /* BI Connection */

2025-11-25T18:58:41Z

BI Connection

← Older revision		Revision as of 18:58, 25 November 2025
Line 77:		Line 77:
	<br />		<br />

	* BI Tool to access Gold Tier data and Platnium Tier data via JDBC interface exposed by ~~a MPP engine~~		* Gold Tier data and Platnium Tier data are replicated to RDBMS, such as Oracle DB
	** Why? A MPP Engine provide better interactive SQL query speed than Spark Thrift Server		* BI Tool to access Gold Tier data and Platnium Tier data via JDBC interface exposed by RDBMS
			* This pattern does not work for very large datalake since Gold Tier and Platnium Tier are too large to be replicated to RDBMS

	</div>		</div>
	</div>		</div>
	<p></p>		<p></p>

Stonezhong: /* BI Connection */

2025-11-25T18:56:52Z

BI Connection

@@ Line 49: / Line 49: @@
      PlatniumTier --> MPP
      MPP --> JDBC --> BI
 }}
 <br />

Stonezhong: /* BI Connection */

2025-11-25T18:55:31Z

BI Connection

← Older revision		Revision as of 18:55, 25 November 2025
Line 35:		Line 35:
	= BI Connection =		= BI Connection =
	<div class="toccolours mw-collapsible mw-collapsed expandable">		<div class="toccolours mw-collapsible mw-collapsed expandable">
	<div class="mw-collapsible-preview">~~ETL Flow~~</div>		<div class="mw-collapsible-preview">Using MPP Engine</div>
	<div class="mw-collapsible-content">		<div class="mw-collapsible-content">
	{{#mermaid:		{{#mermaid:

Stonezhong: /* BI Connection */

2025-11-25T18:54:09Z

BI Connection

← Older revision		Revision as of 18:54, 25 November 2025
Line 43:		Line 43:
	PlatniumTier[Platnium Tier]		PlatniumTier[Platnium Tier]
	end		end
			JDBC[JDBC Interface]
	MPP[MPP Engine #40;Starburst Trino#41;]		MPP[MPP Engine #40;Starburst Trino#41;]
	BI[BI Tool #40;Power BI#41;]		BI[BI Tool #40;Power BI#41;]
	GoldTier --> MPP		GoldTier --> MPP
	PlatniumTier --> MPP		PlatniumTier --> MPP
	MPP --> BI		MPP --> JDBC --> BI
	}}		}}
	<br />		<br />

Stonezhong: /* BI Connection */

2025-11-25T18:53:22Z

BI Connection

← Older revision		Revision as of 18:53, 25 November 2025
Line 39:		Line 39:
	{{#mermaid:		{{#mermaid:
	graph TD		graph TD
	~~Scheduler[Apache Airflow/Scheduler]~~		subgraph storage
	~~ETLE~~[~~ETL Executor<Airflow Task>~~]		GoldTier[Gold Tier]
	LC[~~Local ETL Code~~]		PlatniumTier[Platnium Tier]
	~~ER[ETL Code Repo]~~		end
	~~JDBC~~[~~JDBC&lt~~;~~Thrift Server&gt~~;]		MPP[MPP Engine #40;Starburst Trino#41;]
	~~User~~[~~User&lt~~;~~Data Engineer&gt~~;]		BI[BI Tool #40;Power BI#41;]
	~~Spark[Apache Spark]~~		GoldTier --> MPP
	~~Scheduler --2: trigger~~--> ~~ETLE~~		PlatniumTier --> MPP
	~~ETLE --3: git pull~~ -->LC		MPP --> BI
	~~ETLE --4: dbt--> JDBC~~
	~~JDBC --5:--> Spark~~
	~~ER --> LC~~
	~~User --1: git push~~-->ER
	}}		}}
	<br />		<br />

	* ~~1: User pushes ETL code into ETL Code Repo~~		* BI Tool to access Gold Tier data and Platnium Tier data via JDBC interface exposed by a MPP engine
	* 2: Airflow Scheduler trigger DAG (DAG is generated based on metadata)		** Why? A MPP Engine provide better interactive SQL query speed than Spark Thrift Server
	** The ETL job is a ~~task within an airflow DAG~~
	* ~~3: ETL executor pulls code from ETL Code Repo into loacl disk~~
	* ~~4: ETL executor uses dbt library to submit job to Apache~~ Spark ~~via JDBC interface (e.g. via~~ Thrift Server)
	* 5: Thrift Server take the SQL and pass it to Apache Spark to execute
	</div>		</div>
	</div>		</div>
	<p></p>		<p></p>

Stonezhong: /* ETL */

2025-11-25T18:43:44Z

ETL

@@ Line 2: / Line 2: @@
 = ETL =
 <div class="toccolours mw-collapsible mw-collapsed expandable">
 <div class="mw-collapsible-preview">ETL Flow</div>