Book a free call

Tinitiate Data Engineering

Databricks ELT:
S3 Landing Zone → Snowflake Analytics

Internal operational systems (ERP, Oracle DB) land raw in an AWS S3 zone. Databricks validates records against master data management rules, cleanses and standardizes them, then loads a Snowflake stage. Snowflake merges the stage into analytical tables that feed Power BI reporting and GenAI chat agents — the whole pipeline orchestrated end-to-end by AWS MWAA.

3Source Systems
3Databricks Stages
2Consumption Channels

ETL Pipeline Diagram

1. S3 Landing Zone → Databricks → Snowflake

  • Source — internal operational systems: ERP and Oracle DB (systems A1, A2, A3)
  • Landing — raw records land in an AWS S3 landing zone
  • Transform — Databricks validates for MDM, cleanses & standardizes, then loads a Snowflake stage
  • Target — Snowflake analytical DB tables, served to Power BI reporting and GenAI chat agents
  • Orchestration — AWS MWAA (Managed Workflows for Apache Airflow) schedules and triggers every stage

Databricks ELT — S3 to Snowflake

Node detail

Click a node

Select any box in the diagram to see what it does and how it connects.

Pipeline Walkthrough

How the data flows

Step 01

Extract from Source Systems

ERP and Oracle DB — the internal operational systems of record (A1, A2, A3) — are extracted on a scheduled batch interval triggered by AWS MWAA.

ERP + Oracle DB
Step 02

Land in S3

Raw extracts are written untouched into an AWS S3 landing zone, preserving an immutable copy of source data before any transformation happens.

Amazon S3
Step 03

Validate for MDM

A Databricks job checks incoming records against master data management rules — identity resolution, required fields, and reference-data integrity — before anything moves downstream.

Databricks
Step 04

Cleanse & Standardize

Validated records are cleansed and standardized — consistent formats, types, and naming — so every downstream table follows the same conventions regardless of source system.

Databricks + Spark
Step 05

Load to Snowflake Stage

Cleansed output is written into a Snowflake internal stage, ready for a bulk COPY INTO load without re-touching the Databricks cluster.

Snowflake Stage
Step 06

Load Command to Analytical Tables

A Snowflake LOAD command merges the staged data into the analytical DB tables that power reporting and AI consumption.

Snowflake SQL
Step 07

Serve Power BI & GenAI Agents

Analytical tables feed Power BI dashboards for human reporting and GenAI chat agents for natural-language, self-service access to the same data.

Power BI + GenAI

Technology Stack

Skills & services powering the pipeline

ERP System
Internal operational source
Oracle DB
Internal operational source
Amazon S3
Landing zone storage
Databricks
MDM validation & cleansing
Snowflake
Analytical DB tables
AWS MWAA
Orchestration (Airflow)
Power BI
Business reporting
GenAI Agents
Conversational analytics

Architecture Insights

Why this approach works

 📦

One Landing Zone, Many Sources

ERP and Oracle DB both land in the same S3 zone in their raw form, so new internal systems can be onboarded without changing the transformation layer.

 ✅

MDM Gate Before Anything Downstream

Validation runs before cleansing and load, so bad or unresolved records are caught at the earliest possible point instead of polluting analytical tables.

 ✨

ELT, Not ETL

Databricks handles validation and standardization, then hands off to Snowflake’s own compute for the final load and merge — each engine does the part it’s best at.

 🔁

Orchestrated End-to-End with MWAA

A single AWS MWAA DAG schedules extraction, triggers the Databricks job, and fires the Snowflake load command — one source of truth for pipeline timing and retries.