
## Motivation of Designing Data Pipeline Framework

To improve and strongly go-live the data pipeline, besides apply best practices and pillar for Data Pipeline Native Solution, a design framework and pattern are robustly help us in:

- Follow typical data pipeline
- Organize pipeline and adopt market
- Refer the other people use besides yours

Its easy to follow if we will discuss the pattern and solution with SWAT and pros/cons analysis and aks ourself for initiative questions such as: when to use and when not to use.

## Choosing your data pipeline by

### 1. Ask Question

- Need historical data in output ?
  - Yes ? Replayable source ?
  - No ? Non-Replayable source ?
  - Size of data to be pulled
    - Large: Time raged
    - Small: Full Snapshot
    - Only past n period: Lookback
    - Streaming data: Streaming
  - Transformational Complexity
    - Standard: In-Transform
    - Depends on time of run or input value/MDM: Conditional flow
    - Multi teams: Disconnected pipeline/Follow fatten data pattern and post-transform
  - Is sink append only?
    - Yes: Non-overwritable sink
    - No: Overwritable sink
  - If Idempotent pipeline possible: Source overwrite sink

### 2. Source and Sink

Before designing data pipeline and perform magic in data movement, we have to understand where we are and where we will go to help us direct the proper direction.

- Source: data systems where provide input(s) to data pipeline
- Sink: data systems where retrieve output(s) from data pipeline

#### 2.1 Source replayability

Can we answer question **What did the data look like n period ago (n can be min/hour/day/months/years)** ?
To able to answer that question, the data source need to support a data journey from every state of data. For example, Event stream, web server logs, delta change in database likes create/update/delete (CDC), ect.

#### 2.2 Source Ordering

Does source system is event streaming or log-out event and push data into data pipeline in order? Especially streaming data. List of techniques are using to handle such as "backoff", "watermaking", "handling late event" need to be address when dealing with order events.

#### 2.3 Sink Overwritability

Overwrite is required to prevent duplication of data processing and makes data more controllable and avoid partial data when pipeline fails. The unique key is used for tracking and overwriting data in:

- Overwritable sinks:
  - Database table has primary key
  - Cloud Storage has unique run id/row_id
- Non-overwritable sinks: Kafka queue/Streaming queue, then we have to store immediately and post-process events

### 3. Data pipeline patterns

Before jumping into any specific or pattern of system design for data pipeline, remember **Every solution need to be under consideration (pros/cons)**, there are 3 questions referred from experts in data foundation:

1. Extraction: How the data in source systems will be ingested (pull/push) ?
2. Behavior: When an error occurs, how data pipeline will re-act to ? (self-healing/bypass/refill)
3. Structural: What is the structure and variety of processing layer of task/transformation in data pipeline (multi-hop routing)

Now, detailing what we are taking

#### 3.1 Extraction

##### 3.1.1 Time ranged/Delta

Data pipeline only pulls the data corresponding to a specific time frame like daily/hourly/...
**Notes**: we need to update sink/destination reasonably by Slowly Changing on table to capture current state of data.
- **Pros**
   - Fast data pulls, only necessary data
   - Parallelize running of pipeline from single source
- **Cons**
  - More complicated when building Slowly Changing and build UPSERTs/MERGE INTOs statement to update latest data
  - Rely on source system to support replayable where pipeline will capture the time frame and delta

##### 3.1.2 Snapshot

Data pipeline scan and pull entire data from the source, and we need a additional column named **run_id** (on Database or new folder in cloud storage system) that uniquely identifies each pipeline run. Later used for data versioning.

- **Pros**
  - Simple to build the data pipeline
  - Easy to track when issue happened
  - Data versioning for each run
  - Simplify table structure
- **Cons**
  - Need to pull data from replica database, not primary one
  - Latency, slow execution
  - Schema changes may break the pipeline, whereas can be resolved by Infer Schema/Tooling like Delta lake
  - Store too much data leads to cost escalation
  - Not suitable for event data and large data

##### 3.1.3 Lookback

As an advanced data processing, lookback helps to handle source system which are continuously update and has late arriving events for particular record. That help to answer aggregate metric for the past n period because the data in fact table being changed.

- **Pros**
  - Great fit for fact data with KPI Dashboard tracking
  - Easy to build and maintain in Apache Beam where we careless about deployment. Focus on programing model-watermark-fired events...
- **Cons**
  - Report and Dashboard makes confusion for end-user if data late events are come a lot

##### 3.1.4 Streaming

Each record flows through data pipeline with enriched, registered, filtered, ect as needed. This is popular topic on market because users want to see the data as soon as possible

- **Pros**
  - Low latency
  - Real Quick look and action on data
- **Cons**
  - Have to handle scaling in downstream at high traffic and can break data pipeline
  - Source replayable is required for handling issue of failure/outage
  - Decoupling pattern for data pipeline to isolate source - pipeline - sink

#### 3.2 Behavioral

##### 3.2.1 Idempotent

Data pipeline does not cause duplication data/partial data/schema changes whenever it runs numerous time with the same inputs.

To implement the Idempotent data pipeline: the delete-write pattern is strongly recommended with highly carefulness. It require we understand sink systems mechanism.

Example: for database SQL

```sql
CREATE TEMP TABLE TEMP_YYYY_MM_DD
AS
SELECT c1,
    c2,
    SOME_TRANSFORMATION_FUNCTION(c3) as c3
FROM stage_table
WHERE day = 'yyyy-mm-dd';

-- note the delete-write pattern
DELETE FROM final_table
WHERE day = 'yyyy-mm-dd';

INSERT INTO final_table(c1, c2, c3)
SELECT c1,
    c2,
    c3
FROM TEMP_YYYY_MM_DD;

DROP TEMP TABLE TEMP_YYYY_MM_DD;
```

- **Pros**
  - Easy to build, maintain, further reruns and backfills
  - Easy to tracking data lineage

- **Cons**
  - Longer dev time
  - Hard to maintain with changing requirements
  - Good fit for OLAP and Relational Database

##### 3.2.2 Self-healing

The straightforward design for self-healing pipeline is all unprocessed data will be "catch-up" for the next cycle run when an error occurs during a run.
Whereas time ranged pipeline simply automatically run from the last checkpoint failed run before starting the run, or full snapshot doesn't need to care historical data, or lookback pipeline will skip failed run, Self-healing behavior need a meta running table to control the checkpoint and run_id that it can be challenging during implementation.

- **Pros**
  - Reduce alert fatigue and monitoring
  - Well handle an interruption and broken from upstream
  - Combine with idempotent runs, catch-up pipeline will handle as re-try flow
- **Cons**
  - Code bus may not be caught be debug as most assume pipeline would self-heal
  - System may be crashed by re-try as many time
  - Need to ensure overwritability on sink, no duplication, no partial
  - Need to handle metadata of pipeline

#### 3.3 Structural

##### 3.3.1 Multi-hop pipeline

An idea of multi-hop is keeping data separated at different levals/layer of cleanliness. Multiple layers of transformation help:

- Catch issues as soon as data quality checks after each layer with specific method for each layer
- Debug issue as fast as alerting and following a consistent pattern of applying transformation. (example: Type Error/Schema Error --> Check layer 1/Cleansing, Transform Erorr --> Check layer 2, Enrich Error --> Check layer 3 and so on...)
- The staging tables/temp table are key concept to figure out data structural layer, preferred approaches in here for vary standard and popular today:

1. [Stage/Intermediate/Marts from dbt](https://docs.getdbt.com/guides/best-practices/how-we-structure/1-guide-overview)
2. [Medallion architect from Databrick](https://docs.databricks.com/lakehouse/medallion.html)

- **Pros**
   - Rerun only failed transformations and their dependencies
   - Build new logic at any step of data processing
   - Pinpoint data if an issue occurred in the code. Debug step by step.
- **Cons**
  - Storage costs since we are storing copies of dataset from various layers
  - Processing costs with large data and rerun data

##### 3.3.2 Conditional/ Dynamic pipeline

Additional consideration when keep an eye on th exploding complexity when pipeline grows and evolves. The requirement may need complex flows and pipeline do have different tasks based on different condition based on input. For example, we organize tasks in pipeline when input from user changes frequently.

- **Pros**
  - One pipeline(repo) to control data flow(data lineage)
  - Easy to deliver complex requirement of data flow
- **Cons**
  - Hard to debug, slow to develop
  - Critical thinking about design pattern(OOP)
  - Difficult for testing when need to simulate all the different input scenarios

##### 3.3.3 Disconnected pipeline - Connected source storage

Disconnected data pipeline depend on data sinks of other data pipelines, but careless data sources. Define boundary of data pipeline based on Ontology/Semantic

- **Pros**
  - Quick to build
  - De-coupling development where teams can implement independently
- **Cons**
  - Hard to debug, tracking data lineage across system
  - Hard to define SLAs

### 4. Conclusion

The post provides idea how to get starting to consider and figure out the best fit for resolving problem, and how a typical question made when we are asked to create and organize data flows through data pipeline.

Last but not least, because of making development go well and maintenance more efficiency, the communication and get feedback are critical important during design and implement. Apply Scum method in software development is the best of choice.

I must lack of knowledge and experience and please email me if you have any questions, comments or advices.
Have a talk and make it better.


To improve and strongly go-live the data pipeline, besides apply best practices and pillar for Data Pipeline Native Solution, a design framework and pattern are robustly help us in...

Data Pipeline Design Framework


## What is MapReduce?
Let's start with an example.

We are the head of census bureau for the state of California and tasked with finding the finding the population of all cities in California. All the resources we want are ready but we have only four months to finish the task. Calculating the population of all cities for a big state like california is not an easy task. The sensible thing to do is to divide the state by city and make individuals in charge of each city to calculate the population of each city where he is in charge of.

![](assets/mapreduce_mr-example-1.webp)

For illustration purpose, there are three cities: San Francisco (SFO), San Jose (SJOSE) and LA.

Person 1 will be in charge for SFO, person 2 will be in charge for San Jose, and person 3 will be in charge of LA.

We have divided California into cities and each city is assigned to a person and he is responsible for finding the population of the assigned city. We now need to give instructions to each person on what they have to do. Thus, we ask each person to go to a home knock on the door and when someone answers the door, ask how many people live in the home and note it down.  Each one notes down the city they're responsible for and the number of people live in the home. Then, the person has to go to the next home and repeat the same process until he covers all homes in the assigned city.

For a person who covers SFO, he goes to first home, there are five people in the home so he note down `SFO 5`, three people are living in the second home so he note down `SFO 3`, so on.  It's a classic divide and conquer approach.
Same instructions will be carried out by everyone involved person 2 will go to San Jose and Person 2 will do the same in LA. When each person is done with their assigned city, we ask them to submit their results to the state's headquarters.
We'll have a person in the headquarters to receive the results from all cities and aggregate them by city to come up with population of each city for the entire state.
Therefore, four months in with this strategy, we're able to calculate the population of california.

![](assets/mapreduce_mr-example-2.webp)

Next year, we're asked to do the same job, we have all the resources we want but this time we have two months to finish the task. So we would simply double the number of people to perform the task. We will divide SFO into two divisions and add one person to each division and we will do the same thing for San Jose and LA. Each person responsible for a division will perform the same task as before, we can also do the same thing at the headquarters.

Let's divide the headquarters into two: `CA HQ 1` and `CA HQ 2` and one person to each division.  With twice as much people, we can finish the task in half the time but there is one small problem, we want the census takers for SFO, called `SFO 1` and `SFO 2`, send their results to either `CA HQ 1` or `CA HQ 2`.

We don't want `SFO 1` sending results to `CA HQ 1` and `SFO 2` sending their results to `CA HQ 2` because this would result an population count for SFO divided between `CA HQ 1` or `CA HQ 2`.  That is not ideal because we want consolidated population count by city, not partial counts.

So, what we can do is to instruct census takers in `SFO 1` and `SFO 2` to send their results to either `CA HQ 1` or `CA HQ 2`.  Similarly we should instruct census takers for San Jose and LA, they should either send it to `CA HQ 1` or `CA HQ 2`.
With this model, again, we were able to complete the census calculation in two months.

If next year if we were asked to do the same thing in a month, we know exactly what to do and we can simply double the resources and apply our model. Now, we have a good enough model, not only the model works but it also can scale.

The model we have here is called _MapReduce_. MapReduce is a programming model for distributed computing. It's not a programming language, it is a programming model which we can use to process huge data sets in a distributed fashion.

![](assets/mapreduce_mr-example-3.webp)

Now let's look at the faces involved in Mapreduce. The phase where individuals collect the population of their assigned city or part of the city is called a [_Map phase]().

The individual person involved in the actual calculation is called the _Mapper_ and the city or the part of the city he is working with is known as the _Input Split_.
The output from each mapper is a _Key Value pair_ as we can see the key is `SFO` the value is `5` or the key is `SJSOE` the value is `2`.

The phase where you aggregate the intermediate results from each city or mappers in the headquarters is called the _Reduced phase_ and the individuals who work in the headquarters are known as _Reducers_ because they reduce or consolidate the output from many different mappers. Each reducer will produce a result set.

The phase in which the values from the different mappers are copied or transferred to reducers is known as the _Shuffle phase_. The shuffle phase comes in between map and the reduced phase. Therefore, map phase, shuffle phase and reduce face are the three phases of Mapreduce.

## Summary
MapReduce is:

- A distributed programming model for processing large data sets
- Conceived at Google and Hadoop's adapt this programming model.
- Can be implemented in any programming language and Hadoop supports a lot of programming language to write Mapreduce programs. We can write a Mapreduce program in Scala, Python, C/C++ and of course Java.
- Mapreduce is not a programming language, it is a programming model.
- Hadoop implements Mapreduce so that the Mapreduce system in Hadoop manages the communicationsm, data transfer, parallel execution across the distributed servers or nodes.


We are the head of census bureau for the state of California and tasked with finding the finding the population of all cities in California. All the resources we want are ready but we have only four months to finish the task. Calculating the population of all cities for a big state like california is not an easy task. The sensible thing to do is to divide the state by city and make individuals in charge of each city to calculate the population of each city where he is in charge of...

MapReduce


## Motivation of Vector Database

- The motivation of this thoughts that trying to discover the LLMs and Generative AI
- In order to create or build, train any models there are multiple factors will need to be considered: lakes of data, metadata, data management, infrastructure, etc.
- Vector Databases are used like underlying of Gen AI
- Where everything are tokenized, vectorized and serialized into a single language
- Explored blog posts, articles from [What is a AWS Vector Database?](https://aws.amazon.com/what-is/vector-databases/)

## Learning Notes

- Data from internet/database/users are inputted and converted and encoded (video, speech, images, text, etc.) to a vector using a vector embedding model such as Gemini, OpenAI, etc.
- Developing Products: Drant*, Milvus*, redis, pgvector(extension), Pinecone, LanceDB, oracle, MongoDB(Atlas), AthenaDB
- Different with Relation DB where in Vector Database has multiple dimensions (**legacy from multi-dimensional data mode**l)
- Data structure: save the vector; don't define the structure for the table.
- How to query data? Query the vector that is similar to others in DB base on algorithms (KNN and could be different/better model as I think)
- Indexing the data in the database, Indexing Algorithms: Flat, HNSW, IVF ==> I think we have room for optimizing the caches and indexes.

- Working flow of Vector Database with Gen AI:
  - An embedding model converts the data to a vector data structure. (We need to optimize the algorithm for the huge of parameters)
  - Convert the input(end-user) to a vector.
  - Querying data in the database.
  - Compare the query vector with the database/cache to find out the resources in the DB

- Diagram to describe the Working Flow

![](assets/quick-learning-vector-database_ab8dc870fb19553f7616442fe4e9e6c5_md5.webp)

- Benefits:
  - Flexibility: schemaless, non-structured
  - Scalability: bucketing data, dynamic programming
  - Speed/ performance: Search vector
  - Standardization: formatting all data into a single format

- Use cases:
  - Feeding Realtime data for LLMs models
  - Resolving searching problems
  - Serving Video/Image recognition problems
  - Better similarity recommendation with contexts and situations

- Technical Challenges:
  - Performance: indexing for Flat (elastic search, redis, pgvector) querying using vector
  - Optimize long-term caching memory
  - Hardware optimization for hosting and computing data
  - Improve KNN performance models


The motivation of this thoughts that trying to discover the LLMs and Generative AI...

Playground > 01_literature > Engineering > Data