Loading Data into a Fabric Warehouse with COPY INTO and the Pipeline Copy Activity: Bulk Ingestion from Parquet and CSV Files in OneLake

Introduction

Imagine you've just finished building a Fabric Warehouse with a clean schema — dimension tables, fact tables, the whole structure — and now you're staring at a folder full of Parquet files that your upstream data engineering team drops into OneLake every night. The files are there. The warehouse is ready. But how do you actually get data from point A to point B efficiently, at scale, without writing a Spark job or pulling everything through memory?

This is a genuinely common situation. Most Fabric projects involve two distinct phases: schema design and bulk ingestion. The design phase gets a lot of attention in tutorials, but ingestion is where production systems live or die. If you're loading hundreds of millions of rows from partitioned Parquet files or CSV exports, you need approaches that are fast, reliable, and operationalizable — meaning you can schedule them, monitor them, and troubleshoot them when something goes sideways at 2 AM.

By the end of this lesson, you'll know how to load data into a Fabric Warehouse using two complementary approaches: the COPY INTO T-SQL statement, which lets you run bulk loads directly from a SQL script, and the Pipeline Copy Activity, which wraps that same power inside a visual, schedulable pipeline. You'll understand when to use each, what the real gotchas are with Parquet and CSV files, and how to verify that your data actually landed correctly.

What you'll learn:

• How COPY INTO works in a Fabric Warehouse and why it's faster than row-by-row inserts
• How to target files in OneLake — including wildcard patterns for partitioned datasets
• How to handle CSV quirks like headers, delimiters, and encoding in your COPY INTO syntax
• How to build a Pipeline Copy Activity that points at OneLake files and writes to a warehouse table
• How to verify row counts and spot data type mismatches before they become downstream problems

---

Prerequisites

Before diving in, you should be comfortable with the following:

• You have a Fabric workspace with an active capacity. If you're still getting that set up, start with Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace.
• You have a Fabric Warehouse created and at least one table defined with a schema that matches your source files. If you need to build one first, see Building a Fabric Data Warehouse with T-SQL: Tables, Views, and Cross-Database Queries.
• You have source files — Parquet or CSV — stored somewhere in OneLake, either in a lakehouse Files section or uploaded directly. The article on OneLake Explained: One Copy of Data, Delta Tables, and Shortcuts will give you the grounding on how OneLake is organized if this is new territory.
• You're comfortable reading basic T-SQL. You don't need to be an expert, but you should recognize CREATE TABLE, SELECT, and WHERE clauses.

---

What Is COPY INTO and Why Does It Matter?

COPY INTO is a bulk load statement — the same conceptual animal as SQL Server's BULK INSERT or Snowflake's own COPY INTO command. Instead of inserting rows one at a time through a normal INSERT statement, COPY INTO reads entire files and loads them into a table in a highly parallelized, optimized way. The warehouse engine doesn't have to parse every row through the same path as a transactional insert; it can read and distribute the work across multiple threads.

The practical difference is significant. A well-tuned COPY INTO against a properly formatted Parquet file can load tens of millions of rows in minutes rather than hours. This matters in two scenarios: initial historical loads where you're seeding a warehouse with years of data at once, and recurring overnight batch loads where files accumulate throughout the day and need to be pulled in during a maintenance window.

In Microsoft Fabric specifically, COPY INTO reads files from OneLake using the https:// URL format that OneLake exposes. This means your source files can be sitting in a lakehouse's Files section, in a OneLake folder you've organized yourself, or even pointed at via a shortcut — and COPY INTO can reach them as long as you have the right path.

---

Understanding the OneLake File Path

Before writing any COPY INTO statement, you need to know the URL format for your files. OneLake exposes every item through a predictable HTTPS path:

https://onelake.dfs.fabric.microsoft.com/{workspace-name}/{item-name}.Lakehouse/Files/{folder-path}

For example, if your workspace is called SalesDataPlatform, your lakehouse is RawIngestion, and your files are in a folder called orders/2024/, the path would look like:

https://onelake.dfs.fabric.microsoft.com/SalesDataPlatform/RawIngestion.Lakehouse/Files/orders/2024/

You can find the exact URL for any file or folder in the Fabric portal. Navigate to your lakehouse, find the file or folder in the Files section, right-click on it (or use the ellipsis menu), and select "Properties." The URL will be listed there — copy it directly rather than constructing it by hand, especially if your workspace name has spaces or special characters.

COPY INTO also supports wildcard patterns. If your files follow a naming convention like orders_2024_01.parquet, orders_2024_02.parquet, and so on, you can use:

https://onelake.dfs.fabric.microsoft.com/SalesDataPlatform/RawIngestion.Lakehouse/Files/orders/*.parquet

This is enormously useful for partitioned datasets where a new file is written every day or every month. Instead of listing each file explicitly, you define a pattern and let the engine discover which files match.

---

Loading Parquet Files with COPY INTO

Parquet is the ideal format for COPY INTO because the schema is embedded in the file itself. Parquet is a columnar binary format — meaning data is stored column by column rather than row by row — which makes it highly efficient for both storage and analytical reads. When you load Parquet into a warehouse table, the engine can verify that column data types align with your table schema.

Here's a concrete example. Suppose you have a table in your warehouse called fact_sales with this structure:

CREATE TABLE fact_sales (
    sale_id         BIGINT,
    customer_id     INT,
    product_id      INT,
    sale_date       DATE,
    quantity        INT,
    unit_price      DECIMAL(10, 2),
    total_amount    DECIMAL(12, 2),
    region_code     VARCHAR(10)
);

And you have a folder of Parquet files at Files/sales/2024/ in your lakehouse. The COPY INTO statement looks like this:

COPY INTO fact_sales
FROM 'https://onelake.dfs.fabric.microsoft.com/SalesDataPlatform/RawIngestion.Lakehouse/Files/sales/2024/*.parquet'
WITH (
    FILE_TYPE = 'PARQUET'
);

That's genuinely the minimal form for Parquet. Because Parquet files carry their own schema, you don't need to specify column order, delimiters, or encoding. The engine reads the schema from the file header and maps columns to your table by name.

If your Parquet files have columns that don't exist in the target table, those columns are simply ignored. If your table has columns that don't exist in the Parquet files, those columns will be filled with NULL (as long as the column allows nulls). This is forgiving, but can silently produce incomplete data if you're not paying attention.

---

Loading CSV Files with COPY INTO

CSV files require more explicit configuration because they have no embedded schema. You have to tell COPY INTO everything: how fields are delimited, whether there's a header row, what the encoding is, and how to handle text qualifiers (the quotes that surround string fields).

Here's a realistic CSV load scenario. Your finance team exports a daily accounts receivable file called ar_export_20241115.csv with this structure:

invoice_id,customer_name,invoice_date,due_date,amount_usd,status
10001,"Contoso Ltd","2024-11-01","2024-11-30",4500.00,"Open"
10002,"Fabrikam Inc","2024-11-02","2024-12-02",12750.50,"Paid"

Your warehouse table accounts_receivable is:

CREATE TABLE accounts_receivable (
    invoice_id      INT,
    customer_name   VARCHAR(200),
    invoice_date    DATE,
    due_date        DATE,
    amount_usd      DECIMAL(12, 2),
    status          VARCHAR(50)
);

The COPY INTO statement for this file:

COPY INTO accounts_receivable
FROM 'https://onelake.dfs.fabric.microsoft.com/SalesDataPlatform/RawIngestion.Lakehouse/Files/finance/ar_export_20241115.csv'
WITH (
    FILE_TYPE       = 'CSV',
    FIRSTROW        = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    FIELDQUOTE      = '"',
    ENCODING        = 'UTF8'
);

Let's walk through each option:

• FILE_TYPE = 'CSV' — tells the engine to treat this as delimited text rather than binary Parquet
• FIRSTROW = 2 — skips the header row. If your CSV has no header, set this to 1
• FIELDTERMINATOR = ',' — defines the column separator. Tab-delimited files would use '\t'
• ROWTERMINATOR = '\n' — defines where rows end. Windows line endings use '\r\n'
• FIELDQUOTE = '"' — specifies the text qualifier. Without this, a value like "Contoso Ltd" would include the quotation marks as part of the string
• ENCODING = 'UTF8' — handles extended characters. Finance exports from European systems often have accented characters that require this

---

Column Mapping in COPY INTO

Sometimes your source file has columns in a different order than your target table, or it has more columns than you want to load. COPY INTO supports an explicit column list to handle this:

COPY INTO accounts_receivable
    (invoice_id, customer_name, invoice_date, due_date, amount_usd, status)
FROM 'https://onelake.dfs.fabric.microsoft.com/SalesDataPlatform/RawIngestion.Lakehouse/Files/finance/ar_export_20241115.csv'
WITH (
    FILE_TYPE       = 'CSV',
    FIRSTROW        = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    FIELDQUOTE      = '"',
    ENCODING        = 'UTF8'
);

For CSV specifically, the column list maps by position — the first column in your list corresponds to the first field in the CSV. This is important: if your CSV has six columns and you specify a list of six columns, the mapping is strictly positional.

---

Building the Pipeline Copy Activity

The COPY INTO statement is powerful for ad hoc loads and for running inside SQL scripts, but in a production data platform you almost always want ingestion triggered automatically — on a schedule, after an upstream process completes, or as part of a larger pipeline. That's where the Pipeline Copy Activity comes in.

The Copy Activity in Fabric Data Pipelines is a visual wrapper around data movement. You configure a source (where the data comes from), a sink (where it goes), and column mappings in a point-and-click interface. Under the hood, it uses the same high-throughput engine as COPY INTO, but it integrates with pipeline scheduling, activity chaining, error handling, and monitoring.

Here's how to build it step by step:

Step 1: Open a new Data Pipeline

In your Fabric workspace, click "New item" and select "Data pipeline." Give it a descriptive name like load_fact_sales_from_parquet.

Step 2: Add a Copy Activity

On the pipeline canvas, click "Add pipeline activity" and select "Copy data." A Copy Data block will appear on the canvas. Click on it to open its configuration panel at the bottom.

Step 3: Configure the Source

In the configuration panel, click the "Source" tab. Click "New" next to the connection dropdown to create a new connection, and choose "OneLake files" (or "Microsoft OneLake") as the connector type.

In the source settings, you'll configure:

• Connection: Select your workspace and lakehouse from the dropdown menus
• File path: Navigate to the folder containing your Parquet files. To use a wildcard, type the folder path manually and enable the "Wildcard file path" option. Enter the folder as the directory path (e.g., sales/2024) and the wildcard as the file name pattern (e.g., *.parquet)
• File format: Select "Parquet" from the format dropdown

Step 4: Configure the Sink

Click the "Sink" tab. Click "New" and select "Warehouse" as the connector type. Choose your workspace and the specific warehouse item from the dropdowns.

For the sink, set:

• Table: Select the target table (e.g., fact_sales) from the dropdown, or type the schema-qualified name like dbo.fact_sales
• Write method: Choose "Copy command" — this is the option that triggers the high-performance COPY INTO path rather than a row-by-row insert approach
• Copy behavior: For a full table reload, choose "Overwrite" if you want to truncate first, or "Append" to add rows to existing data

Step 5: Column Mapping

Click the "Mapping" tab. If you click "Import schemas," Fabric will read the Parquet file's schema and your warehouse table's schema and attempt to auto-map columns by name. Review the mapping carefully — any column that appears with a blank source or blank destination means it won't be loaded.

Step 6: Run and Validate

Click "Run" in the toolbar (or save and then click "Debug") to execute the pipeline. The activity will show a progress indicator, and when it completes, you'll see a summary showing rows read, rows written, and elapsed time. Rows read should equal rows written for a clean load.

After the pipeline completes, open a new query window on your warehouse and verify:

SELECT COUNT(*) AS total_rows FROM dbo.fact_sales;

SELECT TOP 10 * FROM dbo.fact_sales ORDER BY sale_date DESC;

---

Verifying Your Load and Catching Silent Failures

One of the most important habits to build is post-load verification. A COPY INTO or Copy Activity can complete successfully from the engine's perspective — no errors thrown, exit code zero — and still produce a table with bad data. Here are three checks to run every time.

Row count reconciliation: Before loading, if you know the source file row count, compare it to what landed in the table. For Parquet files, you can get a row count from within a Spark notebook using a quick df.count(). For CSV, a simple line count (minus the header) should match your COUNT(*) in the warehouse.

NULL audit: Check columns that should never be null:

SELECT 
    SUM(CASE WHEN sale_id IS NULL THEN 1 ELSE 0 END)      AS null_sale_id,
    SUM(CASE WHEN sale_date IS NULL THEN 1 ELSE 0 END)    AS null_sale_date,
    SUM(CASE WHEN total_amount IS NULL THEN 1 ELSE 0 END) AS null_total_amount
FROM dbo.fact_sales;

If any of these return non-zero counts, you have a column mapping problem, a schema mismatch, or a data quality issue in the source.

Value range spot check: For numeric and date columns, check that the ranges are sane:

SELECT 
    MIN(sale_date)      AS earliest_sale,
    MAX(sale_date)      AS latest_sale,
    MIN(total_amount)   AS min_amount,
    MAX(total_amount)   AS max_amount,
    AVG(total_amount)   AS avg_amount
FROM dbo.fact_sales;

Negative amounts where you expect only positives, dates in 1970 (a classic epoch conversion failure), or averages that are orders of magnitude off from what you'd expect — all of these are red flags that catch real problems before they propagate into downstream reports.

---

Hands-On Exercise

This exercise walks you through loading a realistic dataset end-to-end. You'll need a Fabric workspace with a warehouse and a lakehouse, with at least F2 capacity (or a trial).

Scenario: You're a data engineer at a retail company. Your e-commerce platform generates daily order exports as CSV files and monthly product catalog snapshots as Parquet. Your job is to load both into the RetailWarehouse.

Part 1: Prepare the files

Create a lakehouse called IngestionStaging if you don't have one. In the Files section, create two folders: orders/ and products/.

Create a sample CSV file called orders_nov2024.csv with this content (save it as UTF-8):

order_id,customer_id,order_date,product_id,quantity,unit_price
5001,1042,2024-11-01,887,2,29.99
5002,1087,2024-11-01,312,1,149.00
5003,1042,2024-11-02,994,3,9.99
5004,2201,2024-11-03,887,1,29.99

Upload this file to the orders/ folder in IngestionStaging.

Part 2: Create the target tables

Open a SQL query window in your warehouse and run:

CREATE TABLE dbo.stg_orders (
    order_id    INT,
    customer_id INT,
    order_date  DATE,
    product_id  INT,
    quantity    INT,
    unit_price  DECIMAL(10, 2)
);

Part 3: Load with COPY INTO

In the same query window, run:

COPY INTO dbo.stg_orders
FROM 'https://onelake.dfs.fabric.microsoft.com/{your-workspace}/IngestionStaging.Lakehouse/Files/orders/orders_nov2024.csv'
WITH (
    FILE_TYPE       = 'CSV',
    FIRSTROW        = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    FIELDQUOTE      = '"',
    ENCODING        = 'UTF8'
);

Replace {your-workspace} with your actual workspace name or GUID.

Part 4: Verify the load

SELECT COUNT(*) FROM dbo.stg_orders;          -- Should return 4
SELECT * FROM dbo.stg_orders ORDER BY order_id;  -- Review all rows

Part 5: Build the pipeline

Create a new Data Pipeline called load_stg_orders. Add a Copy Activity, set the source to the orders/ folder with a *.csv wildcard, configure the sink to write to dbo.stg_orders using the Copy command write method. Run it.

Note: running the pipeline after your COPY INTO will append 4 more rows (since you chose Append). Confirm your row count is now 8, then run a TRUNCATE TABLE dbo.stg_orders to reset and test an Overwrite run.

---

Common Mistakes & Troubleshooting

"File not found" errors when the file clearly exists Double-check the OneLake URL character by character. Spaces in workspace or lakehouse names must be URL-encoded as %20, or you can use the workspace GUID instead of the name. Also verify the lakehouse suffix — it must be .Lakehouse (capital L), not .lakehouse.

All rows load but date columns are NULL Parquet date columns sometimes store as INT32 (days since Unix epoch) rather than a native date type, depending on the tool that wrote the file. The warehouse may not auto-convert this. You can work around it by loading the column as INT into a staging table, then transforming it using DATEADD(day, date_column_as_int, '1970-01-01') in a follow-up INSERT INTO to your final table.

CSV load produces garbled data in the last column of every row You almost certainly have a \r\n line ending but specified \n as ROWTERMINATOR. Change it to \r\n and re-run.

Pipeline Copy Activity completes but zero rows were written Check the Mapping tab — if no columns are mapped, the activity runs without error but copies nothing. Click "Import schemas" and verify that at least one column has both a source and destination mapping.

COPY INTO fails with a data type conversion error mid-load The load is not transactional for partial file errors in all configurations. Check whether your target table has rows that loaded before the failure — you may need to TRUNCATE before re-running after fixing the source file. For production pipelines, consider loading to a staging table first, validating, then doing a final INSERT INTO ... SELECT FROM into the production table.

---

Summary & Next Steps

You now have two reliable tools for bulk-loading files into a Fabric Warehouse: COPY INTO for SQL-driven, scriptable loads, and the Pipeline Copy Activity for scheduled, monitored, visually managed pipelines. Both approaches leverage the same high-throughput engine under the hood — the choice between them is really about where the load fits in your broader workflow.

For Parquet, the experience is clean: schema inference handles most of the heavy lifting, and the main thing to watch is column name alignment. For CSV, you earn your keep by configuring delimiters, encodings, and row terminators precisely — but once you get it right, the same configuration runs reliably for months.

The verification habits — row count checks, NULL audits, and value range spot checks — are not optional extras. They're what separate a data engineer from someone who just ran a script and hoped for the best.

From here, there are a few natural directions to explore:

• If you're building a layered data architecture where raw files flow through bronze, silver, and gold stages before landing in the warehouse, the Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers article will show you how those layers work together.
• Once your warehouse tables are populated, you can expose them to Power BI without importing the data again — the Direct Lake Mode in Power BI: How It Works and When to Use It over Import and DirectQuery article covers how to connect reports directly to warehouse data.
• For scheduling and automating the pipelines you've built here — adding retries, alert emails, and monitoring — see Orchestrating Loads with Fabric Data Pipelines: Copy Activities, Parameters, and Schedules.