DEV Community: Roxana-Maria Haidiner

Open a SQLite database in VisuaLeaf and explore it step by step: browse tables, query and join records, read JSON values, export results to Excel, and create charts.

Roxana-Maria Haidiner — Wed, 14 Oct 2026 17:42:13 +0000

VisuaLeaf

Oct 14

How to Open a SQLite Database: Query and Explore Your Data

#sql #sqlite #database #softwaredevelopment

9 min read

What's New in MongoDB 9.0: Query Settings, Validation, and Resource Limits

Roxana-Maria Haidiner — Wed, 14 Oct 2026 17:41:15 +0000

A new database release is easier to understand when you run the same command on the old and new versions. Does it accept an option that was previously rejected? Does it enforce a rule differently? Can you complete a task that previously needed a workaround?

For this article, I used MongoDB 9.0.2 in Docker and kept my MongoDB 8.2.5 instance available for comparison. I connected to both through VisuaLeaf and used a streaming-platform dataset for the query examples.

This article covers the following changes.

Change	Before 9.0	What changed	Useful example
Query settings	Query settings were stored for matching query shapes.	Pass settings directly to one command.	Try an index choice for one execution, then inspect the Explain plan.
Constraint validation	Adding strict validation did not check existing documents retroactively.	Upgrading to `constraint` checks existing data against the validator.	Enforce valid subscription plans, prices, and currencies.
Query timeout	Timeouts could be supplied with individual commands.	Store a timeout for matching query shapes.	Put a time budget on a recurring reporting query.
Memory limit	Queries had individual stage limits, but no overall query memory cap.	An overall memory limit applies to a query operation.	Inspect the configured limit and memory-limit failure counter.
Time-series rename	`renameCollection` did not support time-series collections.	Rename a time-series collection directly.	Rename playback metrics and verify the settings and documents.

Reported performance improvements

MongoDB reports the following improvements in its internal benchmarks comparing 9.0 with 8.0:

Up to *35% faster find-one queries*.
Up to *30% faster update-one queries*.
Up to *20% higher throughput for transactional workloads*.
Up to *2× throughput on large instances*.

Results vary by workload, hardware, deployment, and configuration. My local tests below explore feature behavior; they do not measure these performance gains.

Connect to the test instance

I used a separate Docker container and data volume for the test. The original database stayed available on port 27017, while the test instance used port 27021.

In VisuaLeaf, I created a connection named *MongoDB 9.0 Test*, with host 127.0.0.1 and port 27021. This local test instance had authentication disabled. Use the authentication settings configured for your own server.

Connecting to the local MongoDB 9.0 test instance in VisuaLeaf.

Before running the examples, I checked the server version and feature compatibility version:

db.version()

db.adminCommand({
  getParameter: 1,
  featureCompatibilityVersion: 1
})

The server returned 9.0.2 and feature compatibility version 9.0.

I also restored a BSON dump through VisuaLeaf's Task Manager. The restore job completed, and the streaming-platform database was available for the query tests. Restoring data is part of the test setup, rather than a new MongoDB 9.0 feature.

Restoring a BSON dump to the MongoDB 9.0 test database using a VisuaLeaf task.

Try an index choice for one query

Let's suppose you want to check how a reporting query behaves with a particular index. You want to inspect that execution before deciding whether to apply a setting more broadly.

MongoDB 9.0 accepts querySettings directly in find, distinct, and aggregate commands. These settings apply to that execution and are not saved for future matching queries. Existing stored settings take precedence when both specify the same field.

For the first test, I queried paid payments in EUR with an amount of at least 10, sorted by payment date. The command allowed MongoDB to use the existing status_1 index:

db.getSiblingDB("streaming_platform_db").runCommand({
  find: "payments",
  filter: {
    status: "paid",
    currency: "EUR",
    amount: { $gte: 10 }
  },
  sort: { paid_at: -1 },
  limit: 5,
  singleBatch: true,
  querySettings: {
    indexHints: {
      ns: { db: "streaming_platform_db", coll: "payments" },
      allowedIndexes: ["status_1"]
    }
  }
})

On MongoDB 9.0.2, the command returned ok: 1 and five documents.

On 8.2.5, it failed because the command did not recognize the querySettings field.

MongoDB 8.2.5 rejects querySettings passed directly to a find command.

To check the index selection, I wrapped the same find command in explain and requested executionStats. VisuaLeaf displayed an IXSCAN using status_1, followed by FETCH and SORT.

The separate sort made sense: status_1 indexes the payment status, not the payment date. MongoDB examined roughly 5,900 keys and documents to return five results. This confirmed the index choice, but it did not establish that this was the best index for the query.

The Explain plan shows an index scan using status_1, followed by FETCH and SORT.

The useful part is being able to test a choice and inspect its consequences. A successful command alone does not tell you whether the query is efficient.

Use the same option in an aggregation

I also tested a five-stage aggregation: filter the payments, group by plan and payment method, calculate revenue, sort the groups, and return the top ten.

Aggregation results on MongoDB 9.0, showing revenue and payment counts by subscription plan and payment method.

The 9.0.2 command returned eight groups. The family/card group contained 194 payments, with an average of EUR 14.99 and total revenue of EUR 2,908.06. The family/Apple Pay group contained 193 payments and EUR 2,893.07 in revenue.

The pipeline stages themselves work in older MongoDB versions. The change demonstrated here is the command-level querySettings option.

MongoDB 9.0: The new constraint validation level

Schema validation already existed before MongoDB 9.0. You could require fields, restrict values, and reject invalid inserts or updates.

The new constraint level addresses existing data too. Adding strict validation does not retroactively check old documents. Upgrading an existing collection to constraint scans them and fails if any violate the validator.

For the test, I created mongodb9_checks.validated_plans with three requirements: a supported plan, a non-negative double price, and EUR currency.

db.getSiblingDB("mongodb9_checks").createCollection(
  "validated_plans",
  {
    validator: {
      $jsonSchema: {
        bsonType: "object",
        required: ["plan", "monthlyPrice", "currency"],
        properties: {
          plan: { enum: ["basic", "premium", "family"] },
          monthlyPrice: { bsonType: "double", minimum: 0 },
          currency: { enum: ["EUR"] }
        }
      }
    },
    validationLevel: "constraint",
    validationAction: "error"
  }
)

Creating validated_plans with constraint validation in MongoDB 9.0 returns ok: 1.

Creating a new collection with this level is straightforward. An existing collection needs MongoDB’s preparation procedure before upgrading.

A valid document looks like this:

{ plan: "family", monthlyPrice: 14.99, currency: "EUR" }

A negative price would fail validation under either strict or constraint. The difference concerns documents already stored: strict can leave old invalid documents in place, while upgrading to constraint cannot succeed until they satisfy the validator.

Inspect the rules in Schema Manager

I opened validated_plans in VisuaLeaf’s Schema Manager to inspect the saved validator. It displays the plan and currency enums, the price minimum, the required fields, and the active constraint level.

Inspecting the subscription validation rules and active constraint level in VisuaLeaf’s Schema Manager.

Change the rules after leaving constraint mode

I also tried changing the validator with collMod while constraint was active. MongoDB rejected the command, and VisuaLeaf displayed:

Validator cannot be changed when Validation level is 'constraint'.

MongoDB rejects a validator change through collMod while constraint validation is active.

To change the rules, first switch the collection to strict and apply that change separately. Then update the validator. Before returning to constraint, prepare the collection again; the upgrade will fail if any existing documents violate the updated rules.

Set a timeout for a recurring query

Before 9.0, you could pass maxTimeMS with an individual query. You can now save a timeout for a query shape, the query’s structure, so it applies to matching queries even when filter values change. The stored timeout takes precedence over one supplied in the command.

This gives a recurring payment report a shared time limit without every caller adding it. It stops queries that exceed the limit; it does not make them faster.

Stored query settings require a replica set or sharded cluster. I used a single-node Docker replica set named rs9.

I saved a 1 ms timeout for this payment report. This deliberately small limit demonstrates enforcement; for a real report, choose a timeout based on its normal execution time.

Saving a 1 ms timeout for the payment report’s query shape in MongoDB 9.0

Matching query shapes can involve very different amounts of work. A report for one day and a report for a full year may inherit the same timeout. Before choosing a shared budget, test several realistic filter values and compare execution times, documents examined, and how often requests complete within the limit. When investigating unexpected timeouts, check both the stored setting and the command’s maxTimeMS: the stored value takes precedence.

Run the report without adding a timeout

I then ran the matching aggregation with no maxTimeMS in the command:

db.getSiblingDB("streaming_platform_db").payments.aggregate([
  { $match: { status: "paid", currency: "EUR" } },
  { $group: {
    _id: "$subscription_plan",
    totalRevenue: { $sum: "$amount" },
    paymentCount: { $sum: 1 }
  } },
  { $sort: { totalRevenue: -1 } }
]).toArray()

MongoDB stopped the operation, and VisuaLeaf displayed:

operation exceeded time limit

The report inherited the stored budget from its matching query shape.

The payment report exceeds the stored 1 ms timeout, even without maxTimeMS in the command.

Remove the setting and rerun the same report

After capturing the error, I removed the test setting:

db.adminCommand({
  removeQuerySettings: {
    aggregate: "payments",
    pipeline: [
      { $match: { status: "paid", currency: "EUR" } },
      { $group: {
        _id: "$subscription_plan",
        totalRevenue: { $sum: "$amount" },
        paymentCount: { $sum: 1 }
      } },
      { $sort: { totalRevenue: -1 } }
    ],
    cursor: {},
    $db: "streaming_platform_db"
  }
})

After removing the setting, I reran the same aggregation, and it returned the revenue result.

The same payment report completes successfully after removing the stored timeout.

The report did not change between executions. The stored time budget changed whether it could complete.

Inspect the overall query memory limit

MongoDB 9.0 adds a total memory limit for a query operation. Its default is the greater of 1 GB or 20% of the memory available to the server process. The existing 100 MB per-stage limit and stage spill behavior remain unchanged.

I inspected two server metrics through VisuaLeaf:

var queryMetrics = db.getSiblingDB("admin")
  .runCommand({ serverStatus: 1 }).metrics.query;

({
  memoryLimitBytes:
    queryMetrics.configuredMaxMemoryUsageBytesPerOperation,
  operationsFailedDueToMemoryLimit:
    queryMetrics.operationsFailedDueToMemoryLimit
})

Result	MongoDB 9.0.2	MongoDB 8.2.5
Configured limit	1,608,660,582 bytes, approximately 1.50 GiB	Metric unavailable
Operations rejected for exceeding the limit	0	Metric unavailable

VisuaLeaf displayed the unavailable values on 8.2.5 as Null.

This is useful when investigating expensive reports or aggregations: you can see the configured cap and whether operations have hit it. The number belongs to this Docker setup, so do not expect every server to report the same limit.

This test checked the metrics. It did not deliberately run a query that exceeded the cap.

Rename a time-series collection without rebuilding it

MongoDB 9.0 adds support for renaming time-series collections with renameCollection.

I tested this with a small playback metrics collection. It used recorded_at as its time field, metadata as its meta field, and minutes granularity. The two documents recorded active stream counts and buffering rates for smart TVs in the EU.

The rename command was:

db.getSiblingDB("admin").runCommand({
  renameCollection: "mongodb9_checks.playback_metrics",
  to: "mongodb9_checks.streaming_metrics"
})

After renaming it, I checked both the collection settings and its documents:

db.getSiblingDB("mongodb9_checks")
  .getCollectionInfos({ name: "streaming_metrics" })

db.getSiblingDB("mongodb9_checks")
  .streaming_metrics.find().toArray()

The collection still reported type timeseries with the same time field, meta field, and granularity. Both documents remained: 1,250 and 1,320 active streams, with buffering rates of 0.8 and 0.6. VisuaLeaf also identified the collection with its TS badge.

Renaming the time-series collection preserves its settings and both documents in MongoDB 9.0.

This is useful when a collection name no longer describes its purpose. Here, you can rename the collection, and follow-up checks confirmed that its settings and data remained available.

Other MongoDB 9.0 changes

MongoDB 9.0 also expands query monitoring and encrypted searches. $queryStats collects statistics for both reads and writes by default, sampling 1% of operations.

Queryable Encryption now supports generally available prefix, suffix, and substring searches on encrypted strings. These features weren’t part of my local tests, but they are worth exploring for workload monitoring and applications that need searchable encrypted data.

Which changes are worth trying first?

If you troubleshoot queries, start with per-command query settings and inspect the Explain plan. If you need a guarantee about existing data, look at constraint validation. For reporting workloads, the timeout and memory controls are useful additions to understand. Time-series rename solves a smaller but concrete maintenance task.

These tests used MongoDB 9.0.2, feature compatibility version set to 9.0, and VisuaLeaf 1.0.2432 on a local Docker deployment. They demonstrate specific commands and results, not a performance benchmark or full compatibility certification.

Before upgrading a production deployment, review MongoDB’s compatibility changes and known issues for your target patch version.

Sources

MongoDB Sharding: Manage a Sharded Cluster Visually

Roxana-Maria Haidiner — Fri, 18 Sep 2026 06:34:08 +0000

MongoDB sharding helps a database handle growth by dividing a large collection into smaller blocks called chunks and distributing them across multiple shards. A shard key, such as customerId or region, determines how the documents are divided.

In this guide, I’ll show you how to connect an existing MongoDB sharded cluster to VisuaLeaf and manage it visually. You’ll see where your data is stored, how it is distributed, whether the balancer is running, and how to review or change the shard key.

For this example, I used a 405 MB orders collection distributed across two shards.

How a MongoDB sharded cluster is organized

A MongoDB sharded cluster has three core components:

Shards store subsets of the data. Each production shard should be a replica set.
**mongos** is the query router used by applications and database clients.
Config servers store the cluster metadata and configuration.

MongoDB divides sharded collections into chunks according to a shard key. The balancer can move those chunks between shards when MongoDB detects enough difference in data distribution.

MongoDB explains these roles in its documentation for sharded cluster components.

The important connection detail is mongos. To see and manage the complete cluster, connect VisuaLeaf to a mongos endpoint, not directly to an individual shard or config server.

Connect VisuaLeaf to an existing MongoDB sharded cluster

For my local test, mongos was exposed on port 27020, so I used:

mongodb://127.0.0.1:27020

For a remote environment, use its real mongos hostname, authentication, TLS settings, and required connection options. Do not expose credentials in screenshots.

Once the connection is active:

Right-click the MongoDB connection.
Select Sharding.

This opens the sharding controls for the complete cluster.

Check shards, collections, and cluster health

The dashboard immediately identifies the connection as a sharded topology. Its overview includes:

Shard names and hosts
Health status for every shard
Chunk counts
Sharded collections and their shard keys
Databases with sharding enabled
Global balancer status
Zone assignments

In the test cluster, shard1RS and shard2RS both reported OK. The application collection appeared as ecommerce_demo.orders, with { customerId: "hashed" } shown directly beside it.

The dashboard also listed config.system.sessions. That is an internal MongoDB collection, not part of the ecommerce application. This distinction matters when using collection-level actions: select ecommerce_demo.orders, not the system collection.

Inspect MongoDB chunk distribution

Expand ecommerce_demo.orders to see how the collection is distributed. VisuaLeaf shows the following values for each shard:

Chunks
Documents
Logical data size
Average document size
Jumbo chunks
Individual shard-key ranges

It also adds a visual distribution bar, which makes a large imbalance easier to notice than a block of shell output.

The original test contained 50,000 documents and about 14 MB of data. That was enough to verify the connection, but not a convincing example of a large collection. After adding more sample orders, the logical size reached approximately 405 MB.

The collection uses a hashed customerId shard key. Hashed sharding supports more even distribution because MongoDB distributes hashed values instead of placing similar source values next to each other. The trade-off is that range queries on the original field can become scatter-gather queries.

Do not expect both shards to contain the same number of documents. A small difference is normal. The more useful questions are whether the shards are healthy, whether data is distributed reasonably, and whether any chunks are marked jumbo.

Control the MongoDB balancer and chunks

The MongoDB balancer runs in the background and moves data when the difference between shards reaches MongoDB's migration thresholds. From the dashboard, you can see its current state, start or stop it, and configure a balancing schedule.

Collection-level controls are available separately. For ecommerce_demo.orders, the expanded view shows whether balancing and auto-merge are enabled. It also exposes actions for splitting, merging, and moving chunks.

These controls run real MongoDB administrative operations. Moving or splitting data can increase network, storage, and replication work, so don't use them just to make the distribution bars look equal. In a healthy cluster, the balancer should handle routine distribution.

MongoDB advises against leaving the balancer disabled for long periods because an uneven distribution can affect cluster performance. Teams can instead schedule balancing for a quieter period when migrations would have less impact. See MongoDB's balancer documentation for the current behavior and thresholds.

Review or change the MongoDB shard key

Collection size alone is not a reason to change a shard key. Resharding becomes useful when the existing key causes hotspots, distributes data unevenly, or does not match the application’s common queries.

MongoDB recommends checking cardinality, value frequency, monotonic growth, and query patterns when choosing a shard key.

In VisuaLeaf, you can use:

Shard Key Advisor to evaluate possible keys
The key icon beside a collection to open the resharding form

For this test, I changed the shard key from { customerId: "hashed" } to { orderId: "hashed" }. The form generated the MongoDB command before I ran it.

After starting the operation, VisuaLeaf displayed its progress directly in the sharding dashboard. MongoDB created a temporary system.resharding... collection and copied all 215,000 documents using the new shard key.

Once the operation finishes, the temporary collection disappears and ecommerce_demo.orders displays { orderId: "hashed" } as its shard key.

Changing a shard key can require significant time and temporary storage on a large production collection. Test the operation first and review MongoDB’s resharding requirements.

Monitor the MongoDB cluster during activity

The Sharding dashboard shows where the data lives. System Monitor shows what the connection is doing.

During the sample-data load, I opened System Monitor for the sharded connection. It displayed connections, new connection rate, network throughput, and request activity while documents were inserted through mongos.

This is useful when testing a load, observing balancing activity, or checking whether an administrative operation corresponds with increased requests or network traffic.

Where VisuaLeaf fits

VisuaLeaf works on top of an existing MongoDB sharded cluster. It does not provision shard replica sets, config servers, or mongos routers.

Once connected through mongos, you can visually inspect and manage the cluster—including sharding a collection, controlling the balancer, managing chunks and zones, and changing a shard key. VisuaLeaf simplifies these operations, but decisions about data distribution and shard-key selection still belong to the user.

Conclusion

In this test, I connected a two-shard MongoDB cluster to VisuaLeaf, checked how a 405 MB collection was distributed, and changed its shard key from customerId to orderId. I could follow the entire resharding operation from the same workspace, including its progress and effect on the cluster.

If you already manage a MongoDB sharded cluster and want a more visual workflow, download VisuaLeaf and connect it to your mongos endpoint.

How to Use DuckDB for Local Data Analysis

Roxana-Maria Haidiner — Thu, 03 Sep 2026 07:11:52 +0000

You have data on your computer and want to explore it with SQL. It might be a CSV export, an existing database, or files produced by another analytical tool. Setting up a database server just to answer a few questions adds work before you have learned anything from the data.

DuckDB offers a simpler option for this kind of local analysis. It runs directly on your computer, stores data in a local file, and lets you query external files without importing everything first.

This guide follows the workflow we tested on Windows: create a persistent database, open it in a visual client, inspect and edit its data, query CSV and Parquet files, view an ER diagram, and chart the results. It also covers a limitation we found while updating a nested value on a table with a primary key.

Browsing Parquet-backed event data beside a monthly revenue chart.

What Is DuckDB?

DuckDB is an in-process analytical database. It runs inside the application using it rather than as a separate database server. It is built mainly for analytical queries that scan, filter, join, group, and summarize many rows. These are often called OLAP workloads.

You can save tables and views in a persistent .duckdb file, or create an in-memory database for temporary work. A persistent file remains after the application closes. An in-memory database disappears when the process ends. In a normal local setup, there is no host, port, username, or password to configure. You open a file path and start working with SQL. The official connection guide explains both modes.

The database can also run inside applications written in Python, R, Java, Node.js, and other languages through its client APIs. This does not mean those languages run inside a normal SQL editor. The application connects to the database and sends SQL through a library; the editor itself still executes SQL.

How Is It Different from Traditional Databases?

MySQL and PostgreSQL normally run as database servers. An application connects through a host and port and authenticates with a user account. That model suits applications with many users, frequent writes, user permissions, and continuous transactional traffic.

With an embedded database, you can open one local file from a command-line tool, programming language, or GUI. Use a server when several users or applications must write continuously. Use a local OLAP database when your main work is reading, joining, grouping, and summarizing data.

When Should You Use DuckDB?

It is a good fit when you need to:

inspect a CSV export from another application;
analyze one or more Parquet files;
prototype an analytical query before moving it into a larger pipeline;
create a small persistent database for local reporting;
add analytical SQL to a Python, R, or Java application.

For our test, we created a retail analytics database containing customers, products, orders, order items, and clickstream events. It gave us primary keys, foreign keys, constraints, nested data types, and files that we could query alongside the tables.

How Do You Create and Open a Local Database?

After downloading the Windows command-line client, pass it the path where you want to store the database:

& "C:\Program Files\DuckDB\duckdb.exe" "C:\DuckDB\visualeaf_demo.duckdb"

If the file does not exist, this command creates it. If it already exists, the same command opens it. Use a file instead of :memory: when you want to keep your tables, views, and data between sessions.

To open the same database in VisuaLeaf, create a connection and select:

C:\DuckDB\visualeaf_demo.duckdb

No server credentials are required. After connecting, refresh the sidebar to browse the schemas, tables, views, columns, constraints, and indexes available in the file.

Connecting to a local DuckDB database file in VisuaLeaf without configuring a database server.

How Can You Browse and Edit DuckDB Data with a GUI?

Query results are easier to inspect in a data grid than in terminal output, especially when a table contains many columns. You can open a table, sort or filter its records, compare values, and move through the results without writing a new query for every action.

You can also edit values inline or add a row directly from the grid. This works well for small corrections and test records. For bulk changes or operations you need to repeat, use an UPDATE or INSERT statement instead.

DuckDB table data displayed in the VisuaLeaf data grid

VisuaLeaf’s SQL Editor executes SQL only. Applications written in Python, R, or Java connect through their own DuckDB libraries; you cannot paste those languages into the SQL editor. The DuckDB GUI client page covers the complete visual interface.

How Do You Inspect Tables, Keys, and Data Types?

Before writing a query, it helps to check the table definition. In our orders table, order_id is an INTEGER primary key, customer_id is a foreign key, ordered_at uses the TIMESTAMP type, and total_amount is stored as DECIMAL(12,2).

The table editor also shows nullability, default values, indexes, and constraints. These details tell you what values a column accepts and whether it connects to another table.

Inspecting the orders table, column types, primary key, foreign key, and constraints.

The schema also uses nested data types. customers.profile is a STRUCT containing country and segment, while customers.interests is a VARCHAR[] list. You can access both with SQL:

SELECT
    customer_id,
    full_name,
    profile.country AS country,
    profile.segment AS segment,
    unnest(interests) AS interest
FROM main.customers
WHERE list_contains(interests, 'analytics')
LIMIT 20;

STRUCT and LIST keep related values together, but updating them can expose an index limitation. In our test, changing interests on a row with a primary key returned a duplicate-key error even though customer_id had not changed. Running the same update through SQL returned the same error, confirming that the visual editor did not cause it.

The documentation explains that some updates are processed as a delete followed by an insert, which can cause a constraint to be checked too early. The workaround is to delete and reinsert the row in separate transaction steps. Do not remove a valid primary key to make the edit succeed. See the index limitations before updating indexed rows with nested values.

How Do You Query Parquet and CSV Files Directly?

A CSV file stores rows as plain text. It is easy to open and widely supported, but it does not preserve database types reliably. Parquet is a column-based format designed for analytical data. It stores type information and can read only the columns a query needs. You do not need Parquet to start, but it becomes useful with larger analytical exports or data pipelines that already produce it.

DuckDB can query both formats without first copying them into permanent tables. read_parquet() opens a Parquet file as a queryable relation. read_csv_auto() reads a CSV file and detects its columns and types.

SELECT *
FROM read_parquet('C:/DuckDB/events.parquet')
LIMIT 10;

SELECT *
FROM read_csv_auto('C:/DuckDB/orders.csv', header = true)
LIMIT 10;

The query below summarizes clickstream events from events.parquet, summarizes completed sales from orders.csv, and joins the two monthly results:

WITH parquet_activity AS (
    SELECT
        date_trunc('month', occurred_at)::DATE AS month,
        count(*) AS events,
        count(DISTINCT customer_id) AS active_customers,
        round(avg(properties.engagement_score), 1) AS avg_engagement
    FROM read_parquet('C:/DuckDB/events.parquet')
    WHERE occurred_at < TIMESTAMP '2025-11-01'
    GROUP BY ALL
),
csv_sales AS (
    SELECT
        date_trunc('month', ordered_at)::DATE AS month,
        count(*) FILTER (WHERE status = 'completed') AS completed_orders,
        round(
            sum(total_amount) FILTER (WHERE status = 'completed'),
            2
        ) AS revenue
    FROM read_csv_auto('C:/DuckDB/orders.csv', header = true)
    WHERE ordered_at < TIMESTAMP '2025-11-01'
    GROUP BY ALL
)
SELECT
    p.month,
    p.events,
    p.active_customers,
    p.avg_engagement,
    c.completed_orders,
    c.revenue
FROM parquet_activity AS p
JOIN csv_sales AS c USING (month)
ORDER BY p.month;

One SQL query combines monthly activity from _events.parquet_ with revenue from _orders.csv_.

Parquet supports projection and filter pushdown, so the engine can avoid reading columns or row groups that the query does not need. The file-format guide also explains when repeatedly queried data may perform better after loading it into database tables. For CSV, automatic detection is convenient, but you should specify column types if the inferred result is wrong.

Can You Visualize Tables and Relationships in an ER Diagram?

Our relational path is:

customers → orders → order_items → products

Primary keys identify each customer, order, product, and order item. Foreign keys connect orders.customer_id to customers.customer_id, while order_items.order_id and order_items.product_id connect each line to its order and product.

The Retail Analytics Schema showing how customers, orders, order items, and products connect.

VisuaLeaf reads the existing keys and draws the relationships in an ER diagram. This makes the path from a customer to an order and its products easier to follow than reading separate CREATE TABLE statements. The events table remains outside that transactional path because it records customer activity rather than an order or product relationship.

How Do You Turn Query Results into Charts?

Query results can also become charts. In this example, we used the main.monthly_revenue view, selected channel as the category, and calculated the sum of revenue. The chart shows each channel’s share of total revenue across the selected period.

Mobile and partner sales generated the largest shares of revenue, while store and web contributed smaller shares. Charts make these differences easier to notice, but you should still verify the underlying SQL, filters, and aggregation settings.

Comparing total DuckDB revenue across mobile, partner, store, and web sales channels.

Should You Use the CLI or a GUI?

Use the CLI for quick queries, repeatable scripts, and lightweight automation. Use a programming-language client when the database is embedded in a Python, R, Java, or another application. Use a GUI when you need to discover a schema, browse wide results, inspect data types, save SQL, view an ER diagram, or build charts while keeping the underlying data visible.

These options are not mutually exclusive. A persistent file can be created from the CLI, queried from an application, and opened visually. Just avoid concurrent writes from separate processes unless your connection pattern supports them.

Frequently Asked Questions

Do I need to convert a CSV file to Parquet first?

No. Query the CSV directly with read_csv_auto(). Consider Parquet when the dataset is large, you repeatedly read selected columns, or your existing workflow already produces it.

Do I need to import a Parquet file into a table?

No. You can query it directly with read_parquet(). Loading it into a table can still help when you run many repeated queries and want database statistics and storage optimizations.

Conclusion

DuckDB is useful when your data is local, your workload is analytical, and a traditional server would add more setup than value. You can keep data in one persistent file, query CSV and Parquet directly, inspect nested and relational structures, verify foreign keys, and turn SQL results into charts.

Start with one real question about your data. Open the database or source file, inspect its columns, write the smallest query that answers the question, and verify the result before adding another layer. That workflow works whether you prefer a command line, an application, or a visual client.

Want to try the same workflow with your own DuckDB database? Download VisuaLeaf to open your .duckdb file, explore its data, run SQL queries, view relationships, and create charts in one workspace.

Run mongodump from PowerShell, automate MongoDB backups in VisuaLeaf, schedule recurring tasks, inspect BSON files, and verify them with mongorestore.

Roxana-Maria Haidiner — Thu, 03 Sep 2026 07:08:38 +0000

VisuaLeaf

Aug 31

mongodump Tasks Automate and Schedule MongoDB Backups

#mongodb #nosql #database #sql

8 min read

A practical PostgreSQL JSONB workflow for querying nested values, updating individual fields, and indexing containment searches with GIN.

Roxana-Maria Haidiner — Tue, 18 Aug 2026 11:22:12 +0000

VisuaLeaf

Aug 18

PostgreSQL JSONB: Query, Update, and Index JSON Data

#postgres #postgressql #database #sql

5 min read

Design a MongoDB schema from scratch or reverse-engineer an existing database visually. Define fields, relationships, validators, and materialize it.

Roxana-Maria Haidiner — Thu, 06 Aug 2026 12:33:29 +0000

VisuaLeaf

Aug 6

MongoDB Schema Design: Build or Reverse Engineer Visually

#mongodb #database #javascript #software

8 min read

See how VisuaLeaf, MongoDB Compass, and Studio 3T handle large collections, compare data, automate tasks, and analyze queries, and which one fits best.

Roxana-Maria Haidiner — Tue, 04 Aug 2026 07:57:54 +0000

VisuaLeaf

Aug 4

Which MongoDB Client Handles Large Collections Best: VisuaLeaf vs Compass vs Studio 3T

#mongodb #javascript #database #software

8 min read

Learn how to copy a MongoDB collection to PostgreSQL, map nested fields to JSONB, and keep inserts, updates, and deletes in sync.

Roxana-Maria Haidiner — Tue, 28 Jul 2026 07:48:17 +0000

VisuaLeaf

Jul 28

How to Copy and Sync a MongoDB Collection to PostgreSQL

#mongodb #postgres #database #software

5 min read

Learn how to import JSON orders into MongoDB, copy them into a main collection, export a weekly CSV report, and mask customer data during export using VisuaLeaf Task Manager.

Roxana-Maria Haidiner — Thu, 23 Jul 2026 09:21:34 +0000

VisuaLeaf

Jul 23

How to Import JSON into MongoDB and Export to CSV with Data Masking

#mongodb #database #software #softwaredevelopment

8 min read

Learn how to copy a local MongoDB collection to MongoDB Atlas and keep inserts, updates, and deletes synchronized with Mongo Sync.

Roxana-Maria Haidiner — Tue, 21 Jul 2026 11:25:52 +0000

VisuaLeaf

Jul 20

How to Copy and Sync a Local MongoDB Collection to Atlas

#mongodb #database #software #javascript

7 min read

Learn 7 common MongoDB query mistakes with simple find() examples, including $or, $in, nested fields, dates, and text search.

Roxana-Maria Haidiner — Tue, 14 Jul 2026 09:42:37 +0000

VisuaLeaf

Jul 14

7 MongoDB Query Mistakes That Return the Wrong Results

#mongodb #coding #software #database

5 min read