From Raw Athena Data To SageMaker Pipelines

This Was Not Really About Training A Model

Picture a Monday review. A cautious product owner, or an auditor, points at a decision the risk model made last Thursday and asks the only question that matters: why did it score that customer that way, from that data, on that day?

The notebook that trained the model has been renamed or lost. The feature table was overwritten in place sometime over the weekend. The training run that produced this version was kicked off by a terminal command one engineer half-remembers. Nobody can rebuild the same answer twice, and nobody can prove the model saw only what it should have seen.

That gap is the real problem. At Datum Consulting, I worked on data and MLOps platforms for a banking client, and that Monday question was the actual spec, not "train a model."

The hard part was not the model. The hard part was trust.

Could the data science team start from raw data in Athena, transform it through clear stages, preserve the history that mattered, trigger daily runs, train and evaluate models, and promote them without filing a DevOps ticket every time the business wanted to improve an algorithm?

A notebook can be clever for one person. A banking platform has to be boring for many people. It needs roles, networks, lineage, repeatable transformations, clear ownership, and enough operational evidence that someone can answer that Monday question later, with proof.

The shape we built around was an AWS Lakehouse and MLOps stack: S3, Glue, Athena, Iceberg, IAM roles, VPC networking, Lambda, EMR, MWAA Airflow, dbt, SageMaker Studio, SageMaker Pipelines, model registry, drift alerts, and CloudWatch. In Python, the practical bridge into the lakehouse layer was AWS SDK for pandas, still commonly imported as awswrangler.

This article keeps client details redacted, but the engineering pattern is worth writing down.

The Architecture Had One Main Spine

The spine was simple enough to explain in one sentence:

Raw data became governed analytical tables, dbt shaped those tables into model-ready layers, Airflow orchestrated the daily run, and SageMaker Pipelines turned curated data into trainable, reviewable, promotable ML workflows.

The AWS services around that sentence each had a job:

S3: durable data lake storage.
Glue: table catalog and shared metadata for Athena, EMR, and other services.
Athena: SQL access to raw and curated tables without forcing every analyst into Spark.
Iceberg: table semantics over large analytical datasets instead of a pile of files.
awswrangler: Python reads from Athena and writes Pandas DataFrames back into Iceberg tables.
dbt: SQL transformation stages, tests, and reviewable assumptions.
MWAA Airflow: daily orchestration, dependencies, retries, and run visibility.
Lambda: lightweight event glue and small automation tasks.
EMR: heavier distributed processing when SQL or local Python was the wrong tool.
SageMaker Studio: managed development environments for data scientists.
SageMaker Pipelines: ML workflow steps for processing, training, evaluation, registration, and promotion.
IAM roles and VPC boundaries: permission and network shape for each runtime.

This is the important part: none of those services were impressive by themselves. They became useful because the responsibilities were separated.

The AWS Lakehouse Framing

The better name for this architecture is not just data lake and not just warehouse. It is an AWS Lakehouse pattern.

A data lake gives the platform cheap, durable, flexible storage on S3. A warehouse gives the business table semantics, SQL access, governance, quality expectations, and repeatable consumption. A lakehouse tries to keep both sides: open storage and analytical table discipline.

In AWS terms, the core shape looked like this:

S3 was the storage foundation.
Glue was the catalog and metadata layer.
Athena was the SQL query surface.
Iceberg gave the lakehouse table format.
dbt made transformations explicit and testable.
Airflow made daily lakehouse movement observable.
EMR handled distributed compute when the lake needed heavier processing.
SageMaker consumed curated lakehouse tables for ML workflows.

That framing matters because a bank does not only need a place to store data. It needs a place where raw data can become governed data, where governed data can become features, and where those features can be tied back to the version of business reality that created them.

The lakehouse was the shared contract between analytics and MLOps. Analysts could query curated tables through Athena. Engineers could evolve tables through Iceberg. Data scientists could train from model-ready tables. Operations could see which daily run produced which output. The same storage foundation served several kinds of work without making every workload own a separate copy of truth.

I would be careful not to over-market the word. Lakehouse does not make messy data clean. It does not remove governance work. It does not replace IAM, VPC design, table ownership, dbt tests, or pipeline evidence. It is useful because it gives those things a coherent home.

Raw Data Started In Athena, But Athena Was Not The Architecture

Athena is a strong entry point because it makes raw and curated data queryable with SQL. That matters in a bank because many users already think in tables, joins, partitions, and reconciliations. It also gives engineering a fast way to inspect what happened without starting a custom processing job.

But Athena should not become the entire architecture.

If every transformation is an ad hoc query, the platform slowly becomes a memory test. Which query created this table? Which version was used last Tuesday? Did it overwrite history? Did it pass the same checks as yesterday? Who owns the output?

Athena was the query surface. The platform still needed table format discipline, transformation discipline, orchestration discipline, and model lifecycle discipline.

That is where Iceberg, dbt, Airflow, and SageMaker entered.

Why Iceberg Changed The Data Lake Conversation

S3 is excellent durable storage, but raw object storage does not behave like a database table by default. A folder full of Parquet files is not enough when the platform needs schema evolution, table history, record-level updates, deletes, time travel, and safer concurrent writes.

Apache Iceberg gives the lake a stronger table layer. AWS documents Athena support for Iceberg reads, writes, DDL, updates, and time travel, with modern data lake operations such as schema evolution and partition evolution on S3.

For machine learning, that matters more than people expect.

Training data is not just "the latest table." It is a snapshot of business reality at a point in time. If the customer table is overwritten in place every day, yesterday's training set can become impossible to explain. If the feature table changes without history, the model can learn from data that would not have existed at prediction time.

Iceberg helped move the lake from files toward tables with memory.

The Python Wrangler Layer

A small naming note before the code. The library here is AWS SDK for pandas, still imported as awswrangler. The old name stuck, so you will see both in the wild:

import awswrangler as wr

I used it as a bridge between Python jobs, Athena queries, S3, Glue catalog metadata, and Iceberg writes. It let small and medium data tasks stay in a Python workflow without forcing everything through Spark.

For example, a daily feature preparation job could read a curated Athena query into a DataFrame:

import awswrangler as wr

feature_frame = wr.athena.read_sql_query(
    sql="""
        select
            customer_id,
            business_date,
            account_age_days,
            monthly_transaction_count,
            average_balance
        from analytics.customer_features_staging
        where business_date = date '{{ ds }}'
    """,
    database="analytics",
    workgroup="analytics-production",
    ctas_approach=True,
)

Then the same job could write the result into an Iceberg table through Athena:

wr.athena.to_iceberg(
    df=feature_frame,
    database="ml_curated",
    table="customer_daily_features",
    temp_path="s3://example-athena-temp/ml/customer_daily_features/",
    table_location="s3://example-data-lake/ml/customer_daily_features/",
    partition_cols=["business_date"],
    mode="append",
    schema_evolution=True,
)

The exact account, bucket, database, and table names are placeholders here. The useful idea is the boundary: Python prepares a DataFrame, wr.athena.to_iceberg() stages it through Athena and writes into a governed Iceberg table. For newer S3 Tables workflows, wr.s3.to_iceberg() is another path, backed by PyIceberg.

This layer is not a replacement for dbt, Spark, or SageMaker. It is the practical glue for jobs that need Python ergonomics and AWS data lake integration.

dbt Owned The Transformation Contract

The transformation stages followed a medallion-style mental model:

Raw: data close to the source shape, lightly normalized, not yet trusted for modeling.
Staging: cleaned names, cast types, filtered invalid records, standardized dates, and made source assumptions visible.
Intermediate: joined business concepts, reusable model logic, and deduplicated entities.
Mart or feature-ready: stable outputs that analysts, reports, and ML pipelines could consume.

dbt was useful because it made transformations reviewable. SQL lived in version control. Dependencies were explicit. Tests were attached to models. Documentation could describe the real meaning of a field instead of relying on tribal memory.

In a banking environment, that matters. The risky transformation is often not complicated SQL. It is a tiny assumption hidden inside ordinary SQL:

where account_status = 'ACTIVE'

That line can mean "currently active," "active at transaction time," "active at the end of the day," or "active according to the latest CRM snapshot." Those are different business truths. dbt does not magically solve that, but it gives the team a place to name the assumption and test the output.

Airflow Was The Operating Clock

The daily data platform needed one visible operating clock. For us, that was Airflow through MWAA.

A useful DAG did not try to do the work inline. It coordinated the work:

from airflow.decorators import dag
from airflow.operators.bash import BashOperator
from airflow.providers.amazon.aws.operators.sagemaker import SageMakerStartPipelineOperator
from airflow.providers.amazon.aws.operators.emr import EmrServerlessStartJobOperator
from airflow.providers.common.sql.operators.sql import SQLCheckOperator
from pendulum import datetime


@dag(
    schedule="@daily",
    start_date=datetime(2024, 1, 1),
    catchup=False,
    tags=["data-platform", "mlops"],
)
def customer_model_daily_pipeline():
    raw_data_ready = SQLCheckOperator(
        task_id="raw_data_ready",
        conn_id="athena_analytics",
        sql="""
            select count(*) > 0
            from raw.transactions
            where business_date = date '{{ ds }}'
        """,
    )

    run_dbt_staging = BashOperator(
        task_id="run_dbt_staging",
        bash_command="dbt run --select tag:staging --vars '{business_date: {{ ds }}}'",
    )

    run_heavy_features = EmrServerlessStartJobOperator(
        task_id="run_heavy_features",
        application_id="{{ var.value.emr_application_id }}",
        execution_role_arn="{{ var.value.emr_execution_role_arn }}",
        job_driver={},
    )

    start_training = SageMakerStartPipelineOperator(
        task_id="start_sagemaker_pipeline",
        pipeline_name="customer-risk-training",
        pipeline_parameters={"BusinessDate": "{{ ds }}"},
    )

    raw_data_ready >> run_dbt_staging >> run_heavy_features >> start_training


customer_model_daily_pipeline()

That sketch omits real configuration on purpose. The point is the operating model.

Airflow owned dependencies, schedule, retries, failure visibility, and handoff between systems. dbt owned transformations. EMR owned heavy distributed processing when needed. SageMaker Pipelines owned ML workflow execution. Each system did its own job.

Lambda Was For Glue, Not Bulk Transformation

Lambda is tempting because it is easy to create a function and call it architecture. We used it carefully.

Lambda is a good fit for lightweight event-driven work: receiving a notification, updating metadata, checking that an expected file arrived, triggering a downstream process, writing a small audit record, or integrating two services that do not need a full worker.

It is not a great home for heavy daily transformations. A banking data platform should not hide important batch logic inside dozens of little functions where lineage and retries become scattered.

The rule was simple: Lambda could connect steps. It should not become the data platform.

EMR Was The Heavy Processing Escape Hatch

Most teams do not need Spark for every transformation. Athena and dbt can carry a lot of analytical work. Python with awswrangler can handle many controlled jobs. But some data work is too large, too distributed, or too compute-heavy for local Pandas or simple SQL.

That is where EMR made sense.

EMR is AWS's managed platform for big data frameworks like Spark and Hadoop. In this architecture, it was not a status symbol. It was an escape hatch for work that genuinely needed distributed processing.

The practical question was always: can this be a dbt model, an Athena query, or a small Python job? If yes, keep it simple. If no, move the workload to EMR and make the Airflow dependency explicit.

IAM Roles And VPC Boundaries Were Part Of The Product

In small systems, IAM and networking sometimes arrive late. In banking systems, they are part of the product from day one.

Each runtime needed a role that matched its responsibility:

Airflow: permission to orchestrate, not to casually read every data asset.
dbt execution roles: access to the databases and buckets required for transformation.
EMR roles: access to the input and output paths required for distributed jobs.
Lambda roles: narrow permissions because the functions were narrow.
SageMaker execution roles: read curated training data, write model artifacts, and register model packages.
Human roles: separated from service roles so production did not depend on a person's long-lived permissions.

The VPC design mattered for the same reason. Some work belonged in private subnets. Some service calls needed VPC endpoints. Some access paths needed to be blocked by default rather than remembered by policy review.

Security was not a layer at the end. It was the shape of the platform.

SageMaker Pipelines Turned Data Into MLOps

Once curated data existed, the machine learning problem could move into SageMaker.

SageMaker Studio gave data scientists a managed environment. SageMaker Pipelines gave the ML workflow a repeatable structure. AWS describes Pipelines as a workflow orchestration service for automating ML development, with steps for processing, training, evaluation, deployment, monitoring, auditability, and lineage.

That matters because model work has its own lifecycle:

Build training and validation datasets from the correct feature snapshot.
Run preprocessing.
Train one or more candidate models.
Evaluate metrics against business thresholds.
Compare against the current production model.
Register the approved model package.
Promote only when review and quality gates pass.
Monitor drift and performance after release.

The Datum project notes in this repo describe exactly that kind of platform direction: automated model training pipelines, A/B testing frameworks, drift detection alerts, model registry, versioning, and promotion. The business value was not that a model trained once. It was that data scientists could move new algorithms toward production without waiting on a DevOps ticket for every handoff.

Slowly Changing Dimensions Were Not Academic

SCD types sound like warehouse trivia until you build ML features.

Then they become the difference between a useful historical feature and a lie.

The common types are:

Type 0: keep the original value. Use it when history should never change, such as a creation date.
Type 1: overwrite the old value. Use it for corrections where historical state is not meaningful or not needed.
Type 2: preserve history with effective dates and a current flag. Use it when the value at a point in time matters.
Type 3: keep limited previous values in the same row. Use it rarely, usually for a small number of business-approved previous states.
Type 4: separate current state from historical state. Use it when operational reads and historical analysis need different physical shapes.

For ML, Type 2 is the one that shows up constantly. A customer segment, risk band, address region, employer category, account status, or product eligibility can change over time. If the training job uses today's dimension row for last year's event, the model sees the future.

That is leakage.

A Type 2 dimension gives the feature pipeline a point-in-time join:

select
    transaction.transaction_id,
    transaction.customer_id,
    transaction.business_date,
    customer_segment.segment_code
from fact_transaction as transaction
join dim_customer_segment as customer_segment
  on transaction.customer_id = customer_segment.customer_id
 and transaction.business_date >= customer_segment.valid_from
 and transaction.business_date < coalesce(customer_segment.valid_to, date '9999-12-31')
where transaction.business_date = date '{{ var("business_date") }}'

The extra columns are not ceremony:

valid_from date not null,
valid_to date,
is_current boolean not null,
record_hash varchar

They are how the system tells the truth about time.

dbt snapshots can support this pattern for source changes, and Iceberg table history can help with table-level evolution. They solve different layers of the same problem. SCD Type 2 models business entity history. Iceberg tracks table snapshots and metadata over time. Both matter, but they are not interchangeable.

The Daily Run Was A Chain Of Trust

The platform worked best when the daily run was treated like a chain of trust:

Raw partitions arrive.
Data quality checks confirm that the expected source window exists.
dbt builds staging models and tests assumptions.
Intermediate models build business concepts.
SCD logic preserves the histories that matter.
Feature tables are written into governed Iceberg tables.
Airflow records which date, code version, and tasks produced the run.
SageMaker Pipelines consumes the curated snapshot.
Training and evaluation produce model artifacts and metrics.
The registry records model versions and approval state.
Drift and monitoring signals report whether production behavior is changing.

When any step failed, the failure needed to be visible at the right level. Not hidden in a notebook. Not buried in a one-off script. Not dependent on the engineer who happened to remember the right terminal command.

That is what operational calm looks like.

The Judgment

The biggest lesson from this work is that serious MLOps is mostly data platform discipline.

SageMaker is important, but it is not the whole story. A model pipeline is only as trustworthy as the data contract feeding it. Athena is useful, but it is not enough without governed table formats and transformation ownership. dbt is powerful, but it needs orchestration and runtime boundaries. Airflow is valuable, but it should coordinate systems instead of becoming a pile of hidden business logic. awswrangler is practical, but it should be a bridge, not a place to bury every transformation.

In a banking environment, the boring parts are the product: roles, VPCs, table formats, stage boundaries, SCD history, daily orchestration, model registry, drift checks, and run evidence.

That is the work I enjoyed at Datum. Sitting with the messy business context, turning it into a system where every kind of work has a home, and making the path from raw data to production model feel controlled enough that the people using it can breathe a little easier.