How to Join CSV Files with Database Tables Using DuckDB

Every analyst I know has a folder of CSVs that "should be in the database but aren't." Budget targets from finance. Territory mappings from sales ops. An employee list from HR that gets emailed around as an attachment every quarter.

These files contain data you need for real analysis, but they sit outside your database. So you end up copy-pasting into spreadsheets or writing Python glue scripts to merge them with production data. It's tedious, it's fragile, and you redo it every month.

DuckDB fixes this. It queries CSV files directly with SQL — no import step, no schema definition, no ETL pipeline. And it joins those files with live Postgres tables in a single query.

Querying a CSV with SQL

SELECT department, month, budget_amount
FROM read_csv_auto('budget_targets_2026.csv')
WHERE budget_amount > 50000
ORDER BY month;

read_csv_auto does what it says. Point it at a file, get a table back. DuckDB sniffs the first few thousand rows to detect column names, types, delimiters, and quoting. No configuration needed for well-formatted files.

You don't load anything. The CSV stays on disk. DuckDB reads it on demand.

The join: budget vs. actuals

Here's where it gets useful. Say finance gives you budget_targets_2026.csv with columns department, month, and budget_amount. You want to compare those targets against actual revenue in your Postgres orders table.

Attach your Postgres database and join directly:

ATTACH 'dbname=analytics user=app host=db.prod.internal' AS pg (TYPE POSTGRES);
 
SELECT
    b.department,
    b.month,
    b.budget_amount,
    COALESCE(a.actual_revenue, 0) AS actual_revenue,
    COALESCE(a.actual_revenue, 0) - b.budget_amount AS variance
FROM read_csv_auto('budget_targets_2026.csv') b
LEFT JOIN (
    SELECT
        department,
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount_cents) / 100.0 AS actual_revenue
    FROM pg.public.orders
    WHERE status = 'completed'
    GROUP BY 1, 2
) a ON a.department = b.department
   AND a.month = b.month::DATE
ORDER BY b.month, b.department;

That's a CSV from someone's laptop joined with a production Postgres table. One SQL statement. No intermediate staging table, no pandas merge, no dbt model. The LEFT JOIN and COALESCE ensure departments with zero orders still show up with a negative variance instead of vanishing from the result.

If you've been doing this by exporting both sides to CSV and VLOOKUPing in Excel, stop.

Messy CSVs

Clean CSVs are the exception. Most files from finance tools, CRM exports, or vendor platforms have at least one problem.

SELECT *
FROM read_csv('territory_mapping.csv',
    delim = ';',
    header = true,
    skip = 2,
    columns = {
        'rep_name': 'VARCHAR',
        'region': 'VARCHAR',
        'quota': 'DOUBLE',
        'start_date': 'DATE'
    },
    dateformat = '%d/%m/%Y',
    encoding = 'latin1'
);

Note this uses read_csv (not read_csv_auto) because you're overriding the auto-detection. When you know the file is messy, be explicit.

A few gotchas that will bite you:

UTF-8 BOM. Files exported from Excel on Windows often start with a byte order mark (three invisible bytes: EF BB BF). DuckDB handles this automatically, but if you pipe through other tools first, you might end up with a phantom character in your first column name. If SELECT department fails but SELECT * LIMIT 1 shows the column right there, a BOM is your problem.

Date formats. Is "01/02/2026" January 2nd or February 1st? DuckDB defaults to American format (MM/DD/YYYY). If your data comes from anywhere outside the US, set dateformat = '%d/%m/%Y' explicitly. The dangerous thing: getting this wrong doesn't throw an error. It silently swaps days and months until day 13, when it finally fails. By then you've built a dashboard on corrupted dates and nobody noticed.

Numeric columns with commas. A column containing "1,234.56" parses as VARCHAR, not DOUBLE. You can set decimal_separator and thousands_separator parameters, or cast in your query: REPLACE(revenue, ',', '')::DOUBLE.

Windows line endings. Most tools handle \r\n fine, but if you're reading a file that was FTP'd from an ancient system, you might get \r embedded in your last column's values. The new_line parameter lets you force a specific line terminator.

Multiple files at once

Got a folder of monthly exports? Glob patterns work how you'd expect:

SELECT
    filename,
    COUNT(*) AS row_count,
    SUM(amount) AS total_sales,
    MIN(order_date) AS earliest,
    MAX(order_date) AS latest
FROM read_csv_auto('sales_*.csv', filename = true)
GROUP BY filename
ORDER BY filename;

The filename = true parameter adds a column showing which file each row came from. Useful when sales_q1.csv and sales_q2.csv have overlapping date ranges and you need to track down duplicates.

Recursive globs work too: read_csv_auto('data/**/*.csv') scans every CSV in every subdirectory.

Beyond CSV: Parquet and JSON

CSV is human-readable but slow. On a 2GB file, read_csv_auto spends 8-12 seconds parsing text because it reads every byte. Parquet stores data in a columnar binary format with embedded statistics, so DuckDB can skip entire row groups and read only the columns your query references. Same data, 10-50x faster. I wrote a deeper comparison of DuckDB vs Pandas that covers the performance angle in more detail.

-- Parquet: columnar, fast, handles large files well
SELECT customer_id, SUM(amount) AS total
FROM read_parquet('transactions_2026.parquet')
WHERE region = 'EMEA'
GROUP BY customer_id;
 
-- JSON: for API exports, webhook logs, nested data
SELECT
    response->>'status' AS status,
    COUNT(*) AS request_count
FROM read_json_auto('api_responses/*.json')
GROUP BY 1;

If you control the format, use Parquet. If you're receiving files from external teams, you're stuck with whatever they send. DuckDB reads both the same way.

Excel files

DuckDB doesn't read .xlsx natively. The spatial extension includes an XLSX reader (bundled there for historical reasons):

INSTALL spatial;
LOAD spatial;
SELECT * FROM st_read('headcount_q3.xlsx');

This works for simple, flat spreadsheets. For anything with merged cells, multiple sheets, or formula-dependent values, export to CSV first. The spatial extension reader wasn't built for the kind of formatting chaos that real-world Excel files contain.

The practical workflow

Here's how this plays out in practice:

1. Finance publishes budget targets in Google Sheets.
2. You export as CSV, or connect Sheets directly as a queryable source.
3. DuckDB joins the spreadsheet data with your production Postgres.
4. Budget-vs-actuals in one result set. No warehouse, no staging tables, no waiting for a dbt run.

The friction point is portability. Your queries reference local files nobody else has. Your Postgres credentials live in your environment. Close the laptop and the analysis is gone.

Fastero's managed DuckDB store fills that gap. Upload the CSV or connect your sources, and the same joins run in the cloud — scheduled, shared, and accessible to your team. Same SQL syntax, no local dependencies, nothing to install.

Try Fastero free — join CSV, Parquet, and JSON files with your database tables in managed DuckDB. No installs, no warehouse. No credit card required.