Loading a Lake Table

Databricks and Snowflake are the only destinations that do two jobs. Every other
endpoint writes a file or a row and is finished. These two land the data as
Parquet files first, and can then load those files into something you can
query.

Those are two separate steps, and the second one is optional. This page is about
choosing it.

The two steps

Landing always happens. The task writes Parquet into a Unity Catalog volume
(Databricks) or an internal stage (Snowflake). The endpoint says where; see
Endpoints.

Ingest is what the task does afterwards, and it is set per task on the
Output tab. One endpoint serves many tasks, each with its own
columns, so this cannot live on the connection: a Databricks workspace is a
place, not a table.

Why per task and not per endpoint

An endpoint is a workspace and a volume. Ten tasks can share it, each writing a
different shape. If the table were part of the connection, all ten would load
into the same table. The table comes from the task instead, from its map name.

Where the rows go

The table is the map's name. The name you set on the
Mapping tab names the table, exactly as it does for a SQL
destination. The endpoint supplies the catalog and schema its files land in, and
a dotted prefix in the name overrides them.

With an endpoint landing in catalog main, schema bronze:

Map name	The rows land in
readings	main.bronze.readings
gold.readings	main.gold.readings
analytics.gold.readings	analytics.gold.readings
readings/dt=2026-09-01	main.bronze.readings

Two separators, two meanings:

• . separates namespace parts. Read right to left: table, then schema, then
  catalog. Anything you leave off comes from the endpoint.
• / starts a partition folder. The part after it is a folder beneath the
  table, never part of the table's name.

The slash means something different here

On a Microsoft SQL Server destination, / separates the schema from the table.
On a lake it does not: a slash is already a folder in object storage, so reading
it as a namespace would put your partitioning inside the table name. If you are
converting a SQL task to a lake one, re-read its name.

The Output tab resolves this for you as you type, so you can see the answer
rather than work it out.

A name must resolve to a full three parts

If no catalog or schema can be found (not in the map name, not on the endpoint),
the run is refused before anything is uploaded. An unqualified name handed to a SQL warehouse resolves against its session
default, which is workspace.default on Databricks, so the rows land somewhere
nobody configured and the run still reports success. Refusing is better than a
green run you cannot find the data from.

The four modes

Set Ingest mode on the Output tab. Snowflake offers three of the four:
View is Databricks only, for a reason covered below.

None: land files and stop

Parquet files are written to the volume or stage. Nothing else runs. No table is
created, no view, no COPY INTO.

Use it when something else already picks the files up: Databricks Auto Loader, a
scheduled notebook, a Snowpipe, a partner ETL tool. Stream is then a delivery
mechanism and the downstream job owns the table.

None hides a later failure

The run reports success the moment the files are uploaded, and the watermark
advances. If the job that was supposed to pick them up is broken, Stream has no
way to know and will keep advancing past data nobody loaded. Whatever consumes
the files needs its own monitoring: Stream's task history cannot stand in for
it.

Example. A task landing sensor readings for an Auto Loader stream:

Ingest mode:  None
Map name:     readings

/Volumes/main/bronze/landing/readings/readings_20260906T0900_1_0.parquet

Auto Loader watches /Volumes/main/bronze/landing/readings/ and does the rest.

View: a view over the files (Databricks only)

Stream creates a view that reads the Parquet files where they lie. Nothing is
copied. The data is queryable as soon as the files land, and the view stays
correct as more arrive, because it reads the folder rather than a snapshot of it.

This is the cheapest option that still gives you a table name to query.

Example.

-- Stream creates this for you
SELECT * FROM main.bronze.readings WHERE dt = '2026-09-06';

What you give up:

• Every read rescans the raw files. Fine for tens of files, slow for tens of
  thousands. A view over a year of five-minute batches is not a fast query.
• No Delta history. No time travel, no OPTIMIZE, no VACUUM, no
  transaction log: those are properties of a Delta table, and this is not one.
• No schema enforcement. The view reflects the files; if a later run writes a
  different shape, the view changes with it.

Why Snowflake has no View mode

It is not an oversight. An external table over landed files needs an external
stage, cloud storage Snowflake reads directly, and Stream uploads to an
internal stage, which it cannot. Copy Into is the only way rows reach a
table on Snowflake, so View is not offered and is refused if a stored task
somehow asks for it.

Copy Into: load a table

Stream runs COPY INTO, loading the landed files into a real table. On
Databricks that is a Delta table; on Snowflake a standard table.

This is the option to reach for by default. Reads are fast, the table has proper
statistics, and both platforms track which files they have already loaded, so
rerunning a task does not double-count rows.

Example.

-- What Stream runs, in effect
COPY INTO main.bronze.readings
  FROM '/Volumes/main/bronze/landing/readings/'
  FILEFORMAT = PARQUET;

A failed ingest fails the run

Unlike None, this is visible. If the load fails, the task run fails: the
files are kept, the watermark does not advance, and the window is retried on
the next run. You lose nothing and you find out.

Custom: your own statement

You write the SQL. Use it when the load needs something the standard statement
does not do: a MERGE instead of an insert, a transformation on the way in, a
WHERE filter, extra options.

The statement is required, and these tokens are substituted before it runs:

Token	Becomes
{table}	The resolved table name
{catalog}	The catalog (or database, on Snowflake)
{schema}	The schema
{path}	The folder the files landed in
{files}	The list of files this run wrote

Example: merge instead of insert, so a re-run corrects rows rather than
duplicating them:

MERGE INTO {catalog}.{schema}.{table} AS t
USING (SELECT * FROM parquet.`{path}`) AS s
  ON t.TimeMsec = s.TimeMsec AND t.tag = s.tag
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

A custom statement fails the run the same way Copy Into does, so a mistake in
it holds the watermark rather than silently skipping data.

Which to choose

	None	View	Copy Into	Custom
Available on	Both	Databricks	Both	Both
Copies data	No	No	Yes	Yes
Queryable as a table	No	Yes	Yes	Yes
Read speed	-	Slow at scale	Fast	Fast
Delta history	No	No	Yes	Yes
Failure is visible	No	Yes	Yes	Yes
Needs a SQL warehouse	No	Yes	Yes	Yes

Start with Copy Into. Move to View if you want the data queryable without
the storage cost of a copy and the volumes are modest. Use None only when you
have already built the thing that consumes the files. Use Custom when you
need MERGE or a transformation.

Create table if missing

A checkbox beside the mode, on by default for a new task, and meaningful only for
Copy Into and Custom. View builds its own view and None writes no
table, so it is hidden for those.

With it on, Stream issues CREATE TABLE IF NOT EXISTS from the schema the run is
actually writing, before the first file is uploaded.

This matters more than it sounds. A materialized table can carry hundreds of
columns, and COPY INTO matches them by name. Hand-write that DDL and get one
column name wrong and the load does not fail: it succeeds, and that column
arrives full of nulls. Letting Stream build the table from the same schema it is
writing removes the chance of the two disagreeing.

Three things to know:

• It never alters an existing table. If the table is there, it is used exactly
  as it is. A genuine schema conflict fails at the ingest rather than being
  silently widened.
• The service principal needs CREATE TABLE on the schema. That is why it can
  be turned off: an account often has permission to write files and not to create
  tables.
• Types follow the Parquet, not the map. A column mapped as decimal is
  written to Parquet as a double, so the table says DOUBLE. The table describes
  the files, which is what COPY INTO needs.

Table override

An optional field, below the mode. Leave it empty and the table comes from the map
name, which is what you usually want.

Fill it in when the map name is a pure partitioning expression: a name that
says where a row goes but nothing about what the table is called:

Map name	Table override	Result
dt=2026-09-01	(empty)	A table called dt=2026-09-01, almost certainly wrong
dt=2026-09-01	readings	main.bronze.readings
dt=2026-09-01	gold.readings	main.gold.readings

The Output tab warns you when it spots a name like the first row.

A time token makes one table per period

A map name such as [scope.gmttime.Format=yyyy] resolves to 2026, so it
creates a new table each year, not one table partitioned by year. That is
sometimes exactly what you want. If it is not, put the token after a slash so it
becomes a folder instead: readings/[scope.gmttime.Format=yyyy], or use
Partition folders below.

The table is fixed at a run's first write, so a run that straddles midnight on
New Year's Eve finishes into the table it started in.

Partition folders (Databricks, View or None)

A dropdown offering None, Hourly, Daily, Monthly, Yearly.

Each row's UTC time places its file in a Hive-style folder beneath the table's own
folder:

/Volumes/main/bronze/landing/readings/
    dt=2026-09-01/  readings_20260901T0000_1_0.parquet
    dt=2026-09-02/  readings_20260902T0000_2_0.parquet
    dt=2026-09-03/  readings_20260903T0000_3_0.parquet

The folder names by period are dt=2026-09-06T13 (hourly), dt=2026-09-06
(daily), dt=2026-09 (monthly), dt=2026 (yearly).

Two things this buys you:

• Databricks discovers dt as a partition column on the view and prunes on
  it, so WHERE dt = '2026-09-06' reads one folder instead of all of them. That
  is what makes a view over a large landing area usable.
• The folder becomes your unit of work. Delete dt=2026-09-05/, rewind the
  task over that day, and the view is correct again, without touching anything
  else.

Which granularity to choose

Databricks infers the dt column's type from the folder names it finds, and
only one of the four gives you a column you can compare as a date:

Setting	Folder	The view's dt column
Daily	dt=2026-09-06	DATE
Hourly	dt=2026-09-06T13	STRING
Monthly	dt=2026-09	STRING
Yearly	dt=2026	INT

Daily is the one to prefer. It is the only setting that produces a real
DATE, and it is also the natural unit to reprocess: delete one folder, rewind
the task over that day.

Changing this on a task that has already run

Changing the granularity, including turning it off, leaves two folder shapes
under one table folder, dt=2026-09 beside dt=2026-09-06. Spark cannot type one
column two ways, so the view fails to read either until the old folders are
gone.

Remove the files already landed under the table's folder before the next run. The
Output tab warns you when you change the value on a saved task, but nothing can
check the volume for you: the service cannot see what is there until it runs.

Not available with Copy Into

COPY INTO would meet a dt source column the target table does not have. The
equivalent for a loaded table is clustering, CLUSTER BY on Databricks, which
you set on the table, not in Stream.

Where the files land

{volume or stage}/{prefix}/{table}/[{dt=…}/]{name}_{timestamp}_{run}_{seq}.parquet

One folder per table. Two tasks with completely different columns can share
one endpoint and one prefix without interfering, because each writes into its own
folder and each ingest reads only that folder.

The prefix is an optional subdirectory belonging to the task. It is purely
organisational and does not need to be unique: grouping by plant or by source
system is a reasonable use for it.

Worked examples

A queryable landing area, cheaply

Sensor readings you want to explore in a notebook without paying to store them
twice.

Endpoint:          Databricks — main / bronze / landing
Map name:          readings
Ingest mode:       View
Partition folders: Daily

Files land under /Volumes/main/bronze/landing/readings/dt=…/, and
main.bronze.readings is queryable as soon as the first run finishes. A query
filtered on dt reads only the days it needs.

A production table

The same readings, loaded properly for dashboards that query them all day.

Endpoint:          Databricks — main / bronze / landing
Map name:          gold.readings
Ingest mode:       Copy Into
Create table:      on

Note the dotted name: files still land in the bronze landing volume, but the
table is built in main.gold: raw files in one place, curated table in another.

Snowflake

Endpoint:      Snowflake — STREAMDB / STREAMSCHEMA1 / STREAMSTAGE
Map name:      STREAMTABLE2
Ingest mode:   Copy Into
Create table:  on

Files are uploaded to the internal stage with PUT and loaded with COPY INTO
into STREAMDB.STREAMSCHEMA1.STREAMTABLE2.

Uploaded files may not show in Snowsight straight away

Snowsight's Stage Files tab reads the stage's directory table, which is a
separate piece of metadata from the files themselves. Automatic refresh of it for
internal stages is available only for accounts hosted on AWS, not Azure or
Google Cloud, so elsewhere you refresh it yourself with
ALTER STAGE … REFRESH. LIST @stage reads the files directly and always shows
them, and the load itself is unaffected either way.

What's Next

Give the task a schedule so it runs unattended: continue to
Task Scheduler →.

Related

• Endpoints: configuring a Databricks or Snowflake connection
• Task Output: the rest of the Output tab
• Task Mapping: the map name that becomes the table
• Logs: diagnosing an ingest that failed