SQL for Datasets: Join, Merge & Dedupe Files

SQL for Datasets runs one SQL query over your Apify datasets and your CSV, JSON or Parquet files. Join two datasets like a VLOOKUP, merge many runs into one table, remove duplicates, filter, group and sort. The Actor uses DuckDB, a fast analytical SQL engine. Results go to a dataset, or to a CSV, JSON, JSON Lines or Parquet file.

Three jobs people use it for

• Look up values from one dataset in another (VLOOKUP). Pick the join preset, name the key column, and get one table with columns from both.
• Merge a week of scraper runs into one table. Add each run's dataset ID and pick the merge preset.
• Drop rows you already have. Turn on Only new rows and schedule the Actor. Each run saves only the rows that are new.

SQL for datasets: what it does

• Reads Apify datasets, files in your key-value stores, and CSV, TSV, JSON, JSON Lines or Parquet files from URLs.
• Gives each input a table name: dataset1, file1, record1, or a name you choose.
• Runs any DuckDB SELECT query: joins, GROUP BY, window functions, QUALIFY, PIVOT, UNION ALL BY NAME and more.
• Runs several statements in one query. The last statement must return rows.
• Offers presets that need no SQL: merge all inputs, remove duplicates, and join two inputs by key.
• Only new rows mode: a scheduled run saves only rows that earlier runs did not save.
• Saves rows to the dataset, a file, or both.

SQL inputs and table names

Input	Table names	Example
Apify datasets	dataset1, dataset2, ...	The results of another Actor
File URLs	file1, file2, ... or name = URL	orders = https://example.com/orders.csv
Key-value store records	record1, record2, ... or name = key	prices = prices.parquet

The Actor detects the format from the file's first bytes. CSV delimiters and column types are detected too. CSV files in Latin-1 (older Excel exports) are read as well. XLSX files are not read. Convert them to CSV first, for example with the Spreadsheet Converter Actor.

Other input options

Input	Default	What it does
Key-value store with files	None	A key-value store of yours that holds CSV, JSON or Parquet files. The Actor only reads this store. Field keyValueStoreId
Record keys	None	Keys of the records to read from that store. They become record1, record2 and so on. Write name = key to choose the table name. Field keyValueStoreKeys
Save rows to the dataset	On	Saves each result row as one dataset item, so you can export it as JSON, CSV, Excel or XML. Field saveToDataset
Maximum input size (MB)	200	Stops when one input file or dataset is larger than this. Field maxFileMegabytes
Query time limit (seconds)	300	Stops the query when it runs longer than this. Field queryTimeoutSecs

Use it after another Actor

1. Run any scraper on Apify and wait for it to finish.
2. Open the run, go to the Storage tab, and copy the Dataset ID.
3. Start SQL for Datasets and paste that ID into Apify datasets.
4. Pick a preset or write a query. The scraper's field names are your column names.

How to run SQL on an Apify dataset

1. Pick one or more datasets in Apify datasets, or paste links in File URLs.
2. Write your query in SQL query. Use the table names above.
3. Set Maximum rows to 0 to save every row. The default of 100 keeps test runs cheap.
4. Optional: pick a file format in Also save a file.
5. Click Start. Open the dataset, or the Result file tab.

Example query over two datasets:

SELECT a.url, a.title, b.price
FROM dataset1 a
LEFT JOIN dataset2 b USING (url)
WHERE b.price IS NOT NULL
ORDER BY b.price DESC

SQL presets: merge, remove duplicates, join

Preset	What it does
Merge all inputs	Stacks every input into one table. Columns match by name. A source_table column says where each row came from
Remove duplicates	Keeps the first row for each value of Key columns. Without key columns it removes rows that are equal in every column
Join two inputs (VLOOKUP)	Adds the columns of the second input to the first one where Key columns match. Left, inner, full or right join. Rows keep the order of the first input
Join, anti	Keeps the rows of the first input that have no match in the second input. Only the first input's columns are returned

Turn on Add match column to get a _matched column with each joined row. It is true when the row found a match in both inputs.

Example: find the leads that are missing from your CRM export. Put the leads dataset first and the CRM file second.

{
    "preset": "join",
    "joinType": "anti",
    "keyColumns": ["email"],
    "datasetIds": ["<leads dataset ID>"],
    "fileUrls": ["https://example.com/crm-export.csv"]
}

Output

Each result row becomes one dataset item. The fields are the columns your query returns. Rows from a test run of the sample query, GDP per person from two public CSV files:

{
    "country": "Luxembourg",
    "country_code": "LUX",
    "year": 2023,
    "gdp_per_person_usd": 128678
}

{
    "country": "Ireland",
    "country_code": "IRL",
    "year": 2023,
    "gdp_per_person_usd": 102725
}

Dates come out as 2026-10-19. Timestamps come out in UTC. Nested JSON stays nested.

The run also writes an OUTPUT record to its key-value store. It holds the status, the final SQL, each input table with its columns, the result columns, the number of rows saved, and a link to the result file. When the query has an error, the run ends with the error in its status message and in OUTPUT.

SQL sandbox and limits

• The query can read only the inputs you list. It cannot open other files, reach the network or install DuckDB extensions. Settings are locked.
• File URLs must be public http or https links. Local and private network addresses are refused. The Actor obeys robots.txt and does not log in.
• Each input can be up to 200 MB by default (setting up to 2,000 MB). Inputs are saved to disk, so they can be larger than the run's memory.
• DuckDB gets part of the run's memory and spills large sorts and joins to disk. A very large query can still run out of memory. Filter and aggregate early in the query.
• Each download or API request waits at most 60 seconds and is retried twice. The query stops after the time limit you set (300 seconds by default).

Pricing

You pay per event:

• $0.02 per run.
• $0.30 per 1,000 output rows ($0.0003 per row), saved to the dataset or a file. A row saved to both counts once.
• $0.004 per 1,000 items read from Apify datasets. Each started block of 1,000 items counts as one. Reading file URLs and key-value store records is free.

A run that reads one dataset of 20,000 items and saves 5,000 rows costs $1.60. Rows that the query filters out are not charged as output rows. Set a maximum cost per run in the run options. The Actor then stops at that limit.

Only new rows

Turn on Only new rows and schedule the Actor. Each run saves only rows that earlier runs with the same Monitor name did not save. Give Key columns to compare rows by those columns only, for example a URL or an ID. Without key columns the whole row is compared. The memory sits in the named key-value store sql-over-datasets-state. Inputs that still send the old field stateKey keep working.

Run it on a schedule

1. Set the same inputs you tested, and turn on Only new rows.
2. Give Key columns (for example an ID or a URL) and a Monitor name for this watch list.
3. Create a Schedule in Apify and pick this Actor and its saved input.
4. The first run saves every row. Each later run saves only rows with a new key.

FAQ

Which SQL dialect does it use? DuckDB SQL. It is close to PostgreSQL. Functions such as regexp_extract, strftime, list_aggregate and json_extract work.

Can I read an Excel file? No. Convert it to CSV with the Spreadsheet Converter Actor, then run SQL on the CSV.

Does my data leave Apify? No. The query runs inside the Actor's container. The temp files are deleted at the end of the run.

Why do I get only 100 rows? The default Maximum rows is 100. Set it to 0 to save every row.

Support

Open an issue on the Actor's Issues tab. Please include the run ID and your query.