Designing Your Project's Target Architecture

The Architectural Blueprint

Every complex system needs a blueprint. For our data pipeline, this blueprint defines not just the components but also how data and control signals flow between them. Our architecture is designed to handle both your batch sources (APIs, CSVs) and your streaming source, process them through the medallion layers, and prepare them for your analytics dashboard and NLP service.

Let's begin by looking at a diagram that illustrates this kind of event-driven, layered architecture. While it uses a few different technologies, the pattern is precisely what we will implement.

This diagram elegantly combines the medallion pattern with a modern data stack. We will use it as our guide, adapting it to your specific technology choices. Our object storage will be PostgreSQL, and our processing will be a combination of Python and dbt.

Let's break down the roles of each component in your stack.

Core Components and Their Roles

A successful architecture ensures that each component has a clearly defined responsibility. Here’s how your chosen tools fit together.

As the article explains, these tools form a powerful, modern stack. Let's place them within our architectural blueprint:

• Storage (The Warehouse): PostgreSQL

  • Role: PostgreSQL will serve as the central data warehouse for your entire pipeline. It's not just a temporary holding area; it is the permanent home for all your data, from raw to refined.
  • Implementation: We will implement the medallion architecture using PostgreSQL schemas. You will have a bronze schema for raw data, a silver schema for cleaned and conformed data, and a gold schema for business-ready, aggregated data marts. Your experience with PostgreSQL will be invaluable here.

• Orchestration (The Conductor): Apache Airflow

  • Role: Airflow is the brain of your batch operations. It will schedule, execute, and monitor all the tasks involved in getting data from your batch sources into the bronze layer and triggering the subsequent transformations.
  • Implementation: You will write Directed Acyclic Graphs (DAGs) in Python. For instance, one DAG might be scheduled to run daily. Its tasks would be: 1) fetch data from an API, 2) load it into the bronze Postgres schema, and 3) trigger the dbt transformation jobs.

• Ingestion & Transformation (The Workers): Python & dbt

  • Python Scripts: Your proficiency in Python will be used for the ingestion tasks. These scripts, orchestrated by Airflow, will handle the logic for connecting to APIs, parsing files, and loading the raw data into the bronze PostgreSQL tables using idempotent patterns.
  • dbt Models: dbt will be your primary tool for transformation. It will handle everything that happens after the data lands in the bronze layer. Using your advanced SQL skills, you will write dbt models to clean, join, and aggregate data, moving it progressively from the bronze schema to silver and finally to gold.

• Streaming (The Real-Time Channel): Apache Kafka

  • Role: Kafka will handle your streaming data source. It acts as a durable, high-throughput message queue that decouples the data source from the processing logic.
  • Implementation: A data source will produce messages to a Kafka topic. A separate, continuously running Python service (a "consumer") will subscribe to this topic, read messages in small batches, and write them into the bronze layer of PostgreSQL.

Tying It All Together: The Data Flow

Now, let's trace the journey of data through this system, using the diagram as our reference.

The Batch Workflow (APIs, CSVs)

This flow is for data that you process on a schedule (e.g., once a day, once an hour).

1. Orchestration: An Airflow DAG is triggered on its schedule.
2. Ingestion (Python):
   • The first task in the DAG executes a Python script. This script fetches data from an API or reads a CSV file. This corresponds to step 1 (Manual Upload) in the diagram, though ours will be automated.
   • The script loads this raw data into the bronze schema in PostgreSQL.
3. Transformation (dbt):
   • Once the ingestion task succeeds, Airflow triggers the next task: dbt run.
   • dbt executes its models. It reads from the bronze tables, applies transformations (cleaning, joining), and materializes the results in the silver schema. This is the "Data Cleaning" part, analogous to steps 4 and 5 in the diagram.
   • dbt then runs models that read from the silver schema, perform aggregations, and create the final data marts in the gold schema, ready for your dashboard. This is the "Data Aggregation" part, analogous to steps 8 and 9.

The following video provides a fantastic walkthrough of building this exact batch architecture.

The Streaming Workflow (Kafka)

This flow is for data that arrives continuously and needs to be captured in near real-time.

1. Ingestion (Kafka):
   • Your streaming source sends an event message (a piece of data) to a Kafka topic. In our reference diagram, this is represented by steps 2 and 6. We'll start with a single topic. Let's call it the bronze_topic.
2. Consumption (Python):
   • A dedicated Python consumer service, running independently of Airflow, is constantly listening to this bronze_topic (step 3).
   • When a message arrives, the consumer processes it (e.g., parsing JSON) and loads it into the appropriate table in the bronze PostgreSQL schema. This is an ongoing, low-latency process.
3. Unification (dbt & Airflow):
   • The streaming data now resides in the bronze layer alongside the data from your batch sources.
   • When the next scheduled Airflow DAG runs the dbt run command, dbt is completely agnostic to how the data arrived. It simply sees new rows in the bronze tables and processes them into the silver and gold layers, effectively unifying your batch and streaming data.

This hybrid approach gives you the best of both worlds: scheduled, robust processing for batch sources and low-latency capture for streaming sources, all unified for transformation in a single, consistent framework.

Conclusion

Let's recap the blueprint for your data pipeline:

• Central Storage: PostgreSQL will house all data in bronze, silver, and gold schemas.
• Batch Processing: Airflow will orchestrate Python scripts for ingestion from APIs/CSVs into the bronze schema, followed by dbt jobs to transform data through the silver and gold layers.
• Streaming Processing: A standalone Python consumer will read from a Kafka topic and write data into the bronze schema in near real-time.
• Data Unification: dbt acts as the great unifier, transforming all raw data in the bronze layer—regardless of its origin—into a single, cohesive source of truth in the silver and gold layers.

You now have the complete architectural diagram for your project. This blueprint will be our guide for the rest of the course as we move from theory into practice.

Next time

With our architectural plan in place, the next logical step is to set up the workshop where we'll build it. The next module, "Local Development Stack with Docker," is entirely dedicated to this. In the very next lesson, we will begin by structuring our project and setting up a reproducible Python environment, the first practical step toward turning this blueprint into a reality.