@faruk Good question and worth exploring the options.
1. Handling Oracle NUMBER types
If you are using JDBC and unqualified Oracle NUMBER columns are landing as Decimal(38,10), there are two sensible approaches:
Option A: Keep Bronze close to source and standardize in Silver
This is the cleaner medallion pattern. Let Bronze preserve the source representation, then cast based on metadata/business meaning in Silver.
Eg:
SELECT
CAST(id AS BIGINT) AS id,
CAST(amount AS DECIMAL(18,2)) AS amount
FROM bronze.orders;
This keeps ingestion simple and avoids changing source semantics too early.
Option B: Make the casts metadata-driven
For a large number of Oracle tables, this is usually the more scalable approach. Instead of manually maintaining casts for every table, read Oracle metadata from ALL_TAB_COLUMNS / DBA_TAB_COLUMNS, generate the target type mappings, and store only exceptions in a small Databricks control mapping table.
Eg:
source_table | source_column | oracle_type | target_type
----------------------------------------------------------
ORDERS | ORDER_ID | NUMBER | BIGINT
ORDERS | AMOUNT | NUMBER(18,2) | DECIMAL(18,2)
CUSTOMER | STATUS_CODE | NUMBER | INT
Then dynamically generate silver layer as :
exprs = [
f"CAST({col} AS {dtype}) AS {col}"
for col, dtype in mappings.items()
]
silver_df = spark.table("bronze.orders").selectExpr(*exprs)
This avoids maintaining separate notebooks or hand-written casts for hundreds of tables.
One caution: plain Oracle NUMBER does not always mean integer, so don't cast every such column to BIGINT unless the business meaning or actual data confirms it.
Databricks’ medallion guidance supports keeping Bronze minimally transformed and doing type normalization in Silver.
2. Silver layer: tables or views
Again, two practical approaches.
Option A : Materialize reusable cleaned datasets
If multiple downstream systems/pipelines use the same renamed/cast dataset, create a Silver table/streaming table once.
Eg:
CREATE OR REFRESH STREAMING TABLE silver.orders AS
SELECT
CAST(order_id AS BIGINT) AS order_id,
TRIM(customer_name) AS customer_name,
CAST(amount AS DECIMAL(18,2)) AS amount
FROM STREAM(bronze.orders);
This gives you one reusable definition and avoids repeating the same casts everywhere.
Option B: Use views for one-off transformations
If the transformation is lightweight and only used by one downstream object, a view is completely reasonable.
CREATE VIEW silver.orders_view AS
SELECT
CAST(order_id AS BIGINT) AS order_id,
TRIM(customer_name) AS customer_name
FROM bronze.orders;
Databricks documents views, streaming tables and materialized views as different dataset choices for Lakeflow pipelines.
3. Will everything be reprocessed every 4 hours?
That depends on which object type you choose.
Option A: Streaming table for incremental processing
Best when the transformation is row-level and you only want new data processed. So, New rows arrive and streaming table processes only new input and checkpoint/state advances.
Option B: Materialized view
Useful for joins/aggregations. Pipeline can refresh incrementally when the query supports it, but some queries may fall back to re-computation.
Option C: Normal view
Nothing is stored, the cast/rename logic is evaluated every time the view is queried.
For a large Oracle related ingestion pipelines, I would lean toward metadata-driven type mappings + reusable Silver tables where needed, rather than manually maintaining casts table by table.
The good news is that Databricks now provides a managed Lakeflow Connect Oracle integrated CDC pipeline. It is currently in Beta, but it can simplify Oracle ingestion compared with maintaining custom JDBC logic yourself. worth checking here.
Also another community post for reference on Oracle ingestion here
