How to get invoice line item detail from the Lago Data Pipeline export

The Data Pipeline export splits an invoice across two tables. Invoice-level fields land in invoices, and each line item lands as its own row in fees. To get line item detail you query fees and join it back to invoices. The one thing to get right is the join key: in the export every id column is prefixed with lago_, so a fee links to its invoice through lago_invoice_id, not invoice_id.

How the two tables relate

invoices holds one row per invoice with the header fields: lago_id, number, issuing_date, total_amount_cents, and the other invoice-level totals. fees holds one row per line item, and every fee carries a lago_invoice_id pointing back to the invoice it belongs to. Both tables are in the default synced set, so they are present without any extra configuration.

The naming is consistent across the export. The invoice's own key is lago_id, and every foreign key that references it follows the lago_<entity>_id pattern. That is why the fee side is lago_invoice_id while the invoice side is lago_id. Mixing the two conventions, for example joining f.invoice_id to i.lago_id, is the usual reason a join fails with a "column does not exist" error.

Querying line item detail

Select the invoice fields you want and join each fee to its invoice on the lago_ keys:

SELECT
  i.number,
  i.issuing_date,
  i.total_amount_cents,
  f.*
FROM invoices i
JOIN fees f ON f.lago_invoice_id = i.lago_id;

Each row in the result is one line item, carrying its invoice's number, issuing date, and total alongside the fee's own columns. If your warehouse shows these columns without the lago_ prefix (plain id and invoice_id), confirm the exact names against information_schema.columns for the two tables and adjust the join to match.

This article reflects guidance drawn from customer case resolutions. It is not officially supported documentation and may not apply to all situations.