Skip to main content

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.

KindUsual time in the filenameVINSTATUS_CODERows
Orders not yet produced, and orders in production010000mostly emptyLF03, LF05, LF06about 83,000
Produced vehicles, in transit and in dealer stock011500presentseveralabout 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_inventory is an external asset that represents the files in S3. Dagster does not materialize it.
  • mercedes/mbusa/transformed/mbusa_inventory has one row per vehicle in the files whose filename date is the partition date. Its key is po_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_inventory requests 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.