OpenBI Lake
OpenBI Lake keeps tables as Parquet files with a catalog, DuckLake, on storage of your own: a folder, a share, S3, Azure or Google Cloud Storage. Queries write into it, models read from it, and every change is a version you can go back to.
Lakes have no view of their own: they open in windows. On the Home tab, OpenBI Lake has Get data from a lake…, Lake explorer…, New lake… and Run lake loads. Get data lists OpenBI Lake under Lakehouse, and Transform data has Save to lake.
Which lakes
- The project’s own lake, named “Lake”, in the project’s
.openbifolder, which git leaves out. Its first load makes it. - The model’s: lakes the model reads as data sources, and those its lake loads write.
- This computer’s: lakes you used before.
New lake… makes another, with its catalog as:
- A folder
- On this computer, its files beside the catalog.
- A catalog file (SQLite)
- Its files in a folder or storage of their own: a share, S3 or S3-compatible storage (MinIO, R2…), Azure Blob or Data Lake Storage, Google Cloud Storage.
- PostgreSQL
- A team’s lake, written from several computers.
Read a lake table
Get data from a lake… opens the OpenBI Lake window: choose a lake, find its tables (Table, Rows, Size, Changed), preview one at the latest version or an earlier one, and choose Load or Transform data. The query reads it in M:
OpenBI.Lake("Lake"){[Schema = "main", Item = "orders"]}[Data]OpenBI.Lake is OpenBI’s own, so a Power BI project kept working in Power BI can’t read a lake: make it an OpenBI project first (see Import from Power BI).
Save a query to a lake
In Transform data, Save to lake (or the query’s Destination) gives a query a lake table to write its rows into, each time its lake load runs: here, from the command line, or on OpenBI Server’s schedule. Five steps:
- Lake: which lake.
- Table: A new table (its schema,
mainto start with, and name) or A table the lake has. - How: Replace (the table becomes the query’s rows each time), Append (the rows are added), or Incremental (only the periods that changed are loaded again, by a date column).
- Columns: the query’s columns against the lake table’s, and whether new columns are added.
- Check and save: Check (dry run) checks the rows and the folding first; Save writes the destination and runs nothing; Save and run load now runs it too.
The destination is saved as the project’s lake load, in Fabric Dataflow Gen2’s format. Run lake loads runs every one and lists what each wrote. A lake outside this computer is written only after you say yes, once per project.
The Lake explorer
Lake explorer… opens a window per lake: its schemas and tables on the left, and on the right:
- Versions
- Every version of the lake: when, who, its message and what changed. Double-click one to see the table as it was then; Restore this version makes it current again as a new version, so the history is never rewritten.
- Files
- The Parquet files, their size and deletes.
- Settings
- The lake’s settings, to read.
Maintenance: Compact (small files into larger ones), Remove old versions… (older than 30 days, to start with), Clean files (files no version reads) and Upgrade (an older DuckLake’s catalog, keeping a copy). Each runs as a dry run first and asks before it changes anything.
Backups
Back up… in the Lake explorer backs a lake up to your own storage: Azure Blob Storage, Amazon S3 or S3-compatible, Google Cloud Storage, or A folder or NAS. Nothing goes to OpenBI: the backup goes from your machine to your storage.
- Where, then Sign in (a folder needs none).
- Backup key: Create a new key or Use a key I have. Each backup’s catalog is encrypted with it. A new key is shown once: keep it somewhere safe, apart from this machine and the backups.
- Test, then Back up now, or Back up every day at a time, keeping as many as you say.
Each backup is the lake at one version, its new files copied once. Verify decrypts a backup where it is, checks every file and restores it apart to compare. Restore… always makes a new lake, “<lake> (restored v<N>)”, never over one.