MBUSA Inventory Source (S3)
MBUSA uploads inventory files over SFTP, and they are stored in S3 under
s3://ai-mercedes-sftp-transfer/mbusa-user/incoming/prod/. Files arrive on most days,
but not every day.
Files
Every file is named S44_MBUSA_Inventory_Data_<YYYYMMDDHHMMSS>.csv. Its fields are
separated by |. Its first line is a header row. Every file has the same 23 columns.
Values are padded with spaces.
There are two kinds of file. Each file is a full snapshot of the vehicles of its kind. A date has at most one file of each kind.
| Kind | Usual time in the filename | VIN | STATUS_CODE | Rows |
|---|---|---|---|---|
| Orders not yet produced, and orders in production | 010000 | mostly empty | LF03, LF05, LF06 | about 83,000 |
| Produced vehicles, in transit and in dealer stock | 011500 | present | several | about 121,000 |
On most dates only one of the two files arrives. The filename does not identify the kind.
PO_NUMBER is non-empty and unique within each file. VEHICLE_TYPE is always empty.
ASSIGN_DATE has the format YYYYMMDD. SCHEDULED_PROD_DATE has the format
YYYYMMDDHHMMSS. A date of all zeros means no date.
An order that leaves production between the extraction of a date's two files appears in
both files. The 010000 file has the order with FACTORY_STATUS INPRODUCTION. The
011500 file has the order with its later status, such as FINALINSPTN.
Assets
mercedes/mbusa/raw/mbusa_inventoryis an external asset that represents the files in S3. Dagster does not materialize it.mercedes/mbusa/transformed/mbusa_inventoryhas one row per vehicle in the files whose filename date is the partition date. Its key ispo_number. When an order is in more than one file of the date, the asset keeps its row from the file with the latest filename timestamp. The partition of a date with no file materializes with zero rows.- The sensor
mercedes_mbusa_mbusa_inventoryrequests a run of the transformed asset for each date that has a file. It requests another run of a date when the date's file count changes. The sensor's cursor is the newest partition date with a file. Each tick lists the files whose filename date is three days before the cursor or later, so the sensor also requests a run of an earlier date whose file arrives late.
The transformed asset converts the option-code columns OPTIONS, PACKAGES, PAINTS,
STANDARD_ACCESS and UPHOLSTERY into feature_refs the same way as netstar inventory's
option arrays.
Access
Dagster reads the objects under the prefix with the ECS task role
UserCodeExecutionRole. The role's mbusa-sftp-inventory-read policy in
deployments/aws/cloudformation/ecs-agent-vpc-private.yaml grants s3:GetObject on the
prefix and s3:ListBucket limited to it. Locally, the default boto3 credential chain
applies. Any AWS credentials with the same two permissions work.