Exporting Teradata Data to Microsoft OneLake with FastBCP

FastBCP exports one table at a time from a source database to files, in parallel, and can stream those files straight to cloud object storage without staging them on local disk first. This post takes a single Teradata table, the 4.7-million-row CARRIER_CLAIMS from the CMS SynPUF dataset, and writes it as Parquet directly into a Microsoft Fabric OneLake lakehouse, with four readers running concurrently.

The two runs below differ only in how FastBCP splits the table across those readers. Of its partitioning methods, RangeId and Ntile both work against any source and need only a single distribution column. We run the same export each way and compare the file layout and the timings. The setup is small on purpose: one table, a degree of parallelism of four, and a single-node Vantage Express VM, so the timings describe the methods rather than a production cluster.

Teradata Vantage Express and the SynPUF data

The source is a Teradata Vantage Express 20.00.28.81 appliance in VirtualBox, given four vCPUs and 8 GB of RAM on the host and reached over an emulated host-only network adapter at 192.168.56.101:1025. The full VM and dataset setup is covered in an earlier post, Exporting Data from Teradata to Amazon Redshift with LakeXpress; the same SYNPUF database is reused here. Vantage Express is a single-AMP system, so there is no intra-table parallelism on the Teradata side, and four concurrent readers all draw from the one AMP.

We export a single table, SYNPUF.CARRIER_CLAIMS: 4,741,335 rows across 142 columns, the widest table in the dataset. Written as one Parquet file it is about 245 MB. Its CLM_ID column is a unique 15-digit claim identifier stored as CHAR(15), and it is what both methods distribute on:

     rows      columns  min(CLM_ID)      max(CLM_ID)
---------  -----------  ---------------  ---------------
4,741,335          142  887013384714946  887993389571976

With four readers on a single-AMP instance, spool is the constraint: raising the user's spool space avoids No more spool space errors under concurrency, as described in the earlier post.

Authenticating to OneLake

OneLake is addressed with the onelake:// scheme in the output directory: onelake://<workspace>/<lakehouse>/<path>/. FastBCP writes the files under the lakehouse's Files area. Authentication goes through the standard Azure identity chain, so a service principal is supplied through three environment variables:

export AZURE_TENANT_ID="<tenant-id>"
export AZURE_CLIENT_ID="<application-id>"
export AZURE_CLIENT_SECRET="<client-secret>"

The service principal needs the Admin role on the target Fabric workspace. One more setting helps throughput: FASTBCP_CLOUD_ISBUFFERED=True buffers each partition to a local temp file at disk speed, then uploads it in parallel blocks, the way azcopy does. It is optional but strongly recommended for OneLake: without it FastBCP streams each partition straight to the lakehouse with small flushes, which the endpoint handles slowly. On this table it was the difference between the RangeId export finishing in about 50 seconds and taking several times longer. We set it for every run below.

Parallel export with RangeId

RangeId reads only the minimum and maximum of the distribution column, then hands each reader an equal-width slice of that range. It suits a numeric, reasonably dense key: here CLM_ID holds only digits and its values are spread evenly across the range, so the four slices come out close to equal.

export FASTBCP_CLOUD_ISBUFFERED=True

FastBCP \
  -C teradata \
  -S 192.168.56.101:1025 \
  -U dbc \
  -X '***' \
  -I SYNPUF \
  -s SYNPUF \
  -T CARRIER_CLAIMS \
  -m RangeId \
  -c CLM_ID \
  -p 4 \
  -D onelake://AnalyticsWorkspace/lxpress/carrier_claims/rangeid/ \
  --fileoutput "carrier_claims.parquet"

The flags, in the order they appear (the full list is in the CLI reference):

• -C teradata: connection type, the native Teradata .NET provider.
• -S, -U, -X: server (host:port), user, password.
• -I SYNPUF: source database.
• -s SYNPUF and -T CARRIER_CLAIMS: source schema and table. A query can be passed with -q instead.
• -m RangeId: the parallelism method. -c CLM_ID is the distribution column, which RangeId expects to be numeric.
• -p 4: degree of parallelism, so four concurrent readers.
• -D onelake://...: the output directory, a OneLake lakehouse path.
• --fileoutput "carrier_claims.parquet": the base file name. The .parquet extension selects the format.

FastBCP writes one file per reader by default, so the table lands as four Parquet files, one per RangeId slice, with near-equal row counts (merging them into a single file is not offered for cloud targets):

1,197,581 rows   63.71 MB   carrier_claims_chunk_000.parquet
1,174,222 rows   62.43 MB   carrier_claims_chunk_001.parquet
1,173,346 rows   62.41 MB   carrier_claims_chunk_002.parquet
1,196,186 rows   63.64 MB   carrier_claims_chunk_003.parquet

Total data rows : 4741335 - Total time : Elapsed=51198 ms - 92,606 rows/s

Parallel export with Ntile

Ntile splits the data into equally-sized buckets using the SQL NTILE window function over the distribution column. It works on any column type, numeric, date or string, and does not require the key to be unique, which makes it the safer default when there is no dense numeric key. The command differs from the previous one only in the method:

FastBCP \
  -C teradata \
  -S 192.168.56.101:1025 \
  -U dbc \
  -X '***' \
  -I SYNPUF \
  -s SYNPUF \
  -T CARRIER_CLAIMS \
  -m Ntile \
  -c CLM_ID \
  -p 4 \
  -D onelake://AnalyticsWorkspace/lxpress/carrier_claims/ntile/ \
  --fileoutput "carrier_claims.parquet"

Ntile divides the rows into four buckets of essentially identical size:

1,185,334 rows   63.06 MB   carrier_claims_chunk_000.parquet
1,185,334 rows   63.06 MB   carrier_claims_chunk_001.parquet
1,185,334 rows   63.06 MB   carrier_claims_chunk_002.parquet
1,185,333 rows   63.06 MB   carrier_claims_chunk_003.parquet

Total data rows : 4741335 - Total time : Elapsed=64934 ms - 73,017 rows/s

Results

For reference, the same table was also exported sequentially, with a single reader writing one file (-m None -p 1). All three runs write to OneLake, Zstd-compressed, with buffered upload on:

Method	DOP	Files	Size on OneLake	Time	Throughput	Speedup
None (sequential)	1	1	245.2 MB	77.2 s	61,400 rows/s	1.00x
Ntile	4	4	252.2 MB	64.9 s	73,017 rows/s	1.19x
RangeId	4	4	252.2 MB	51.2 s	92,606 rows/s	1.51x

Both methods produce well-balanced partitions on this data, Ntile by construction and RangeId because CLM_ID is spread evenly across its range. RangeId is the faster of the two: it only reads the key's minimum and maximum to draw the slice boundaries, whereas Ntile runs an extra ordering pass over the column to compute the bucket edges, which costs additional time on a single-AMP instance.

The speedups are modest, 1.5x for RangeId rather than the 4x that four readers might suggest, and the test rig explains most of that. Vantage Express is single-AMP, so the four readers contend for one engine instead of hitting independent AMPs. It also runs in a VirtualBox VM pinned to four vCPUs and 8 GB of RAM, reading over an emulated network adapter, so the readers share a small, virtualized CPU and I/O budget as well as a single upload link to OneLake. On a multi-AMP Teradata system on real hardware, the same commands scale much further. The four-file output is also slightly larger than the single file, 252 MB against 245 MB, because per-file Parquet structures (footer, dictionaries, row-group metadata) are paid four times instead of once.

Package versions

Teradata        : Vantage Express 20.00.28.81 (VirtualBox)
FastBCP         : 1.1.3

Try FastBCP

Ready to try it on your own data? Start a free FastBCP trial, or learn more on the FastBCP landing page.