Skip to main content
The data lake is a stored copy of your ledger built for reports. Insights runs your SQL against that copy. Your ledger is the system of record. Every write lands there first: a posted transaction, a new balance, a new identity. A lake builder then copies those records into daily history, one day at a time. When you run a query, Insights scans only the days inside the date range you pick in the toolbar. That range decides how much history the engine opens. A WHERE filter on created_at can drop rows inside those days. It does not choose which days to open.

From a ledger write to a query result

Keep these lake contracts in mind when you write SQL:
  1. Daily history is append-only. Insights opens every day file in your toolbar date range. Files are never updated in place.
  2. Transactions are lifecycle rows. Core stores each status change as its own row (QUEUED, INFLIGHT, APPLIED, and so on). Filter by status, and use parent_transaction when you need commit or void outcomes. See Transaction lifecycle.
  3. Balances, ledgers, and identities can repeat across days. The same ID may appear in more than one day file. Before you join them to transactions, collapse to one row per ID (for example GROUP BY balance_id with ANY_VALUE(...)). Do not order by created_at to pick a “latest” wallet snapshot: created_at is when the wallet was created, so daily copies can tie and DuckDB can pick any copy.
  4. Current wallet holdings belong on the live ledger. The lake is for history and reports. For live balance amounts, use the Data API or Ledger API.
  5. Parent and child rows can fall on different days. Inflight commits and voids create new rows. Set a date range wide enough to include both the parent and its outcomes, or the report can miss a resolution.
  6. Use TRY_CAST on dates and amounts. See Use TRY_CAST on dates and amounts.
To call the same lake SQL from your backend, use the Data lake API.

​
Need help?

We are very happy to help you make the most of Blnk, regardless of whether it is your first time or you are switching from another tool. To ask questions or discuss issues, please contact us or join our Discord community.