A file for a bank or regulator

Banks, regulators and partners send a layout spec: this column is 10 characters, right-aligned and zero-filled, that amount has an implied decimal point and a trailing sign. You keep the SQL plain and put the layout in YAML, where it can be read and reviewed.

Recorded from a real run: the command, then the file on the SFTP server.

↓ Download the file (payments-20261001.dat)

reports/bank_payments/bank_payments.yml

queries: [payment_lines]
output:
  format: fixed_width
  extension: dat
  header: true
  line_ending: "\n"
  columns:
    - {name: order_id, width: 10, align: right, pad: "0", header: ORDER}
    - {name: order_date, width: 8, date_format: "%Y%m%d", header: DATE}
    - {name: sku, width: 8, truncate: true, header: SKU}
    - {name: qty, width: 4, header: QTY}
    - {name: unit_price, picture: "9(6)V99", header: PRICE}
    - {name: net_amount, width: 12, align: right, pad: "0", decimals: 2, decimal_point: implied, sign: trailing, header: NET}
    - {name: discount_pct, width: 3, align: right, pad: "0", null_fill: " ", header: DSC}
  destination:
    profile: sftp_password
    path: "/upload/payments-{{ run.date.yyyymmdd }}.dat"

reports/payments/payment_lines.sql

select
    l.order_id,
    o.order_date,
    l.sku,
    l.qty,
    l.unit_price,
    -- refunds are negative, to exercise the sign options
    case when o.status = 'refunded' then -1 else 1 end * l.qty * l.unit_price * (100 - l.discount_pct) / 100.0 as net_amount,
    nullif(l.discount_pct, 0) as discount_pct
from order_lines l
join orders o using (order_id)
order by l.order_id, l.line_no
limit 50

terminal

dre run bank_payments