Check the schema, pagination, object counts, invoice details, relationship filters, and output columns.
Key takeaways
- Inspect the source schema before pulling data.
- Check pagination limits.
- Qualify reported counts and record the exact scope of any count you report.
- Distinguish between top-level and line-item identifiers.
- Mark undocumented fields as unknown in your checklist.
Chargebee notes that if consolidated invoicing is enabled, the attribute subscription_id is unavailable when the invoice has line items from multiple subscriptions.
Invoice extract reconciliation: the checks to make
The six checks separate extraction limits from invoice identity. First, inspect the database schema and pagination before extraction. Second, distinguish object count limits from invoice details during extraction. Third, inspect relationship filters and output columns after extraction. These steps define the reconciliation scope for quarter-end evidence.
An invoice extract serves as evidence for bookings reconciliation, but it does not prove a deal qualifies as a booking or recognized revenue. Read Bookings reconciliation: 5 checks for quarter close with this extract. The checklist isolates specific data points: schema synchronization, pagination parameters, object count accuracy, transaction list limits, subscription identifiers, relationship filter propagation, and calculated output columns. Each check addresses a distinct failure mode in the extraction process.
For the subscription identifier check, note that consolidated invoicing affects data availability. Individual subscription identifiers remain accessible under line_items.subscription_id. This distinction matters when mapping invoice rows to subscription records in the reconciliation model.
Before extraction: inspect the database schema and pagination
Start by verifying the connection to your source database before pulling any records. Metabase documentation states that you can sync the database schema and rescan field values, and edit connection details. This synchronization step ensures the query engine recognizes the current structure of the tables you are extracting from. If the schema has changed since the last connection, the extract may miss columns or return errors. Treat schema synchronization as a distinct prerequisite from the data retrieval process itself.
Next, review the pagination parameters for the list endpoint you are using. Stripe specifies a limit on the number of objects to return, ranging between 1 and 100. This parameter controls how many records appear in a single response.
When retrieving subsequent pages, use the identifier of the last object from the previous response. For example, if you make a list request and receive 100 objects, ending with obj_foo, your subsequent call can include starting_after=obj_foo to fetch the next page of the list. This method ensures you continue from the correct position in the dataset.
Do not combine forward and backward pagination cursors in a single request. You can use either the starting_after or ending_before parameter, but not both simultaneously. Choose one direction to navigate the list based on your extraction needs.
Confirm that your extraction script respects these limits and cursor rules before running the full query.
During extraction: distinguish count limits from invoice details
Treat the reported object count as a qualified figure, not a complete inventory. Stripe states that the total number of objects matching the query is only accurate up to 10,000. When your reconciliation query returns a total exceeding this threshold, the number provided by the API may not reflect the true count of all matching records. Record the scope of this documented limit in your evidence file. Do not use the count as an unlimited control total for the entire dataset. Instead, note the exact threshold where accuracy is no longer guaranteed. This distinction matters when you compare the extracted rows against the reported total. Forecast extract validation: 5 checks for missing rows covers a related row check. If the total is above the limit, flag the count as potentially inaccurate in your reconciliation notes.
Inspect the invoice details separately from the pagination limits. Chargebee documents a list of up to 20 transactions for this invoice. This specific limit defines the maximum number of transaction entries returned in the invoice object. It does not mean an invoice can contain only that many transactions in reality. It defines what the API returns in a single response. The distinction between charges, adjustments, payments, discounts, and taxes remains important within the invoice details. Each transaction type carries different implications for revenue recognition. Keep these categories distinct when mapping invoice lines to booking records. Deal and quote line-item rules from 4 publishers is a related line-item check.
After extraction: inspect relationship filters and output columns
Review the relationship filters and output columns after you extract the data. These checks verify how the query connects tables and how it names the calculated results.
Relationship filter propagation
A model relationship propagates filters applied on the column of one model table to a different model table, according to Microsoft. Relationship paths are deterministic, meaning that filters are always propagated in the same way and without random variation. In the Model relationships in Power BI Desktop documentation, the Year table filter propagates to further filter the Sales table. This results in just the one sales row that is for products assigned to category Cat-A and that was ordered in year CY2018.
Unmatched rows in the relationship
In the same documentation, a new row is added to the Sales table, and it has a production identifier value (9) that has no matching value in the Product table. This illustrates a row where the identifier does not match the related table. When reviewing your reconciliation query, check for rows where the invoice identifier or subscription identifier has no matching record in the related table.
Calculated output columns
If an arbitrary value expression is used in the select list, it conceptually adds a new virtual column to the returned table, according to PostgreSQL Global Development Group. The value expression is evaluated once for each result row, with the row's values substituted for any column references. The entries in the select list can be assigned names for subsequent processing, such as for use in an ORDER BY clause or for display by the client application.
Invoice detail enumeration
The Invoices | Chargebee API documentation states that it enumerates all the charges, adjustments, payments, discounts and taxes associated with the sale, according to Chargebee.
Checklist for this section
- Verify that relationship filters propagate as expected.
- Check for rows with identifiers that have no matching value in the related table.
- Confirm that calculated output columns are named for subsequent processing.
- Verify that the invoice details enumerate all charges, adjustments, payments, discounts and taxes.
- Locate the individual subscription ids under line_items.subscription_id.
Invoice evidence reference table by publisher
The table below maps seven reference rows to the documented rule for each area, and the subscription identifier row is part of the invoice details check. Use it to verify that your extraction settings match the publisher's stated limits before you treat the extract as evidence.
| Publisher | Check Area | Documented Rule or Limit |
|---|---|---|
| Metabase | Database schema | You can sync the database schema and rescan field values, and edit connection details. |
| Stripe | Pagination | This specifies a limit on the number of objects to return, ranging between 1 and 100. |
| Stripe | Object count | The total number of objects that match the query, only accurate up to 10,000. |
| Chargebee | Invoice detail | A list of up to 20 transactions for this invoice. |
| Chargebee | Subscription identifier | If consolidated invoicing is enabled, the attribute subscription_id is unavailable when the invoice has line items from multiple subscriptions. |
| Microsoft | Relationship filter | The Year table filter propagates to further filter the Sales table, resulting in just the one sales row that is for products assigned to category Cat-A and that was ordered in year CY2018. |
| PostgreSQL Global Development Group | Output column | If an arbitrary value expression is used in the select list, it conceptually adds a new virtual column to the returned table. |
Review each row against your current extraction query. If a cell describes a limit you have not tested in your environment, mark that field as unknown in your checklist. Do not assume a limit applies to a different endpoint or a different publisher than the one named in the row. The Stripe rows apply specifically to the pagination interface documented there; other endpoints may use different count fields or interfaces. The Chargebee rows apply to the invoice object as documented; the transaction list limit describes the API response structure, not a hard cap on the number of transactions an invoice can hold in the system. The Microsoft row illustrates filter propagation in a model example; the specific category and year values are illustrative, not benchmarks. The PostgreSQL row describes how calculated columns work in a select list; use this distinction when reviewing your reconciliation query to ensure you are naming the output columns explicitly.
Mark an undocumented field
Review each row against the specific publisher documentation for your data source. Do not assume that a rule from one platform applies to another. Mark a field as "unknown" if the documentation does not explicitly state the limit or behavior. This review supports the reconciliation process by identifying where evidence is missing. Complete the review before finalizing the forecast-to-bookings reconciliation.
Illustrative example of one extract page
You request one list page and receive 100 invoice rows. That page uses a return limit of 100, inside the documented range of 1 to 100. The reported total of matching objects is 10,000, so you record the total as accurate up to 10,000 and you flag the count only if it rises above that limit. One invoice in the page shows a list of 20 transactions. You note the list holds up to 20 transactions, then you read the charges, adjustments, payments, discounts and taxes within the invoice details. You also look for a line-item subscription identifier when consolidated invoicing is enabled and the invoice has line items from multiple subscriptions.
Next step
Take one existing quarter-end invoice extract and mark undocumented or untested fields as unknown. Compare those marks with the reference table above.
FAQ: invoice evidence in a bookings reconciliation
How do I check an invoice extract for reconciliation?
Run the six checks in sequence: database schema, pagination, object counts, invoice details, relationship filters, and output columns. Start with the schema to confirm field definitions match your source system, then verify pagination settings to ensure you captured the full record set. Check object counts against expected totals, inspect invoice details for missing identifiers, review relationship filters for unintended narrowing, and finally confirm output columns carry the correct calculated values.
Does the missing subscription identifier rule apply to my Chargebee consolidated invoices?
Yes, if consolidated invoicing is enabled and the invoice contains line items from multiple subscriptions, the top-level subscription_id attribute is unavailable. Chargebee documents this specific limitation for consolidated invoices. In that scenario, individual subscription identifiers appear under line_items.subscription_id instead of the invoice-level attribute.
Can I use both Stripe pagination cursors in one request?
No, you can use either the starting_after or ending_before parameter, but not both simultaneously. Stripe explicitly prohibits combining these two cursor directions in a single request. Choose the direction that matches your extraction strategy and supply only that parameter.
What happens to a Power BI total when relationship filters narrow the sales rows?
Filters propagate deterministically through the model relationships, narrowing the sales rows to those matching the filter criteria. Microsoft illustrates this with a category-and-year filter that reduces the sales table to only rows matching both conditions. The resulting total reflects only the filtered subset, not the unfiltered dataset.
How can I name a calculated PostgreSQL output column?
Assign names to select list entries for subsequent processing, such as for use in an ORDER BY clause or for display by the client application. PostgreSQL Global Development Group documents this naming capability for calculated columns. Use explicit output names when reviewing reconciliation queries to ensure downstream tools reference the correct fields.