Hands-off SFTP-to-analytics for daily sales & stock data
An automated ELT pipeline that detects distributor files landing on SFTP, validates and homologates them, and lands analytics-ready data in S3 for real-time querying via StarRocks.
Distributors of corporate clients like Unilever upload sales and stock data to SFTP every day. The data had to be detected, validated, homologated and made query-ready in real time, with no manual handling and corrections reflected immediately.
Manual intervention
Data freshness
Cross-distributor consistency
A Dagster-orchestrated ELT flow. It extracts on file arrival, validates format and fields, loads RAW to PostgreSQL, transforms and homologates against history in dbt, stages, then publishes to S3 for StarRocks analytics.
Dagster detects SFTP arrivals and triggers extraction automatically.
Two-tier validation: initial format/field checks, then consistency checks against historical data.
dbt transforms and homologates product codes and units of measure for cross-distributor comparability.
Approved data lands in S3 and is queryable in real time through StarRocks.
CSV from distributors
Format + field checks
PostgreSQL
dbt homologation
Real-time analytics
Eliminated manual handling of daily distributor uploads end-to-end.
Unified product codes and units so all distributor data is comparable.
Gave clients an always-current view of sales and stock operations.
What it costs to automate a manual process, from a real build: where the money actually goes, what the estimate missed, and how to size the payback first.
How to schedule a Python script to run automatically: cron on Mac and Linux, Task Scheduler on Windows, the schedule library, and the gotchas that break them.
Dagster vs Airflow vs Prefect for ETL in 2026, compared on the one thing they disagree on: what a pipeline is made of. Notes from a 50M-records/day build.
Whether that is ongoing technical ownership, one independent read on a problem, or someone to run the project. Let's scope it — I reply within one business day.