You need to import a 20 GB CSV file into SQL Server every night from a .NET service running in a container with a 1 GB memory limit. How would you design it?

1. Why naive approaches fail

File.ReadAllLines(20 GB)  →  ~20 GB of UTF-16 strings = ~40 GB + array → OOMKilled
EF Core Add + SaveChanges →  change tracking per entity, one INSERT per row → hours

2. Back-pressure with bounded channels

var channel = Channel.CreateBounded<Row[]>(new BoundedChannelOptions(4)
{
    FullMode = BoundedChannelFullMode.Wait,
    SingleReader = false,
    SingleWriter = true
});

When the database is slow, the channel fills and WriteAsync waits, so the reader pauses instead of buffering gigabytes. Memory is roughly capacity × batch size × row size.

3. Parsing without allocating per field

A CSV row of 20 fields as string.Split creates 21 strings. For 100 million rows that is 2 billion allocations and heavy GC. Options:

• A parser that exposes fields as ReadOnlySpan<char> and parses numbers and dates directly from spans.
• PipeReader over the file, find newlines in ReadOnlySequence<byte>, parse UTF-8 with Utf8Parser / int.TryParse(ReadOnlySpan<byte>).
• Only materialise strings for text columns you store.

CSV is not "split on commas": quoted fields can contain commas and newlines, so use a real parser.

4. Parallelism options

5. Idempotency and restart

• Store the file's name, size and hash before starting; skip if already loaded.
• Checkpoint progress in the same transaction as each batch, or make batches idempotent with a batch id column.
• Byte offsets need care: StreamReader reads ahead in large blocks, so FileStream.Position is not the position of the current line. Either track offsets yourself (count the UTF-8 bytes of each line plus its newline, or use a PipeReader where you see exact positions), or checkpoint the line number and skip that many lines on restart (simpler, slower to resume).
• With several writers, batches can commit out of order. Only advance the checkpoint to the highest batch where all earlier batches have committed.
• The final swap is one transaction, so a half-finished run never becomes visible.

6. SQL Server specifics

• SqlBulkCopy with SqlBulkCopyOptions.TableLock into an empty heap staging table enables minimal logging in the simple or bulk-logged recovery model.
• Load into staging with no non-clustered indexes, then build the indexes on staging after the load. ALTER TABLE ... SWITCH requires staging and target to have the same columns, indexes, constraints and filegroup, so the swap is metadata-only and instant.
• If matching the schema for SWITCH is not practical, use a set-based MERGE or INSERT ... SELECT in batches instead.
• EnableStreaming = true matters when the source is a streaming IDataReader, so rows flow through without buffering.
• BatchSize 10,000–50,000 is a good start; measure.

7. Try it yourself

1. Generate a 2 GB CSV with a script.
2. Implement version 1 with File.ReadAllLines and watch memory in dotnet-counters.
3. Implement version 2 with a streaming parser, bounded channel and SqlBulkCopy against a local SQL Server container.
4. Kill the process halfway, restart, and confirm it resumes from the checkpoint without duplicates.

public sealed class NightlyImport(string connectionString, ILogger<NightlyImport> log)
{
    private const int BatchSize = 20_000;

    public async Task RunAsync(string path, long startOffset, CancellationToken ct)
    {
        var channel = Channel.CreateBounded<List<OrderRow>>(new BoundedChannelOptions(4)
        {
            FullMode = BoundedChannelFullMode.Wait,
            SingleWriter = true
        });

        var writers = Enumerable.Range(0, 3)
            .Select(_ => WriteBatchesAsync(channel.Reader, ct))
            .ToArray();

        await ReadFileAsync(path, startOffset, channel.Writer, ct);
        await Task.WhenAll(writers);
    }

    private async Task ReadFileAsync(string path, long startOffset,
        ChannelWriter<List<OrderRow>> output, CancellationToken ct)
    {
        try
        {
            await using var fs = new FileStream(path, new FileStreamOptions
            {
                Mode = FileMode.Open, Access = FileAccess.Read, Share = FileShare.Read,
                BufferSize = 1024 * 1024,
                Options = FileOptions.SequentialScan | FileOptions.Asynchronous
            });
            fs.Seek(startOffset, SeekOrigin.Begin);
            using var reader = new StreamReader(fs, Encoding.UTF8);

            var batch = new List<OrderRow>(BatchSize);
            long lineNo = 0;
            while (await reader.ReadLineAsync(ct) is { } line)
            {
                lineNo++;
                if (OrderRow.TryParse(line, out var row)) batch.Add(row);
                else log.LogWarning("Rejected line {Line}", lineNo); // also write to a reject file

                if (batch.Count == BatchSize)
                {
                    await output.WriteAsync(batch, ct);   // waits when writers fall behind
                    batch = new List<OrderRow>(BatchSize);
                }
            }
            if (batch.Count > 0) await output.WriteAsync(batch, ct);
        }
        finally
        {
            output.Complete();
        }
    }

    private async Task WriteBatchesAsync(ChannelReader<List<OrderRow>> input, CancellationToken ct)
    {
        await foreach (var batch in input.ReadAllAsync(ct))
        {
            await using var conn = new SqlConnection(connectionString);
            await conn.OpenAsync(ct);
            using var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, null)
            {
                DestinationTableName = "staging.Orders",
                BatchSize = batch.Count,
                EnableStreaming = true,
                BulkCopyTimeout = 0
            };
            await bulk.WriteToServerAsync(OrderRow.ToDataReader(batch), ct);
            // record checkpoint for this batch here
        }
    }
}

-- Final step, one transaction: swap staging into place
BEGIN TRAN;
  TRUNCATE TABLE dbo.Orders_Old;
  ALTER TABLE dbo.Orders SWITCH TO dbo.Orders_Old;
  ALTER TABLE staging.Orders SWITCH TO dbo.Orders;
COMMIT;