From API to Business Insights: Building a Medallion Pipeline on AWS

When I started this project, I didn't want to stop at fetching data from an API and saving it to Amazon S3. I wanted to understand what happens after the data arrives: how it gets cleaned, how we check whether it is reliable, how multiple processing steps work together, and how the final output becomes useful for analysis.

So I built an API-based Medallion Data Pipeline using AWS.

The project uses the YouTube Data API v3 to collect trending video information and processes it through Bronze, Silver, and Gold layers. Along the way, I worked with AWS Lambda, Amazon S3, AWS Glue, PySpark, AWS Step Functions, Amazon SNS, Amazon CloudWatch, Amazon EventBridge, the AWS Glue Data Catalog, and Amazon Athena.

In this post, I'll walk through the complete architecture and explain the role of each service, how the data moves through the pipeline, and what I learned while putting the pieces together.

GitHub repository: API-Medallion-Data-Pipeline

1. What does the pipeline actually do?

The source is the YouTube Data API v3. It provides information about trending videos, including view counts, likes, channels, categories, and regional trends.

The pipeline takes that source data and prepares it for analysis.

The main goals were to:

• Ingest data from an external API.
• Store the original input separately from processed data.
• Clean and standardize records.
• Validate data before analytical processing.
• Generate reusable analytics datasets.
• Coordinate execution without manually running every component.
• Make the results accessible through SQL.

I also included support for historical CSV datasets and category reference data, which can be useful for testing and backfilling.

2. The complete architecture

Here is how the components fit together.

                 YouTube Data API v3
                          |
                          v
                  AWS Lambda
                  API ingestion
                          |
                          v
                    Amazon S3
                   Bronze layer
                          |
             +------------+-------------+
             |                          |
             v                          v
      AWS Glue + PySpark          AWS Lambda
      Statistics processing       Category JSON
             |                    to Parquet
             v                          |
                    Amazon S3 <----------+
                   Silver layer
                          |
                          v
                  AWS Lambda
                 Data quality
                          |
                   Quality passed?
                    /          \
                  Yes           No
                   |             |
                   v             v
                AWS Glue      Amazon SNS
              Gold analytics   Alert
                   |
                   v
                 Amazon S3
                 Gold layer
                   |
                   v
                AWS Athena
                 SQL queries

          AWS Step Functions
          Coordinates the workflow

          Amazon EventBridge
          Starts scheduled runs

          Amazon CloudWatch
          Logs and monitoring

The key idea is that each service has a specific responsibility. Lambda handles lightweight tasks, Glue processes datasets, S3 stores the data, Step Functions coordinates the workflow, and Athena provides the query interface.

Now let's go through the pipeline from beginning to end.

3. AWS Lambda: Getting data from the API

Every pipeline needs an entry point. In this project, that entry point is an AWS Lambda function that calls the YouTube Data API.

The ingestion function reads its configuration, requests trending video information for the configured regions, processes the response, and writes the raw output to S3.

The general flow is:

1. Read the API configuration and region list.
2. Make the API request.
3. Handle the response.
4. Write the raw data to the Bronze layer.

I used Lambda here because API ingestion is a relatively lightweight task and doesn't require a continuously running server.

There are a few things to consider when implementing this part. API credentials should not be hardcoded, request quotas need to be monitored, and the function should handle API errors and unexpected responses appropriately.

The ingestion function should also be designed carefully if the same workflow might be retried. Without a clear strategy for identifying each ingestion run, retries can create duplicate data.

4. Amazon S3: Organizing the Bronze, Silver, and Gold layers

Amazon S3 is the storage layer for the entire pipeline.

Rather than mixing raw data, cleaned data, and final aggregates in one location, I separated them into different layers.

This is where the Medallion Architecture becomes useful.

Bronze: Keep the original input

The Bronze layer stores raw source data with minimal transformation.

An example of the folder structure is:

s3://<bronze-bucket>/
└── youtube/
    ├── raw_statistics/
    │   └── region=IN/
    │       └── date=YYYY-MM-DD/
    │           └── hour=HH/
    └── raw_statistics_reference_data/
        └── region=IN/

Organizing data by region and ingestion time makes it easier to locate the data associated with a particular run.

Keeping the raw input also gives us something to inspect when a downstream transformation produces unexpected results.

Silver: Store cleaned data

The Silver layer contains standardized records that are more suitable for processing and analysis.

For example:

s3://<silver-bucket>/
├── statistics/
│   └── region=IN/
└── category_reference/
    └── region=IN/

The statistics and category reference data follow separate transformation paths, but both become structured datasets.

Gold: Prepare analytics-ready datasets

The Gold layer contains aggregated datasets designed around analytical use cases.

Instead of repeatedly processing raw API responses whenever someone wants a report, the pipeline prepares datasets that can be queried directly.

Keeping these layers separate also makes it easier to identify where a data issue occurred.

5. AWS Glue and PySpark: Transforming Bronze into Silver

Raw API responses aren't always consistent enough for analytics.

Fields may need type conversion, dates may need parsing, and duplicate records may appear when data is collected across multiple runs.

For the statistics transformation, I used AWS Glue with PySpark.

The job performs operations such as:

• Enforcing a consistent schema.
• Converting numeric fields to the correct data types.
• Parsing dates and standardizing region codes.
• Handling null values and duplicate records.
• Retaining the latest record for each video, region, and date.
• Calculating derived metrics such as like ratio and engagement rate.

After processing, the job writes the cleaned dataset to S3 in Parquet format.

Why AWS Glue?

AWS Glue provides a managed environment for running data integration jobs without having to manage the underlying Spark infrastructure directly.

PySpark is useful here because it lets me express transformations using Spark DataFrames and process larger datasets in a distributed environment when needed.

Why Parquet?

The raw layer can preserve JSON or CSV input, while the Silver and Gold layers use Parquet for analytical workloads.

Parquet is a columnar format, which helps analytical engines read the columns they need. It also supports compression, reducing the amount of data that may need to be stored or scanned.

In this project, the transformed datasets use Parquet with Snappy compression.

A separate path for category reference data

The category reference data is processed by another Lambda function.

That function normalizes category JSON into tabular records, removes duplicate entries, and writes Parquet output using AWS SDK for pandas.

This is different from the main statistics transformation because the reference data has a different structure and processing requirement.

Separating the two jobs keeps their responsibilities clear.

6. Data quality: Don't trust a successful job blindly

One thing I wanted to address was the difference between a job completing successfully and a dataset actually being suitable for analytics.

A transformation can finish without throwing an error, yet still produce incomplete or unexpected data.

For example, an API response might contain fewer records than expected, a required field might be missing, or the latest data might be too old.

To address this, the pipeline includes a dedicated data quality Lambda function that checks the Silver data before Gold processing.

The configured checks include:

Validation	Expected condition
Minimum row count	At least 10 rows
Critical-column nulls	No more than 5%
Schema validation	Required columns exist
Value validation	View counts pass sanity checks
Data freshness	Latest data is less than 48 hours old

These thresholds can be configured through Lambda environment variables.

The decision is straightforward:

• If the data passes validation, the workflow can continue to Gold processing.
• If validation fails, the workflow is designed to stop before aggregation and send an alert through Amazon SNS.

CloudWatch logs can then help identify what went wrong.

The thresholds are examples of project-level rules, not universal standards. A production pipeline should define them according to the source data and business requirements.

7. AWS Glue: Building the Gold analytics layer

After the data quality gate passes, another AWS Glue job transforms the Silver data into analytics-ready datasets.

I organized the output into three main datasets.

Trending analytics

This dataset summarizes trending activity by region and date.

It includes:

• Total videos, views, and likes.
• Average views per video.
• Average like ratio and engagement rate.
• Unique channels and categories.

It can help answer questions about how trending activity varies across regions and dates.

Channel analytics

This dataset focuses on channel-level performance.

It includes total videos, total views, average engagement rate, how often a channel appeared in trending data, and its ranking within a region.

Category analytics

This dataset summarizes category performance.

It includes video counts, total views, average engagement rate, and the percentage share of views associated with a category.

The Gold outputs are stored as Parquet datasets in S3.

The main advantage is that downstream consumers can query prepared aggregates instead of rebuilding the same calculations every time.

8. AWS Glue Data Catalog: Making the datasets discoverable

Files in S3 don't automatically become convenient SQL tables.

The AWS Glue Data Catalog stores metadata such as table definitions, schemas, and the S3 locations associated with datasets.

In this project, the Gold datasets are registered in the catalog so that Athena can query them using table names.

This separates two things:

• Amazon S3 stores the actual data.
• The Glue Data Catalog describes how the data should be interpreted.

That distinction is useful when working with a data lake because the same storage location can contain datasets that serve different purposes.

9. Amazon Athena: Querying the final output

Once the Gold datasets are registered in the Glue Data Catalog, Amazon Athena provides a SQL interface for exploring them.

For example, this query returns the top trending channels in India by total views:

SELECT
    channel_title,
    total_views,
    times_trending,
    rank_in_region
FROM yt_pipeline_gold_dev.channel_analytics
WHERE region = 'IN'
ORDER BY total_views DESC
LIMIT 10;

To compare metrics across regions:

SELECT
    region,
    SUM(total_videos) AS total_videos,
    SUM(total_views) AS total_views,
    AVG(avg_engagement_rate) AS avg_engagement_rate
FROM yt_pipeline_gold_dev.trending_analytics
GROUP BY region
ORDER BY total_views DESC;

And to explore category-level performance:

SELECT
    category,
    region,
    SUM(total_views) AS total_views,
    AVG(view_share_pct) AS avg_view_share_pct
FROM yt_pipeline_gold_dev.category_analytics
GROUP BY category, region
ORDER BY total_views DESC
LIMIT 20;

These examples assume the table names and database configuration match the deployed Glue Data Catalog.

Athena is useful here because I can query the S3-based datasets directly without setting up a separate database server for the analytical layer.

10. AWS Step Functions: Connecting everything into one workflow

By this stage, the project has multiple Lambda functions and Glue jobs. Running each component manually would make the pipeline difficult to operate consistently.

That is where AWS Step Functions comes in.

Step Functions manages the order in which the tasks execute and passes control between them.

The intended workflow is:

1. Invoke the API ingestion Lambda.
2. Wait briefly where required for data consistency.
3. Run the statistics transformation and category reference-data processing in parallel.
4. Execute the data quality validation.
5. Run Gold aggregation when validation succeeds.
6. Handle failures and route notifications according to the workflow configuration.

The statistics and reference-data transformations are separate tasks, so they can run in parallel when their dependencies and resource constraints allow it.

Step Functions also provides execution history. If a pipeline run fails, I can inspect the state machine to find the task that failed instead of treating the entire pipeline as a single script.

Retries, timeouts, and error handling can also be configured for appropriate failure scenarios.

The important distinction is that Step Functions orchestrates the workflow; Lambda and Glue perform the actual work.

11. Amazon EventBridge: Scheduling executions

A pipeline becomes more useful when it can run on a predictable schedule.

Amazon EventBridge can trigger the Step Functions state machine at a configured interval.

For example, a schedule could be configured to run the workflow every six hours, depending on data freshness requirements.

The schedule should also account for API quota usage, expected data volume, and AWS costs.

EventBridge is responsible for triggering the workflow. It doesn't perform the data transformation itself.

12. Amazon CloudWatch and Amazon SNS: Monitoring and alerts

Automation is only part of the solution. We also need to know when something goes wrong.

Amazon CloudWatch

CloudWatch provides access to Lambda and Glue logs and supports monitoring metrics such as execution duration and errors.

When a task fails, its logs help investigate issues such as incorrect parameters, API errors, schema mismatches, or S3 permission problems.

Amazon SNS

Amazon SNS provides a way to send notifications to subscribed endpoints, such as email addresses.

In this pipeline, SNS is used for configured failure alerts associated with data quality and workflow execution.

The notification path depends on how the state machine and Lambda functions are configured. The important point is that failures should be visible rather than silently leaving downstream data incomplete.

13. IAM: Giving each component the right permissions

IAM is the security layer that controls which AWS resources each component can access.

For example:

• The ingestion Lambda needs access to the API configuration and the appropriate Bronze S3 location.
• The Glue jobs need permission to read their inputs and write their outputs.
• The validation Lambda needs access to the Silver data and permission to publish alerts.
• Step Functions needs permission to invoke its tasks and start the relevant Glue jobs.
• EventBridge needs permission to start the state machine.

These permissions should be granted through appropriate execution roles using the principle of least privilege.

I also recommend keeping S3 Block Public Access enabled, using suitable encryption, and never committing API keys or other secrets to GitHub.

14. How I would troubleshoot this pipeline

When working with a multi-service pipeline, I wouldn't start by debugging every component at once. I'd first identify the state that failed and then inspect the corresponding service.

Here are some examples:

Problem	Where I would investigate
API requests fail	Lambda logs, API key configuration, and quota usage
Raw files aren't written	S3 bucket, object path, and IAM permissions
Glue transformation fails	Glue logs, input schema, job arguments, and execution role
Quality checks fail	Row counts, null percentages, schema, and freshness
Gold data is missing	Step Functions execution history and Glue output paths
Athena can't find a table	Glue Data Catalog metadata and table locations
Scheduled execution doesn't start	EventBridge schedule and execution permissions

This approach makes troubleshooting more systematic because each service has a clearly defined responsibility.

15. What I learned from building the project

The biggest lesson was that building a data pipeline involves much more than writing transformation code.

The individual AWS services are only part of the design. The real work is connecting them correctly and deciding how the system should behave when data is incomplete, a task fails, or a downstream dependency isn't ready.

A few concepts stood out to me:

• Keeping raw data separate from cleaned and aggregated datasets makes the workflow easier to investigate.
• Glue and PySpark provide a useful approach for distributed transformations.
• Data quality checks help prevent unsuitable data from reaching analytical processing.
• Step Functions makes task dependencies and execution history easier to manage.
• S3, the Glue Data Catalog, and Athena work together to provide a practical data lake analytics workflow.
• IAM, monitoring, retries, and cost considerations are part of pipeline design, not optional extras.

There are also areas I would continue improving, including infrastructure as code, automated testing, incremental processing, stronger schema contracts, and cost optimization.

I see this project as a practical way to understand how API ingestion, data lake storage, transformation, orchestration, validation, and SQL analytics fit into one end-to-end workflow.

16. Explore the code

I've published the project source code, Glue scripts, Lambda functions, data quality logic, Step Functions definition, and supporting files in the repository.

Repository: https://github.com/Rahulfire/API-Medallion-Data-Pipeline

If you're building something similar, I'd be interested in hearing how you approach data quality and orchestration in your own pipelines.

Would you use a dedicated Lambda function for the quality gate, or would you implement the validation as part of the Glue transformation itself?

Thanks for reading!